On Sun, Mar 09, 2003 at 12:33:10PM -0800, Jonathan Leffler wrote: > Tim Bunce wrote: > >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 [...] > > >>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.
Yeap. > It doesn't deal with the pathological case shown above, of course. It does in as much as it says "if a table has both SERIAL and SERIAL8 columns then the SERIAL column is given priority." :) > 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. I think we have to discuss them together for now. We'll wait and see how the DBI specification ends up, but it will need to clarify auto-values vs sequences. Tim.
