473,372 Members | 1,368 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

calling stored procedure from Perl code

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);
END @

Here are my server db2 registry variables:
[i] DB2_SQLROUTINE_COMPILER_PATH=/export/home/db2v7/sqllib/function/
routine/sr_cpath
[i] DB2_SQLROUTINE_COMPILE_COMMAND=gcc -I/export/home/db2v7/sqllib/
include SQLROUTINE_FILENAME.c -G -o SQLROUTINE_FILENAME -L/export/home/
db2v7/sqllib/lib -R/export/home/db2v7/sqllib/lib -ldb2

I issue the create procedure stmt on the client, and this does indeed
create a stored procedure on the server in thef following directory: /
export/home/db2v7/sqllib/function/routine/sqlproc/SAMPLE/DB2USER. I
cp the resulting shared library (P0840200) to /export/home/db2v7/
sqllib/function.

I then invoke the SP from the command line: db2 "call x(3)" .. and
this works because a row is indeed inserted into table t1.

Now here's the problem: I want to call the same SP from perl code:
#!/usr/bin/perl -w

use strict;
use DBI;
use DBD::DB2;

$ENV{'DB2INSTANCE'} = "db2prod";
my $db_connect="dbi:DB2:SAMPLE";
my $user="db2user";
my $passwd="jD8l14";

my $dbh = DBI->connect( $db_connect, "$user", "$passwd" ,
{AutoCommit =0} ) || die "connect failed";

sub Main {
my $rc = $dbh->do("call P0840200!x(36)") || die "call SP
failed";
print STDOUT " result: $rc \n\n";

$dbh->rollback;
$dbh->disconnect;
}
Main;
Unfortunately, this does NOT work .. the following is returned when
executing the perl application:
DBD::DB2::db do failed: [IBM][CLI Driver][DB2/SUN] SQL10010N The
specified library, "P0840200", was loaded, but the function "x" could
not be executed.
call SP failed at x.pl line 18.

Any ideas? The output of the /export/home/db2v7/sqllib/function/
routine/sqlproc/SAMPLE/DB2USER/P0840200.log file is as follows:

*** BIND /export/home/db2v7/sqllib/function/routine/sqlproc/
SAMPLE/DB2USER/tmp/P0840200.sqc ***

LINE MESSAGES FOR P0840200.sqc
------
--------------------------------------------------------------------
SQL0060W The "C" precompiler is in progress.
SQL0091W Precompilation or binding was ended with "0"
errors and "0" warnings.

*** COMPILE /export/home/db2v7/sqllib/function/routine/sqlproc/
SAMPLE/DB2USER/tmp/P0840200.c ***

DB2_SQLROUTINE_COMPILER_PATH=/export/home/db2v7/sqllib/function/
routine/sr_cpath

DB2_SQLROUTINE_COMPILE_COMMAND=gcc -fPIC -I/export/home/db2v7/
sqllib/include SQLROUTINE_FILENAME.c -G -o SQLROUTINE_FILENAME -L/
export/home/db2v7/sqllib/lib -R/export/home/db2v7/sqllib/lib -ldb2

#!/bin/sh
PATH=$PATH:/usr/local/bin/gcc:/usr/local/bin
export PATH

# CC=gcc
# export CC

SQLROUTINE_FILENAME=P0840200
export SQLROUTINE_FILENAME

gcc -fPIC -I/export/home/db2v7/sqllib/include P0840200.c -G -o
P0840200 -L/export/home/db2v7/sqllib/lib -R/export/home/db2v7/sqllib/
lib -ldb2

*** /export/home/db2v7/sqllib/function/routine/sqlproc/SAMPLE/
DB2USER/tmp/P0840200.exp ***
pgsjmp


----------------------------------------------------------------------------
Note the last line in the .log file .. if I instead change the perl
code to CALL P0840200!pgsjmp instead of P0840200!x, then the following
is returned:
DBD::DB2::db do failed: [IBM][CLI Driver][DB2/SUN] SQL1131N DARI
(Stored Procedure) process has been terminated abnormally.
SQLSTATE=38503
call SP failed at x.pl line 15.

Instead of CALL P0840200!x, if I issue P0840200!x or P0840200!XX or
P0840200!abc, I still get the same SQL10010N error:
DBD::DB2::db do failed: [IBM][CLI Driver][DB2/SUN] SQL10010N The
specified library, "P0840200", was loaded, but the function "xx" could
not be executed.
call SP failed at x.pl line 15.

If the shared library is indeed being loaded, why is the function x
not being recognized? Does this have anything to do with .exp?
Shouldn't "x" be there in place of "pgsjmp"? if so, how can this be
changed given that the SP is being created automatically?

Or does this have anything to do with the DBD::DB2 driver I'm using,
and whether it supports the invocation of SPs? I would think so given
that the shared library is being loaded???

Thanks in advance for your help.
Oct 16 '08 #1
1 4995
Ian
ra****@gmail.com wrote:
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);
END @

Here are my server db2 registry variables:
[i] DB2_SQLROUTINE_COMPILER_PATH=/export/home/db2v7/sqllib/function/
routine/sr_cpath
[i] DB2_SQLROUTINE_COMPILE_COMMAND=gcc -I/export/home/db2v7/sqllib/
include SQLROUTINE_FILENAME.c -G -o SQLROUTINE_FILENAME -L/export/home/
db2v7/sqllib/lib -R/export/home/db2v7/sqllib/lib -ldb2

I issue the create procedure stmt on the client, and this does indeed
create a stored procedure on the server in thef following directory: /
export/home/db2v7/sqllib/function/routine/sqlproc/SAMPLE/DB2USER. I
cp the resulting shared library (P0840200) to /export/home/db2v7/
sqllib/function.
You don't need to copy this file. DB2 knows how to resolve where the
file should be.
I then invoke the SP from the command line: db2 "call x(3)" .. and
this works because a row is indeed inserted into table t1.
Good.

Now here's the problem: I want to call the same SP from perl code:

[...]

sub Main {
my $rc = $dbh->do("call P0840200!x(36)") || die "call SP
failed";
Why aren't you just doing

my $rc = $dbh->do("call x(36)") || die "call SP failed";
You don't need to specify the name of the library or anything else.
When DB2 creates the stored procedure, it keeps track of what stored
procedures reference what libraries.

Oct 16 '08 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
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...
2
by: Woody Splawn | last post by:
I am using SQL Server 2000 as the back-end. I have created a stored procedure in SQL server called usp_AddContract. This Stored procedure inserts a new contract into a contracts table. I have...
2
by: singlal | last post by:
Hi, my question was not getting any attention because it moved to 2nd page; so posting it again. Sorry for any inconvenience but I need to get it resolved fast. Need your help! ...
4
by: Jack | last post by:
Hi, I am trying to run an example code from a book. However I am getting the following error message: Number: -2147217900 Description: Syntax error or access violation Source: Microsoft OLE...
3
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 " ; >...
4
by: eighthman11 | last post by:
I'm calling a stored procedure on a sql server from an access application. I just need the stored procedure to run I do not need any data returned from the stored procedure to my Access...
1
by: amgupta8 | last post by:
Note: This problem occurred when I updated the JDK from 1.3.1 to 1.4.1 or 1.4.2. Nothing else was changed in the code, other than updating the JDK on the database server (dbm cfg parm jdk_path) and...
3
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...
6
Soniad
by: Soniad | last post by:
Hello, I am excecuting a stored procedure in my ASP page , it has one out parameter (@confirm) . after executing the procedure i want to retreive this out parameter and assign it to variable...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
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
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

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.