Bug found when using a stored procedure with DB2 and DBD::ODBC

Environment:
RHEL 4
perl-5.8.5-24.RHEL4
perl-DBI-1.40-8
perl-DBD-ODBC-1.13-1
unixODBC-2.2.11-3.FC4.1
iSeriesAccess-5.4.0-1.0.i386.rpm

I am trying to call a simple stored procedure on an iSeries / AS400:

#!/usr/bin/perl -w

use strict;

$ENV{PERL_DL_NONLAZY}=1;

use DBD::ODBC;

my( $dbh ) = DBI->connect('dbi:ODBC:iseries01', 'user', 'password');
my( $sth ) = $dbh->prepare( "{call CODELIB.ODBC(?, ?, ?)}");
my( $a ) = 1;
my( $b ) = 2;
my( $c ) = undef;
$sth->bind_param(1, \$a );
$sth->bind_param(2, \$b );
$sth->bind_param_inout(3, \$c, 1 );
$sth->execute();

I will always get returned:
DBD::ODBC::st execute failed: [unixODBC][IBM][iSeries Access ODBC
Driver]Error in assignment. (SQL-22018)(DBD: st_execute/SQLExecute
err=-1) at ./odbc.pl line 26.

I opened up a support request with IBM...after sending them my traces,
they have determined that there is a bug in the perl code:

"The values for Col Def & Scale in the SQLBindparameter do not match
with the parameters descriptions returned in the prepare "

Is there someone who can fix this bug so that perl will be able to
call an iSeries stored procedure ?


-Thanks
Stephen More

Re: Bug found when using a stored procedure with DB2 and DBD::ODBC

Stephen More wrote:
> Environment:
> RHEL 4
> perl-5.8.5-24.RHEL4
> perl-DBI-1.40-8
> perl-DBD-ODBC-1.13-1
> unixODBC-2.2.11-3.FC4.1
> iSeriesAccess-5.4.0-1.0.i386.rpm
>
> I am trying to call a simple stored procedure on an iSeries / AS400:
>
> #!/usr/bin/perl -w
>
> use strict;
>
> $ENV{PERL_DL_NONLAZY}=1;
>
> use DBD::ODBC;
>
> my( $dbh ) = DBI->connect('dbi:ODBC:iseries01', 'user', 'password');
> my( $sth ) = $dbh->prepare( "{call CODELIB.ODBC(?, ?, ?)}");
> my( $a ) = 1;
> my( $b ) = 2;
> my( $c ) = undef;
> $sth->bind_param(1, \$a );
> $sth->bind_param(2, \$b );
> $sth->bind_param_inout(3, \$c, 1 );
> $sth->execute();
>
> I will always get returned:
> DBD::ODBC::st execute failed: [unixODBC][IBM][iSeries Access ODBC
> Driver]Error in assignment. (SQL-22018)(DBD: st_execute/SQLExecute
> err=-1) at ./odbc.pl line 26.
>
> I opened up a support request with IBM...after sending them my traces,
> they have determined that there is a bug in the perl code:
>
> "The values for Col Def & Scale in the SQLBindparameter do not match
> with the parameters descriptions returned in the prepare "
>
> Is there someone who can fix this bug so that perl will be able to
> call an iSeries stored procedure ?
>
>
> -Thanks
> Stephen More

If you can supply the procedure, and a method for creating it I will see
what I can do - no promises for a resolution though.

Martin

Re: Bug found when using a stored procedure with DB2 and DBD::ODBC

On 28-Sep-2006 Martin J. Evans wrote:
> Stephen More wrote:
>> Environment:
>> RHEL 4
>> perl-5.8.5-24.RHEL4
>> perl-DBI-1.40-8
>> perl-DBD-ODBC-1.13-1
>> unixODBC-2.2.11-3.FC4.1
>> iSeriesAccess-5.4.0-1.0.i386.rpm
>>
>> I am trying to call a simple stored procedure on an iSeries / AS400:
>>
>> #!/usr/bin/perl -w
>>
>> use strict;
>>
>> $ENV{PERL_DL_NONLAZY}=1;
>>
>> use DBD::ODBC;
>>
>> my( $dbh ) = DBI->connect('dbi:ODBC:iseries01', 'user', 'password');
>> my( $sth ) = $dbh->prepare( "{call CODELIB.ODBC(?, ?, ?)}");
>> my( $a ) = 1;
>> my( $b ) = 2;
>> my( $c ) = undef;
>> $sth->bind_param(1, \$a );
>> $sth->bind_param(2, \$b );
>> $sth->bind_param_inout(3, \$c, 1 );
>> $sth->execute();
>>
>> I will always get returned:
>> DBD::ODBC::st execute failed: [unixODBC][IBM][iSeries Access ODBC
>> Driver]Error in assignment. (SQL-22018)(DBD: st_execute/SQLExecute
>> err=-1) at ./odbc.pl line 26.
>>
>> I opened up a support request with IBM...after sending them my traces,
>> they have determined that there is a bug in the perl code:
>>
>> "The values for Col Def & Scale in the SQLBindparameter do not match
>> with the parameters descriptions returned in the prepare "
>>
>> Is there someone who can fix this bug so that perl will be able to
>> call an iSeries stored procedure ?
>>
>>
>> -Thanks
>> Stephen More
>
> If you can supply the procedure, and a method for creating it I will see
> what I can do - no promises for a resolution though.
>
> Martin

For anyone following this thread Stephen gave me the procedure offline which
was:

CREATE PROCEDURE ODBC( IN A INTEGER,
IN B INTEGER,
OUT C INTEGER )
LANGUAGE SQL
BEGIN
SET C = A + B;
END

Before I went to get the DB2 ODBC driver to try this out I gave it a quick try
with DBD::DB2 and gues what - same error. It was then I realised an error in
the code.

Stephen. I would try correcting your code which calls bind_param incorrectly:

$sth->bind_param(1, \$a ); -> $sth->bind_param(1, $a );
$sth->bind_param(2, \$b ); -> $sth->bind_param(2, $b );

I'm surprised IBM did not spot this.

BTW
what is with all those "my( $a ) = 1;" statements? You don't need the ().
and why do "$ENV{PERL_DL_NONLAZY}=1;"?

If this still does not work let me know.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com

Re: Bug found when using a stored procedure with DB2 and DBD::ODBC

On 9/29/06, Martin J. Evans wrote:
> Stephen. I would try correcting your code which calls bind_param incorrectly:
>
> $sth->bind_param(1, \$a ); -> $sth->bind_param(1, $a );
> $sth->bind_param(2, \$b ); -> $sth->bind_param(2, $b );

I changed my code with:
$sth->bind_param(1, $a );
$sth->bind_param(2, $b );

But I am still getting "DBD::ODBC::st execute failed:
[unixODBC][IBM][iSeries Access ODBC Driver]Error in assignment"

> BTW
> what is with all those "my( $a ) = 1;" statements? You don't need the ().

I know they are not needed...This was a company coding standard that
was put in place and I have been following it ever since :-)

> and why do "$ENV{PERL_DL_NONLAZY}=1;"?
This is left over from a previous problem...actually you told me to try it.
In May of 2005 I was getting a seg fault:
http://www.codecomments.com/archive237-2005-5-498915.html


-Thanks for you assistance
Steve More

Re: Bug found when using a stored procedure with DB2 and DBD::ODBC

On 29-Sep-2006 Stephen More wrote:
> On 9/29/06, Martin J. Evans wrote:
>> Stephen. I would try correcting your code which calls bind_param
>> incorrectly:
>>
>> $sth->bind_param(1, \$a ); -> $sth->bind_param(1, $a );
>> $sth->bind_param(2, \$b ); -> $sth->bind_param(2, $b );
>
> I changed my code with:
> $sth->bind_param(1, $a );
> $sth->bind_param(2, $b );
>
> But I am still getting "DBD::ODBC::st execute failed:
> [unixODBC][IBM][iSeries Access ODBC Driver]Error in assignment"

OK, I'll try and get hold of a DB2 ODBC driver and see what I can find.

>> BTW
>> what is with all those "my( $a ) = 1;" statements? You don't need the ().
>
> I know they are not needed...This was a company coding standard that
> was put in place and I have been following it ever since :-)
>
>> and why do "$ENV{PERL_DL_NONLAZY}=1;"?
> This is left over from a previous problem...actually you told me to try it.
> In May of 2005 I was getting a seg fault:
> http://www.codecomments.com/archive237-2005-5-498915.html

:-) That was a method to see if your unixODBC had been built right - the
solution if PERL_DL_NONLAZY fixed it was to rebuild unixODBC. However, that is
up to you.

>
> -Thanks for you assistance
> Steve More

Do you know where I can get the DB2 ODBC driver for Linux. I have not got a rpm
based system but I could probably unpack the rpm and manually install it.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com

Re: Bug found when using a stored procedure with DB2 and DBD::ODBC

On 9/29/06, Martin J. Evans wrote:
> Do you know where I can get the DB2 ODBC driver for Linux. I have not got a rpm
> based system but I could probably unpack the rpm and manually install it.

The rpm can be downloaded from: http://www.ibm.com/eserver/iseries/access/linux/
If you do not have an IBM id, you will need to register and get one.


-Steve More

RE: Bug found when using a stored procedure with DB2 and DBD::ODBC

On 28-Sep-2006 Stephen More wrote:
> Environment:
> RHEL 4
> perl-5.8.5-24.RHEL4
> perl-DBI-1.40-8
> perl-DBD-ODBC-1.13-1
> unixODBC-2.2.11-3.FC4.1
> iSeriesAccess-5.4.0-1.0.i386.rpm
>
> I am trying to call a simple stored procedure on an iSeries / AS400:
>
>#!/usr/bin/perl -w
>
> use strict;
>
> $ENV{PERL_DL_NONLAZY}=1;
>
> use DBD::ODBC;
>
> my( $dbh ) = DBI->connect('dbi:ODBC:iseries01', 'user', 'password');
> my( $sth ) = $dbh->prepare( "{call CODELIB.ODBC(?, ?, ?)}");
> my( $a ) = 1;
> my( $b ) = 2;
> my( $c ) = undef;
> $sth->bind_param(1, \$a );
> $sth->bind_param(2, \$b );
> $sth->bind_param_inout(3, \$c, 1 );
> $sth->execute();
>
> I will always get returned:
> DBD::ODBC::st execute failed: [unixODBC][IBM][iSeries Access ODBC
> Driver]Error in assignment. (SQL-22018)(DBD: st_execute/SQLExecute
> err=-1) at ./odbc.pl line 26.
>
> I opened up a support request with IBM...after sending them my traces,
> they have determined that there is a bug in the perl code:
>
> "The values for Col Def & Scale in the SQLBindparameter do not match
> with the parameters descriptions returned in the prepare "
>
> Is there someone who can fix this bug so that perl will be able to
> call an iSeries stored procedure ?
>
>
> -Thanks
> Stephen More

Stephen set up a server I could access and I've now been able to look into this.

IBM are correct in that the scale is always set to 1 but that is not why it is
failing. The procedure has a 3rd parameter which is output only. It would appear
it is failing because the undef in the 3rd output parameter before the
parameter is called is not set correctly when bound and the iSeries ODBC driver
does not like it. This does seem to differ from what the DB2 ODBC driver for
Linux does (which accepts it ok).

A workaround for now is to set output only arguments to something which is not
undef. Also the above calls to bind_param need to be changed to pass the
parameter by value. I'm attempting to find a permanent fix.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com