RE: Getting last inserted record from database
Problem with that is when you are dealing with timing in less than a second that can go wrong... For example assume before you start an insert the max_id = 1. Then 2 people go to make an insert at the same time... Insert A - max_id increments to 2. Insert B - max_id increments to 3 (this insert slipped in before the next step for A). Select MAX_ID for A. Returns 3. Select MAX_ID for B. Returns 3. Because of the timing ColdFusion would work as if both of them were 3. It is for reasons such as this that there are multiple ways in MS SQL to get the last record id and they all have little nuances. SCOPE_IDENTITY, @@IDENTITY, etc. Steve -Original Message- From: Timothy Laureska [mailto:tlaure...@dhmh.state.md.us] Sent: Monday, October 26, 2009 3:18 PM To: cf-talk Subject: Re: Getting last inserted record from database I would think you'd use a max_id scenario , where you query for the last id number using cfquery ... SELET Max (id) as max_id /cfquery Azadi Saryev az...@sabai-dee.com 10/26/2009 3:06 PM On 27/10/2009 03:03, Arsalan Tariq Keen wrote: I have a form of placing purchase order. Each purchase order is uniquely identified by the 'ID' column in the database table which is an Auto Increment integer field. I want to know, how can I obtain the 'ID' of an order which I just placed in the database table? Regards, Arsalan ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327754 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Getting last inserted record from database
Since serializable is the only isolation level that is meant to eliminate phantom reads, that means that this approach will probably only work if you set the isolation level to serializable (and, of course, that your RDBMS supports it). Which includes the performance implications that this isolation level brings. I don't think there's any danger of phantom reads occurring in this case. Phantom reads occur when a single transaction reads the same set twice and gets different values each time due to changes between the first and second queries. http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Repeatable_reads_.28phantom_reads.29 Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327755 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Getting last inserted record from database
However without a serializable transaction (assuming read committed is therefore the isolation level used), another query could insert a new row and commit before the commit in this transaction; this would result in an incorrect ID. mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ 2009/10/27 Dave Watts: Since serializable is the only isolation level that is meant to eliminate phantom reads, that means that this approach will probably only work if you set the isolation level to serializable (and, of course, that your RDBMS supports it). Which includes the performance implications that this isolation level brings. I don't think there's any danger of phantom reads occurring in this case. Phantom reads occur when a single transaction reads the same set twice and gets different values each time due to changes between the first and second queries. http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Repeatable_reads_.28phantom_reads.29 ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327762 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Getting last inserted record from database
Exactly. On Tue, Oct 27, 2009 at 10:32 AM, James Holmes james.hol...@gmail.comwrote: However without a serializable transaction (assuming read committed is therefore the isolation level used), another query could insert a new row and commit before the commit in this transaction; this would result in an incorrect ID. mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ 2009/10/27 Dave Watts: Since serializable is the only isolation level that is meant to eliminate phantom reads, that means that this approach will probably only work if you set the isolation level to serializable (and, of course, that your RDBMS supports it). Which includes the performance implications that this isolation level brings. I don't think there's any danger of phantom reads occurring in this case. Phantom reads occur when a single transaction reads the same set twice and gets different values each time due to changes between the first and second queries. http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Repeatable_reads_.28phantom_reads.29 ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327763 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Getting last inserted record from database
I have a form of placing purchase order. Each purchase order is uniquely identified by the 'ID' column in the database table which is an Auto Increment integer field. I want to know, how can I obtain the 'ID' of an order which I just placed in the database table? Regards, Arsalan ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327685 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Getting last inserted record from database
On 27/10/2009 03:03, Arsalan Tariq Keen wrote: I have a form of placing purchase order. Each purchase order is uniquely identified by the 'ID' column in the database table which is an Auto Increment integer field. I want to know, how can I obtain the 'ID' of an order which I just placed in the database table? Regards, Arsalan ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327686 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Getting last inserted record from database
What database are you using? There a specific methods for each type. On Mon, Oct 26, 2009 at 12:06 PM, Azadi Saryev az...@sabai-dee.com wrote: On 27/10/2009 03:03, Arsalan Tariq Keen wrote: I have a form of placing purchase order. Each purchase order is uniquely identified by the 'ID' column in the database table which is an Auto Increment integer field. I want to know, how can I obtain the 'ID' of an order which I just placed in the database table? Regards, Arsalan ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327687 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Getting last inserted record from database
I am using H2. -- From: Alan Rother alan.rot...@gmail.com Sent: Tuesday, October 27, 2009 1:09 AM To: cf-talk cf-talk@houseoffusion.com Subject: Re: Getting last inserted record from database What database are you using? There a specific methods for each type. On Mon, Oct 26, 2009 at 12:06 PM, Azadi Saryev az...@sabai-dee.com wrote: On 27/10/2009 03:03, Arsalan Tariq Keen wrote: I have a form of placing purchase order. Each purchase order is uniquely identified by the 'ID' column in the database table which is an Auto Increment integer field. I want to know, how can I obtain the 'ID' of an order which I just placed in the database table? Regards, Arsalan ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327688 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Getting last inserted record from database
Here are the help docs for your database type. http://www.h2database.com/html/functions.html#identity http://www.h2database.com/html/functions.html#identityIn the same CFQUERY as your insert do a select and follow the syntax indicated on that help page. You also need to specify a name for your query so it can return the resulting ID. Keep in mind, in a high transaction environment, this may not always return the ID of the row you just inserted, it could return another query's ID. If you are going to have a high volume of transactions you may want to use a separate query that looks for the data you just inserted =] -- Alan Rother Adobe Certified Advanced ColdFusion MX 7 Developer Manager, Phoenix Cold Fusion User Group, AZCFUG.org ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327690 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Getting last inserted record from database
select the max id straight after the insert and wrap it all in cftransaction tags ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327698 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Getting last inserted record from database
You may also want to consider the use of sequences instead of an identity column (a la Oracle and others). I don't know if that is an option for you though. Allen Alan Rother wrote: Here are the help docs for your database type. http://www.h2database.com/html/functions.html#identity http://www.h2database.com/html/functions.html#identityIn the same CFQUERY as your insert do a select and follow the syntax indicated on that help page. You also need to specify a name for your query so it can return the resulting ID. Keep in mind, in a high transaction environment, this may not always return the ID of the row you just inserted, it could return another query's ID. If you are going to have a high volume of transactions you may want to use a separate query that looks for the data you just inserted =] ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327699 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Getting last inserted record from database
Be careful if you do this, oracle and some others reuse deleted IDs if you don't use sequences. Using cftransaction and querying for the data you just input would be my suggestion. Actually, if you can using a sequence would probably be best if you have access to them. --Original Message-- From: Brian McCairn To: ColdFusion Mailing List ReplyTo: ColdFusion Mailing List Subject: Re: Getting last inserted record from database Sent: Oct 26, 2009 3:09 PM select the max id straight after the insert and wrap it all in cftransaction tags ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327701 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Getting last inserted record from database
I would think you'd use a max_id scenario , where you query for the last id number using cfquery ... SELET Max (id) as max_id /cfquery Azadi Saryev az...@sabai-dee.com 10/26/2009 3:06 PM On 27/10/2009 03:03, Arsalan Tariq Keen wrote: I have a form of placing purchase order. Each purchase order is uniquely identified by the 'ID' column in the database table which is an Auto Increment integer field. I want to know, how can I obtain the 'ID' of an order which I just placed in the database table? Regards, Arsalan ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327702 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Getting last inserted record from database
Since serializable is the only isolation level that is meant to eliminate phantom reads, that means that this approach will probably only work if you set the isolation level to serializable (and, of course, that your RDBMS supports it). Which includes the performance implications that this isolation level brings. On Mon, Oct 26, 2009 at 3:09 PM, Brian McCairn brian.mcca...@medicapp.euwrote: select the max id straight after the insert and wrap it all in cftransaction tags ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327703 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
