SQLCODE

HI: I WOULD LIKE TO HELP ME WITH SQLCODE STAMENT...IT WILL BE MUCH BETTER WITH AN EXAMPLE IN STORED PROCEDURE. I WANNA KNOW IF AN INSERT DO IT WELL BUT I DON'T KNOW USE THE SQLCODE STAMENT. I'M USING INFORMIX VERSION 7 WITH .NET. REGARDS...

Hi Carlos, Not sure if you are looking for following or not.. Here is what I would do...If my table has a serialno column, a successfull insert statement will give me a serialno of just inserted record. To get the serialno in Stored Procedure, use; INSER INTO <table_name> values (.......); LET lv_ins_serialno = DBINFO('SQLCA.SQLERRD1'); Now, check whether or not the lv_ins_serialno is > 0. If it is, it means record was iserted into a table successfully otherwise not. You may now return a value accordingly to your calling routine. I would recommend you to see more examples of DBINFO... Regards, Dharmendra > To: ids@iiug.org > From: carlos_lv8@hotmail.com > Subject: SQLCODE [21234] > Date: Sat, 11 Sep 2010 12:14:56 -0400 > > HI: > > I WOULD LIKE TO HELP ME WITH SQLCODE STAMENT...IT WILL BE MUCH BETTER WITH AN > EXAMPLE IN STORED PROCEDURE. > I WANNA KNOW IF AN INSERT DO IT WELL BUT I DON'T KNOW USE THE SQLCODE STAMENT. > I'M USING INFORMIX VERSION 7 WITH .NET. > > REGARDS... > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

In informix v7 you did not have access to the sqlcode variable within a stored procedure. You only have access to the sqlerrd[2] code which indicates how many rows were affected by the previous SQL statement. To get access to that you have to use the DBINFO( 'sqlerrd2' ) function. So, in the procedure: INSERT INTO .....; IF DBINFO( 'sqlerrd2' ) != 1 THEN -- Error condition END IF The only other alternative, to capture errors from SQL statements within the procedure, in Informix version 7, is to use the ON EXCEPTION clause to capture and return the error to the caller or to rollback the transaction. In Informix version 11.50xC1 and later you have direct access to the error handling variables like SQLCODE, but not in 7.xx. You need to upgrade to 11.50, IDS 7.31 went out-of-support almost a year ago! IBM may even cut you a deal to upgrade. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sat, Sep 11, 2010 at 12:14 PM, CARLOS LINO <carlos_lv8@hotmail.com>wrote: > HI: > > I WOULD LIKE TO HELP ME WITH SQLCODE STAMENT...IT WILL BE MUCH BETTER WITH > AN > EXAMPLE IN STORED PROCEDURE. > I WANNA KNOW IF AN INSERT DO IT WELL BUT I DON'T KNOW USE THE SQLCODE > STAMENT. > I'M USING INFORMIX VERSION 7 WITH .NET. > > REGARDS... > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cd14f90dd6fa904900657ae