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 databases that don't support outbound parameters. But that
just means that you don't pass in a reference to a scalar as a param
when you're using those databases, no?
Thanks,
David
