How to retrieve a SERIAL value in a STORED PROCEDURE ?

etienne write: >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 >--------------------------------------------------------------- > etienne@cri.univ-lr.fr > (NeXT/MIME-mail are welcome !) >--------------------------------------------------------------- The answer depends on the version of Online you are using. For V6+ use dbinfo(sqlca.sqlerrd[2]) in the procedure. For V5, its more complex. Create insert triggers on each table you want to get serial values from. The trigger is something like: create trigger ins_taby_trig insert on table_yreferencing new as nrec for each row (execute procedure setglob_taby(nrec.serial_field); create procedure setglob_taby(a_serial integer) define global taby_serial integer default 0; end procedure; create procedure ret_serial() returning integer; define global taby_serial integer default 0; return taby_serial; end procedure; Hope this helps Mark Denham BBC London, UK Mark.Denham@bbc.co.uk