6.2.5 JDBC MySQL Connector - Cannot SELECT

Q&A
1

I’ve created a simple process using the standard, out-of-the box JDBC MySQL connector. I entered a simple SELECT statement in the connector, and I get the following error:

org.bonitasoft.engine.connector.exception.SConnectorException: org.bonitasoft.engine.connector.ConnectorException: org.bonitasoft.engine.connector.ConnectorException: java.sql.BatchUpdateException: Can not issue SELECT via executeUpdate().

Strangely, any INSERT statements work just fine.

I’ve researched for hours to no avail. I’m assuming I have some kind of configuration problem, or perhaps system incompatibility?

BOS installed from the BonitaBPMCommunity-6.2.5 bundle. MySQL version 5.5.37. Tested on both Ubuntu 14 and MacOS.

2

I have the same problem and I find out I have “;” inside the “Command separator” field when I create the MySQL connector. I remove the “;” and it works for me.
If you have “;” defined inside your MySQL connector, try remove it.

Marcos

3

Hi,

I have the same problem with 6.3.1.
In my query I insert a new line in a table and then I select max(id) from this table.

INSERT INTO table (col1, col2) VALUES (val1, val2); SELECT max(id) FROM table

org.bonitasoft.engine.connector.ConnectorException: java.sql.BatchUpdateException: Can not issue SELECT via executeUpdate().

I try Marcos’s solution but it doesn’t work for me :
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 max(id) FROM table’ at line 2

4

This query works if I copy/paste it in phpmyadmin.
It doesn’t work only in Bonita mysql connector.

5

Hello, this must be related to the fact that JDBC batch execution cannot handle SELECT. SELECT queries return a resultset while executeBatch returns an array of int corresponding to the number of updated records for each statement. Therefore, you can not put a SELECT query as a member of a batch update.

6

Ok.
I cut my query into two successive connectors to avoid this problem.