ericr999
Enthusiast
Enthusiast

SQl Plugin - LAST_INSERT_ID()

Jump to solution

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

0 Kudos
1 Solution

Accepted Solutions
iiliev
VMware Employee
VMware Employee

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();

}

View solution in original post

0 Kudos
3 Replies
ChristianWehner
VMware Employee
VMware Employee

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 😉

0 Kudos
ericr999
Enthusiast
Enthusiast

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.

0 Kudos
iiliev
VMware Employee
VMware Employee

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();

}

0 Kudos