All,
I'm running out of ideas trying to get a call to a DB2 stored
procedure to return a result set to the DBI::DB2 interface.
The stored procedure is about as simple as they come:
CREATE PROCEDURE
TEST1 ()
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE C1 CURSOR
WITH RETURN TO CALLER FOR
SELECT ID, NAME
FROM HIRSCH.STAFF;
OPEN C1;
END
The table 'STAFF' is simply a copy of the like-named table supplied in
the DB2 sample database.
After creating the procedure, I'm able to execute it from the command
line processor (CLP) by doing:
db2=> call test1()
whereupon it returns the expected results. So far, so good. The
problem is that from Perl, this just can't be convinced to return
anything at all:
.
.
. (connect to database)
.
# Dynamically prepare call to stored proc
my $sth;
unless ( $sth = $dbh->prepare("CALL TEST1()") ) {
print STDERR "\nPREPARE failed:\n\n";
print STDERR wrap( " ", " ", $dbh->errstr ) . "\n\n";
goto cleanup
}
# Try..
unless ( $sth->execute ) {
print STDERR "\nEXECUTE failed:\n\n";
print STDERR wrap( " ", " ", $dbh->errstr ) . "\n\n";
# Catch..
goto cleanup
}
my $num = $sth->{NUM_OF_FIELDS};
print "num = $num\n";
$sth->dump_results;
$sth->finish;
cleanup:
$dbh->disconnect();
.
.
.
Here's what occurs (lines wrapped by me..).
num = 0
dbih_setup_fbav: invalid number of fields: 0, \
NUM_OF_FIELDS attribute probably not set right at \
/usr/local/lib/perl5/site_perl/5.6.0/i686-linux/DBI.pm line 581.
Database handle destroyed without explicit disconnect.
Am I smoking something or shouldn't this be returning the same result
set as the CLP invocation?
It's hard for me to believe that something so fundamental could be
broken with the DB2 driver.
Steve
