Hi,

I have a weird problem with a DB2 driver for Perl and stored procedures. Can anyone help me getting a result set from DB2 processed? The SQL procedure looks the like:

//***************************************
CREATE PROCEDURE getValue (IN myId INT, OUT myValue VARCHAR(50))

LANGUAGE SQL
BEGIN

DECLARE c1 CURSOR FOR
select value
from table
where id = myId;

OPEN c1;
FETCH c1 INTO myValue;
CLOSE c1;

END @
//****************************************

It can be registered and a CLP statement "call getValue(1,?)" works just fine. The error occurs
in the do-while loop used in the perl script.

//****************************************
#!/usr/bin/perl

use DBI;
use DBD:B2::Constants;

my $stmt = $dbh->prepare( "call getValue(?,?)");

$stmt->bind_param( 1, 1);
$stmt->bind_param_inout( 2, \my $value, 50 );

my $rc = $stmt->execute()
or die "Can't execute stmt: $DBI::errstr";

do {
print "$field \n";
} while ($field = $stmt->fetchrow);

warn $DBI::errstr if $DBI::err;

//***************************************

The error message is from the CLI Driver:

[IBM][CLI Driver] CLI0115E Invalid cursor state. SQLSTATE=24000 at getValue.pl line 14.

I checked the functions fetchrow_array() and fetchrow_ref() but with the same result. Does anyone know if DBI driver for DB2 supports stored procedures with output parameters and how
to how to use them?

Every idea is appreciated...

Ciao,
Holger