Number of host variables not valid

General discussion on Zend Server for IBM System i
Post Reply
stephen_lenhart
Posts: 2
Joined: Thu Jun 24, 2010 3:16 pm

Number of host variables not valid

Post by stephen_lenhart » Thu Jun 24, 2010 4:27 pm

I have several php apps that run stored procedures. I use the db2_bind_param to pass my parameters. Everything works great until I use more than 7 parameters.

Stored Procdeure ----- 10 parms
CREATE PROCEDURE PHPPARM7 (
IN I_GTHSYS CHAR(11),
IN I_PRPRTY DECIMAL(6,0),
IN I_EFFYR DECIMAL(4,0),
IN I_EFFMO DECIMAL(2,0),
IN I_REVDCK CHAR(2),
IN I_DESC CHAR(30),
IN I_ADESC CHAR(30),
IN I_BDESC CHAR(30),
IN I_CDESC CHAR(30),
IN I_QRYTYPE CHAR(1))

PHP ----- 10 parms
$sql = 'CALL TEST.PHPPARM7 (?,?,?,?,?,?,?,?,?,?)';
if ($conn) {
$stmt = db2_prepare ( $conn, $sql );
db2_bind_param ( $stmt, 01, "gthsys", DB2_PARAM_IN );
db2_bind_param ( $stmt, 02, "prprty", DB2_PARAM_IN, DB2_DOUBLE, 06, 02 );
db2_bind_param ( $stmt, 03, "effyr", DB2_PARAM_IN, DB2_DOUBLE, 04, 00 );
db2_bind_param ( $stmt, 04, "effmo", DB2_PARAM_IN, DB2_DOUBLE, 02, 00 );
db2_bind_param ( $stmt, 05, "revdck", DB2_PARAM_IN );
db2_bind_param ( $stmt, 06, "desc", DB2_PARAM_IN );
db2_bind_param ( $stmt, 07, "adesc", DB2_PARAM_IN );
db2_bind_param ( $stmt, 08, "bdesc", DB2_PARAM_IN );
db2_bind_param ( $stmt, 09, "cdesc", DB2_PARAM_IN );
db2_bind_param ( $stmt, 10, "qrytype", DB2_PARAM_IN );
var_dump($sql);
echo"<br>";
var_dump($stmt);
if ($stmt) {
$result = db2_execute ( $stmt .....

Result ------ 10 parms
string(44) "CALL TEST.PHPPARM7 (?,?,?,?,?,?,?,?,?,?)"
resource(2) of type (DB2 Statement) exec error: 07001
exec errormsg: Number of host variables not valid.

Fall down go boom.



Same process minus A/B/CDESC works great.
Stored procedure ----- 7 parms
CREATE PROCEDURE PHPPARM7 (
IN I_GTHSYS CHAR(11),
IN I_PRPRTY DECIMAL(6,0),
IN I_EFFYR DECIMAL(4,0),
IN I_EFFMO DECIMAL(2,0),
IN I_REVDCK CHAR(2),
IN I_DESC CHAR(30),
-- IN I_ADESC CHAR(30),
-- IN I_BDESC CHAR(30),
-- IN I_CDESC CHAR(30),
IN I_QRYTYPE CHAR(1))

PHP ------ 7 parms
$sql = 'CALL TEST.PHPPARM7 (?,?,?,?,?,?,?)';

if ($conn) {
$stmt = db2_prepare ( $conn, $sql );
db2_bind_param ( $stmt, 01, "gthsys", DB2_PARAM_IN );
db2_bind_param ( $stmt, 02, "prprty", DB2_PARAM_IN, DB2_DOUBLE, 06, 02 );
db2_bind_param ( $stmt, 03, "effyr", DB2_PARAM_IN, DB2_DOUBLE, 04, 00 );
db2_bind_param ( $stmt, 04, "effmo", DB2_PARAM_IN, DB2_DOUBLE, 02, 00 );
db2_bind_param ( $stmt, 05, "revdck", DB2_PARAM_IN );
db2_bind_param ( $stmt, 06, "desc", DB2_PARAM_IN );
// db2_bind_param ( $stmt, 07, "adesc", DB2_PARAM_IN );
// db2_bind_param ( $stmt, 08, "bdesc", DB2_PARAM_IN );
// db2_bind_param ( $stmt, 09, "cdesc", DB2_PARAM_IN );
db2_bind_param ( $stmt, 07, "qrytype", DB2_PARAM_IN );
var_dump($sql);
echo"<br>";
var_dump($stmt);
if ($stmt) {........

Result ----- 7 Parms
string(38) "CALL TEST.PHPPARM7 (?,?,?,?,?,?,?)"
resource(2) of type (DB2 Statement)

Good result set.

What the heck?

zend_i5
Posts: 158
Joined: Mon Mar 23, 2009 5:22 pm

Re: Number of host variables not valid

Post by zend_i5 » Sun Jul 04, 2010 2:18 pm

IBM has made extensive changes in IBM-DB2 extension which will be available in the next product release. Send me your email and I will sent you updated IBM-DB2 extension so you can check if new extension fixes the problem

Post Reply