Getting TFilterCriteria CaseSensitive to use LOWER rather than UPPER

rbuser

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

rbuser

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

nardmoseley

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

rbuser

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

nardmoseley

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


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