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 8 7713
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
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
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! =-----
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! =-----
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
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
by: Dino L. |
last post by:
How can I run stored procedure (MSSQL) ?
|
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 " ;
>...
|
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...
|
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...
|
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
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
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...
|
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,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
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...
|
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...
| |