Re: SPL, how to check for errors after executing Inserts, updates ,

Stanislav Here is what you can do: CREATE PROCEDURE p1 (arg1 type1,...) RETURNING rettype1,...; DEFINE esql SMALLINT; DEFINE eisam SMALLINT; BEGIN ON EXCEPTION SET esql, eisam RAISE EXCEPTION esql, eisam; END EXCEPTION -- Your "real" SQL goes here ... END END PROCEDURE; When you encounter any error in your SQL, it will bounce up to the EXCEPTION block which will raise the exception that you can then trap in your application program using sqlca.sqlcode. To find if a singleton SELECT returned a NOTFOUND, you can do the following: SELECT ...; LET rowsReturned = DBINFO('sqlca.sqlerrd2'); IF rowsReturned = 0 THEN RAISE EXCEPTION 100, 0; END IF; This would raise an exception to the EXCEPTION block which will then raise the exception to the calling application program, and sqlca.sqlcode will be set to 100, which you can then trap in your application program. For INSERTs, DBINFO('sqlca.sqlerrd2') would give you the number of rows inserted which you can then check in the stored procedure. For DELETEs and UPDATEs, the DBINFO('sqlca.sqlerrd2') gives you the number of rows deleted and updated, respectively. Hope this answers your questions. Sujit Stanislav Paltis <stas@ccs.neu.edu> on 02/10/99 12:29:19 PM Please respond to Stanislav Paltis <stas@ccs.neu.edu> To: informix-list@iiug.org cc: (bcc: Sujit Pal) Subject: SPL, how to check for errors after executing Inserts, updates, etc.. Hi, I am writing Stored procedure for informix 7 server. I execute Insert statement inside SP and can't really check SQLCODE, or any other variable if row was inserted correctly. Thgeir examples don't give you anything about error checking after execution of SQL statements in SP. For Example, they do Selects and than check host variables for returned values, but it is no use for me. Oracle, Sybase will allow you to check SQLCODE or something like that if error occured, but Informix won't take sqlcode. Also, would like to know if the error actually occured, how would I get the SQl error text. Please advise Thanks in advance for your help. Stan