I'm going to be using SQL Server and I wanted something like Oracle's sequences. I found this:
http://jamesthornton.com/software/coldfusion/nextval.html Mike James Holmes wrote: > (This is based on Oracle defaults; ymmv with MySql). It's essentially > what Mike said, but only if the other insert is committed first. It's > like this: > > 1) My transaction is started > 2) My INSERT is done > 3) Someone else's transaction starts > 4) Their INSERT is done > 5) Their SELECT is done > 6) Their transaction ends and is committed > 7) My SELECT is done (and I get the wrong ID). > 8) My transaction ends and is committed. > > It's unlikely, since both transactions are doing the same thing and if > I start first I should finish first, but it can happen. Make this > serializable and it can't happen (but then you risk "transaction can't > be serialized" errors and performance decreases). > > The best way is to select the ID first, from a sequence or some other > thing that guarantees a unique value (this is why a UUID is so good > for this) and use that in the insert. It can be done in a Stored Proc > to keep it all within the DB if you like. > > You can google the CF-Talk archives from the site. > > On 1/9/06, Mike Soultanian <[EMAIL PROTECTED]> wrote: >> What if someone were to insert another city just before the SELECT >> statement executed? >> >> Mike >> >> Baz wrote: >>> James, in which cases is it not 100% reliable? Are the CF-Talk archives >>> searchable? >>> >> >>>> On 1/9/06, Baz <[EMAIL PROTECTED]> wrote: >>>>> Is this the best way to do this: >>>>> >>>>> <cftransaction> >>>>> <cfquery name="InsertAddress" datasource="#DSN#"> >>>>> INSERT INTO Address (City) >>>>> VALUES (<cfqueryparam value="#City#" cfsqltype="cf_sql_integer" />) >>>>> </cfquery> >>>>> >>>>> <cfquery name="getInsertedID" datasource="#DSN#"> >>>>> SELECT MAX(AddressID) as MaxID >>>>> FROM Address >>>>> </cfquery> >>>>> </cftransaction> >>>>> >>>>> I'm using MySQL. >>>> -- >>>> CFAJAX docs and other useful articles: >>>> http://jr-holmes.coldfusionjournal.com/ >>>> >>>> >>> >>> >>> >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228795 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
