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.36 by root, Fri Mar 3 14:12:22 2006 UTC vs.
Revision 1.43 by root, Mon Mar 4 06:25:32 2019 UTC

… …
8 8
9 my $st = sql_exec $DBH, "select ... where a = ?", $a; 9 my $st = sql_exec $DBH, "select ... where a = ?", $a;
10 10
11 local $DBH = <database handle>; 11 local $DBH = <database handle>;
12 my $st = sql_exec \my($bind_a, $bind_b), "select a,b ..."; 12 my $st = sql_exec \my($bind_a, $bind_b), "select a,b ...";
13 my $st = sql_insertid 13 my $id = 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 "table where name like 'a%'" 18 sql_exists "table where name like 'a%'"
… …
25 25
26=head1 DESCRIPTION 26=head1 DESCRIPTION
27 27
28This module provides you with easy-to-use functions to execute sql 28This module provides you with easy-to-use functions to execute sql
29commands (using DBI). Despite being easy to use, they are also quite 29commands (using DBI). Despite being easy to use, they are also quite
30efficient and allow you to write faster programs in less lines of code. It 30efficient and allow you to write faster programs in fewer lines of
31should work with anything from perl-5.004_01 onwards, but I only support 31code. It should work with anything from perl-5.004_01 onwards, but I only
325.005+. UTF8 handling (the C<sql_u*> family of functions) will only be 32support 5.005+. UTF8 handling (the C<sql_u*> family of functions) will
33effective with perl version 5.006 and beyond. 33only be effective with perl version 5.006 and beyond.
34 34
35If the descriptions here seem terse or if you always wanted to know 35If the descriptions here seem terse or if you always wanted to know
36what PApp is then have a look at the PApp module which uses this module 36what PApp is then have a look at the PApp module which uses this module
37extensively but also provides you with a lot more gimmicks to play around 37extensively but also provides you with a lot more gimmicks to play around
38with to help you create cool applications ;) 38with to help you create cool applications ;)
39 39
40=cut 40=cut
41 41
42package PApp::SQL; 42package PApp::SQL;
43 43
44use Carp ();
44use DBI (); 45use DBI ();
45 46
46BEGIN { 47BEGIN {
47 use base qw(Exporter DynaLoader); 48 use base qw(Exporter DynaLoader);
48 49
49 $VERSION = '1.02'; 50 $VERSION = '2.002';
50 @EXPORT = qw( 51 @EXPORT = qw(
51 sql_exec sql_fetch sql_fetchall sql_exists sql_insertid $sql_exec 52 sql_exec sql_fetch sql_fetchall sql_exists sql_insertid $sql_exec
52 sql_uexec sql_ufetch sql_ufetchall sql_uexists 53 sql_uexec sql_ufetch sql_ufetchall sql_uexists
53 ); 54 );
54 @EXPORT_OK = qw( 55 @EXPORT_OK = qw(
… …
56 ); 57 );
57 58
58 bootstrap PApp::SQL $VERSION; 59 bootstrap PApp::SQL $VERSION;
59} 60}
60 61
62boot2 DBI::SQL_VARCHAR, DBI::SQL_INTEGER, DBI::SQL_DOUBLE;
63
61our $sql_exec; # last result of sql_exec's execute call 64our $sql_exec; # last result of sql_exec's execute call
62our $DBH; # the default database handle 65our $DBH; # the default database handle
63our $Database; # the current SQL::Database object, if applicable 66our $Database; # the current SQL::Database object, if applicable
64 67
65our %dbcache; 68our %dbcache;
… …
98 101
99(not exported by by default) 102(not exported by by default)
100 103
101Connect to the database given by C<($dsn,$user,$pass)>, while using the 104Connect to the database given by C<($dsn,$user,$pass)>, while using the
102flags from C<$flags>. These are just the same arguments as given to 105flags from C<$flags>. These are just the same arguments as given to
103C<DBI->connect>. 106C<< DBI->connect >>.
104 107
105The database handle will be cached under the unique id 108The database handle will be cached under the unique id
106C<$id|$dsn|$user|$pass>. If the same id is requested later, the 109C<$id|$dsn|$user|$pass>. If the same id is requested later, the
107cached handle will be checked (using ping), and the connection will 110cached handle will be checked (using ping), and the connection will
108be re-established if necessary (be sure to prefix your application or 111be re-established if necessary (be sure to prefix your application or
… …
144 147
145 # then connect anew 148 # then connect anew
146 $dbcache{$id} = 149 $dbcache{$id} =
147 eval { DBI->connect($dsn, $user, $pass, $flags) } 150 eval { DBI->connect($dsn, $user, $pass, $flags) }
148 || eval { DBI->connect($dsn, $user, $pass, $flags) } 151 || eval { DBI->connect($dsn, $user, $pass, $flags) }
149 || die "unable to connect to database $dsn: $DBI::errstr\n"; 152 || Carp::croak "unable to connect to database $dsn: $DBI::errstr\n";
150 $connect->($dbcache{$id}) if $connect; 153 $connect->($dbcache{$id}) if $connect;
151 } 154 }
152 $dbcache{$id}; 155 $dbcache{$id};
153} 156}
154 157
… …
180database handle in C<$PApp::SQL::DBH>, which you can set before calling 183database handle in C<$PApp::SQL::DBH>, which you can set before calling
181these functions. 184these functions.
182 185
183=end comment 186=end comment
184 187
185The actual return value from the C<$sth->execute> call is stored in the 188The actual return value from the C<< $sth->execute >> call is stored in
186package-global (and exported) variable C<$sql_exec>. 189the package-global (and exported) variable C<$sql_exec>.
187 190
188If any error occurs C<sql_exec> will throw an exception. 191If any error occurs C<sql_exec> will throw an exception.
189 192
190C<sql_uexec> is similar to C<sql_exec> but upgrades all input arguments to 193C<sql_uexec> is similar to C<sql_exec> but upgrades all input arguments to
191UTF-8 before calling the C<execute> method. 194UTF-8 before calling the C<execute> method.
… …
295Returns the last automatically created key value. It must be executed 298Returns the last automatically created key value. It must be executed
296directly after executing the insert statement that created it. This is 299directly after executing the insert statement that created it. This is
297what is actually returned for various databases. If your database is 300what is actually returned for various databases. If your database is
298missing, please send me an e-mail on how to implement this ;) 301missing, please send me an e-mail on how to implement this ;)
299 302
303 mariadb: first C<AUTO_INCREMENT> column set to NULL
300 mysql: first C<AUTO_INCREMENT> column set to NULL 304 mysql: first C<AUTO_INCREMENT> column set to NULL
301 postgres: C<oid> column (is there a way to get the last SERIAL?) 305 postgres: C<oid> column (is there a way to get the last SERIAL?)
302 sybase: C<IDENTITY> column of the last insert (slow) 306 sybase: C<IDENTITY> column of the last insert (slow)
303 informix: C<SERIAL> or C<SERIAL8> column of the last insert 307 informix: C<SERIAL> or C<SERIAL8> column of the last insert
304 sqlite: C<last_insert_rowid()> 308 sqlite: C<last_insert_rowid()>
… …
306Except for sybase, this does not require a server access. 310Except for sybase, this does not require a server access.
307 311
308=cut 312=cut
309 313
310sub sql_insertid($) { 314sub sql_insertid($) {
311 my $sth = shift or die "sql_insertid requires a statement handle"; 315 my $sth = shift or Carp::croak "sql_insertid requires a statement handle";
312 my $dbh = $sth->{Database}; 316 my $dbh = $sth->{Database};
313 my $driver = $dbh->{Driver}{Name}; 317 my $driver = $dbh->{Driver}{Name};
314 318
319 $driver eq "MariaDB" and return $sth->{mariadb_insertid};
315 $driver eq "mysql" and return $sth->{mysql_insertid}; 320 $driver eq "mysql" and return $sth->{mysql_insertid};
316 $driver eq "Pg" and return $sth->{pg_oid_status}; 321 $driver eq "Pg" and return $sth->{pg_oid_status};
317 $driver eq "Sybase" and return sql_fetch($dbh, 'SELECT @@IDENTITY'); 322 $driver eq "Sybase" and return sql_fetch ($dbh, 'SELECT @@IDENTITY');
318 $driver eq "Informix" and return $sth->{ix_sqlerrd}[1]; 323 $driver eq "Informix" and return $sth->{ix_sqlerrd}[1];
319 $driver eq "SQLite" and return sql_fetch($dbh, 'SELECT last_insert_rowid ()'); 324 $driver eq "SQLite" and return sql_fetch ($dbh, 'SELECT last_insert_rowid ()');
320 325
321 die "sql_insertid does not spport the dbd driver '$driver', please see PApp::SQL::sql_insertid"; 326 $dbh->last_insert_id (undef, undef, undef, undef)
322} 327}
323 328
324=item [old-size] = cachesize [new-size] 329=item [old-size] = cachesize [new-size]
325 330
326Returns (and possibly changes) the LRU cache size used by C<sql_exec>. The 331Returns (and possibly changes) the LRU cache size used by C<sql_exec>. The
… …
358 363
359=cut 364=cut
360 365
361reinitialize; 366reinitialize;
362 367
368=head2 Type Deduction
369
370Since every database driver seems to deduce parameter types differently,
371usually wrongly, and at leats in the case of DBD::mysql, different in
372every other release or so, and this can and does lead to data corruption,
373this module does type deduction itself.
374
375What does it mean? Simple - sql parameters for placeholders will be
376explicitly marked as SQL_VARCHAR, SQL_INTEGER or SQL_DOUBLE the first time
377a statement is prepared.
378
379To force a specific type, you can either continue to use e.g. sql casts,
380or you can make sure to consistently use strings or numbers. To make a
381perl scalar look enough like a string or a number, use this when passing
382it to sql_exec or a similar functions:
383
384 "$string" # to pass a string
385 $num+0 # to pass a number
386
387=cut
388
363package PApp::SQL::Database; 389package PApp::SQL::Database;
364 390
365=head2 The Database Class 391=head2 The Database Class
366 392
367Again (sigh) the problem of persistency. What do you do when you have 393Again (sigh) the problem of persistency. What do you do when you have

Diff Legend

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