Issue with MSSQL LIKE Query and Escape Characters in SmartGWT EE 13.1p Release (2025-07-23)

[kvkamesh]

Dear Team,

We are encountering an issue with the smartgwtee-13.1p_release_2025-07-23 build. Specifically, when executing MSSQL queries using the LIKE operator, escape characters (\) are being automatically added before underscore (_) characters.

This behavior is causing our query execution to fail.

Logger statements for reference:

2025-07-25 16:35:34,050 INFO [qtp1239912604-29] (com.isomorphic.datasource.DSRequest:94) - Executing sysCodeMasterTbl->sysCodeMaster.fetch rows: 0->30 with
criteria: {code_type:"OGA_APPROVAL_PERIOD"}
2025-07-25 16:35:34,050 INFO [qtp1239912604-29] (com.isomorphic.datasource.DSRequest:94) - Executing sysCodeMasterTbl->sysCodeMaster.fetch rows: 0->30 with
criteria: {code_type:"OGA_APPROVAL_PERIOD"}
2025-07-25 16:35:34,052 INFO [qtp1239912604-29] (com.isomorphic.sql.SQLDataSource:94) - derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause ORDER BY DESCRIPTION_ENG ASC
2025-07-25 16:35:34,052 INFO [qtp1239912604-29] (com.isomorphic.sql.SQLDataSource:94) - derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause ORDER BY DESCRIPTION_ENG ASC
2025-07-25 16:35:34,054 INFO [qtp1239912604-29] (com.isomorphic.SQL:94) - Executing SQL query on 'SQLServer': SELECT COUNT(*) FROM TTFB_SYS_CODE_MASTER WHERE (TTFB_SYS_CODE_MASTER.code_type LIKE '%OGA\_APPROVAL\_PERIOD%' AND TTFB_SYS_CODE_MASTER.code_type IS NOT NULL)
2025-07-25 16:35:34,054 INFO [qtp1239912604-29] (com.isomorphic.SQL:94) - Executing SQL query on 'SQLServer': SELECT COUNT(*) FROM TTFB_SYS_CODE_MASTER WHERE (TTFB_SYS_CODE_MASTER.code_type LIKE '%OGA\_APPROVAL\_PERIOD%' AND TTFB_SYS_CODE_MASTER.code_type IS NOT NULL)
2025-07-25 16:35:34,112 INFO [qtp1239912604-29] (com.isomorphic.datasource.DSResponse:94) - DSResponse: List with 0 items
2025-07-25 16:35:34,112 INFO [qtp1239912604-29] (com.isomorphic.datasource.DSResponse:94) - DSResponse: List with 0 items


Could you please advise on a possible fix or workaround for this issue?

Thank you for your support.

[Isomorphic]

Please see the Debugging overview in the reference to understand the information you need to share to make it possible for someone to help you.

[kvkamesh]

Now query generated like this:
2025-07-25 16:35:34,054 INFO [qtp1239912604-29] (com.isomorphic.SQL:94) - Executing SQL query on 'SQLServer': SELECT COUNT(*) FROM TTFB_SYS_CODE_MASTER WHERE (TTFB_SYS_CODE_MASTER.code_type LIKE '%OGA\_APPROVAL\_PERIOD%' AND TTFB_SYS_CODE_MASTER.code_type IS NOT NULL)


Expected query should be:
2025-07-25 16:35:34,054 INFO [qtp1239912604-29] (com.isomorphic.SQL:94) - Executing SQL query on 'SQLServer': SELECT COUNT(*) FROM TTFB_SYS_CODE_MASTER WHERE (TTFB_SYS_CODE_MASTER.code_type LIKE '%OGA\_APPROVAL\_PERIOD%' ESCAPE '\' AND TTFB_SYS_CODE_MASTER.code_type IS NOT NULL)

Let me know, if you need any further information

[Isomorphic]

Yup, definitely need further information - as previously indicated, please take a look at the Debugging topic to understand the information that would be needed to allow people to help you.

You seem to be doing some kind of upgrade - this would be a good time to buy Hourly Support if you need some help.

https://smartclient.com/services/#support

As far as the Free support we're offering here - please expect that we will basically just point you to the docs unless you create a clear, standalone test case showing that there is a bug in the framework.

[kvkamesh]

Dear Team,

Upon debugging the isomorphic-sql.jar, I have identified an issue in the SQLWhereClause.class file. Specifically, within the relevant method, the else block is missing a call to driver.escapeClause(value). This call is present in the if block to support the {ESCAPE '\'} clause for LIKE operations.

This omission may be contributing to the current issue we are facing.

Please let me know if further details are needed.


private String substringFilter(String lvalue, Object rvalue, SQLDriver driver, boolean isNumber, SQLDataSource ds, DSField field) {
boolean likeIsCaseSensitive = field != null && field.containsKey("sqlForceInsensitive") ? field.getBoolean("sqlForceInsensitive") : (field != null && field.containsKey("sqlLikeIsCaseSensitive") ? field.getBoolean("sqlLikeIsCaseSensitive") : driver.likeIsCaseSensitive());
if (likeIsCaseSensitive) {
String sql = driver.caseInsensitiveStrategy() == 2 ? "UPPER(" : "LOWER(";
if (driver.castNumbersBeforeLikeCompare() && isNumber) {
sql = sql + "CAST(" + lvalue + " AS varchar(50))";
} else {
sql = sql + lvalue;
}

String compare = rvalue.toString();
if (driver.caseInsensitiveStrategy() == 2) {
compare = compare.toUpperCase();
} else {
compare = compare.toLowerCase();
}

return sql + ") " + driver.caseInsensitiveLikePredicate() + " " + driver.sqlFilterTransform(compare, field, ds, this.filterStyle) + driver.escapeClause(compare) + " AND " + lvalue + " IS NOT NULL";
} else {
String sql;
if (driver.castNumbersBeforeLikeCompare() && isNumber) {
sql = "CAST(" + lvalue + " AS varchar(50))";
} else {
sql = lvalue;
}

String escapedValue = driver.escapeValueForFilter(rvalue.toString(), this.filterStyle, !this.dsRequest.usePreparedStatements());
return sql + " " + driver.caseInsensitiveLikePredicate() + " " + this.prepareSQLParam(ds, driver, field, escapedValue) + " AND " + lvalue + " IS NOT NULL";
}
}

[Isomorphic]

Please revisit the Debugging topic to understand what you need to provide in order to make it possible to help you.

As a hint, random bits of decompiled code is not what the Debugging topic advises you to provide. We need to see a standalone test case showing that the framework is not doing what it is documented to do.

[kvkamesh]

I'm not familiar with the "Debugging topic" you mentioned. Could you please share a reference or link to it? That would help me better understand what information is expected and how I can provide a more useful test case.

Appreciate your guidance.

[Blama]

Hi kvkamesh,

you find it here under "Debugging". All the docs are a good read. For good bug reports see "If you believe you've discovered a bug in Smart GWT" in the "Debugging"-docs.

Best regards
Blama

[kvkamesh]

Thank you very much Blama