How to retrieve a SERIAL value in a STORED PROCEDURE ?

> > > Hello, > > 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. > > Is there a better solution than this one : > > set lock mode ... > lock table in share mode > insert into zob ( num ) values ( 0 ) <------ num : serial> select max(num) into var from zob > commit work 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_caseINSERT ON case REFERENCING NEW as new FOR EACH ROW (EXECUTE PROCEDURE get_serial(new.int_case_num) ) cheers j. _____________________________________________________________________________ Jack Parker - Hewlett Packard, BSMC Boise, Idaho, USA jparker@hpbs3645.boi.hp.com _____________________________________________________________________________ Back up my hard drive? You mean these things can actually run in reverse? _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________