Getting last inserted id from SQL itself in Informix

Question

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?

Welcome to Stack Overflow. Please read the [About] page soon. Please do not use abbreviations like 'knw abt'; use full English words. — Jonathan Leffler, Aug 02 '13 at 15:37

score 3 · Answer 1 · edited May 23 '17 at 12:00

3

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).

edited May 23 '17 at 12:00

Community

1
1

answered Aug 02 '13 at 15:52

Jonathan Leffler

730,956
141
904
1,278

Hi Jonathan,Thanks for that and sorry about not using full english words. now I am after returning the last inserted serial id from the sql itself..for example,if you have a sql to insert a record to a table and if that table has a serial column how to make it return the last inserted serial id ? an example sql would be useful .Thanks – Sean Mshan Aug 06 '13 at 14:43
My initial question stands — which language? And SQL is not an answer; SPL might be. – Jonathan Leffler Aug 06 '13 at 17:11

Belmiris · Answer 2 · 2017-03-15T11:50:52.263

2

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 */

edited Mar 15 '17 at 11:50

answered Mar 15 '17 at 11:32

Belmiris

2,741
3
25
29

score 0 · Answer 3 · answered Mar 11 '19 at 10:18

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.

Getting last inserted id from SQL itself in Informix

3 Answers3