473,549 Members | 2,626 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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:$datab ase","user1","p asswd1") || die
"cannot connect to db2";
my $callstmt = "CALL SPACESP('DB','T EXAS')";
my $sth = $dbh->prepare($calls tmt) || 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 7727
In article <69************ *************@p osting.google.c om>, 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_arg uments, $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************ *************@p osting.google.c om>, 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_arg uments, $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('D B'), 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($calls tmt);
$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('D B'), 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($calls tmt);
$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:$datab ase","user1","p asswd1") || die
"cannot connect to db2";
my $callstmt = "CALL SPACESP('DB','T EXAS')";
my $sth = $dbh->prepare($calls tmt) || 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:$datab ase","user1","p asswd1") || die
"cannot connect to db2";
my $callstmt = "CALL SPACESP('DB','T EXAS')";
my $sth = $dbh->prepare($calls tmt) || 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:$datab ase","user1","p asswd1") || die
"cannot connect to db2";
my $callstmt = "CALL SPACESP('DB','T EXAS')";
my $sth = $dbh->prepare($calls tmt) || 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:$datab ase","user1","p asswd1") || die
"cannot connect to db2";
my $callstmt = "CALL SPACESP('DB','T EXAS')";
my $sth = $dbh->prepare($calls tmt) || 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
22105
by: dinesh prasad | last post by:
I'm trying to use a servlet to process a form, then send that data to an SQL server stored procedure. I'm using the WebLogic 8 App. server. I am able to retrieve database information, so I know my application server can talk to the database. I've determined the failure occurs when the the following statement is executed: cstmt.execute(); (due...
0
513
by: Wonderinguy | last post by:
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...
3
4604
by: Samarth | last post by:
Folks, I am calling a DB2 stored procedure through Perl using the DBI:ODBC module. I am not sure if I can do this or not because I have been able to connect to and also issue select statements using DBI:ODBC and also have them run successfully. When it comes to stored procedures, I am not sure whether I can do this through DBI::ODBC or...
3
2103
by: mdaetwyler | last post by:
Hi all I am trying to call a DB/2 v8.2 stored procedure from Perl DBI and am getting an error message telling me, that the routine could not be found in the library path. SQL0444N Routine "*_deposit" (specific name "SQL050519190148810") is implemented with code in library or path "\finban.cac_deposit", function "finban.cac_deposit" which...
2
5441
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
3
4385
by: Shiraz | last post by:
Updated to the latest version of DBD-mysql using perl -MCPAN -e "install DBD-mysql" and now the calling mysql function r2() within perl work > $SQL_Text = "select r2() from dual " ; > $sth=$dbh->prepare($SQL_Text); > $sth->execute(); > while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; } for reference here is the mysql...
3
4161
by: andrewkl | last post by:
hi, I have the following Perl code that inserts a string to an Oracle DB via a stored procedure: #!/usr/local/bin/perl ## Perl v5.8.6 built for sun4-solaris use strict; BEGIN { $ENV{'TNS_ADMIN'} = '/auto/engweb/oracle/sqlnet'; $ENV{'ORACLE_HOME'} = '/usr/packages/dbdoracle/9.2.0'; }
1
5014
by: rajpar | last post by:
Environment: Solaris (client + server) db2 version 7.2 latest fixpak (DB2 v7.1.0.111", "s050516" and "U803330") Compiler: gcc Here is my SP code executed on the client: CREATE PROCEDURE db2user.x (IN value INT) LANGUAGE SQL BEGIN INSERT INTO db2dba.t1 values (value);
0
2596
Saii
by: Saii | last post by:
How can we pass a perl array to stored procedure in Oracle. The parameter I am using in Oracle is type of table of varchar(500). I am using the system command in perl to execute the procedure Sample: system("sqlplus -S $user_det_orcl <<EOF \n "."whenever sqlerror exit failure; \n"."exec p_test('\@array\');\n"."exit;\n"."EOF\n" ); When I...
0
7464
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7734
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7497
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7826
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5385
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3493
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1960
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1074
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
781
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.