471,580 Members | 1,539 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,580 software developers and data experts.

Perl DBI and DB2 Stored Procedure

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
Nov 12 '05 #1
8 7560
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

Nov 12 '05 #2
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

Nov 12 '05 #3
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! =-----
Nov 12 '05 #4
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! =-----
Nov 12 '05 #5
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
Nov 12 '05 #6
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
Nov 12 '05 #7
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
Nov 12 '05 #8
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
Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Wonderinguy | last post: by
3 posts views Thread by Samarth | last post: by
3 posts views Thread by mdaetwyler | last post: by
2 posts views Thread by Dino L. | last post: by
3 posts views Thread by Shiraz | last post: by
1 post views Thread by rajpar | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by lumer26 | last post: by
reply views Thread by Vinnie | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.