Home RAP
New Blog Posts: Merging Reports - Part 1 and Part 2

Getting TFilterCriteria CaseSensitive to use LOWER rather than UPPER

edited June 2009 in RAP
RB 10.07 Delphi 7

When I set the "TFilterCriteria" CaseSensitive property to false the query
has the SQL UPPER added. This is giving me problems with Russian characters.
If I past the generated query into Borland's Database Explorer I can see
that the problem isn't to do with Report Builder. If I change the query to
use "LOWER" rather than UPPER it works. My question is, which Report Builder
unit does the "UPPER" get set. I won't to change the source so that I can
change it to use LOWER.

Ian

Comments

  • edited June 2009
    I have found the unit that sets the "UPPER" value in the SQL sting. Once I
    changed this to "LOWER" my Russian problem went away. I would like to point
    out that in your code you use the Delphi command "UpperCase" This doesn't
    work with Russian characters so the command "AnsiUpperCase" should be used
    instead. If you step into the Delphi source for the UpperCase command, you
    will see that it looks for characters in the range "a" to "z". These
    characters obviously don't appear in Russian so the command fails to convert
    the text.

    Many thanks,

    Ian

  • edited June 2009

    Can you specify what unit(s) are causing the issue? Or how should I go about
    researching this?


    --
    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited June 2009
    Hello,

    The unit I changed is called "daSQL". In this unit there is a function
    called "FormatValue" which is inside the "ResolveCriteria" function. In here
    there is an "If" statement that checks the "aCriteria.CaseSensitive". The
    line directly after this is "Result := Uppercase(Result)". This should be
    changed to "Result := AnsiUppercase(Result);". If you step into the
    "UpperCase" function (it's in the SysUtils unit) you will see that it checks
    each letter of the passed in string to see if it falls between "a" and "z".
    This doesn't work with Russian characters so the resulting string isn't
    changed. If you look at the Delphi help for "UpperCase" it recommends using
    the AnsiUpperCase function. I don't know if the "UpperCase" function has
    changed with the Unicode support in the latest Delphi IDE. It may be that
    this is no longer an issue ? I'm using RB 10.07 and Delphi 7.

    After I made this change I still had problems. When I changed
    "AnsiUpperCase" to "AnsiLowerCase" and changed the follow line "lsField :=
    'UPPER('+ lsField + ')'" in the "daSQL.ResolveCriteria" function to
    "lsField := 'LOWER('+ lsField + ')', the problem went away. By problem, I
    mean that a result set was returned. I don't think this is a problem with RB
    as I saw the same problem when I pasted the resulting SQL in Database
    Explorer. The way to reproduce the problem is to set your locale settings to
    Russian and use the virtual keyboard to enter mixed case Russian text in a
    table. I then ran a simple TQuery to execute a Select statement and used the
    AnsiUpperCase to convert part of the query parameters. You will see that the
    combination of SQL UPPER and AnsiUpperCase fail but LOWER and AnsiLowerCase
    work, all very strange but this is why I had to make the changes in the
    daSQL unit.

    I hope that is a bit clearer, let me know if you need more details. Maybe
    another property that defines if the case insensitive feature uses Upper or
    Lower would be handy.

    Ian



  • edited June 2009

    Thanks for the additional detail. I modified daSQL, FormatValue to use
    AnsiUpperCase going forward.


    --
    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
This discussion has been closed.