RE: Las record
Thank you! I am typing with one arm and I wanted to tell you I figured it out about five minutes after I wrote the email, but I dislocated my shoulder so it is hard to type right now.. Thanks alot I did use the cftransaction info though.. -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 19, 2002 9:46 AM To: CF-Talk Subject: Re: Las record Bryan Love wrote: > Different DB's provide different ways of doing this, but the only universal > way I know of is this: > > Assuming the key is numeric and increments ascending: > > > do the insert > > > SELECT MAX(whateverID) as maxID > FROM tablename > > And that does *NOT* work. Let me repeat that, because I know it is a very common mistake. That does *NOT* work. The only thing that will protect you from a phantom read is a transaction with the isolation level "serializable". According to the relevant standards, transactions should default to the isolation level "serializable" and you should be right. However, most databases default to "read committed", and therefore will not protect you from phantom reads. Explicitly adding isolation="serializable" is required. Jochem __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Las record
Bryan Love wrote: > Different DB's provide different ways of doing this, but the only universal > way I know of is this: > > Assuming the key is numeric and increments ascending: > > > do the insert > > > SELECT MAX(whateverID) as maxID > FROM tablename > > And that does *NOT* work. Let me repeat that, because I know it is a very common mistake. That does *NOT* work. The only thing that will protect you from a phantom read is a transaction with the isolation level "serializable". According to the relevant standards, transactions should default to the isolation level "serializable" and you should be right. However, most databases default to "read committed", and therefore will not protect you from phantom reads. Explicitly adding isolation="serializable" is required. Jochem __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Las record
Bryan said: >Different DB's provide different ways of doing this, but the only universal >way I know of is this: .. Actually, there's some contention about this method with Oracle. Some poeple say there's an implicit commit with every call from CF to Oracle, thereby blowing away your transaction after the first cfquery. Other people disagree with that, but in the "better safe than sorry" approach, if you're using Oracle, you should consider selecting the next value from a sequence FIRST, and then use the value to populate your primary key second. -d Deanna Schneider Interactive Media Developer [EMAIL PROTECTED] __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Las record
You also have the option.. of @@IDENTITY.. if you are SQL Server... and it autmatically incremented...can remember what it is on Oracle. insert into... whatever select @@IDENTITY as MAXID from tableName Joe > -Original Message- > From: Bryan Love [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, September 18, 2002 6:49 PM > To: CF-Talk > Subject: RE: Las record > > > Different DB's provide different ways of doing this, but the only > universal > way I know of is this: > > Assuming the key is numeric and increments ascending: > > > do the insert > > > SELECT MAX(whateverID) as maxID > FROM tablename > > > The transaction tags prevent another thread from doing an insert > between the > two queries and screwing up the SELECT MAX... statement. > > If the key is random then you likely are generating it in the code and > already know it before the query is even run. > > +---+ > Bryan Love > Macromedia Certified Professional > Internet Application Developer > Database Analyst > TeleCommunication Systems > [EMAIL PROTECTED] > +---+ > > "...'If there must be trouble, let it be in my day, that my child may have > peace'..." > - Thomas Paine, The American Crisis > > "Let's Roll" > - Todd Beamer, Flight 93 > > > > -Original Message- > From: Webmaster [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, September 18, 2002 2:49 PM > To: CF-Talk > Subject: Las record > > > How do I get the Unique ID from the last field I inserted? > > __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Las record
SELECTmax(unique_id) FROM tablename -Original Message- From: Webmaster [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 18, 2002 5:49 PM To: CF-Talk Subject: Las record How do I get the Unique ID from the last field I inserted? __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Las record
Different DB's provide different ways of doing this, but the only universal way I know of is this: Assuming the key is numeric and increments ascending: do the insert SELECT MAX(whateverID) as maxID FROM tablename The transaction tags prevent another thread from doing an insert between the two queries and screwing up the SELECT MAX... statement. If the key is random then you likely are generating it in the code and already know it before the query is even run. +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst TeleCommunication Systems [EMAIL PROTECTED] +---+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis "Let's Roll" - Todd Beamer, Flight 93 -Original Message- From: Webmaster [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 18, 2002 2:49 PM To: CF-Talk Subject: Las record How do I get the Unique ID from the last field I inserted? __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Las record
Explore the custom tag cf_maxID That should do it for you. At 02:48 PM 9/18/02, you wrote: >How do I get the Unique ID from the last field I inserted? > __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
