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

Reply via email to