Getting the ID of an INSERT in mysql

sicruise

I'm using a JDBC connector to talk to "MySQL 4.1.14"

At the moment to return the ID I am performing an Insert followed by

SELECT MAX(ID) FROM TABLE;

This works fine for now but it is not future proof... obviously if a transaction happens before I call the MAX(ID) then this will not return the ID for the original Insert.

SELECT LAST_INSERT_ID(); only returns 0 for me... but it would have the same result as above, with increased traffic errors will occur.

So basically, does anyone know how to return the value of the autoincrement column of the record you are inserting?

daymobrew

I haven't read the stuff at the following two links but they seem to deal with what you are asking about.
http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-basic.html#connector-j-usagenotes-last-insert-id
http://www.shiloff.net/mysql_5_1_documentation/connectors.html#cj-retrieve-autoinc

pH

If the table has a column with AUTO_INCREMENT then and stmt is a prepared statement which is an insert for that table then:

    public int performInsert(PreparedStatement stmt) {
        
        try{
            stmt.executeUpdate();

             ResultSet rs = stmt.getGeneratedKeys();
             if (rs.next()) {
                 return rs.getInt(1);
             } else
                 throw new RuntimeException("MYSQL : No generated keys.");
        } catch (SQLException ex){
            throw new RuntimeException("SQLINSERT[" + stmt.toString() + "]",ex);
        }
    }

is_that_so

You might find the code below useful as well.

function insertRec($db_query)
{
mysql_query("LOCK TABLES table_name WRITE;");
mysql_query($db_query); //table insert here
$ID=mysql_insert_id();
mysql_query("UNLOCK TABLES");
return $ID;
}

cgarvey

sicruise wrote:

So basically, does anyone know how to return the value of the autoincrement column of the record you are inserting?

INSERT INTO user( id, name ) VALUES( NULL, 'user1' );
SELECT MAX( last_insert_id() ) FROM user;

last_insert_id() is connection/session-safe.

sicruise

Thanks a million for that PH ... I never noticed that method before...

Works a treat!

Thanks everyone else too... was racking my brain for a good while on this