Re: Stored Procedure equiv to esql sqlerrd[1]

"Robert J. Cline" <rcline@gtetel.com> writes: >Does anyone know of a informix variable name in Stored Procedures >that is the equiv to esql's sqlca.sqlerrd[1]. I need a stored >to return back to a trigger the serial value after an insert that >was performed in the stored procedure. In 6.0 and later versions, the function dbinfo() will return the inserted serial value. For 5.0 versions, a triggered stored procedure which stores the inserted serial value in a global variable will do the trick. See the following example: - David -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- When inserting a row into a table with a serial column, an insert trigger on that table sees the assigned serial value. By triggering a procedure which assigns the passed-in value of the serial column to a global variable, the assigned serial value is made available to the triggering procedure in that global variable. The below listed example illustrates the mechanism. -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- Create a procedure to insert a row into the customer table, defining a -- global variable which will be assigned the serial value of the inserted row. CREATE PROCEDURE ins_customer (p_customer_num INTEGER) RETURNING INTEGER;DEFINE GLOBAL pg_serial INTEGER DEFAULT 0; INSERT INTO customer VALUES (p_customer_num);RETURN pg_serial; END PROCEDURE; -- Create the generalized procedure get_serial to assign the input parameter -- value to the global variable defined in procedures that require access to -- the serial value of inserted rows. CREATE PROCEDURE get_serial (p_serial INTEGER)DEFINE GLOBAL pg_serial INTEGER DEFAULT 0; LET pg_serial = p_serial; END PROCEDURE; -- Create a trigger on the insert of a row into customer that executes the -- procedure get_serial with the value inserted to customer_num. CREATE TRIGGER i_customerINSERT ON customer REFERENCING NEW AS new FOR EACH ROW (EXECUTE PROCEDURE get_serial (new.customer_num)); -- Execute the procedure ins_customer, passing the value 0 to insert into the -- serial column, and observe the returned value. EXECUTE PROCEDURE ins_customer (0);