MySQL last_insert_id() error
I have a connector that insert a row in an external mysql db that works correctly. Now, I wanted to extend such connector in order to return the id of the lastly inserted row (the mysql table has a auto_incremented field) which I want to save in a variable to use it for future editing within the same process (I do that in the output operation definition part of the connector). The groovy code looks like this:
def script = ""; script = "INSERT INTO visit (id, name, surname) VALUES (NULL, ' " + name + " ', ' " + surname + " ');"; script = script + "SELECT LAST_INSERT_ID();";
and the log returns the following error:
2017-10-16 12:13:32.306 +0200 SEVERE: org.bonitasoft.engine.execution.work.FailureHandlingBonitaWork THREAD_ID=89 | HOSTNAME=ubuntu | TENANT_ID=1 | The work [ExecuteConnectorOfActivity: flowNodeInstanceId = 20, connectorDefinitionName = createVisit] failed. The failure will be handled.
2017-10-16 12:13:32.330 +0200 SEVERE: org.bonitasoft.engine.execution.work.FailureHandlingBonitaWork THREAD_ID=89 | HOSTNAME=ubuntu | TENANT_ID=1 | org.bonitasoft.engine.core.connector.exception.SConnectorException : "PROCESS_DEFINITION_ID=8765235906381377328 | PROCESS_NAME=Visit | PROCESS_VERSION=1.0 | PROCESS_INSTANCE_ID=7 | ROOT_PROCESS_INSTANCE_ID=7 | FLOW_NODE_DEFINITION_ID=9119904366680108586 | FLOW_NODE_INSTANCE_ID=20 | FLOW_NODE_NAME=Step3 | CONNECTOR_IMPLEMENTATION_CLASS_NAME=createVisit | CONNECTOR_INSTANCE_ID=20 | org.bonitasoft.engine.connector.exception.SConnectorException: java.util.concurrent.ExecutionException: org.bonitasoft.engine.connector.exception.SConnectorException: org.bonitasoft.engine.connector.ConnectorException: 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 'SELECT LAST_INSERT_ID()' at line 1"
Could it be due to the fact that I perform two operations (INSERT and SELECT) within the same connector? I believe reading that only one SELECT per connector is possible, but maybe the combination INSER + SELECT also creates problems?
Thanks in advance.