By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,017 Members | 979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,017 IT Pros & Developers. It's quick & easy.

Perl and DB2 Stored Procedures

P: n/a
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 not because the many postings that I have
read on calling DB2 stored procedures they have all been done through
DBI::DB2.

I would like to know if this is even possible or not.

However just to test out, I prepared the statement, my $rv = $dbh ->
prepare( 'call dq0gwcat.eposp522(\'p1\',\'p2\',\'p3\',\'p4\',\'20 04-08-05-12.10.23.798322\',\'N\',0,0,0,\'
\',\' \',\'Y\',\' \',115436746,364285835)' ); and then executed it.
The Perl code did not throw any errors, but on querying the database
there was no change to the information. The stored procedure is
supposed to delete a row. All the parameters have a value already
assigned to them in the stored procedure call.

I am not sure that if this is the correct approach or not, any help in
this matter would be greatly helpful.

Thanks in advance,
Samarth
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
sa******@yahoo.com (Samarth) wrote in message news:<bd**************************@posting.google. com>...
Folks,

When it comes to stored procedures, I am not sure whether I can do
this through DBI::ODBC or not because the many postings that I have
read on calling DB2 stored procedures they have all been done through
DBI::DB2.

However just to test out, I prepared the statement, my $rv = $dbh ->
prepare( 'call dq0gwcat.eposp522(\'p1\',\'p2\',\'p3\',\'p4\',\'20 04-08-05-12.10.23.798322\',\'N\',0,0,0,\'
\',\' \',\'Y\',\' \',115436746,364285835)' ); and then executed it.
The Perl code did not throw any errors, but on querying the database
there was no change to the information. The stored procedure is
supposed to delete a row. All the parameters have a value already
assigned to them in the stored procedure call.


Hi:

I just tried running the following (very simple) Perl script that
calls a stored procedure through DBD::ODBC which simply deletes a row
from the EMPLOYEE table of the sample database... it worked as
expected.

#####
use strict;
use warnings;
use DBI;

my $dbh = DBI->connect('dbi:ODBC:sample', '', '');
my $sth = $dbh->prepare(
"call daniels.procedure3(?)"
) or die "Can't prepare CALL: $DBI::errstr";
$sth->bind_param(1, 'jason');
my $rc = $sth->execute();
$rc = $sth->finish();
$dbh->disconnect();
#####

As you can see, I used the bind_param approach because it helps me
separate the CALL statement from the values I'm passing to the CALL
statement--but putting the values explicitly in your prepare statement
will work as well. One suggestion: to avoid having to escape all of
those quotes in your SQL statement, consider using double-quotes or
the q{ } method of escaping strings. For example, you could use
something like:

my $rv = $dbh ->prepare( "call
dq0gwcat.eposp522('p1','p2','p3','p4','2004-08-05-12.10.23.798322','N',0,0,0,'
',' ','Y',' ',115436746,364285835)" );

or:

my $rv = $dbh ->prepare( q{call
dq0gwcat.eposp522('p1','p2','p3','p4','2004-08-05-12.10.23.798322','N',0,0,0,'
',' ','Y',' ',115436746,364285835)} );

I find it hard to read strings that have too many leaning toothpicks.

Some other things to consider:
* does the stored procedure work when you call it directly from the
command line?
* have you turned off auto-commit, and forgotten to commit your
transaction after calling the stored procedure?

Dan
Nov 12 '05 #2

P: n/a
da*******@ca.ibm.com (Dan Scott) wrote in message news:<db**************************@posting.google. com>...
Hi:

I just tried running the following (very simple) Perl script that
calls a stored procedure through DBD::ODBC which simply deletes a row
from the EMPLOYEE table of the sample database... it worked as
expected.

#####
use strict;
use warnings;
use DBI;

my $dbh = DBI->connect('dbi:ODBC:sample', '', '');
my $sth = $dbh->prepare(
"call daniels.procedure3(?)"
) or die "Can't prepare CALL: $DBI::errstr";
$sth->bind_param(1, 'jason');
my $rc = $sth->execute();
$rc = $sth->finish();
$dbh->disconnect();
#####

As you can see, I used the bind_param approach because it helps me
separate the CALL statement from the values I'm passing to the CALL
statement--but putting the values explicitly in your prepare statement
will work as well. One suggestion: to avoid having to escape all of
those quotes in your SQL statement, consider using double-quotes or
the q{ } method of escaping strings. For example, you could use
something like:

my $rv = $dbh ->prepare( "call
dq0gwcat.eposp522('p1','p2','p3','p4','2004-08-05-12.10.23.798322','N',0,0,0,'
',' ','Y',' ',115436746,364285835)" );

or:

my $rv = $dbh ->prepare( q{call
dq0gwcat.eposp522('p1','p2','p3','p4','2004-08-05-12.10.23.798322','N',0,0,0,'
',' ','Y',' ',115436746,364285835)} );

I find it hard to read strings that have too many leaning toothpicks.

Some other things to consider:
* does the stored procedure work when you call it directly from the
command line?
* have you turned off auto-commit, and forgotten to commit your
transaction after calling the stored procedure?

Dan


Hello,

Thanks for your inputs, I did progress a step further in trying to get
this to work. Now atleast there are entries in the web-server error
log, the error log entry is like this, "DBD::ODBC::st execute failed:
[IBM][CLI Driver][DB2] SQL0113N "dq0gwcat" contains a character that
is not allowed or does not contain any characters. SQLSTATE=42602".

This is the exact log entry. I have also attached the actual prepare
command, "my $sth = $dbh -> prepare(q{call
dq0gwcat.eposp522('AVGI00','CIA','WECIACAT','CREWN ET','2004-08-17-11.56.20.948427','N',0,0,0,'
',' ','Y',' ',115436746,364285835)});"

Taking the exact command and putting it in the DB2 Connect Client and
then invoking the stored procedure executes the stored procedure.

I am not sure if the prepare command is adding something to the stored
procedure call to cause the error log message.

Samarth
Nov 12 '05 #3

P: n/a
da*******@ca.ibm.com (Dan Scott) wrote in message news:<db**************************@posting.google. com>...
Hi:

I just tried running the following (very simple) Perl script that
calls a stored procedure through DBD::ODBC which simply deletes a row
from the EMPLOYEE table of the sample database... it worked as
expected.

#####
use strict;
use warnings;
use DBI;

my $dbh = DBI->connect('dbi:ODBC:sample', '', '');
my $sth = $dbh->prepare(
"call daniels.procedure3(?)"
) or die "Can't prepare CALL: $DBI::errstr";
$sth->bind_param(1, 'jason');
my $rc = $sth->execute();
$rc = $sth->finish();
$dbh->disconnect();
#####

As you can see, I used the bind_param approach because it helps me
separate the CALL statement from the values I'm passing to the CALL
statement--but putting the values explicitly in your prepare statement
will work as well. One suggestion: to avoid having to escape all of
those quotes in your SQL statement, consider using double-quotes or
the q{ } method of escaping strings. For example, you could use
something like:

my $rv = $dbh ->prepare( "call
dq0gwcat.eposp522('p1','p2','p3','p4','2004-08-05-12.10.23.798322','N',0,0,0,'
',' ','Y',' ',115436746,364285835)" );

or:

my $rv = $dbh ->prepare( q{call
dq0gwcat.eposp522('p1','p2','p3','p4','2004-08-05-12.10.23.798322','N',0,0,0,'
',' ','Y',' ',115436746,364285835)} );

I find it hard to read strings that have too many leaning toothpicks.

Some other things to consider:
* does the stored procedure work when you call it directly from the
command line?
* have you turned off auto-commit, and forgotten to commit your
transaction after calling the stored procedure?

Dan


Hello,

Thanks for your inputs, I did progress a step further in trying to get
this to work. Now atleast there are entries in the web-server error
log, the error log entry is like this, "DBD::ODBC::st execute failed:
[IBM][CLI Driver][DB2] SQL0113N "dq0gwcat" contains a character that
is not allowed or does not contain any characters. SQLSTATE=42602".

This is the exact log entry. I have also attached the actual prepare
command, "my $sth = $dbh -> prepare(q{call
dq0gwcat.eposp522('AVGI00','CIA','WECIACAT','CREWN ET','2004-08-17-11.56.20.948427','N',0,0,0,'
',' ','Y',' ',115436746,364285835)});"

Taking the exact command and putting it in the DB2 Connect Client and
then invoking the stored procedure executes the stored procedure.

I am not sure if the prepare command is adding something to the stored
procedure call to cause the error log message.

Samarth
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.