Re: Getting the serial number of latest inserted record in SP

Ravi Joseph Pinto wrote: > > Subject: > > Hi all, > > How does one determine the value of latest serial number inserted > in a table in a stored procedure ? For versions prior to 6.0: The code below shows how triggers can be used to pass in the serial value of an inserted row into a stored procedure: create trigger i_case insert on tab_name referencing new as new for each row (execute procedure get_serial (new.serial_col_name)) create procedure get_serial (p_serial integer) define global pg_serial integer default 0; let pg_serial = p_serial; --The serial id can now be used by other procedures called from the --same application, since it is stored in a global variable. end procedure; If you are running DSA 6.0 or higher, you can make the following function call: create procedure serial_insert() define ser int; insert into orders (order_num, order_date, customer_num) values (0,"04/01/93",102); let ser = dbinfo("sqlca.sqlerrd1"); return ser; end procedure; The variable ser can now be used in another statement further down in the code. The SQLCA structure is not available directly in stored procedures, but the dbinfo function call will allow you to examine the SQLCA after an SQL statement. > Also, when using "on exception statements" , one has to specify > the error numbers. Does anyone out there have a compilation of error > numbers related to actions ? For example while doing inserts, one > might check for error numbers -391, -275 etc. Where can I get more > info on this ? There is an Informix Error Messages book available which lists all of the error messages and what they mean. Keaton Adams Informix Software