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.
David
----- 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
to
> > MySql. I've had my bumps along the way, but all in all, I sort of like
the
> > 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
> > 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
where
> > I lock the table and then immediately execute a second query to get the
ID
> > just auto-incremented? Or do I just wing it by turning off the
> > auto-increment stuff and enforcing my own primary key using GUIDs
generated
> > by Java? I don't know.
> >
> > This seems like a reasonable common function . . . maybe this ought to
be a
> > 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 =
DriverManager.getConnection(url,login,password);
> >
> > 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,
Content) VALUES
> > ('"
> > + request.getParameter("Title")
+ "', '"
> > +
request.getParameter("Author") + "', '"
> > + request.getParameter("Email")
+ "', '"
> > +
request.getParameter("Content") + "')"
> > + "SELECT @@IDENTITY As
'Identity'";
> >
> > 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();
> >
> >
response.sendRedirect("http://bondstreet.media.mit.edu/memangles.jsp?MemeID=
> > " + 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.
>
>
