You might also check out the lastet MM (Mark Matthews) JDBC driver at
http://www.worldserver.com/mm.mysql He has an extension that will let you
get that right away, but you can also do a second query after the insert to
get it, using the function Sasha mentioned.
----- Original Message -----
From: Sasha Pachev <sasha@stripped>
To: Carson Reynolds <carsonr@stripped>
Cc: MySql Help <mysql@stripped>; Java MySql Help
<java@stripped>; General SQL <general-sql@stripped>
Sent: Thursday, July 08, 1999 9:59 AM
Subject: Re: @@IDENTITY Support
> Carson Reynolds wrote:
> > I am in the process of migrating an application off of MS SQL Server 7.0
> > MySql. I've had my bumps along the way, but all in all, I sort of like
> > comparative simplicity, and anti-bloat-ware constructions.
> > But I've got this one hitch. The application I'm creating needs to do
> > something a little odd. When I insert a new record to one of my tables i
> > need to immediately get back the auto-incremented primary key. Microsoft
> > supports this using the @@IDENTITY keyword
> > <http://msdn.microsoft.com/library/sdkdoc/sql/globals_7.htm>.
> > So I have a little Java which executes the following (example) query:
> > INSERT INTO Memes (Title, Author, Email, Content)
> > VALUES ('A Meme Title', 'Carson Reynolds', 'carsonr@stripped',
> > Blah . . .')
> > SELECT @@IDENTITY As 'Identity'
> > Okay, so it seems (as far as I can tell) that MySql does not support
> > @@IDENTITY. What other options do I have? Can I do some sort of thing
> > I lock the table and then immediately execute a second query to get the
> > just auto-incremented? Or do I just wing it by turning off the
> > auto-increment stuff and enforcing my own primary key using GUIDs
> > by Java? I don't know.
> > This seems like a reasonable common function . . . maybe this ought to
> > feature request (grin).
> > Cheers,
> > -carson-
> > MIT Media Lab
> > Affective Computing Group
> > www.media.mit.edu/~carsonr
> > PS: The current JSP code is:
> > file://load the class with the driver
> > Class.forName("com.inet.tds.TdsDriver").newInstance();
> > file://set a timeout for login and query
> > DriverManager.setLoginTimeout(10);
> > file://open a connection to the database
> > Connection connection =
> > file://select a database
> > connection.setCatalog("Memangles");
> > file://create a statement
> > Statement st = connection.createStatement();
> > // Query to insert new meme
> > String newMeme = "INSERT INTO Memes (Title, Author, Email,
> > ('"
> > + request.getParameter("Title")
+ "', '"
> > +
request.getParameter("Author") + "', '"
> > + request.getParameter("Email")
+ "', '"
> > +
request.getParameter("Content") + "')"
> > + "SELECT @@IDENTITY As
> > ResultSet rs = st.executeQuery(newMeme);
> > // [SNIP] unrelated code you don't care about
> > // Get memeID for meme you just inserted
> > rs.next();
> > int newMemeID = rs.getInt("Identity");
> > file://close the objects
> > st.close();
> > connection.close();
> > " + newMemeID);
> IDENTITY == AUTO_INCREMENT
> @@IDENTITY is probably equivalent to LAST_INSERT_ID(), but I am not
> exactly sure. Read the manual.
> Sasha Pachev
> http://www.sashanet.com/ (home)
> http://www.direct1.com/ (work)
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail java-thread182@stripped
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail java-unsubscribe@stripped instead.