Suppose I have multi users inserting records at the same time, and it will not be ok with this code.
A common question I am asked is: “How do you obtain primary key (auto increment integer) of the record I just inserted?”.
The most common solution I have found people use is to insert the data, then attempt to query the table filtering by the data you just inserted. This actually works, most of the time on low traffic sites/applications. Want a better, more reliable way? This is a quick stab at how to retrieve the ID of the record you just inserted into a table. I will only cover MS Access, MSSQL and MySQL.
|
<cftransaction>
</cftransaction> |
This will ensure that whatever is in this block will be executed in the same scope. This may slow things down a little, but is well worth it.
Now, inside the <cftransaction> block we’ll add our insert query:
| <cftransaction> <cfquery name=“insertRecord“ datasource=“myAccessDB“> INSERT INTO tblComments (author,title,message) VALUES (‘John Doe’,’I really like this tutorial’,’This is the best one yet!’) </cfquery> </cftransaction> |
Okay, easy enough. Now I want to return the user to a confirmation page with the book they just entered. The problem is, how do I know what book was just entered? Well, as I stated above, the most common solution I’ve seen is to query the table for the last record filtering on data we just entered. This approach leaves you wide open for errors if someone enters the same or similar enough data.
However, in our case we wrapped the transaction in a <cftransaction> block. That means we can ensure that subsequent queries inside this block execute in the same scope before any other database requests from other sessions. It locks the table for that brief moment and queues up any transactions that were submitted after this one. So, how do we get the ID? Like this:
| <cftransaction> <cfquery name=“insertRecord“ datasource=“AccessDB“> INSERT INTO tblComments (author,title,message) VALUES (‘John Doe’,’I really like this tutorial’,’This is the best one yet!’) </cfquery> <cfquery name=“getNewID“ datasource=“myAccessDB“> SELECT MAX(id) as newID FROM tblComments </cfquery> <cfset newID = getNewID.newID> </cftransaction> |
There it is. Now we just pass back #newID# and we can identify the record we just inserted.
<cfquery name=“insertRecord“ datasource=“MSSQLDB“> SELECT Scope_Identity () as newID |
Even easier right? Now the local variable newID contains the ID of the record you just inserted.
<cftransaction> <cfset newID = getNewID.newID> |
Suppose I have multi users inserting records at the same time, and it will not be ok with this code.
The same tutorial which previously been submitted by Pablo. :D
Very cool example. However, it does not work when you are using more complex ids. IE, if you were to use UUID's or MSUID's for your pk's this will only return the numeric value of the id and not the id it's self.
how can i get the ID of the second last entry from a database
Excellent