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