List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:July 8 1999 4:59pm
Subject:Re: @@IDENTITY Support
View as plain text  
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:
> 
>         //load the class with the driver
>         Class.forName("com.inet.tds.TdsDriver").newInstance();
> 
>         //set a timeout for login and query
>         DriverManager.setLoginTimeout(10);
> 
>         //open a connection to the database
>         Connection connection = DriverManager.getConnection(url,login,password);
> 
>         //select a database
>         connection.setCatalog("Memangles");
> 
>         //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");
> 
>         //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)
Thread
RE: MySQL 4.0.18 has been releasedJohn Griffin13 Feb
  • Re: MySQL 4.0.18 has been releasedDan Nelson13 Feb