db2 calling stored procedures

General discussion on Zend Server for IBM System i
eaburk
Posts: 82
Joined: Mon Jan 04, 2010 3:42 pm

db2 calling stored procedures

Post by eaburk » Thu Jan 09, 2014 5:36 pm

I have the following code:

Code: Select all

$sql = "call mypgm(?,?)";
$stmt = db2_prepare($db,$sql);

$key='KEY'; //defined in RPG program as Char(10)
$nbr='0'; //defined in RPG program as Decimal(15)

db2_bind_param($stmt,1,"key",DB2_PARAM_INOUT);
db2_bind_param($stmt,2,"nbr",DB2_PARAM_INOUT);

db2_execute($stmt);
echo $nbr;
This code runs perfectly in ZendServer5. Under ZendServer6 however it returns this sql error message:

Code: Select all

Character in CAST argument not valid. SQLCODE=-420
I can't figure out what the problem is. Any ideas?

eaburk
Posts: 82
Joined: Mon Jan 04, 2010 3:42 pm

Re: db2 calling stored procedures

Post by eaburk » Thu Jan 23, 2014 1:50 pm

Am I the only one experiencing this issue? No one has any ideas?

aseiden
Posts: 875
Joined: Thu Apr 09, 2009 5:45 pm

Re: db2 calling stored procedures

Post by aseiden » Mon Jan 27, 2014 5:31 pm

Try padding the decimal number to its full length.

Code: Select all

$nbr='000000000000000'; //defined in RPG program as Decimal(15)
-- Alan Seiden

eaburk
Posts: 82
Joined: Mon Jan 04, 2010 3:42 pm

Re: db2 calling stored procedures

Post by eaburk » Mon Feb 10, 2014 6:33 pm

Just wanted to update on this issue. I wasn't able to try specifically my example because I've since gotten rid of that code. But I had another situation almost identical come up. Zero filling a quoted string seemed to work. I do not understand why in ZS5 it works and in ZS6 I have to zerofill the string. This is a major hurdle for migrating to ZS6 among other issues I have where queries worked in ZS5 and not in ZS6. It's been a slow and frustrating experience to migrate our code to ZS6.

Thanks.

User avatar
rodflohr
Zend Global Support
Posts: 56
Joined: Mon Dec 29, 2008 5:28 pm

Re: db2 calling stored procedures

Post by rodflohr » Tue Feb 11, 2014 12:04 am

Can you post your Stored Procedure definition here for the RPG program in your original example? This would be the CREATE PROCEDURE statement used to define your RPG program to DB2 as an external procedure.

eaburk
Posts: 82
Joined: Mon Jan 04, 2010 3:42 pm

Re: db2 calling stored procedures

Post by eaburk » Tue Feb 11, 2014 1:44 pm

Here is its:

Code: Select all

CREATE PROCEDURE LIBRARY/PROGRAM(INOUT key DEC (9, 0), INOUT rtnmsg VARCHAR (5000 )) LANGUAGE RPGLE NOT DETERMINISTIC MODIFIES SQL DATA EXTERNAL NAME LIBRARY/PROGRAM PARAMETER STYLE GENERAL  

User avatar
rodflohr
Zend Global Support
Posts: 56
Joined: Mon Dec 29, 2008 5:28 pm

Re: db2 calling stored procedures

Post by rodflohr » Tue Feb 11, 2014 5:23 pm

We were looking for the CREATE PROCEDURE from your original example, that would have matched this code:

$key='KEY'; //defined in RPG program as Char(10)
$nbr='0'; //defined in RPG program as Decimal(15)

db2_bind_param($stmt,1,"key",DB2_PARAM_INOUT);
db2_bind_param($stmt,2,"nbr",DB2_PARAM_INOUT);

Or, can you show us the PHP code and RPG parameter definitions that match the CREATE PROCEDURE you just showed us. The idea is to be able to see all three, the CREATE PROCEDURE, the parameter binding in PHP, and the parameter definitions in the RPG program.

eaburk
Posts: 82
Joined: Mon Jan 04, 2010 3:42 pm

Re: db2 calling stored procedures

Post by eaburk » Tue Feb 11, 2014 10:34 pm

Apologies for my ommission/confusion. Here is hopefully everything you need.

The PHP Code:

Code: Select all

		$sql = "call LIBRARY/PROGRAM(?,?)";
		$stmt = db2_prepare($dbcnx_db2, $sql);

		$key=0; //if i use '000000000' the call would work
		$errors="";

		db2_bind_param($stmt,1,"key",DB2_PARAM_INOUT);
		db2_bind_param($stmt,2,"errors",DB2_PARAM_INOUT);
		if(db2_execute($stmt)){
			echo $errors;
		}
		else{
			echo db2_stmt_errormsg();
		}
The RPG Definition:

Code: Select all

D OPRP243         Pr                          
D  Key                           9p 0         
D  rtnMsg                     5000a   varying    
The SQL Stored Procedure Creation Statement:

Code: Select all

CREATE PROCEDURE LIBRARY/PROGRAM(INOUT key DEC (9, 0), INOUT 
rtnmsg VARCHAR (5000 )) LANGUAGE RPGLE NOT DETERMINISTIC MODIFIES
SQL DATA EXTERNAL NAME PROGRAM/LIBRARY PARAMETER STYLE GENERAL  

aseiden
Posts: 875
Joined: Thu Apr 09, 2009 5:45 pm

Re: db2 calling stored procedures

Post by aseiden » Thu Feb 13, 2014 2:59 am

eaburk,

We're working with IBM to provide a fix for this issue.

Contact me offline (alan@alanseiden.com) and I'll send you a test ibm_db2 extension, compiled by me, intended to resolve this issue.

Thanks,
Alan Seiden

aseiden
Posts: 875
Joined: Thu Apr 09, 2009 5:45 pm

Re: db2 calling stored procedures

Post by aseiden » Sun Sep 07, 2014 11:24 pm

The fix was included in Zend Server 7. It may also be in Zend Server 6.3 but I'm not sure. Zend Server 7 is an easy upgrade from 6.x.
http://www.zend.com/en/products/server/ ... mi#IBM%20i

Alan

Post Reply