Re: Re: pdo lastInsertID()

That is what the MySQL,SQLite and PostgreSQL drivers do already. Ilia Davey Shafik wrote:

Hey, I am in complete agreement with Lukas here, *last*InsertId should return the *last* used insert ID (or sequence equivelent). Add a nextInsertId() method if you want the next insert ID or sequence equivelent. - Davey Lukas Smith wrote:

Hi, I was looking at the lastInsertID() method and noticed that it: "Returns the ID of the last inserted row, or the next value from a sequence object." I think this is needs some thought. Essentially RDBMS that support autoincrement need a way to get the last inserted ID on a per connection basis. RDBMS that support sequences need a way to get the current and the next value of a sequence. Here are the usage scenarios. 1) I want to insert a new row, so I get the next sequence value or NULL for auto increment, stick the given value into my INSERT statement and I am done. In this scenario I dont need the ID for later use at all. 2) In scenario 1) I had to do two calls to the RDBMS when I am using a sequence. What makes much more sense is to stick a trigger on the column and have the database increment the sequence and stick that in as the value for the column. PostGreSQL actually provides an datatype for exactly this purpose [1], but other RDBMS can easily be setup in much the same way. 3) Now scenario 2) requires just a single call and it works more or less the same on all RDBMS (regardless of if they support sequences or autoincrement) as long as I setup my schema accordingly. However if I want to get the generated ID I would now need to get the last inserted ID on autoincrement databases and the current value of the sequences associated with the column on all others. 4) Obviously I can use sequences for other things than generating unique row identifiers. So just getting the next sequence ID might just be all that I need. On databases that dont support sequences I would then have to jump through some hoops by emulating sequences with a table and an autoincrement column. This however is beyond the scope of PDO. So to conclude: - sequences users need to get the next and the current ID from a sequence - autoincrement users need to get the last insert ID - getting the current sequence ID and the last insert ID will often have the same semantical meaning Therefore I propose that by default the lastInsertID() method returns the last inserted ID or the current value of the sequence. Either through a flag or a separate method PDO should still provide a way to get the next sequence ID (since lastInsertID() is already a loaded method and since I think it is kinda unexpected to have a lastInsertID() method return the next sequence ID I would prefer a separate method). regards, Lukas [1] http://www.postgresql.org/docs/8.0/static/datatype.html#DATATYPE-SERIAL