Servoy Forum

Hi Everyone. I am moving some functions from a servoy 5.x codebase to 8.x and I'm having issues with one of the three implementations of databaseManager.getDataSetByQuery (the implementation which takes a straight string as the query args being serverName: string, query: string, args: array, limit: int)

var query = 'select tenant.tenant_id '
query += 'FROM tenant, staff WHERE tenant.tenant_id = staff.tenant_id AND staff.is_active = 1 AND tenant.is_active = 1 '
query += 'AND staff.user_name = ?'
query += ' AND tenant.minor_id = ?';

var args = new Array();
args[0] = fvUserName
args[1] = fvSiteID

var dataset = databaseManager.getDataSetByQuery('cesoft', query, args, 1);

When that query gets run I'm getting the following console error:

ERROR com.servoy.j2db.util.Debug - select tenant.tenant_id FROM tenant, staff WHERE tenant.tenant_id = staff.tenant_id AND staff.is_active = 1 AND tenant.is_active = 1 AND staff.user_name = ? AND tenant.minor_id = ? parameters: ['param1' ,type: java.lang.String, 'param2' ,type: java.lang.String]
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 'OPTION SQL_SELECT_LIMIT=2' at line 1

I'm actually thinking this is a JDBC driver issue except for the fact that the LIMIT is being indexed automatically somewhere (note that the limit was set as 1 in the call to databaseManager, but the error above has LIMIT=2; in fact it's always showing N+1 whatever limit N we use)

FYI this is the same DB and MySQL DB driver as I am using in 5.x
I've tried both the org.gjt.mm.mysql.Driver and the com.mysql.jdbc.Driver

Any advice appreciated

Seems it was a driver issue.

I upgraded from mysql-connector-java-5.1.7-bin.jar to mysql-connector-java-5.1.47-bin.jar and used the org.gjt.mm.mysql.Driver, no more MySQL error.
This is with MySQL version 5.7.21