Re: Get last inserted ID
dont know about mysql, but this is how i do it in mssql2k : cfquery name=getMaxObject datasource=#dsn# SET NOCOUNT ON INSERT INTO Objects (EmployeeID, patientID, ObjectTypeID) VALUES (cfqueryparam cfsqltype=CF_SQL_INTEGER value=#session.whoami#, cfqueryparam cfsqltype=CF_SQL_INTEGER value=#form.patientid#, cfqueryparam cfsqltype=CF_SQL_INTEGER value=27) SELECT @@identity as maxObjectID SET NOCOUNT OFF /cfquery then to get the value i do #getMaxObject.maxObjectID#. give it a shot? -alex 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. Cheers, Baz ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:229392 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
Re: Get last inserted ID
If you are only inserting into the address table in this bit of code, you could also serialize the whole thing by using cflock around the transaction, but I don't like using application-level locking where I should be using database-level locking. 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. Cheers, Baz ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:229407 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
Re: Get last inserted ID
Assuming there's only one CF server (not a cluster) and no other method of accessing the DB is used (no other server platforms, developers etc). On 1/13/06, Robert Munn [EMAIL PROTECTED] wrote: If you are only inserting into the address table in this bit of code, you could also serialize the whole thing by using cflock around the transaction, but I don't like using application-level locking where I should be using database-level locking. -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:229415 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Get last inserted ID
Of course, but there is nothing stopping you from having both is there? You can have an autonumber id that is your primary key if you like, then have a uuid field that is also a key. As long as it is unique, you can use the two interchangably. Or, if you want a friendly order number, why not manufacture the order number, where you have information in there about maybe the date the order was taken and any other information. Lots of ways to skin this cat. On 1/8/06, Baz [EMAIL PROTECTED] wrote: Using UUIDs does seem good but they are quite unfriendly. For example lets say you have ORDERS... It's much easier to tell your colleague to look into OrderID: 155000 rather than OrderID: hagfhdvklchjcvadj... -Original Message- From: Bobby Hartsfield [mailto:[EMAIL PROTECTED] Sent: Sunday, January 08, 2006 10:32 PM To: CF-Talk Subject: RE: Get last inserted ID This should insert your record and return the new primary key as 'newid' cfquery NAME='myqry' Insert into address (city) Values (cfqueryparam value=#City# cfsqltype=cf_sql_integer /); Select LAST_INSERT_ID() as newid; /cfquery Primary keys that you control would probably be the better solution though. CF generated UUID's sound like a great solution. cfset mynewid = createuuid() cfquery... Insert into address (id, city) Values (#mynewid#, #city#) /cfquery So you always know what the new records ID is... It not only keeps you from having to worry about the ids getting crossed when you use max() and 2 or more people insert something at the same time. (or worrying about transactions to stop that from happening) AND... if the data gets moved to another datasource down the road... all the relations can be maintained since the IDs aren't autoincrements. That alone is enough to use your own defined IDs over autoincrementing ones. ...:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Baz [mailto:[EMAIL PROTECTED] Sent: Sunday, January 08, 2006 7:24 PM To: CF-Talk Subject: Get last inserted ID 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. Cheers, Baz ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228818 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
RE: Get last inserted ID
What version of MySQL was this? ..:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Baz [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 1:00 AM To: CF-Talk Subject: RE: Get last inserted ID Thanks for the info guys - all this makes me really sad... I remember researching this a long, long time ago and was recommended using cftransaction - so I did - for EVERY insert for all my apps. Now it seems I have major changes ahead of me... If I don't post for a while you'll know why... Baz P.S. Bobby, the query returned this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; Select LAST_INSERT_ID() as newid' at line 2 This is the exact test query I used: cfquery NAME='myqry' datasource=mydsn Insert into customer (customerdate,customeruserid) Values (cfqueryparam value=#now()# cfsqltype=CF_SQL_TIMESTAMP,cfqueryparam value=1 cfsqltype=CF_SQL_INTEGER); Select LAST_INSERT_ID() as newid; /cfquery My DB is MySQL 5 -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 12:45 AM To: CF-Talk Subject: Re: Get last inserted ID It happens because Oracle and most other DBs do not default to serializable transactions; Oracle, for example, defaults to read committed, which means that your transaction can read other committed data even if it happened during your transaction (although in Oracle this level does provide statement-level consistency). MySql defaults to repeatable read as far as I could tell from a quick google. Info on Oracle: http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html and MySQL http://dev.mysql.com/doc/refman/4.1/en/innodb-transaction-isolation.html On 1/9/06, Baz [EMAIL PROTECTED] wrote: What does cftransaction do then? The livedocs say that it can be used to group multiple queries that use CFQUERY into one business event. How do other events occur in between one event? -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228823 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Get last inserted ID
MySQL5... someone mentioned to append: ?MultipleQuery=True (or something like that) to my connection string in the DB... but it didn't work. -Original Message- From: Bobby Hartsfield [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 9:26 AM To: CF-Talk Subject: RE: Get last inserted ID What version of MySQL was this? ...:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Baz [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 1:00 AM To: CF-Talk Subject: RE: Get last inserted ID Thanks for the info guys - all this makes me really sad... I remember researching this a long, long time ago and was recommended using cftransaction - so I did - for EVERY insert for all my apps. Now it seems I have major changes ahead of me... If I don't post for a while you'll know why... Baz P.S. Bobby, the query returned this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; Select LAST_INSERT_ID() as newid' at line 2 This is the exact test query I used: cfquery NAME='myqry' datasource=mydsn Insert into customer (customerdate,customeruserid) Values (cfqueryparam value=#now()# cfsqltype=CF_SQL_TIMESTAMP,cfqueryparam value=1 cfsqltype=CF_SQL_INTEGER); Select LAST_INSERT_ID() as newid; /cfquery My DB is MySQL 5 -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 12:45 AM To: CF-Talk Subject: Re: Get last inserted ID It happens because Oracle and most other DBs do not default to serializable transactions; Oracle, for example, defaults to read committed, which means that your transaction can read other committed data even if it happened during your transaction (although in Oracle this level does provide statement-level consistency). MySql defaults to repeatable read as far as I could tell from a quick google. Info on Oracle: http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html and MySQL http://dev.mysql.com/doc/refman/4.1/en/innodb-transaction-isolation.html On 1/9/06, Baz [EMAIL PROTECTED] wrote: What does cftransaction do then? The livedocs say that it can be used to group multiple queries that use CFQUERY into one business event. How do other events occur in between one event? -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228825 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Get last inserted ID
Sorry, I missed the version at the end of your message. I wouldn't know about the ?MultipoQuery=True bit. Ive never used or heard of it. In the select...try Select @@identity as newid; Odds are that it's going to give you the same thing since @@identity is basically a shortcut for last_insert_id() but who knows. ..:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Baz [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 9:32 AM To: CF-Talk Subject: RE: Get last inserted ID MySQL5... someone mentioned to append: ?MultipleQuery=True (or something like that) to my connection string in the DB... but it didn't work. -Original Message- From: Bobby Hartsfield [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 9:26 AM To: CF-Talk Subject: RE: Get last inserted ID What version of MySQL was this? :.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Baz [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 1:00 AM To: CF-Talk Subject: RE: Get last inserted ID Thanks for the info guys - all this makes me really sad... I remember researching this a long, long time ago and was recommended using cftransaction - so I did - for EVERY insert for all my apps. Now it seems I have major changes ahead of me... If I don't post for a while you'll know why... Baz P.S. Bobby, the query returned this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; Select LAST_INSERT_ID() as newid' at line 2 This is the exact test query I used: cfquery NAME='myqry' datasource=mydsn Insert into customer (customerdate,customeruserid) Values (cfqueryparam value=#now()# cfsqltype=CF_SQL_TIMESTAMP,cfqueryparam value=1 cfsqltype=CF_SQL_INTEGER); Select LAST_INSERT_ID() as newid; /cfquery My DB is MySQL 5 -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 12:45 AM To: CF-Talk Subject: Re: Get last inserted ID It happens because Oracle and most other DBs do not default to serializable transactions; Oracle, for example, defaults to read committed, which means that your transaction can read other committed data even if it happened during your transaction (although in Oracle this level does provide statement-level consistency). MySql defaults to repeatable read as far as I could tell from a quick google. Info on Oracle: http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html and MySQL http://dev.mysql.com/doc/refman/4.1/en/innodb-transaction-isolation.html On 1/9/06, Baz [EMAIL PROTECTED] wrote: What does cftransaction do then? The livedocs say that it can be used to group multiple queries that use CFQUERY into one business event. How do other events occur in between one event? -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228828 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Get last inserted ID
mySQL has the LAST_INSERT_ID() function. I do this: SELECT LAST_INSERT_ID() as id FROM yourTableName !//-- andy matthews web developer ICGLink, Inc. [EMAIL PROTECTED] 615.370.1530 x737 --//- -Original Message- From: Baz [mailto:[EMAIL PROTECTED] Sent: Sunday, January 08, 2006 6:24 PM To: CF-Talk Subject: Get last inserted ID 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. Cheers, Baz ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228843 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
RE: Get last inserted ID
Yeah Baz... you could try adding the from address to the select @@identity or last_insert_id() I pointed out earlier as well. But it should assume that. Both work fine for me on MySQL5 without the 'from tablename'. ..:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Andy Matthews [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 10:42 AM To: CF-Talk Subject: RE: Get last inserted ID mySQL has the LAST_INSERT_ID() function. I do this: SELECT LAST_INSERT_ID() as id FROM yourTableName !//-- andy matthews web developer ICGLink, Inc. [EMAIL PROTECTED] 615.370.1530 x737 --//- -Original Message- From: Baz [mailto:[EMAIL PROTECTED] Sent: Sunday, January 08, 2006 6:24 PM To: CF-Talk Subject: Get last inserted ID 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. Cheers, Baz ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228848 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Get last inserted ID
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. Cheers, Baz ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228784 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
Re: Get last inserted ID
It's only 100% reliable if you use a serializable transaction, as far as I remember. This is certainly the case with Oracle. There is an extensive thread on this in the archives somewhere. 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:228786 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
Re: Get last inserted ID
What I usually do is have a column that just holds a specific timestamp or uuid of some sorts. I create a variable to hold the timestamp or uuid right before the insert query, then I select against the table looking for that timestamp or uuid. If you are using a db that stores a pretty detailed timestamp, especially down to the millisecond (not sure how mysql does it), that would work very well. And if you can use a uuid for the primary key instead of an autonumber, then you will have the id before you enven to the insert statement. Just some ideas. On 1/8/06, James Holmes [EMAIL PROTECTED] wrote: It's only 100% reliable if you use a serializable transaction, as far as I remember. This is certainly the case with Oracle. There is an extensive thread on this in the archives somewhere. 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:228787 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
RE: Get last inserted ID
James, in which cases is it not 100% reliable? Are the CF-Talk archives searchable? Ryan, your method seems ideal... but I think it would take me a month to do it... what DB datatype is your UUID? Cheers, Baz -Original Message- From: Ryan Guill [mailto:[EMAIL PROTECTED] Sent: Sunday, January 08, 2006 8:08 PM To: CF-Talk Subject: Re: Get last inserted ID What I usually do is have a column that just holds a specific timestamp or uuid of some sorts. I create a variable to hold the timestamp or uuid right before the insert query, then I select against the table looking for that timestamp or uuid. If you are using a db that stores a pretty detailed timestamp, especially down to the millisecond (not sure how mysql does it), that would work very well. And if you can use a uuid for the primary key instead of an autonumber, then you will have the id before you enven to the insert statement. Just some ideas. On 1/8/06, James Holmes [EMAIL PROTECTED] wrote: It's only 100% reliable if you use a serializable transaction, as far as I remember. This is certainly the case with Oracle. There is an extensive thread on this in the archives somewhere. 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:228790 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
Re: Get last inserted ID
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:228791 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
Re: Get last inserted ID
(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:228794 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Get last inserted ID
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
Re: Get last inserted ID
yes, this topic has been beaten to death. All posts here have links at the bottom, one of which is to the archives http://www.houseoffusion.com/cf_lists/threads.cfm/4 With Oracle all you need is a sequence defined and use it. No trans is needed, the sequence always gives a unique value. other RDBSs should be talked about in the archives. DK On 1/8/06, James Holmes [EMAIL PROTECTED] 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:228796 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Get last inserted ID
Just a string for the uuid. On 1/8/06, Baz [EMAIL PROTECTED] wrote: James, in which cases is it not 100% reliable? Are the CF-Talk archives searchable? Ryan, your method seems ideal... but I think it would take me a month to do it... what DB datatype is your UUID? Cheers, Baz -Original Message- From: Ryan Guill [mailto:[EMAIL PROTECTED] Sent: Sunday, January 08, 2006 8:08 PM To: CF-Talk Subject: Re: Get last inserted ID What I usually do is have a column that just holds a specific timestamp or uuid of some sorts. I create a variable to hold the timestamp or uuid right before the insert query, then I select against the table looking for that timestamp or uuid. If you are using a db that stores a pretty detailed timestamp, especially down to the millisecond (not sure how mysql does it), that would work very well. And if you can use a uuid for the primary key instead of an autonumber, then you will have the id before you enven to the insert statement. Just some ideas. On 1/8/06, James Holmes [EMAIL PROTECTED] wrote: It's only 100% reliable if you use a serializable transaction, as far as I remember. This is certainly the case with Oracle. There is an extensive thread on this in the archives somewhere. 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:228798 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Get last inserted ID
This should insert your record and return the new primary key as 'newid' cfquery NAME='myqry' Insert into address (city) Values (cfqueryparam value=#City# cfsqltype=cf_sql_integer /); Select LAST_INSERT_ID() as newid; /cfquery Primary keys that you control would probably be the better solution though. CF generated UUID's sound like a great solution. cfset mynewid = createuuid() cfquery... Insert into address (id, city) Values (#mynewid#, #city#) /cfquery So you always know what the new records ID is... It not only keeps you from having to worry about the ids getting crossed when you use max() and 2 or more people insert something at the same time. (or worrying about transactions to stop that from happening) AND... if the data gets moved to another datasource down the road... all the relations can be maintained since the IDs aren't autoincrements. That alone is enough to use your own defined IDs over autoincrementing ones. ..:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Baz [mailto:[EMAIL PROTECTED] Sent: Sunday, January 08, 2006 7:24 PM To: CF-Talk Subject: Get last inserted ID 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. Cheers, Baz ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228802 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Get last inserted ID
James, CFTransaction doesn't take care of that? Baz -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Sunday, January 08, 2006 8:39 PM To: CF-Talk Subject: Re: Get last inserted ID (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:228804 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Get last inserted ID
Not without the serializable isolation level, no. On 1/9/06, Baz [EMAIL PROTECTED] wrote: James, CFTransaction doesn't take care of that? -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228805 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Get last inserted ID
What does cftransaction do then? The livedocs say that it can be used to group multiple queries that use CFQUERY into one business event. How do other events occur in between one event? -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Sunday, January 08, 2006 11:19 PM To: CF-Talk Subject: Re: Get last inserted ID Not without the serializable isolation level, no. On 1/9/06, Baz [EMAIL PROTECTED] wrote: James, CFTransaction doesn't take care of that? -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228808 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Get last inserted ID
Using UUIDs does seem good but they are quite unfriendly. For example lets say you have ORDERS... It's much easier to tell your colleague to look into OrderID: 155000 rather than OrderID: hagfhdvklchjcvadj... -Original Message- From: Bobby Hartsfield [mailto:[EMAIL PROTECTED] Sent: Sunday, January 08, 2006 10:32 PM To: CF-Talk Subject: RE: Get last inserted ID This should insert your record and return the new primary key as 'newid' cfquery NAME='myqry' Insert into address (city) Values (cfqueryparam value=#City# cfsqltype=cf_sql_integer /); Select LAST_INSERT_ID() as newid; /cfquery Primary keys that you control would probably be the better solution though. CF generated UUID's sound like a great solution. cfset mynewid = createuuid() cfquery... Insert into address (id, city) Values (#mynewid#, #city#) /cfquery So you always know what the new records ID is... It not only keeps you from having to worry about the ids getting crossed when you use max() and 2 or more people insert something at the same time. (or worrying about transactions to stop that from happening) AND... if the data gets moved to another datasource down the road... all the relations can be maintained since the IDs aren't autoincrements. That alone is enough to use your own defined IDs over autoincrementing ones. ...:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Baz [mailto:[EMAIL PROTECTED] Sent: Sunday, January 08, 2006 7:24 PM To: CF-Talk Subject: Get last inserted ID 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. Cheers, Baz ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228809 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Get last inserted ID
What does cftransaction do then? The livedocs say that it can be used to group multiple queries that use CFQUERY into one business event. How do other events occur in between one event? Transactions are a bit complicated, and can be used to achieve different sorts of results. One thing that transactions allow you to do is to force all queries to either succeed or fail. Within a transaction, if the first query succeeds but the second fails, the first one will be undone - this is called a rollback. However, that by itself doesn't guarantee that nothing else will be going on while your transaction is running. Databases are designed to allow multiple concurrent users to work with the same data, while simultaneously guaranteeing the integrity of that data. Typically, this is done by using locks to prevent one user from reading or writing data while another user is doing something with that data. Different transaction isolation levels have different effects on how long locks are maintained. Instead of trying to explain this adequately, I'll just point you here: http://en.wikipedia.org/wiki/Isolation_(computer_science) 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! ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228810 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
Re: Get last inserted ID
It happens because Oracle and most other DBs do not default to serializable transactions; Oracle, for example, defaults to read committed, which means that your transaction can read other committed data even if it happened during your transaction (although in Oracle this level does provide statement-level consistency). MySql defaults to repeatable read as far as I could tell from a quick google. Info on Oracle: http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html and MySQL http://dev.mysql.com/doc/refman/4.1/en/innodb-transaction-isolation.html On 1/9/06, Baz [EMAIL PROTECTED] wrote: What does cftransaction do then? The livedocs say that it can be used to group multiple queries that use CFQUERY into one business event. How do other events occur in between one event? -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228811 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Get last inserted ID
Thanks for the info guys - all this makes me really sad... I remember researching this a long, long time ago and was recommended using cftransaction - so I did - for EVERY insert for all my apps. Now it seems I have major changes ahead of me... If I don't post for a while you'll know why... Baz P.S. Bobby, the query returned this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; Select LAST_INSERT_ID() as newid' at line 2 This is the exact test query I used: cfquery NAME='myqry' datasource=mydsn Insert into customer (customerdate,customeruserid) Values (cfqueryparam value=#now()# cfsqltype=CF_SQL_TIMESTAMP,cfqueryparam value=1 cfsqltype=CF_SQL_INTEGER); Select LAST_INSERT_ID() as newid; /cfquery My DB is MySQL 5 -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 12:45 AM To: CF-Talk Subject: Re: Get last inserted ID It happens because Oracle and most other DBs do not default to serializable transactions; Oracle, for example, defaults to read committed, which means that your transaction can read other committed data even if it happened during your transaction (although in Oracle this level does provide statement-level consistency). MySql defaults to repeatable read as far as I could tell from a quick google. Info on Oracle: http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html and MySQL http://dev.mysql.com/doc/refman/4.1/en/innodb-transaction-isolation.html On 1/9/06, Baz [EMAIL PROTECTED] wrote: What does cftransaction do then? The livedocs say that it can be used to group multiple queries that use CFQUERY into one business event. How do other events occur in between one event? -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228812 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Get last inserted ID
You can just modify your cftransaction tags to use the serializable isolation level if you want, but do some load testing first because there will be locks-a-plenty if you do... On 1/9/06, Baz [EMAIL PROTECTED] wrote: Thanks for the info guys - all this makes me really sad... I remember researching this a long, long time ago and was recommended using cftransaction - so I did - for EVERY insert for all my apps. Now it seems I have major changes ahead of me... -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228813 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Get last inserted ID
So James, just this: cftransaction isolation=SERIALIZABLE And problem solved? I will load test as you suggest, but I don't think it should be a problem - inserts are so rare relative to everything else... Thanks! -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 1:14 AM To: CF-Talk Subject: Re: Get last inserted ID You can just modify your cftransaction tags to use the serializable isolation level if you want, but do some load testing first because there will be locks-a-plenty if you do... On 1/9/06, Baz [EMAIL PROTECTED] wrote: Thanks for the info guys - all this makes me really sad... I remember researching this a long, long time ago and was recommended using cftransaction - so I did - for EVERY insert for all my apps. Now it seems I have major changes ahead of me... -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228814 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
