Informix: how to get an id of the last inserted record

identity, informix, sql

Solution

The value of the last SERIAL insert is stored in the SQLCA record, as the second entry in the sqlerrd array. Brian's answer is correct for ESQL/C, but you haven't mentioned what language you're using.

If you're writing a stored procedure, the value can be found thus:

LET new_id = DBINFO('sqlca.sqlerrd1');

It can also be found in `$sth->{ix_sqlerrd}[1]` if using DBI

There are variants for other languages/interfaces, but I'm sure you'll get the idea.

Problem

What's the most efficient way of getting the value of the SERIAL column after the INSERT statement? I.e. I am looking for a way to replicate `@@IDENTITY` or `SCOPE_IDENTITY` functionality of MS SQL

Original source