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.42 by root, Sat Oct 7 15:54:35 2017 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.001';
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.
… …
306Except for sybase, this does not require a server access. 309Except for sybase, this does not require a server access.
307 310
308=cut 311=cut
309 312
310sub sql_insertid($) { 313sub sql_insertid($) {
311 my $sth = shift or die "sql_insertid requires a statement handle"; 314 my $sth = shift or Carp::croak "sql_insertid requires a statement handle";
312 my $dbh = $sth->{Database}; 315 my $dbh = $sth->{Database};
313 my $driver = $dbh->{Driver}{Name}; 316 my $driver = $dbh->{Driver}{Name};
314 317
315 $driver eq "mysql" and return $sth->{mysql_insertid}; 318 $driver eq "mysql" and return $sth->{mysql_insertid};
316 $driver eq "Pg" and return $sth->{pg_oid_status}; 319 $driver eq "Pg" and return $sth->{pg_oid_status};
317 $driver eq "Sybase" and return sql_fetch($dbh, 'SELECT @@IDENTITY'); 320 $driver eq "Sybase" and return sql_fetch ($dbh, 'SELECT @@IDENTITY');
318 $driver eq "Informix" and return $sth->{ix_sqlerrd}[1]; 321 $driver eq "Informix" and return $sth->{ix_sqlerrd}[1];
319 $driver eq "SQLite" and return sql_fetch($dbh, 'SELECT last_insert_rowid ()'); 322 $driver eq "SQLite" and return sql_fetch ($dbh, 'SELECT last_insert_rowid ()');
320 323
321 die "sql_insertid does not spport the dbd driver '$driver', please see PApp::SQL::sql_insertid"; 324 Carp::croak "sql_insertid does not support the dbd driver '$driver', at";
322} 325}
323 326
324=item [old-size] = cachesize [new-size] 327=item [old-size] = cachesize [new-size]
325 328
326Returns (and possibly changes) the LRU cache size used by C<sql_exec>. The 329Returns (and possibly changes) the LRU cache size used by C<sql_exec>. The
… …
358 361
359=cut 362=cut
360 363
361reinitialize; 364reinitialize;
362 365
366=head2 Type Deduction
367
368Since every database driver seems to deduce parameter types differently,
369usually wrongly, and at leats in the case of DBD::mysql, different in
370every other release or so, and this can and does lead to data corruption,
371this module does type deduction itself.
372
373What does it mean? Simple - sql parameters for placeholders will be
374explicitly marked as SQL_VARCHAR, SQL_INTEGER or SQL_DOUBLE the first time
375a statement is prepared.
376
377To force a specific type, you can either continue to use e.g. sql casts,
378or you can make sure to consistently use strings or numbers. To make a
379perl scalar look enough like a string or a number, use this when passing
380it to sql_exec or a similar functions:
381
382 "$string" # to pass a string
383 $num+0 # to pass a number
384
385=cut
386
363package PApp::SQL::Database; 387package PApp::SQL::Database;
364 388
365=head2 The Database Class 389=head2 The Database Class
366 390
367Again (sigh) the problem of persistency. What do you do when you have 391Again (sigh) the problem of persistency. What do you do when you have

Diff Legend

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