databaseManager.getDataSetByQuery() is creating bad query

Discuss all problems you have with Servoy here. It might help to mention the Servoy version and Operating System version you are using

databaseManager.getDataSetByQuery() is creating bad query

Postby antonio » Wed Jul 17, 2019 5:22 am

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
Tony
Servoy 8 - 2022.03 LTS
antonio
 
Posts: 638
Joined: Sun Apr 02, 2006 2:14 am
Location: Australia

Re: databaseManager.getDataSetByQuery() is creating bad quer

Postby antonio » Wed Jul 17, 2019 9:14 am

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
Tony
Servoy 8 - 2022.03 LTS
antonio
 
Posts: 638
Joined: Sun Apr 02, 2006 2:14 am
Location: Australia


Return to Discuss possible Issues and Bugs

Who is online

Users browsing this forum: No registered users and 5 guests