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)
