Using the 'sqlca.sqlerrd1' Option

The 'sqlca.sqlerrd1' option returns a single integer that provides the last serial value that is inserted into a table. To ensure valid results, use this option immediately following a singleton INSERT statement that inserts a single row with a serial value into a table.

Tip: To obtain the value of the last SERIAL8 value that is inserted into a table, use the 'serial8' option of DBINFO. For more information, see Using the 'serial8' and 'bigserial' options.

The following example uses the 'sqlca.sqlerrd1' option:

EXEC SQL create table fst_tab (ordernum serial, partnum int);
EXEC SQL create table sec_tab (ordernum serial);
EXEC SQL insert into fst_tab VALUES (0,1);
EXEC SQL insert into fst_tab VALUES (0,4);
EXEC SQL insert into fst_tab VALUES (0,6);
EXEC SQL insert into sec_tab values (dbinfo('sqlca.sqlerrd1'));

This example inserts a row that contains a primary-key serial value into the fst_tab table, and then uses the DBINFO function to insert the same serial value into the sec_tab table. The value that the DBINFO function returns is the serial value of the last row that is inserted into fst_tab.

Because the SQLCA structure does not record serial values that are inserted by triggers, you cannot call the DBINFO function with the 'sqlca.sqlerrd1', 'bigserial', or 'serial8' options to return a serial value that a triggered action inserts.

For more information about the SQL Communications Area (SQLCA) data structure, within which sqlca.sqlerrd1 is a field, see the HCL OneDB™ Guide to SQL: Tutorial.