Criteria being incorrectly quoted in generated SQL

Smart GWT Technical Q&A
1

We are using a nightly build of SmartGWTEE and when trying to set criteria for a fetch we get DB2 exceptions because an integer value is being quoted in the generated SQL.

We are using the sql connector with DB2 and have created a very simple criteria, namely:

 Criteria c = new Criteria();
 c.addCriteria("publisher_pkid", new Integer(549));
 dataSource.filterData(c);

The resulting SQL is:

SELECT AuthorizationDef.publisher_pkid, .AuthorizationDef.name, AuthorizationDef.pkid FROM AuthorizationDef WHERE (AuthorizationDef.publisher_pkid=‘549’)

publisher_pkid is an integer field, not text. The DB2 JDBC driver returns an error about the type mismatch.

The datasource is as follows:

<DataSource
ID=“authdef_DataSource”
serverType=“sql”
tableName=“edenmodel.AuthorizationDef”
dropExtraFields=“true”
>
<fields>
<field primaryKey=“true” name=“pkid” type=“integer” hidden=“true” />
<field name=“publisher_pkid” type=“integer” />
<field name=“name” type=“text” />

&lt;/fields&gt;

</DataSource>

I see the following in the log, no quoting of publisher_pkid in the criteria:

=== 2011-06-03 16:15:55,687 [l0-3] DEBUG RPCManager - Request #1 (DSRequest) payload: {
criteria:{
__gwt_ObjectId:13,
publisher_pkid:549
},
operationConfig:{
dataSource:“authdef_DataSource”,
operationType:“fetch”,
textMatchStyle:“substring”
},
appID:“builtinApplication”,
operation:“authdef_DataSource_fetch”,
oldValues:{
__gwt_ObjectId:13,
publisher_pkid:549
}
}

2

Could you please post the complete server-side log for the request (always post it this way please - details matter).

3

I thought I had provided the relevant details, which included the client code that set the criteria and the generated SQL that was submitted to DB2, but I have attached the server log as you requested.

I have since confirmed that if I specify a criteria using a character field it works as expected, though it does seem to converted to lower(field) like ‘%xxx%’ where all I have really specified is the “xxx”.

  • Joel -

SmartClientPreamble.txt (16.4 KB)

SmartClientSQLError.txt (16 KB)

4

Looks like you’re testing in Google Chrome Hosted Mode - don’t do this, it’s broken due to core GWT/Chrome bugs (see FAQ).

As far as the lowercase substring match, note textMatchStyle:“substring” in the logs and see the docs for DSRequest.textMatchStyle. If you need more exact operators or different operators per field, see AdvancedCriteria.

Finally, the versions we’ve tested don’t care about quotes around a numeric value - can you tell us:

  1. what exact version of DB2 is this?
  2. where exactly did you download the JDBC driver from and what’s its version?
  3. what’s the SQL schema for this column?
5

Turns out you are exactly correct. For development we use OS X, and the only version of DB2 available is a 9.5 beta. I just tried a query against our 9.7.2 version running on a linux server and it happily accepts numeric values in quotes.

I will switch over to running against a linux version since IBM has shown no interest in maintaining the OS X version of DB2.

I will also take note of your other suggestions about Chrome. Since we will switch to a modern release of DB2 we probably don’t need to worry about advanced queries for now.

Thank you for your prompt attention and resolution of these issues !

  • Joel -