Mark, What about trying this:
<cftransaction> <cfquery> INSERT into Table value = 'value' </cfquery> <CFQUERY NAME="qGetLastID" DATASOURCE="dsn"> SELECT @@Identity as LastID FROM tablename </cfquery> </cftransaction> qGetLastID.LastID should be what you need. Regards, Steve c -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mark M Sent: Friday, 6 February 2004 11:45 AM To: CFAussie Mailing List Subject: [cfaussie] mySQL and getting last inserted auto_increment Hey all - Looking at 2 different ways to retrieve the last inserted ID on a table that has auto_increment on it. 1) <cftransaction> <cfquery> INSERT into Table value = 'value' </cfquery> <cfquery> SELECT MAX(tableid) AS MaxID FROM Table </cfquery> </cftransaction> Which seems to have no major issues. Maybe(?) some threading issues with concurrent inserts on a table. 2) <cftransaction> <cfquery> INSERT into Table value = 'value' </cfquery> <cfquery> SELECT LAST_INSERT_ID() as MaxID; </cfquery> </cftransaction> The only issue with this is - mySQL states: 'The most recently generated ID is maintained in the server on a per-connection basis. It will not be changed by another client.' Does this mean per transaction / session - or does it mean per connection, as in the entire CF connection? I'd prefer to use the Last_insert_ID - but I'm worried about other threading issues - i.e. 2 tables being inserted at the same time / close to by different clients, and boom... wonky values. Any thoughts? Mark ------------------------------------------------------------------ [EMAIL PROTECTED] ICQ: 3094740 Safe From Bees [ www.safefrombees.com ] --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
