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.7 by root, Mon Jan 15 00:19:55 2001 UTC vs.
Revision 1.10 by root, Mon Feb 5 14:05:08 2001 UTC

… …
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 # to be written 8
9 my $st = sql_exec $DBH, "select ... where a = ?", $a;
10
11 local $DBH = <database handle>;
12 my $st = sql_exec \my($bind_a, $bind_b), "select a,b ...";
13 my $st = sql_insertid
14 sql_exec "insert into ... values (?, ?)", $v1, $v2;
15 my $a = sql_fetch "select a from ...";
16 sql_fetch \my($a, $b), "select a,b ...";
17
18 sql_exists "name from table where name like 'a%'"
19 or die "a* required but not existent";
20
21 my $db = new PApp::SQL::Database "", "DBI:mysql:test", "user", "pass";
22 local $PApp::SQL::DBH = $db->checked_dbh; # does 'ping'
23
24 sql_exec $db->dbh, "select ...";
9 25
10=head1 DESCRIPTION 26=head1 DESCRIPTION
11 27
12This module provides you with easy-to-use functions to execute sql 28This module provides you with easy-to-use functions to execute sql
13commands (using DBI). Despite being easy to use, they are also quite 29commands (using DBI). Despite being easy to use, they are also quite
14efficient and allow you to write faster programs in less lines of code. 30efficient and allow you to write faster programs in less lines of code. It
31should work with anything from perl-5.004_01 onwards, but I only support
325.005+.
15 33
16=over 4 34If the descriptions here seem terse or if you always wanted to know
35what PApp is then have a look at the PApp module which uses this module
36extensively but also provides you with a lot more gimmicks to play around
37with to help you create cool applications ;)
17 38
18=cut 39=cut
19 40
20package PApp::SQL; 41package PApp::SQL;
21 42
22use DBI; 43use DBI ();
23
24#use PApp::Exception; # not yet used
25 44
26BEGIN { 45BEGIN {
27 use base Exporter; 46 use base qw(Exporter DynaLoader);
28 47
29 $VERSION = 0.11; 48 $VERSION = 0.12;
30 @EXPORT = qw( 49 @EXPORT = qw(
31 sql_exec sql_fetch sql_fetchall sql_exists sql_insertid $sql_exec 50 sql_exec sql_fetch sql_fetchall sql_exists sql_insertid $sql_exec
32 ); 51 );
33 @EXPORT_OK = qw( 52 @EXPORT_OK = qw(
34 connect_cached 53 connect_cached
35 ); 54 );
36 55
37 require XSLoader; 56 bootstrap PApp::SQL $VERSION;
38 XSLoader::load PApp::SQL, $VERSION;
39} 57}
40 58
41our $sql_exec; # last result of sql_exec's execute call 59our $sql_exec; # last result of sql_exec's execute call
42our $DBH; # the default database handle 60our $DBH; # the default database handle
43our $database; # the current SQL::Database object, if applicable 61our $Database; # the current SQL::Database object, if applicable
44 62
45our %dbcache; 63our %dbcache;
64
65=head2 GLOBAL VARIABLES
66
67=over 4
68
69=item $sql_exec
70
71Since the C<sql_exec> family of functions return a statement handle there
72must eb another way to test the return value of the C<execute> call. This
73global variable contains the result of the most recent call to C<execute>
74done by this module.
75
76=item $PApp::SQL::DBH
77
78The default database handle used by this module if no C<$DBH> was
79specified as argument and no C<$DBH> is found in the current package. See
80C<sql_exec> for a discussion.
81
82=item $PApp::SQL::Database
83
84The current default C<PApp::SQL::Database>-object. Future versions might
85automatically fall back on this database and create database handles from
86it if neccessary. At the moment this is not used by this module but might
87be nice as a placeholder for the database object that corresponds to
88$PApp::SQL::DBH.
89
90=back
91
92=head2 FUNCTIONS
93
94=over 4
46 95
47=item $dbh = connect_cached $id, $dsn, $user, $pass, $flags, $connect 96=item $dbh = connect_cached $id, $dsn, $user, $pass, $flags, $connect
48 97
49(not exported by by default) 98(not exported by by default)
50 99
… …
195 244
196=cut 245=cut
197 246
198=item $lastid = sql_insertid $sth 247=item $lastid = sql_insertid $sth
199 248
200Returns the last automatically created key value (e.g. for mysql 249Returns the last automatically created key value. It must be executed
201AUTO_INCREMENT or sybase IDENTITY fields). It must be executed directly
202after executing the insert statement that created it. 250directly after executing the insert statement that created it. This is
251what is actually returned for various databases. If your database is
252missing, please send me an e-mail on how to implement this ;)
253
254 mysql: first C<AUTO_INCREMENT> column set to NULL
255 postgres: C<oid> column (is there a way to get the last SERIAL?)
256 sybase: C<IDENTITY> column of the last insert (slow)
257 informix: C<SERIAL> or C<SERIAL8> column of the last insert
258
259Except for sybase, this does not require a server access.
203 260
204=cut 261=cut
205 262
206sub sql_insertid($) { 263sub sql_insertid($) {
207 my $sth = shift or die "sql_insertid requires a statement handle"; 264 my $sth = shift or die "sql_insertid requires a statement handle";
208 my $dbh = $sth->{Database}; 265 my $dbh = $sth->{Database};
209 my $driver = $dbh->{Driver}{Name}; 266 my $driver = $dbh->{Driver}{Name};
210 267
211 $driver eq "mysql" and return $sth->{mysql_insertid}; 268 $driver eq "mysql" and return $sth->{mysql_insertid};
269 $driver eq "Pg" and return $sth->{pg_oid_status};
212 $driver eq "Sybase" and return sql_fetch($dbh, 'SELECT @@IDENTITY'); 270 $driver eq "Sybase" and return sql_fetch($dbh, 'SELECT @@IDENTITY');
213 $driver eq "Informix" and return $sth->{ix_sqlerrd}[1]; 271 $driver eq "Informix" and return $sth->{ix_sqlerrd}[1];
214 272
215 die "sql_insertid does not spport the dbd driver '$driver', please see PApp::SQL::sql_insertid"; 273 die "sql_insertid does not spport the dbd driver '$driver', please see PApp::SQL::sql_insertid";
216} 274}
217 275
… …
232 290
233=cut 291=cut
234 292
235=item reinitialize [not exported] 293=item reinitialize [not exported]
236 294
237Clears any internal caches (statement cache, database handle cache). 295Clears any internal caches (statement cache, database handle
296cache). Should be called after C<fork> and other accidents that invalidate
297database handles.
238 298
239=cut 299=cut
240 300
241sub reinitialize { 301sub reinitialize {
242 cachesize cachesize 0; 302 cachesize cachesize 0;
… …
310 370
311=cut 371=cut
312 372
313sub dsn($) { 373sub dsn($) {
314 my $self = shift; 374 my $self = shift;
315 $self->[1][1]; 375 (split /\x00/, $self->[0])[1];
316} 376}
317 377
318=back 378=back
319 379
320=cut 380=cut
321 381
3221; 3821;
323
324=head1 BUGS
325
326As of this writing, sql_fetch and sql_fetchall are not very well tested
327(they were just re-written in C).
328
329sql_exists could be faster (it is written very ugly to not change the
330current package).
331 383
332=head1 SEE ALSO 384=head1 SEE ALSO
333 385
334L<PApp>. 386L<PApp>.
335 387

Diff Legend

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