stored procedure and serial insert
You need to get the serial value from the dbinfo function, as in
"dbinfo('sqlca.sqlerrd1')" immediately following your insert in the stored
procedure. You do this by including the dbinfo call in a SELECT statement
that returns one row. A good way to do this is to select a unique row from
one of the system catalog tables, such as systables. The following example
passes 2 params to a SP which performs an INSERT, and returns the error code
and serial value:
create procedure I_test_table(Pcolumn1 char(10),Pcolumn2 smallint) returning
smallint,integer;
define error_code smallint;
define sql_err int;
define isam_err int;
define sql_text char(20);
define serial_val integer;
on exception set sql_err, isam_err, sql_text
rollback work;
let error_code=sql_err;
return error_code,0;
end exception;
begin work;
insert into test_table (column1,column2,serial_col) values
(Pcolumn1,Pcolumn2,0);
select dbinfo('sqlca.sqlerrd1') into serial_val from systables where
tabname="systables";
commit work;
let error_code=0;
return error_code,serial_val;
end procedure;
"Steve Weiland" <steve@ebusiness-partners.com> wrote in message
news:ViaL4.10956$Ib7.142457@typhoon2.kc.rr.com...
> In a stored procedure, how do I obtain the generated value of a serial
> column after an insert?
>
> Thanks!
>
> Steve
>
>
In a stored procedure, how do I obtain the generated value of a serial column after an insert? Thanks! Steve