Problems with LAST_INSERT_ID() and mysql_insert_id()

Question

I am trying to create 2 MySQL statements that will insert a new row in one column and reference that same row in another, like this:

In case you need to know, I am using Java/com.mysql.jdbc.Driver

insertNewMessage = connection
        .prepareStatement(new StringBuilder(128)
                .append("INSERT INTO messages (Message,SHA256) VALUES (?,?);")
                .append("INSERT INTO messagelog (MESSAGE_ID,SERVER_ID,CLIENT_ID) VALUES ((SELECT LAST_INSERT_ID()),?,?)")
                .toString());

So it make it clearer, my SQL statement is:

INSERT INTO messages (Message,SHA256) VALUES (?,?);
INSERT INTO messagelog (MESSAGE_ID,SERVER_ID,CLIENT_ID) VALUES ((SELECT LAST_INSERT_ID()),?,?)

I want to take the new ROWID value created in the messages table and insert it into the messagelog table.

I am finding LAST_INSERT_ID(), mysql_insert_id() both not working. I get the following exception thrown:

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO messagelog (MESSAGE_ID,SERVER_ID,CLIENT_ID) VALUES ((SELECT LAST_INS' at line 1

In the documentation for MYSQL, it sounds like Last_Insert_ID should work, but I am guessing there are caveats. I don't know what those caveats are. (Yes A.I is set on the ROWIDs)

You are attempting to cram two SQL queries into one. You can't do that unless you've specified `allowMultiQuery=true` for the connection. See: http://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement — Brian Roach, Jun 26 '13 at 17:56
Adding that didn't work, but it seems like the way to go is creating a store procedure. — E.S., Jun 26 '13 at 18:10

score 1 · Answer 1 · answered Jun 26 '13 at 17:53

1

You shouldn't be calling SELECT like that inside a VALUES clause.

The correct statement is pretty simple, you just call the function directly:

INSERT INTO messagelog (MESSAGE_ID,SERVER_ID,CLIENT_ID) VALUES (LAST_INSERT_ID(),?,?)

Additionally it looks like you're creating a compound statement with two INSERT queries combined into one. You will need to execute these sequentially. LAST_INSERT_ID() is specific to your connection, so be sure your database layer doesn't execute these two calls on independent connections.

answered Jun 26 '13 at 17:53

tadman

208,517
23
234
262

As I said, I tried 4 combinations in my testing. But I think you answered it, I need to separate this out into two separate calls. Is there any way around that though? These two tables are linked so it'd be nice to just have one call to handle both inserts... – E.S. Jun 26 '13 at 18:07
1

@EricS - rather than worrying about doing them in the same call, just do them both as separate calls but in the same transaction. – Eric Petroelje Jun 26 '13 at 18:10
@EricP, that is a very good idea actually. I suppose I wanted to make my code simpler on the Java end. – E.S. Jun 26 '13 at 18:11

score 0 · Answer 2 · answered Jun 26 '13 at 21:18

try this:

connection.createStatement().executeUpdate("INSERT INTO blablabla");
ResultSet rs = connection.createStatement().executeQuery("select last_insert_id() as mID");
if(rs.next()){
  connection.createStatement().executeUpdate(
    "INSERT INTO blablabla VALUES(" + rs.getString("mID") + ",blablabla");
}

Problems with LAST_INSERT_ID() and mysql_insert_id()

2 Answers2