Re: inserting record with serial field in procedural SQL

K. Carter wrote: > > I am trying to write a stored procedure that inserts some data > into a table where the first field is a serial number. I want > to return the serial number of this inserted field as the result. > > Hi Kathy, you don't mention what Version of an Informix Engine you are using so I forward you the solutions for < V6 and >= V6 Engines as it was posted recently in the c.d.i by Keaton Adams from Informix: 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. Regards Tolis PS: Kerry, I think this question is a candidate for the next version of the FAQ +---------------------------------------------------------------------+ | V+K Relational Solutions email: tvarnas@compulink.gr | | Deligiorgi 26 tvarnas@orbit.de | | 546 42 Thessaloniki Voice: (30) 31 820270 | | Greece Fax: (30) 31 865463 | +---------------------------------------------------------------------+