ViewVC Help
View File | Revision Log | Show Annotations | Download File
/cvs/PApp-SQL/SQL.pm
(Generate patch)

Comparing PApp-SQL/SQL.pm (file contents):
Revision 1.15 by root, Sun Apr 22 14:38:27 2001 UTC vs.
Revision 1.22 by root, Sun Apr 7 16:22:56 2002 UTC

1=head1 NAME 1=head1 NAME
2 2
3PApp::SQL - absolutely easy yet fast and powerful sql access 3PApp::SQL - absolutely easy yet fast and powerful sql access.
4 4
5=head1 SYNOPSIS 5=head1 SYNOPSIS
6 6
7 use PApp::SQL; 7 use PApp::SQL;
8 8
… …
13 my $st = sql_insertid 13 my $st = sql_insertid
14 sql_exec "insert into ... values (?, ?)", $v1, $v2; 14 sql_exec "insert into ... values (?, ?)", $v1, $v2;
15 my $a = sql_fetch "select a from ..."; 15 my $a = sql_fetch "select a from ...";
16 sql_fetch \my($a, $b), "select a,b ..."; 16 sql_fetch \my($a, $b), "select a,b ...";
17 17
18 sql_exists "name from table where name like 'a%'" 18 sql_exists "table where name like 'a%'"
19 or die "a* required but not existent"; 19 or die "a* required but not existent";
20 20
21 my $db = new PApp::SQL::Database "", "DBI:mysql:test", "user", "pass"; 21 my $db = new PApp::SQL::Database "", "DBI:mysql:test", "user", "pass";
22 local $PApp::SQL::DBH = $db->checked_dbh; # does 'ping' 22 local $PApp::SQL::DBH = $db->checked_dbh; # does 'ping'
23 23
… …
44use DBI (); 44use DBI ();
45 45
46BEGIN { 46BEGIN {
47 use base qw(Exporter DynaLoader); 47 use base qw(Exporter DynaLoader);
48 48
49 $VERSION = 0.123; 49 $VERSION = 0.13;
50 @EXPORT = qw( 50 @EXPORT = qw(
51 sql_exec sql_fetch sql_fetchall sql_exists sql_insertid $sql_exec 51 sql_exec sql_fetch sql_fetchall sql_exists sql_insertid $sql_exec
52 sql_ufetch sql_ufetchall sql_uexists 52 sql_uexec sql_ufetch sql_ufetchall sql_uexists
53 ); 53 );
54 @EXPORT_OK = qw( 54 @EXPORT_OK = qw(
55 connect_cached 55 connect_cached
56 ); 56 );
57 57
… …
69=over 4 69=over 4
70 70
71=item $sql_exec 71=item $sql_exec
72 72
73Since the C<sql_exec> family of functions return a statement handle there 73Since the C<sql_exec> family of functions return a statement handle there
74must eb another way to test the return value of the C<execute> call. This 74must be another way to test the return value of the C<execute> call. This
75global variable contains the result of the most recent call to C<execute> 75global variable contains the result of the most recent call to C<execute>
76done by this module. 76done by this module.
77 77
78=item $PApp::SQL::DBH 78=item $PApp::SQL::DBH
79 79
… …
111__LINE__ work fine as well). 111__LINE__ work fine as well).
112 112
113The reason C<$id> is necessary is that you might specify special connect 113The reason C<$id> is necessary is that you might specify special connect
114arguments or special flags, or you might want to configure your $DBH 114arguments or special flags, or you might want to configure your $DBH
115differently than maybe other applications requesting the same database 115differently than maybe other applications requesting the same database
116connection. If none of this is becessary for your application you can 116connection. If none of this is necessary for your application you can
117leave $id empty (i.e. ""). 117leave C<$id> empty (i.e. "").
118 118
119If specified, C<$connect> is a callback (e.g. a coderef) that will be 119If specified, C<$connect> is a callback (e.g. a coderef) that will be
120called each time a new connection is being established, with the new 120called each time a new connection is being established, with the new
121C<$dbh> as first argument. 121C<$dbh> as first argument.
122 122
123Examples: 123Examples:
124 124
125 # try your luck opening the papp database without access info 125 # try your luck opening the papp database without access info
126 $dbh = connect_cached __FILE__, "DBI:mysql:papp"; 126 $dbh = connect_cached __FILE__, "DBI:mysql:papp";
127
128Mysql-specific behaviour: The default setting of
129C<mysql_client_found_rows> is TRUE, you can overwrite this, though.
127 130
128=cut 131=cut
129 132
130sub connect_cached { 133sub connect_cached {
131 my ($id, $dsn, $user, $pass, $flags, $connect) = @_; 134 my ($id, $dsn, $user, $pass, $flags, $connect) = @_;
132 # the following line is duplicated in PApp::SQL::Database::new 135 # the following line is duplicated in PApp::SQL::Database::new
133 $id = "$id\0$dsn\0$user\0$pass"; 136 $id = "$id\0$dsn\0$user\0$pass";
134 unless ($dbcache{$id} && $dbcache{$id}->ping) { 137 unless ($dbcache{$id} && $dbcache{$id}->ping) {
135 #warn "connecting to ($dsn|$user|$pass|$flags)\n";#d#
136 # first, nuke our statement cache (sooory ;) 138 # first, nuke our statement cache (sooory ;)
137 cachesize cachesize 0; 139 cachesize cachesize 0;
140
141 # then make mysql behave more standardly by default
142 $dsn =~ /^[Dd][Bb][Ii]:mysql:/
143 and $dsn !~ /;mysql_client_found_rows/
144 and $dsn .= ";mysql_client_found_rows=1";
145
138 # then connect anew 146 # then connect anew
139 $dbcache{$id} = 147 $dbcache{$id} =
140 eval { DBI->connect($dsn, $user, $pass, $flags) } 148 eval { DBI->connect($dsn, $user, $pass, $flags) }
141 || eval { DBI->connect($dsn, $user, $pass, $flags) } 149 || eval { DBI->connect($dsn, $user, $pass, $flags) }
142 || die "unable to connect to database $dsn: $DBI::errstr\n"; 150 || die "unable to connect to database $dsn: $DBI::errstr\n";
… …
212 my($name, $amount) = sql_fetch "select ...", args... 220 my($name, $amount) = sql_fetch "select ...", args...
213 221
214... and it's still quite fast unless you fetch large amounts of data. 222... and it's still quite fast unless you fetch large amounts of data.
215 223
216C<sql_ufetch> is similar to C<sql_fetch> but upgrades all input values to 224C<sql_ufetch> is similar to C<sql_fetch> but upgrades all input values to
217utf8 and forces all result values to utf8. 225utf8 and forces all result values to utf8 (this does I<not> include result
226parameters, only return values. Using bind variables in cinjunction with
227sql_u* functions results in undefined behaviour).
218 228
219=item sql_fetchall <see sql_exec> 229=item sql_fetchall <see sql_exec>
220 230
221=item sql_ufetchall <see sql_uexec> 231=item sql_ufetchall <see sql_uexec>
222 232
… …
239 for (sql_fetchall "select name, age, place from user") { 249 for (sql_fetchall "select name, age, place from user") {
240 my ($name, $age, $place) = @$_; 250 my ($name, $age, $place) = @$_;
241 } 251 }
242 252
243C<sql_ufetchall> is similar to C<sql_fetchall> but upgrades all input 253C<sql_ufetchall> is similar to C<sql_fetchall> but upgrades all input
244values to utf8 and forces all result values to utf8. 254values to utf8 and forces all result values to utf8 (see the caveats in
255the description of C<sql_ufetch>, though).
245 256
246=item sql_exists "<table> where ...", args... 257=item sql_exists "<table_references> where <where_condition>...", args...
247 258
248=item sql_uexists <see sql_exists> 259=item sql_uexists <see sql_exists>
249 260
250Check wether the result of the sql-statement "select xxx from 261Check wether the result of the sql-statement "select xxx from
251$first_argument" would be empty or not (that is, imagine the string 262$first_argument" would be empty or not (that is, imagine the string

Diff Legend

– Removed lines
+ Added lines
< Changed lines
> Changed lines