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
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();
}
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 😉
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.
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();
}