Perl DBI and DB2 Stored Procedure

[Wonderinguy]

Hi everybody , I have been trying to execute a simple DB2 stored
Procedure from perl. But it doesn't work. Could anybody please help me
find out why this is happening :

here is my perl script that execute the SP :
<snip>
my $dbh = DBI->connect( "dbiB2:$database","user1","passwd1") || die
"cannot connect to db2";
my $callstmt = "CALL SPACESP('DB','TEXAS')";
my $sth = $dbh->prepare($callstmt) || die "can't do
prepare",$dbh->errstr(),"\n";
$sth->execute || die "Can't do executed:" , $dbh->errstr(),"\n";

<snip>

When I execute this I get this on the apache error.log :

Premature end of script headers: get_dbspace.cgi, referer:
http://localhost/space.html
[Fri May 07 15:14:56 2004] [error] [client 127.0.0.1] Use of
uninitialized value in warn at C:/Program Files/Apache
Group/Apache2/cgi-bin/space/get_dbspace.cgi line 8., referer:
http://localhost/space.html
[Fri May 07 15:14:56 2004] [error] [client 127.0.0.1] Warning:
something's wrong at C:/Program Files/Apache
Group/Apache2/cgi-bin/space/get_dbspace.cgi line 8., referer:
http://localhost/space.html
[Fri May 07 15:14:56 2004] [error] [client 127.0.0.1] DBD:B2::st
execute failed: [IBM][CLI Driver][DB2] SQL0440N No function by the
name "SPACESP" having compatible arguments was found in the function
path. SQLSTATE=42884
, referer: http://localhost/space.html
[Fri May 07 15:14:56 2004] [error] [client 127.0.0.1] Can't do
executed:[IBM][CLI Driver][DB2] SQL0440N No function by the name
"SPACESP" having compatible arguments was found in the function path.
SQLSTATE=42884
, referer: http://localhost/space.html
[Fri May 07 15:14:56 2004] [error] [client 127.0.0.1] , referer:
http://localhost/space.html
[Fri May 07 15:14:56 2004] [error] [client 127.0.0.1] Database handle
destroyed without explicit disconnect., referer:
http://localhost/space.html



I have tested the stored procedure manually. I can execute the stored
procedure from a command line or a third party tool.

Any input is greatly appreciated

Thanks

 

[Kevin Collins]

Hi everybody , I have been trying to execute a simple DB2 stored
Procedure from perl. But it doesn't work. Could anybody please help me
find out why this is happening :
-snip-

I have tested the stored procedure manually. I can execute the stored
procedure from a command line or a third party tool.

Any input is greatly appreciated

See 'perldoc DBI':

"func"
$h->func(@func_arguments, $func_name);

The "func" method can be used to call private non-standard and
non-portable methods implemented by the driver. Note that the
function name is given as the last argument.

This method is not directly related to calling stored procedures.
Calling stored procedures is currently not defined by the DBI.
Some drivers, such as DBD::Oracle, support it in non-portable
ways. See driver documentation for more details.

Even though you are not attempting to use func(), this tells me that stored
procedures are not necessarily supported. Maybe 'perldoc DBD:B2' can shed
light for you on whether or not DB2 does in some 'non-portable' way.

Also:

"This is the DBI specification that corresponds to the DBI version 1.21"

Quite possible that my DBI is older than yours...

Kevin

 

[Steven N. Hirsch]

Hi everybody , I have been trying to execute a simple DB2 stored
Procedure from perl. But it doesn't work. Could anybody please help me
find out why this is happening :

here is my perl script that execute the SP :
<snip>
my $dbh = DBI->connect( "dbiB2:$database","user1","passwd1") || die
"cannot connect to db2";
my $callstmt = "CALL SPACESP('DB','TEXAS')";
my $sth = $dbh->prepare($callstmt) || die "can't do
prepare",$dbh->errstr(),"\n";
$sth->execute || die "Can't do executed:" , $dbh->errstr(),"\n";

DB2 needs some help in order to properly dispatch the call. Try
something like:

CALL SPACESP( CAST( ? AS VARCHAR(n)), CAST( ? AS VARCHAR(n)) )

for the prepared statement and bind your input to the placeholders:

$sth->bind_param( 1, 'DB', $attrib_char );
$sth->bind_param( 2, 'TEXAS', $attrib_char );

You will need to replace 'n' in the CAST expressions with a length
appropriate to what the actual stored-procedure is expecting. For all I
know, it may want CHAR rather than VARCHAR, so dig into it a bit.

BTW, this drove me nuts the first time I ran into it! The CLP
(command-line processor) is much smarter about call dispatching than a
prepared statement.

Steve

 

[Knut Stolze]

Hi everybody , I have been trying to execute a simple DB2 stored
Procedure from perl. But it doesn't work. Could anybody please help me
find out why this is happening :

here is my perl script that execute the SP :
<snip>
my $dbh = DBI->connect( "dbiB2:$database","user1","passwd1") || die
"cannot connect to db2";
my $callstmt = "CALL SPACESP('DB','TEXAS')";
my $sth = $dbh->prepare($callstmt) || die "can't do
prepare",$dbh->errstr(),"\n";
$sth->execute || die "Can't do executed:" , $dbh->errstr(),"\n";

<snip>

When I execute this I get this on the apache error.log :

Premature end of script headers: get_dbspace.cgi, referer:
http://localhost/space.html
[Fri May 07 15:14:56 2004] [error] [client 127.0.0.1] Use of
uninitialized value in warn at C:/Program Files/Apache
Group/Apache2/cgi-bin/space/get_dbspace.cgi line 8., referer:
http://localhost/space.html
[Fri May 07 15:14:56 2004] [error] [client 127.0.0.1] Warning:
something's wrong at C:/Program Files/Apache
Group/Apache2/cgi-bin/space/get_dbspace.cgi line 8., referer:
http://localhost/space.html
[Fri May 07 15:14:56 2004] [error] [client 127.0.0.1] DBD:B2::st
execute failed: [IBM][CLI Driver][DB2] SQL0440N No function by the
name "SPACESP" having compatible arguments was found in the function
path. SQLSTATE=42884

You should also verify that the schema name is the same when you execute on
the command line and inside the script. Better yet: always use the fully
qualified name of the procedure, i.e:

CALL user1.spacesp(...)