Jonathan Leffler wrote:Tim Bunce wrote:Can you tell if the last insert generated a SERIAL or SERIAL8 id? Or would the application need to give you a hint?
I don't need an application hint, though I can't readily tell either.
The SERIAL I can guess pretty reliably simply by looking at the field in the SQLCA -- if it isn't zero, chances are a serial was inserted.
So the SQLCA is cleared for each statement and only set non-zero for an insert into a table with a SERIAL column?
The SQLCA is the SQL Communication Area. It is logically cleared for each statement, and the part (one element of one of the arrays) that records the last serial column is set non-zero for an insert into a table with a serial column (certainly if you insert the special value, 0, which indicates that the actual value should be auto-generated; if you insert a (non-zero) value, I don't know whether it is returned).
The SQLCA also contains information such as the SQL error number (sqlca.sqlcode), and data for the error message, and the number of rows affected by the last operation, and costs, and warnings, and so on. (There's also a totally different set of meanings for the fields in the SQLCA when you connect to a database, but that's not material to this discussion).
With SERIAL8, I have no information readily available.
But if the above is true, then if the application asks for $dbh->last_insert_id and the SQLCA field is 0 then you could reasonably assume that the SERIAL8 should be used.
Not necessarily. Consider:
CREATE TABLE Rubbish(s4 SERIAL UNIQUE, s8 SERIAL8 UNIQUE); INSERT INTO Rubbish VALUES(0, 0);
Not sensible - but legal. In this case, I'd have both the SERIAL
and the SERIAL8 to worry about. Also, there are plenty of INSERT operations where neither type of SERIAL is involved. Certainly, I could decide that if the SERIAL value is zero, I should try the SERIAL8 instead. That would be feasible. It might generate spurious data on occasion - if someone asked for the last_insert_id inappropriately - but at least it is some sort of algorithm. It doesn't deal with the pathological case shown above, of course.
[I agree that dbh is better than sth, and the DBI spec will say that the info is potentially very short lived and should be got immediately after the insert.]
Yes, the information is volatile and must be retrieved quickly, before it is trampled.
Since PostgreSQL, Oracle, DB2 and Informix (and maybe others) all have sequences, I think the DBI specification should specifically exclude them from the discussion of last_insert_id -- just to prevent misunderstandings.
--
Jonathan Leffler ([EMAIL PROTECTED], [EMAIL PROTECTED]) #include <disclaimer.h>
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
