We have a DB with multiple tables having "serial" type column. The legacy C application writes the data to these tables and uses "sqlerrd[2]" fetch the serial number of the data just inserted.

insert into email(serial_number, email_id) values(0, '[email protected]')

Due to serial number getting exhausted we are migrating to "bigserial". However, the above logic of fetching the serial number from "sqlerrd[2]" dont work, since it is of type "integer".

What is the right solution for handling this?

Note: As a work around we are currently making a call to ifx_getbigserial to fetch the serial number. However, we are concerned that, there are multiple tables and concurrent transactions as well and we are not sure, how does this API works? This API dont mention table name and our unit testing shows, we get right data, but not sure, is this the right solution.

CodePudding user response：

Using the ifx_getbigserial() function is correct. It returns the information about the last BIGSERIAL value generated for the current session. Other sessions will not affect it. However, if you insert values into multiple tables that each have a BIGSERIAL column, you must retrieve the inserted value via ifx_getbigserial() after you insert into each table and before the next insert into either the same table or any other table with a BIGSERIAL column.