Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

    SQL Server behaving differently with criteria in ds.xml files

    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)

    Code:
    <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.
    Last edited by rodp; 7 Feb 2023, 00:45. Reason: Edited to clarify that for us SQL Server is case sensitive to match Oracle.

    #2
    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.

    Comment


      #3
      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

      Comment


        #4
        Thanks Isomorphic, your reply has explained the change.

        Comment

        Working...
        X