From: David Wall
Date: July 8 1999 5:18pm
Subject: Re: @@IDENTITY Support
List-Archive: http://lists.mysql.com/java/184
Message-Id: <004201bec965$dfb036e0$562b7ad8@expertrade.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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.
>
>