--- PApp-SQL/SQL.pm 2000/10/21 19:00:53 1.1 +++ PApp-SQL/SQL.pm 2001/02/10 01:28:26 1.11 @@ -5,28 +5,47 @@ =head1 SYNOPSIS use PApp::SQL; - # to be written + + my $st = sql_exec $DBH, "select ... where a = ?", $a; + + local $DBH = ; + my $st = sql_exec \my($bind_a, $bind_b), "select a,b ..."; + my $st = sql_insertid + sql_exec "insert into ... values (?, ?)", $v1, $v2; + my $a = sql_fetch "select a from ..."; + sql_fetch \my($a, $b), "select a,b ..."; + + sql_exists "name from table where name like 'a%'" + or die "a* required but not existent"; + + my $db = new PApp::SQL::Database "", "DBI:mysql:test", "user", "pass"; + local $PApp::SQL::DBH = $db->checked_dbh; # does 'ping' + + sql_exec $db->dbh, "select ..."; =head1 DESCRIPTION This module provides you with easy-to-use functions to execute sql commands (using DBI). Despite being easy to use, they are also quite -efficient and allow you to write faster programs in less lines of code. - -=over 4 +efficient and allow you to write faster programs in less lines of code. It +should work with anything from perl-5.004_01 onwards, but I only support +5.005+. + +If the descriptions here seem terse or if you always wanted to know +what PApp is then have a look at the PApp module which uses this module +extensively but also provides you with a lot more gimmicks to play around +with to help you create cool applications ;) =cut package PApp::SQL; -use DBI; - -#use PApp::Exception; # not yet used +use DBI (); BEGIN { - use base Exporter; + use base qw(Exporter DynaLoader); - $VERSION = 0.1; + $VERSION = 0.12; @EXPORT = qw( sql_exec sql_fetch sql_fetchall sql_exists sql_insertid $sql_exec ); @@ -34,16 +53,46 @@ connect_cached ); - require XSLoader; - XSLoader::load PApp::SQL, $VERSION; + bootstrap PApp::SQL $VERSION; } our $sql_exec; # last result of sql_exec's execute call our $DBH; # the default database handle -our $database; # the current SQL::Database object, if applicable +our $Database; # the current SQL::Database object, if applicable our %dbcache; +=head2 GLOBAL VARIABLES + +=over 4 + +=item $sql_exec + +Since the C family of functions return a statement handle there +must eb another way to test the return value of the C call. This +global variable contains the result of the most recent call to C +done by this module. + +=item $PApp::SQL::DBH + +The default database handle used by this module if no C<$DBH> was +specified as argument and no C<$DBH> is found in the current package. See +C for a discussion. + +=item $PApp::SQL::Database + +The current default C-object. Future versions might +automatically fall back on this database and create database handles from +it if neccessary. At the moment this is not used by this module but might +be nice as a placeholder for the database object that corresponds to +$PApp::SQL::DBH. + +=back + +=head2 FUNCTIONS + +=over 4 + =item $dbh = connect_cached $id, $dsn, $user, $pass, $flags, $connect (not exported by by default) @@ -52,11 +101,18 @@ flags from C<$flags>. These are just the same arguments as given to Cconnect>. -The database handle will be cached under the unique id C<$id>. If the same -id is requested later, the cached handle will be checked (using ping), and -the connection will be re-established if necessary (be sure to prefix your -application or module name to the id to make it "more" unique. Things like -__PACKAGE__ . __LINE__ work fine as well). +The database handle will be cached under the unique id +C<$id|$dsn|$user|$pass>. If the same id is requested later, the +cached handle will be checked (using ping), and the connection will +be re-established if necessary (be sure to prefix your application or +module name to the id to make it "more" unique. Things like __PACKAGE__ . +__LINE__ work fine as well). + +The reason C<$id> is necessary is that you might specify special connect +arguments or special flags, or you might want to configure your $DBH +differently than maybe other applications requesting the same database +connection. If none of this is becessary for your application you can +leave $id empty (i.e. ""). If specified, C<$connect> is a callback (e.g. a coderef) that will be called each time a new connection is being established, with the new @@ -75,13 +131,13 @@ $id = "$id\0$dsn\0$user\0$pass"; unless ($dbcache{$id} && $dbcache{$id}->ping) { #warn "connecting to ($dsn|$user|$pass|$flags)\n";#d# - # first, nuke our cache (sooory ;) + # first, nuke our statement cache (sooory ;) cachesize cachesize 0; # then connect anew $dbcache{$id} = eval { DBI->connect($dsn, $user, $pass, $flags) } || eval { DBI->connect($dsn, $user, $pass, $flags) } - || die $DBI::errstr; + || die "unable to connect to database $dsn: $DBI::errstr\n"; $connect->($dbcache{$id}) if $connect; } $dbcache{$id}; @@ -188,9 +244,33 @@ =cut -# uncodumented, since unportable (only works with DBH even!). yet it is exported (aaargh!) -sub sql_insertid { - $DBH->{mysql_insertid}; +=item $lastid = sql_insertid $sth + +Returns the last automatically created key value. It must be executed +directly after executing the insert statement that created it. This is +what is actually returned for various databases. If your database is +missing, please send me an e-mail on how to implement this ;) + + mysql: first C column set to NULL + postgres: C column (is there a way to get the last SERIAL?) + sybase: C column of the last insert (slow) + informix: C or C column of the last insert + +Except for sybase, this does not require a server access. + +=cut + +sub sql_insertid($) { + my $sth = shift or die "sql_insertid requires a statement handle"; + my $dbh = $sth->{Database}; + my $driver = $dbh->{Driver}{Name}; + + $driver eq "mysql" and return $sth->{mysql_insertid}; + $driver eq "Pg" and return $sth->{pg_oid_status}; + $driver eq "Sybase" and return sql_fetch($dbh, 'SELECT @@IDENTITY'); + $driver eq "Informix" and return $sth->{ix_sqlerrd}[1]; + + die "sql_insertid does not spport the dbd driver '$driver', please see PApp::SQL::sql_insertid"; } =item [old-size] = cachesize [new-size] @@ -212,14 +292,16 @@ =item reinitialize [not exported] -Clears any internal caches (statement cache, database handle cache). +Clears any internal caches (statement cache, database handle +cache). Should be called after C and other accidents that invalidate +database handles. =cut sub reinitialize { cachesize cachesize 0; for (values %dbcache) { - eval { $_->disconnect }; + eval { $_->{InactiveDestroy} = 1 }; } undef %dbcache; } @@ -228,6 +310,8 @@ =cut +reinitialize; + package PApp::SQL::Database; =head2 THE DATABASE CLASS @@ -288,25 +372,15 @@ sub dsn($) { my $self = shift; - $self->[1][1]; + (split /\x00/, $self->[0])[1]; } =back =cut -reinitialize; - 1; -=head1 BUGS - -As of this writing, sql_fetch and sql_fetchall are not very well tested -(they were just re-written in C). - -sql_exists could be faster (it is written very ugly to not change the -current package). - =head1 SEE ALSO L.