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
> 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).
> MIT Media Lab
> Affective Computing Group
> PS: The current JSP code is:
> //load the class with the driver
> //set a timeout for login and query
> //open a connection to the database
> Connection connection = DriverManager.getConnection(url,login,password);
> //select a database
> //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
> int newMemeID = rs.getInt("Identity");
> //close the objects
> " + newMemeID);
IDENTITY == AUTO_INCREMENT
@@IDENTITY is probably equivalent to LAST_INSERT_ID(), but I am not
exactly sure. Read the manual.