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( "dbi:DB2:$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::DB2::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]

In article <69*************************@posting.google.com> , Wonderinguy wrote:

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::DB2' 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

[Kevin Collins]

In article <69*************************@posting.google.com> , Wonderinguy wrote:

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::DB2' 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

[Ian]

Wonderinguy wrote:

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 :
[Fri May 07 15:14:56 2004] [error] [client 127.0.0.1] DBD::DB2::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

The problem is that you're passing VARCHAR datatypes, and your stored
procedure is defined with some other data type (probably CHAR). The
constant string 'DB' is varchar(2)

This is not related to perl, DBI or DBD::DB2.

You have 2 options:

$callstmt = "CALL SPACESP(CHAR('DB'), CHAR('TEXAS'))";

or (better, using parameter markers, since you can re-use the statement
handle if you call the stored proc multiple times):

$p1 = "DB";
$p2 = "TEXAS";

$callstmt = "CALL SPACESP(?, ?)";

$sth = $dbh->prepare($callstmt);
$sth->execute($p1, $p2);


Good luck,

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Ian]

Wonderinguy wrote:

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 :
[Fri May 07 15:14:56 2004] [error] [client 127.0.0.1] DBD::DB2::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

The problem is that you're passing VARCHAR datatypes, and your stored
procedure is defined with some other data type (probably CHAR). The
constant string 'DB' is varchar(2)

This is not related to perl, DBI or DBD::DB2.

You have 2 options:

$callstmt = "CALL SPACESP(CHAR('DB'), CHAR('TEXAS'))";

or (better, using parameter markers, since you can re-use the statement
handle if you call the stored proc multiple times):

$p1 = "DB";
$p2 = "TEXAS";

$callstmt = "CALL SPACESP(?, ?)";

$sth = $dbh->prepare($callstmt);
$sth->execute($p1, $p2);


Good luck,

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Steven N. Hirsch]

Wonderinguy wrote:

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( "dbi:DB2:$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

[Steven N. Hirsch]

Wonderinguy wrote:

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( "dbi:DB2:$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]

Wonderinguy wrote:

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( "dbi:DB2:$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::DB2::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(...)

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Knut Stolze]

Wonderinguy wrote:

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( "dbi:DB2:$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::DB2::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(...)

--
Knut Stolze
Information Integration
IBM Germany / University of Jena