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.17 by root, Sat Aug 11 02:46:16 2001 UTC vs.
Revision 1.23 by root, Sun Apr 7 16:23: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.124; 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_uexec 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(
… …
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";
… …
155statement handle. The command and the statement handle will be cached 163statement handle. The command and the statement handle will be cached
156(with the database handle and the sql string as key), so prepare will be 164(with the database handle and the sql string as key), so prepare will be
157called only once for each distinct sql call (please keep in mind that the 165called only once for each distinct sql call (please keep in mind that the
158returned statement will always be the same, so, if you call C<sql_exec> 166returned statement will always be the same, so, if you call C<sql_exec>
159with the same dbh and sql-statement twice (e.g. in a subroutine you 167with the same dbh and sql-statement twice (e.g. in a subroutine you
160called), the statement handle for the first call mustn't be used. 168called), the statement handle for the first call mustn't not be in use
169anymore, as the subsequent call will re-use the handle.
161 170
162The database handle (the first argument) is optional. If it is missing, 171The database handle (the first argument) is optional. If it is missing,
163C<sql_exec> first tries to use the variable C<$DBH> in the current (= 172C<sql_exec> first tries to use the variable C<$DBH> in the current (=
164calling) package and, if that fails, it tries to use database handle in 173calling) package and, if that fails, it tries to use database handle in
165C<$PApp::SQL::DBH>, which you can set before calling these functions. 174C<$PApp::SQL::DBH>, which you can set before calling these functions.
… …
212 my($name, $amount) = sql_fetch "select ...", args... 221 my($name, $amount) = sql_fetch "select ...", args...
213 222
214... and it's still quite fast unless you fetch large amounts of data. 223... and it's still quite fast unless you fetch large amounts of data.
215 224
216C<sql_ufetch> is similar to C<sql_fetch> but upgrades all input values to 225C<sql_ufetch> is similar to C<sql_fetch> but upgrades all input values to
217utf8 and forces all result values to utf8. 226utf8 and forces all result values to utf8 (this does I<not> include result
227parameters, only return values. Using bind variables in cinjunction with
228sql_u* functions results in undefined behaviour).
218 229
219=item sql_fetchall <see sql_exec> 230=item sql_fetchall <see sql_exec>
220 231
221=item sql_ufetchall <see sql_uexec> 232=item sql_ufetchall <see sql_uexec>
222 233
… …
239 for (sql_fetchall "select name, age, place from user") { 250 for (sql_fetchall "select name, age, place from user") {
240 my ($name, $age, $place) = @$_; 251 my ($name, $age, $place) = @$_;
241 } 252 }
242 253
243C<sql_ufetchall> is similar to C<sql_fetchall> but upgrades all input 254C<sql_ufetchall> is similar to C<sql_fetchall> but upgrades all input
244values to utf8 and forces all result values to utf8. 255values to utf8 and forces all result values to utf8 (see the caveats in
256the description of C<sql_ufetch>, though).
245 257
246=item sql_exists "<table> where ...", args... 258=item sql_exists "<table_references> where <where_condition>...", args...
247 259
248=item sql_uexists <see sql_exists> 260=item sql_uexists <see sql_exists>
249 261
250Check wether the result of the sql-statement "select xxx from 262Check wether the result of the sql-statement "select xxx from
251$first_argument" would be empty or not (that is, imagine the string 263$first_argument" would be empty or not (that is, imagine the string

Diff Legend

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