SQL Server behaving differently with criteria in ds.xml files

[rodp]

Thanks Isomorphic, your reply has explained the change.

[Blama]

Hi Isomorphic,

I read this thread and read the docs. It seems that this change is also in 12.1p.

Additionally I noticed, that this feature never made it into the docs - a search for useILike does not bring up any results in the SmartClient 12.1 docs. I think this should definitely be in the docs, as it will help a lot when using Postgres.

Best regards
Blama

[Isomorphic]

There indeed were changes related to this made to SGWT/SC 13.0. Please see the section in this help topic titled, "sql.forceInsensitivity and sql.MyDatabase.forceInsensitivity" as it should resolve your questions.

[rodp]

Noticed on SmartClient Version: v13.0p_2022-06-09/PowerEdition Deployment (built 2022-06-09) but still occurring on latest (2023-02-04).

Our product runs with Oracle and SQL Server databases, so we set SQL Server collation to make comparisons case-sensitive to match Oracle. We have some criteria in ds.xml files, the following example is to only select records which are current (the table holds history records as well)

<operationBindings>
    <operationBinding operationType="fetch">
        <criteria fieldName="EFFECTIVE" value="C"/>
    </operationBinding>
</operationBindings>

This used to result in a SQL statement containing "LOWER(EFFECTIVE) LIKE LOWER(‘%c%’)" as part of the Where clause, and for Oracle it still works that way. However, for SQL Server the SQL now has "EFFECTIVE LIKE %c%", i.e. the LOWERs are no longer included, which means the query doesn't return rows because the Effective column has upper-case C's.

I've fixed it by adding operator=”equals” to the criteria which gives SQL "EFFECTIVE = 'C'".

I'm mentioning it because I'd like to know whether SQL Server is working as expected, or is this a bug? If as expected it will be working differently to Oracle. We have a fix (and should probably have coded the ds.xml that way anyway).

Thanks.