Re: Function Calling Methods
On Wed, 14 May 2008 10:05:22 -0700, Martin Evens wrote:
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.
Huh. How interesting.
I'm sure it's more complicated than it looks, to be sure, but I was
just trying to provide an 80% solution for simple functions that
return a scalar or a result set.
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.
Yes, this is exactly the sort of code I'm starting to write.
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.
Which 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.
Could the call to callPkgFunc() just return a scalar, instead? I don't
mean can you change all of your code; I just mean, could the method
have been implemented that way?
@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.
Oh, that's interesting. I like that.
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.
Yeah, great!
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;
Is that really the syntax for calling functions and procedures in
Oracle? Sheesh!
The code to do this is fairly straight forward, the complexities lie
in the differences between DBDs and databases.
It seems like it could be handled by the DBDs just setting a few
variables, though, I should think.
Maybe something like this:
sub call_proc {
my $dbh= shift;
my $name = shift;
my $opts = shift;
my $places = $dbh-create_places( [EMAIL PROTECTED], $opts );
return $dbh-selectrow_array(
$dbh-proc_sql( $name, $places, $opts ),
undef,
@_
);
}
sub call_func {
my $dbh= shift;
my $name = shift;
my $opts = shift;
my $places = $dbh-create_places( [EMAIL PROTECTED], $opts );
return $dbh-selectrow_array(
$dbh-func_sql( $name, $places, $opts ),
undef,
@_
);
}
sub create_places {
my ($self, $params, $opts) = @_;
join ', ', ('?') x @$params;
}
sub proc_sql {
my ($self, $proc, $places, $opts) = @_;
CALL $proc( $places );
}
sub func_sql {
my ($self, $func, $places, $opts) = @_;
SELECT $func( $places );
}
package DBD::Oracle;
sub create_places {
my ($self, $params, $opts) = @_;
# XXX Do whatever needs to be done for in/out args here.
join ', ', map { :$_ } ([EMAIL PROTECTED]);
}
sub proc_sql {
my ($self, $proc, $places, $opts) = @_;
die No package unless $opts-{package};
begin $opts-{package}.$proc( $places ); end;
}
sub func_sql {
my ($self, $func, $places, $opts) = @_;
die No package unless $opts-{package};
begin :1 := $opts-{package}.$func( $places ); end;;
}
Note how I've overridden the default driver methods for creating a
placeholder string for function and procedure arguments and SQL with
different ones in the DBD::Oracle driver.
I just banged this out as an example of where we might go, of course;
it's far from complete, and doesn't cover how to handle cursors or
result sets, of course. But just as an idea of the sort of thing I
think just might work.
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.
Sure, for
Re: Function Calling Methods
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
Re: Function Calling Methods
I want to throw my support behind this idea in principle, that principle being that it is important to be able to invoke database stored routines efficiently and easily. In my case, considering that a dominant paradigm of Muldis D is to put all database access code in database stored routines, so having less indirection for implementing the language over existing DBMSs should only be helpful. Also as commented, the solution should support inputing or returning arbitrary configurations of data, eg, multiple subject-to-update parameters, as SQL itself supports. -- Darren Duncan
