Re: How do you test sqlcode=NOTFOUND after a select/updt in a stored proc.

> > I am investigating the use of stored procedures as a method of encapsulating > commonly used code. What I can't figure out is how to determine if a > select, delete or update statement was successful. Yes, I know how to trap > errors, but I want to know if the row was found(SQLCODE!=100). > > The following is not acceptable: > LET cnt = SELECT COUNT(*) FROM cust WHERE cust.id = custid; > IF cnt > 0 THEN > UPDATE mytable SET cust.amt = cust.amt + amt WHERE cust.id = custid;> END IF > if (sqlca.sqlcode == 100) /* OR != 100 */ > > It is difficult to believe that something this fundamental could have been > left out of the implementation of stored procedures. I must have missed > something when I read the documentation. Can some one tell me on what page Daniel, As hard as it is to imagine Informix did indeed forget to include access to the SQLCA structure in V5 Stored Procedures. This oversight is apparently corrected in V6 but in the meantime we have implemented a standard of using a foreach statement to test for all not found conditions. let notfound = Y foreach .... select.... update .. where current of... let notfound = N end foreach if notfound = Y whatever end if As I mentioned in a previous email a couple of days ago you can use a trigger to place the serial value of a inserted row in a global variable making it accesable to your stored procedure. This is the other big problem with not having access to SQLCA. Don't thank me for this solution, if I remember correctly, this came from David Bere, Informix Cheers - Jim My opinions are my own. They may vary with time but they remain MINE! ---------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 375-5222 Address: 700 Airport Blvd. #300 Fax: (415) 375-5019 Burlingame, CA 94010-1937 ----------------------------------------------------------------------