/ 
PApp-0.05
River stage zero No dependents
/ PApp::SQL

NAME

PApp::SQL - absolutely easy yet fast and powerful sql access

SYNOPSIS

use PApp::SQL;
# to be written

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.

$dbh = connect_cached $id, $dsn, $user, $pass, $flags, $connect

(not exported by by default)

Connect to the database given by ($dsn,$user,$pass), while using the flags from $flags. These are just the same arguments as given to DBI-connect>.

The database handle will be cached under the unique id $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.

If specified, $connect is a callback (e.g. a coderef) that will be called each time a new connection is being established, with the new $dbh as first argument.

$sth = sql_exec [dbh,] [bind-vals...,] "sql-statement", [arguments...]

sql_exec is the most important and most-used function in this module.

Runs the given sql command with the given parameters and returns the statement handle. The command and the statement handle will be cached (with the database handle and the sql string as key), so prepare will be called only once for each distinct sql call (please keep in mind that the returned statement will always be the same, so, if you call sql_exec with the same dbh and sql-statement twice (e.g. in a subroutine you called), the statement handle for the first call mustn't be used.

The database handle (the first argument) is optional. If it is missing, sql_exec first tries to use the variable $DBH in the current (= calling) package and, if that fails, it tries to use database handle in $PApp::SQL::DBH, which you can set before calling these functions.

The actual return value from the $sth-execute> call is stored in the package-global (and exported) variable $sql_exec.

If any error occurs sql_exec will throw an exception.

Examples:

# easy one
my $st = sql_exec "select name, id from table where id = ?", $id;
while (my ($name, $id) = $st->fetchrow_array) { ... };

# the fastest way to use dbi, using bind_columns
my $st = sql_exec \my($name, $id),
                  "select name, id from table where id = ?",
                  $id;
while ($st->fetch) { ...}

# now use a different dastabase:
sql_exec $dbh, "update file set name = ?", "oops.txt";
sql_fetch <see sql_exec>

Execute a sql-statement and fetch the first row of results. Depending on the caller context the row will be returned as a list (array context), or just the first columns. In table form:

CONTEXT	RESULT
void		()
scalar		first column
list		array

sql_fetch is quite efficient in conjunction with bind variables: #FIXME#NOT YET#

sql_fetch \my($name, $amount),
          "select name, amount from table where id name  = ?",
          "Toytest";

But of course the normal way to call it is simply:

my($name, $amount) = sql_fetch "select ...", args...

... and it's still fast enough unless you fetch large amounts of data.

sql_fetchall <see sql_exec>

Similarly to sql_fetch, but all result rows will be fetched (this is of course inefficient for large results!). The context is ignored (only list context makes sense), but the result still depends on the number of columns in the result:

COLUMNS	RESULT
0		()
1		(row1, row2, row3...)
many		([row1], [row2], [row3]...)

Examples (all of which are inefficient):

for (sql_fetchall "select id from table") { ... }

my @names = sql_fetchall "select name from user";

for (sql_fetchall "select name, age, place from user") {
   my ($name, $age, $place) = @$_;
}
sql_exists "<table> where ...", args...

Check wether the result of the sql-statement "select xxx from $first_argument" would be empty or not (that is, imagine the string "select from" were prepended to your statement (it isn't)). Should work with every database but can be quite slow, except on mysql, where this should be quite fast.

Examples:

print "user 7 exists!\n"
   if sql_exists "user where id = ?", 7;

die "duplicate key"
   if sql_exists "user where name = ? and pass = ?", "stefan", "geheim";
[old-size] = cachesize [new-size]

Returns (and possibly changes) the LRU cache size used by sql_exec. The default is somewhere around 50 (= the 50 last recently used statements will be cached). It shouldn't be too large, since a simple linear listed is used for the cache at the moment (which, for small (<100) cache sizes is actually quite fast).

The function always returns the cache size in effect before the call, so, to nuke the cache (for example, when a database connection has died or you want to garbage collect old database/statement handles), this construct can be used:

PApp::SQL::cachesize PApp::SQL::cachesize 0;

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).

SEE ALSO

PApp.

AUTHOR

Marc Lehmann <pcg@goof.com>
http://www.goof.com/pcg/marc/