How can I obtain SQLCODE in SPL ?

How can I obtain SQLCODE (and sql error message) inside SPL code in Informix ? dbinfo('sqlca.sqlcode') doesn't work ...

On Wed, 26 Nov 2003 07:53:53 -0500, Marek Radzewicz wrote: > How can I obtain SQLCODE (and sql error message) inside SPL code in Informix ? > dbinfo('sqlca.sqlcode') doesn't work ...You have to use EXCEPTION handling to retrieve the SQLCODE and ISAM error code: create procedure some_proc( arg1 int, ... ) returning int, int, int; DEFINE rtnsql INT; -- place holder for exception sqlcode setting DEFINE rtnisam INT; -- isam error code. Should be onpload exit status DEFINE final_result INT; ON EXCEPTION SET rtnsql, rtnisam ROLLBACK WORK; RETURN -1, rtnsql, rtnisam; END EXCEPTION; BEGIN WORK; LET final_result = 0; ..... COMMIT WORK; RETURN final_result, 0, 0; END PROCEDURE; If you prefer the EXCEPTION handler can simply do nothing other than set the variables and fall back to the mainline code where you can test them and handle the results yourself; Art S. Kagel

Thanks, it works ! I have one more question: is it possible to find error description as text, inside SPL ? Marek Radzewicz "Art S. Kagel" <kagel@bloomberg.net> wrote in message news:pan.2003.11.26.08.55.51.84178.12806@bloomberg.net... > On Wed, 26 Nov 2003 07:53:53 -0500, Marek Radzewicz wrote: > > > > How can I obtain SQLCODE (and sql error message) inside SPL code in Informix ? > > dbinfo('sqlca.sqlcode') doesn't work ...> You have to use EXCEPTION handling to retrieve the SQLCODE and ISAM error code: > > create procedure some_proc( arg1 int, ... ) > returning int, int, int;> > DEFINE rtnsql INT; -- place holder for exception sqlcode setting > DEFINE rtnisam INT; -- isam error code. Should be onpload exit status > DEFINE final_result INT; > > ON EXCEPTION SET rtnsql, rtnisam > ROLLBACK WORK; > RETURN -1, rtnsql, rtnisam; > END EXCEPTION; > > BEGIN WORK; > LET final_result = 0; > ..... > COMMIT WORK; > RETURN final_result, 0, 0; > > END PROCEDURE; > > If you prefer the EXCEPTION handler can simply do nothing other than set the > variables and fall back to the mainline code where you can test them and handle > the results yourself; > > Art S. Kagel

"Marek Radzewicz" <marekr@poczta.wp.pl> wrote in message news:bq2dm7$1itg$1@news2.ipartners.pl... > Thanks, it works ! > I have one more question: is it possible to find error description as text, > inside SPL ? yes and no. ON EXCEPTION SET rtnsql, rtnisam, rterrmsg rterrmsg is a char field of possibly char(80) length. will set the reason of the error, usually the column name table name or missing constraint name. But not the full error. For e.g. if you are inserting a null value into a column date_of_birth which has been declared as NOT NULL, then the above EXCEPTION will be set as follows:- rtnsql = -391 rtnisam = 0 rterrmsg = 'date_of_birth' finderr -391 -391 Cannot insert a null into column column-name. This statement tries to put a null value in the noted column. However, that column has been defined as NOT NULL. Roll back the current transaction. If this is a program, review the definition of the table, and change the program logic to not use null values for columns that cannot accept them. No u can construct from the above that you are trying to insert null into date_of_birth column. In ESQL/C one can do sprintf and construct an error string which can look like this: Cannot insert a null into column data_of_birth which is definitely more readable. For me, even the SP way is good enuf to find out the cause. However it is to be noted that if you don't have an exception block and let the server return any error, then the server always returns full error string as described above for ESQL/C. I don't know how the same can be achieved using EXCEPTION block.

... provided you actually wanted the calling routine to receive it as a return value, and not to catch it as an error. If you want to do both you'll have to issue a RAISE EXCEPTION. Otherwise just let the code fall out on its own. Andy