Gotcha. So far, selecting the last_insert_id() is working. I'll see what the CF forums have to say.
thanks. ------- Original Message -------- From: "Jeff Kilbride" To: "Jonnycattt" CC: Subject: Re: Re: ensuring that I'm getting the correct "last insert ID" Date: 02/05/02 I don't have any experience with ColdFusion, but you should be able to execute more than one SQL statement per connection. sounds like it should work -- you can't break a transaction across multiple connections, either. You might try asking this on a ColdFusion list, or re-post it to the MySQL list with something like "ColdFusion and last_insert_id" as the subject. Maybe you'll find someone with relevant experience. I think using the Lock Tables strategy should be your last resort. It will severely impair performance. --jeff ----- Original Message ----- From: "Jonnycattt" To: "Jeff Kilbride" Sent: Thursday, May 02, 2002 2:03 PM Subject: Re: Re: ensuring that I'm getting the correct "last insert ID" > I'm doing this from ColdFusion, so I don't think I have control over when I > can return the connection. Typically in ColdFusion, multiple queries such as > this are handled with wrappers. I can use to > ensure I always have the right ID, but I'm wondering if there's a better > way...a native mySQL way....to do this. > > What if i use Lock Tables around the first insert and the select? then > unlock, then do my second insert? > > ------- Original Message -------- > From: "Jeff Kilbride" > To: "[EMAIL PROTECTED]" , "[EMAIL PROTECTED]" , > "[EMAIL PROTECTED]" > CC: > Subject: Re: ensuring that I'm getting the correct "last insert ID" > Date: 02/05/02 > > Just make sure you call the last_insert_id() function before returning the > connection to the pool. If you're using the mm.mysql driver in Java, you can > cast the statement object to an org.gjt.mm.mysql.Statement object and use > it's getLastInsertID() method: > > long lastInsertID = ((org.gjt.mm.mysql.Statement)stmt).getLastInsertID(); > > A connection pool of this sort can't share connections between different > users simultaneously. The MySQL protocol only allows one user per connection > at any instant. So, as long as you grab the last insert id before returning > the connection to the pool, you will be fine. > > --jeff > > ----- Original Message ----- > From: > To: ; > Sent: Thursday, May 02, 2002 8:36 AM > Subject: RE: ensuring that I'm getting the correct "last insert ID" > > > > if you are using a connection pool the [close database connection] part of > > your > > example doesnt actually close the connection. so > > > > 1. Your code might be using several connections > > 2. Your code might use a different connection for each statement. > > 3. Even if your code only does use the one connection, someone else might > be > > sharing it. > > > > asides from that , you dont have to close your database connection to > > execute multiple queries in java. > > You may have to create multiple statement objects though(depending on the > > type of query) > > > > -----Original Message----- > > From: denonymous [mailto:[EMAIL PROTECTED]] > > Sent: 02 May 2002 08:36 > > To: Sean O'Donnell; [EMAIL PROTECTED] > > Subject: Re: ensuring that I'm getting the correct "last insert ID" > > > > > > But would the same instance of a script drop and pick up different > > connections? I haven't done any Java/MySQL work, but anything I've done > with > > PHP or Perl is based on a model of: > > > > [begin script] > > [open database connection(s)] > > [execute queries] > > [close database connection(s)] > > [end script] > > > > Is it more common in Java to do something like: > > > > [begin script] > > [open database connection] > > [execute query] > > [close database connection] > > [open database connection] > > [execute query] > > [close database connection] > > [etc...] > > [end script] > > > > ? > > > > > > > > ----- Original Message ----- > > From: > > To: ; > > Sent: Thursday, May 02, 2002 11:28 AM > > Subject: RE: ensuring that I'm getting the correct "last insert ID" > > > > > > > its used a lot by java. As setting up and closing down connections to > the > > > database > > > is relatively expensive, you use a pool manager, when the connection is > > > closed by > > > your code, it gets returned to the pool of open connections and is'nt > > > actually closed. > > > just held open and returned next time you want a connection. So I > imagine > > if > > > you > > > have 20 users on the site at once, then they could all be executing > > various > > > snippets of > > > sql over 5 connections. > > > > > > -----Original Message----- > > > From: denonymous [mailto:[EMAIL PROTECTED]] > > > Sent: 02 May 2002 08:27 > > > To: Sean O'Donnell; [EMAIL PROTECTED] > > > Subject: Re: ensuring that I'm getting the correct "last insert ID" > > > > > > > > > Admittedly, I'm no expert. What *is* connection pooling? > > > > > > > > > ----- Original Message ----- > > > From: > > > To: ; > > > Sent: Thursday, May 02, 2002 11:21 AM > > > Subject: RE: ensuring that I'm getting the correct "last insert ID" > > > > > > > > > > what happens if you are using connection pooling though? > > > > > > > > > > > > -----Original Message----- > > > > From: denonymous [mailto:[EMAIL PROTECTED]] > > > > Sent: 02 May 2002 08:09 > > > > To: [EMAIL PROTECTED] > > > > Subject: Re: ensuring that I'm getting the correct "last insert ID" > > > > > > > > > > > > From: "Jonnycattt" > > > > > > > > > Hi all, > > > > > I know this has been asked a bunch of times, but i need some clarity > > > (new > > > > > mySQL user). > > > > > I have an app that inserts a new user into one table, then inserts > > some > > > > user > > > > > preferences into another table. the procedure is as follows: > > > > > 1) insert new user > > > > > 2) query for that user's id using select max(userID) as LastUserID > > from > > > > .. > > > > > 3) insert into user preferences table using the previous query's > > > > LastUserID. > > > > > To be clear, this last insert adds mutliple rows to a table, not > one > > > row. > > > > > > > > > > > > If I were you, I'd use MySQL's LAST_INSERT_ID() function: > > > > http://www.mysql.com/doc/M/i/Miscellaneous_functions.html > > > > > > > > So long as your ID field is AUTO_INCREMENT, this will return the last > > > > auto-generated field in the current handle. > > > > > > > > Something like this: > > > > > > > > INSERT INTO UserTable... (your first user insert) > > > > SELECT LAST_INSERT_ID() FROM UserTable (this will return the userID of > > the > > > > user you just inserted) > > > > INSERT INTO OtherTables (pass the userID you just got to these > queries) > > > > > > > > > > > > You'd mentioned worries that a user could be added while another user > > was > > > > still being processed, and the result would be the wrong userID being > > > > returned. LAST_INSERT_ID() is handle-based, though, so there should be > > no > > > > worries with that -- the sessions will be kept separate. > > > > > > > > Hope this helps! > > > > > > > > > > > > -- > > > > denonymous . : . : . AIM: denonymous > > > > http://www.coldcircuit.net ' : ' : ' http://24.91.199.33 > > > > > > > > "According to one of our readers, the new MacOS X contains another > > > > Satanic holdover from the 'BSD Unix' OS mentioned above; to open up > > > > certain locked files one has to run a program much like the DOS > > > > prompt in Microsoft Windows and type in a secret code: 'chmod 666'." > > > > > > > > > > > > > > > > --------------------------------------------------------------------- > > > > Before posting, please check: > > > > http://www.mysql.com/manual.php (the manual) > > > > http://lists.mysql.com/ (the list archive) > > > > > > > > To request this thread, e-mail > > > > To unsubscribe, e-mail > > > > > > > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > > > > > > > > > > > > > > > --------------------------------------------------------------------- > > Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail > > To unsubscribe, e-mail > > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail > To unsubscribe, e-mail > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > _____________________________________________________________________ > This mail was sent by Cablespeed Webmail > _____________________________________________________________________ This mail was sent by Cablespeed Webmail --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
