Stored Procedure execution issue in IBM Informix O

Posted in 2007

Topics: Stored Procedures & SPL, Connectivity: ODBC / JDBC / .NET, Networking & sqlhosts Configuration

Mukherjee, — Wed, 18 Jul 2007 12:48:12 — source: IIUG Forums & Mailing Lists

> We have some front-end .Net applications which is using IBM Informix > Client SDK 2.81 that in turn use ODBC Driver Version 3.82. The > front-end application is trying to execute a Informix Stored Procedure > which returns some values, and it is always failing. Note that when I > remove the returning from the SP, it is working fine from front-end. > Also, there are no issues while executing the SP at back-end either > using or not using return values. > > I am attaching the code snippet of the Informix SP and .Net that > executes that SP : > > Stored Procedure > --DROP PROCEDURE sp_upd_labbillcd; > CREATE PROCEDURE sp_upd_labbillcd(labserial INT,reg_hrs > DECIMAL(8,2),ot_hrs DECIMAL(8,2)) > RETURNING SMALLINT;> > DEFINE GLOBAL dbserver CHAR(20) DEFAULT DBSERVERNAME; > DEFINE num_rows SMALLINT; > > LET num_rows = 0; > > IF dbserver = "leo" THEN > UPDATE cdi@prodtli:labbillcd > SET ppa_remain_st = reg_hrs, > ppa_remain_ot = ot_hrs > WHERE lab_serial = labserial; > ELSE > UPDATE cdi@testtli:labbillcd > SET ppa_remain_st = reg_hrs, > ppa_remain_ot = ot_hrs > WHERE lab_serial = labserial; > END IF > > LET num_rows=DBINFO('sqlca.sqlerrd2'); > > RETURN num_rows; > > END PROCEDURE; > > .Net Code > > string strConn="Driver={IBM INFORMIX 3.82 32 > BIT};Host=ZZZZZZ;Server=leotest;Service=98990;Protocol=onsoctcp;Databa > se=leoprod;UID=XXXXX;PWD=YYYYY"; > using(OdbcConnection oConn=new > OdbcConnection(strConn)) > { > oConn.Open(); > OdbcCommand oCmd = new OdbcCommand(" > execute procedure sp_upd_labbillcd(11687929,7.0,0.0)",oConn);> > oCmd.CommandType=CommandType.Text; > int i=oCmd.ExecuteNonQuery(); > Response.Write("Returned value:" + > i.ToString()); > } > > The .Net code is returning -1 and we checked in the database table > that update did not happened for the passed data. The same code is > behaving perfectly fine if we remove return statements from SP. > > Kindly help !!! > > Thanks in advance !!! > > Regards, Arindam Mukherjee

↪ replying to Mukherjee,

Everett — Wed, 18 Jul 2007 13:54:46 — source: IIUG Forums & Mailing Lists

Use oCmd.ExecuteScalar() instead. The return value from execute NonQuery is whether the statement failed or succeeded, not anything that the statement generated. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Mukherjee, Arindam > Sent: Wednesday, July 18, 2007 7:48 AM > To: ids@iiug.org > Subject: Stored Procedure execution issue in IBM Inform.... [9576] > > > We have some front-end .Net applications which is using IBM Informix > > Client SDK 2.81 that in turn use ODBC Driver Version 3.82. The > > front-end application is trying to execute a Informix Stored Procedure > > which returns some values, and it is always failing. Note that when I > > remove the returning from the SP, it is working fine from front-end. > > Also, there are no issues while executing the SP at back-end either > > using or not using return values. > > > > I am attaching the code snippet of the Informix SP and .Net that > > executes that SP : > > > > Stored Procedure > > --DROP PROCEDURE sp_upd_labbillcd; > > CREATE PROCEDURE sp_upd_labbillcd(labserial INT,reg_hrs > > DECIMAL(8,2),ot_hrs DECIMAL(8,2)) > > RETURNING SMALLINT;> > > > DEFINE GLOBAL dbserver CHAR(20) DEFAULT DBSERVERNAME; > > DEFINE num_rows SMALLINT; > > > > LET num_rows = 0; > > > > IF dbserver = "leo" THEN > > UPDATE cdi@prodtli:labbillcd > > SET ppa_remain_st = reg_hrs, > > ppa_remain_ot = ot_hrs > > WHERE lab_serial = labserial; > > ELSE > > UPDATE cdi@testtli:labbillcd > > SET ppa_remain_st = reg_hrs, > > ppa_remain_ot = ot_hrs > > WHERE lab_serial = labserial; > > END IF > > > > LET num_rows=DBINFO('sqlca.sqlerrd2'); > > > > RETURN num_rows; > > > > END PROCEDURE; > > > > .Net Code > > > > string strConn="Driver={IBM INFORMIX 3.82 32 > > BIT};Host=ZZZZZZ;Server=leotest;Service=98990;Protocol=onsoctcp;Databa > > se=leoprod;UID=XXXXX;PWD=YYYYY"; > > using(OdbcConnection oConn=new > > OdbcConnection(strConn)) > > { > > oConn.Open(); > > OdbcCommand oCmd = new OdbcCommand(" > > execute procedure sp_upd_labbillcd(11687929,7.0,0.0)",oConn);> > > > oCmd.CommandType=CommandType.Text; > > int i=oCmd.ExecuteNonQuery(); > > Response.Write("Returned value:" + > > i.ToString()); > > } > > > > The .Net code is returning -1 and we checked in the database table > > that update did not happened for the passed data. The same code is > > behaving perfectly fine if we remove return statements from SP. > > > > Kindly help !!! > > > > Thanks in advance !!! > > > > Regards, > Arindam Mukherjee > > > ************************************************************************ ** > ***** > Forum Note: Use "Reply" to post a response in the discussion forum.