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

Question

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

Solution 1

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.

Solution 2

I have seen this used.

if LOCAL_SQLCA^.sqlcode = 0 then
/* return serial */
  Result := LOCAL_SQLCA^.sqlerrd[1]
else
/* return error code */
  Result := -(Abs(LOCAL_SQLCA^.sqlcode));

Comments

[Ilya Kochetov]

You might notice that the question is about Informix, not MS SQL

[Jonathan Leffler]

Which language is that? Is it Pascal? And the negated ABS is a little odd; unless you are making a connection (for which there is, sadly, at least one positive error), all error numbers in Informix are negative.

[Jonathan Leffler]

This is unreliable - someone else might have inserted a newer, larger value since your was inserted.

[FrankRuperto]

The OP did not specify which Informix version is being used, so there can be more than one correct answer.