CODEWITHSUNDEEP
sqlinformix
Sean Mshan
Sean Mshan

Reputation: 57

Getting last inserted id from SQL itself in Informix

How can I get the last inserted ID from an SQL statement in Informix. I know about DBINFO but how to get the SQL like "insert into table_name (fields) values (values)" to return the last inserted ID?

Upvotes: 0

Views: 3928

Answers (3)

glezo
glezo

Reputation: 859

Even if it is obvios, just one note on @Belmiris answer, who might confuse novices: To retrieve the last inserted serial value, you don't need to specify the tabid in the SELECT DBINFO query. Just a

SELECT DBINFO( 'sqlca.sqlerrd1' ) FROM systables LIMIT 1 will sufice.

Upvotes: 0

Belmiris
Belmiris

Reputation: 2805

This seems to work if you want strictly SQL.

select tabid FROM "informix".systables where tabname = 'sm_job_cost' /* 607 */

SELECT DBINFO( 'sqlca.sqlerrd1' ) FROM systables WHERE tabid = 607; /* 0 */

insert into sm_job_cost (smjc_jb_prodlnk, smjc_prft_ctr) values (1, 2)

SELECT DBINFO( 'sqlca.sqlerrd1' ) FROM systables WHERE tabid = 607; /* 1 */

Upvotes: 2

Jonathan Leffler
Jonathan Leffler

Reputation: 753525

Which host language?

The information about the value created for a SERIAL column is in the SQLCA (SQL Communications Area). The information about the value created for a BIGSERIAL column is available via a function call. The information about the value created for a SERIAL8 column is available via a different function call. However, the details differ between ESQL/C and ODBC and JDBC and ... If you have both a SERIAL and either a SERIAL8 or BIGSERIAL, you can find both lots of information (but there would be questions about why you have both types in one table).

The question Informix: how to get an id of the last inserted record provides similar information to this (and thanks to seekerOfKnowledge for finding it).

Upvotes: 3

Related Questions