2

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

Taryn
  • 242,637
  • 56
  • 362
  • 405
Ilya Kochetov
  • 17,988
  • 6
  • 44
  • 60

4 Answers4

11

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.

RET
  • 9,100
  • 1
  • 28
  • 33
1

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));
Paul
  • 4,160
  • 3
  • 30
  • 56
Brian Schmitt
  • 6,008
  • 1
  • 24
  • 36
  • 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 Nov 02 '08 at 21:04
-1

I don't think "efficient" is the word you're looking for here. It's more of a question of accuracy. I'm not sure I can do a better job of explaining it than the SQL Books Online can, but generally, unless you really know what you're doing and have a specific reason for using @@IDENTITY, use SCOPE_IDENTITY. The most obvious reason for this is that @@IDENTITY will not return the identity of the latest record added by your program/sp/etc if there is a trigger attached to the table. Also, there could be issues in high volume applications where two transactions occur at the same time and the following would occur...

  1. Your insert
  2. Other user's insert
  3. Return other user's identity to you
Jody
  • 8,021
  • 4
  • 26
  • 29
-5

The OP did not specify which version of Informix is being used, so there can be different answers

Joe R.
  • 2,032
  • 4
  • 36
  • 72