Re: How to retrieve a SERIAL value in a STORED PROCEDURE ?

: > : > Is there a solution to retrieve the SERIAL number after an INSERT : > in a STORED PROCEDURE as far as the SQLCA.SQLERRD[2] does not works. : > : > ... : > Jack Parker (jparker@hpbs3645.boi.hp.com) wrote: : Apologies. dbinfo is not as of 5.1, I don't remember the specific release : number. 6.0 now sticks in my mind. Between 5.1 and that release you have to : use a trigger (of course 5.0 doesn't have triggers, so SOL). The trigger : solution: : (From the Incorporating SP and Triggers into your database training manual : p 5-102 - this manual cannot be bought by itself - you have to take the class : to get it) : 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, : -- since it is stored in a global : END PROCEDURE; : : CREATE TRIGGER i_case : INSERT ON case : REFERENCING NEW as new : FOR EACH ROW : (EXECUTE PROCEDURE get_serial(new.int_case_num) : ) For the record (and my own ego), I thought we had posted this solution in an FAQ when David Berg (Informix/Denver), David Coburn (Informix/Seattle), and myself (Informix/Denver) came up with it and got a "very clever" award from the developers of Stored Procedures. It is now merely a clever kludge for pre-6.0 engines, of course. But please henceforth refer to it as the "DDD Kludge". ======================================================================= Dennis J. Pimple dennisp@informix.com Opinions expressed Senior Consultant -------------------- are mine, and do not Informix Software Inc Voice: 303-850-0210 necessarily reflect Denver Colorado USA Fax: 303-779-4025 those of my employer.