Hi. I've got a mysql table with a field that is auto-incremented on insert. Sometimes, I need to grab the auto-incremented value of that field right after I do an insert.
I'm using PDO, and lastInsertId seems to fit the bill, but I've read some stuff that suggests that we shouldn't rely on the value returned to be correct. Why not? Is there a reliable way to fetch the auto-incremented value from the record I just inserted?
I think it depends on what DBMS you are actually using. For instance, with PostgreSQL, it will not work (or possibly will with the optional 2nd parameter specifying the sequence object?). Fortunately, PostgreSQL allows you to return it as part of the insert query itself by using its RETURNING syntax (which is what we do where I work). If you're using MySQL, you should be okay, as it will presumably be calling MySQL's LAST_INSERT_ID() function.
This only really becomes an issue if/when you want to change the underlying DBMS or are developing an app that you want to be able to use with multiple DB drivers. In that case, you might need to set up some switches or child classes to deal with different DB drivers.
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
Reply With Quote
Bookmarks