David E. Wheeler wrote:
Howdy dbi-devers,
More and more lately, I'm writing database functions in PL/pgSQL (in
PostgreSQL) or SQL (in MySQL and others) to do the heavy lifting of
interacting with database tables. I've been thinking that I'd really
love a DBI method to call these functions without having to do the usual
prepare / execute / fetch drill. Even using do() or fetchrow_array()
seems a bit silly in this context:
my ($source_id) = $dbh->fetchrow_array(
'SELECT get_source_id(?)',
undef,
$source,
);
What I'd love is a couple of DBI methods to do this for me. I recognize
that this is currently not defined by the DBI, but I'm wondering whether
it might be time. I've no idea whether JDBC implements such an
interface, but I was thinking of something like this for function calls:
sub call {
my $dbh = shift;
my $func = shift;
my $places = join ', ', ('?') x @_;
return $dbh->fetchrow_array(
"SELECT $func( $places )",
undef,
@_
);
}
This would allow me to call a function like so:
my $val = $dbh->call('get_source_id', $source );
Which is a much nicer syntax. Drivers might have to modify it, of
course; for MySQL, it should use CALL rather than SELECT.
For functions or procedures that happen to return sets or a cursor,
perhaps we could have a separate method that just returns a statement
handle that's ready to be fetched from?
That is slightly more complicated than it looks. DBD::Oracle already
magics a sth into existence for reference cursors but some databases can
return more than one result-set from a procedure - e.g., SQL Server and
the SQLMoreResults call to move to the next one.
sub cursor {
my $dbh = shift;
my $func = shift;
my $places = join ', ', ('?') x @_;
my $sth = $dbh->prepare( "SELECT $func( $places )" );
$sth->execute(@_);
return $sth;
}
Just some ideas. I'm sure that there are more complications than this,
but even if we could just have something that handles simple functions
(think last_insert_id() -- eliminate this special case!), I think it'd
go a long way toward not only simplifying the use of database functions
in the DBI, but also toward encouraging DBI users to actually make more
use of database functions.
Thoughts?
Thanks,
David
I have hundreds of functions and procedures in various packages in
Oracle we use via DBD::Oracle. We have no SQL at all outside database
functions/procedures/packages i.e., our Perl does not know anything at
all about the tables or columns in the database and the only SQL
executed is to prepare/execute procedures and functions. We wrap calls
to functions and procedures like this:
$h->callPkgFunc(\%options, $pkg, $func_name, \$ret, @args);
$h->callPkgProc(\%options, $pkg, $proc_name, @parameters);
$pkg is the package name of synonym for the package.
$func_name and $proc_name are the function or procedure name.
$ret is the return value from a function - which may be a reference
cursor for Oracle.
@args is the list of scalar args for the function.
@parameters is the list of parameters for the procedure and if any is a
reference to a scalar it is assumed to be an output parameter.
There are various %options for whether to die etc and ways of handling
error output.
The wrapper handles creating the SQL, preparing it, binding the
parameters, executing the func/proc and returning the output bound
parameters.
This works well for us. We were using the same wrapper for MySQL and DB2
but have since dropped use of MySQL and DB2. Of course, the innards of
the wrapper were significantly different between DB2, MySQL and Oracle.
For Oracle you end up with:
begin :1 := pkg_name.function_name(:2,:3,:4...); end;
begin pkg_name.proc_name(:1,:2,:3...); end;
The code to do this is fairly straight forward, the complexities lie in
the differences between DBDs and databases.
A call-like method in DBI would save a little programming but for some
DBDs it would be difficult - I'm of course thinking of DBD::ODBC.
Although ODBC defines a {call xxx} syntax what actually happens when you
you use it is very database dependent and I even know of ODBC drivers
that expect you to ignore output bound reference cursors in the
parameter list.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
