SQl Plugin - LAST_INSERT_ID()

[ericr999]

Hello,

Trying to use the SQL Plugin, I'm doing a insert into, and then I want to retrieve the id of that operation. But it always returns 0.

My guess is that the executeCustomQuery, vs readCustomQuery, is not in the same connection ? If I go directly into my MySQL server, and do the same insert, I get the id without any issue. But with VRO it doesn't work. I could extract the last id from the table, but that's not the best thing to do, is there something else I could do in my code to get the id ?

Here's my code:

var date = (new Date(Date.now() - ((new Date()).getTimezoneOffset() * 60000))).toISOString().slice(0, 19).replace('T', ' ');
var query = "INSERT INTO nas (project_id, creator_id, request_type_id, charge_io_id, stockage_type_id, service_id, operating_system_id," +
             "site_id, comments, status, servers, created_at, updated_at) VALUES ('" + project_id + "'," + demandeur_id + "," + request_type_id + "," +
             charge_io_id + "," + stockage_type_id + "," + service_id + "," + operating_system_id + "," + site_id + ",'" + comments + "','created','" +
             serveurs + "','" + date + "','" + date + "');";
System.log(query);
var response = execQuery(query,"exec");
System.log(response);
query = "SELECT LAST_INSERT_ID();";
var response = execQuery(query,"read");
System.log(response);
System.log(response[0].getProperty(response[0].getFieldNames()[0]));
/////////////////////////////////////////////////// function /////////////////////////////////////////////////////
function execQuery(query, type){
  try {
  if (type == 'exec'){
  var response = database.executeCustomQuery(query);
  } else if (type == 'read'){
  var response = database.readCustomQuery(query);
  } else {
  throw 'Query type invalide';
  }
  } catch (e) {
  throw 'Couldn\'t get a response from the database. Error: ' + e;
  }
  return response;
}

And the logs:

[2017-04-26 09:07:24.499] [I] INSERT INTO nas (project_id, creator_id, request_type_id, charge_io_id, stockage_type_id, service_id, operating_system_id,site_id, comments, status, servers, created_at, updated_at) VALUES ('536',23,36,39,42,45,80,63,'test','created','asd','2017-04-26 09:07:24','2017-04-26 09:07:24');
[2017-04-26 09:07:24.519] [I] 1
[2017-04-26 09:07:24.528] [I] DynamicWrapper (Instance) : [SQLActiveRecord]-[class com.vmware.o11n.plugin.database.ActiveRecord] -- VALUE : ActiveRecord: {LAST_INSERT_ID()=0}
[2017-04-26 09:07:24.531] [I] 0

[ChristianWehner]

What you can do is, create your query to include the LAST_INSERT_ID statement:

query = "INSERT INTO nas (project_id, creator_id, request_type_id, charge_io_id, stockage_type_id, service_id, operatin......; ";
query += "GO; SELECT LAST_INSERT_ID(); GO;";

This should return what you need 😉

[ericr999]

MySQL doesn't understand the go statement. But from what I learned, when I end the line with ;, it does the same thing.

So I tried your idea, of putting my insert...; select last_insert_id();

It works within the MySQL client, but from VRO, it doesn't work.

From what it seems the executeCustomQuery, cannot do select, if I need to run a select seems I can only do it with readCustomQuery.

[iiliev · Accepted solution]

You cannot mix selects and updates/inserts when using a single call to readCustomQuery()/executeCustomQuery()

For LAST_INSERT_ID() to work, you need to execute insert and select in context of a single database connection. Here is some sample code to try (note: you need to manually get a connection by providing your real database user/password):

var conn = (new JDBCConnection()).getConnection(database.connectionURL, "myuser", "mypass");
try {
  var stmt = conn.prepareStatement("INSERT INTO nas (...) VALUES (...);";
  stmt.execute();
  stmt = conn.prepareStatement("SELECT LAST_INSERT_ID();");
  System.log(st.executeQuery().first().getNumber("LAST_INSERT_ID()"));
} finally {
  conn.close();
}