Thanks jeff, thats a question thats been bugging me for a while. -----Original Message----- From: Jeff Kilbride [mailto:[EMAIL PROTECTED]] Sent: 02 May 2002 10:24 To: Sean O'Donnell; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: ensuring that I'm getting the correct "last insert ID"
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: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> 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: <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > 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: <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > 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" <[EMAIL PROTECTED]> > > > > > > > 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 <[EMAIL PROTECTED]> > > > To unsubscribe, e-mail > > > <[EMAIL PROTECTED]> > > > 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 <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
