What's the Delphi equivalent of doing UpperCase with an InvariantCulture in Unicode Delphi versions? (XE and up)
What's the Delphi equivalent of doing UpperCase with an InvariantCulture in Unicode Delphi versions? (XE and up)
Background: an inherited Delphi project is giving errors `Field "id" not found` in IBX talking to Firebird 2.5 only on some Turkish systems for various database tables even though the tables have an ID field.
I think it's the same issue as https://www.hanselman.com/blog/UpdateOnTheDasBlogTurkishIBugAndAReminderToMeOnGlobalization.aspx
i.e.: Turkey has both dotless and dotted i/I characters which means you need to be careful doing case conversion.
The database has all identifiers in uppercase, but of course the inherited Delphi source code has a mix of uppercase/lowercase mess both in the SQL and Delphi (I've seen Pascal keywords like beGin in some of the sources).
Easiest solution would be to convert all SQL to uppercase in an invariant way at some central place.
https://www.hanselman.com/blog/UpdateOnTheDasBlogTurkishIBugAndAReminderToMeOnGlobalization.aspx
Background: an inherited Delphi project is giving errors `Field "id" not found` in IBX talking to Firebird 2.5 only on some Turkish systems for various database tables even though the tables have an ID field.
I think it's the same issue as https://www.hanselman.com/blog/UpdateOnTheDasBlogTurkishIBugAndAReminderToMeOnGlobalization.aspx
i.e.: Turkey has both dotless and dotted i/I characters which means you need to be careful doing case conversion.
The database has all identifiers in uppercase, but of course the inherited Delphi source code has a mix of uppercase/lowercase mess both in the SQL and Delphi (I've seen Pascal keywords like beGin in some of the sources).
Easiest solution would be to convert all SQL to uppercase in an invariant way at some central place.
https://www.hanselman.com/blog/UpdateOnTheDasBlogTurkishIBugAndAReminderToMeOnGlobalization.aspx
Which Delphi version?
ReplyDeleteUwe Raabe I've edited the post. Preferably XE and up. In this case XE8 and up would do, but not all projects are at XE8 yet.
ReplyDeleteThe single parameter overload of System.SysUtils.Uppercase should do an invariant conversion. On the other hand, even AnsiUpperCase should work, because it uses CharUpperBuf internally, which handles exactly this case correctly as stated in the remarks:
ReplyDeletehttps://msdn.microsoft.com/de-de/library/windows/desktop/ms647475(v=vs.85).aspx
The solution depends on if the current code incorrectly translates the lowercase i into that uppercase turkish uppercase I (actually with a dot) or not. Depending on that you might use AnsiUpperCase or use LCMapString directly. The RTL is kinda inconsistent in using what locale.
ReplyDeleteStefan Glienke right now, the SQL is sent over IBX to Firebird as is. i think that somewhere in that path, a case conversion takes place identifier comparison takes place which uses a wrong locale.
ReplyDeleteMy plan is to convert the SQL part (not the data part) to uppercase in a CultureInvariant way before sending it to IBX.
I need some plumbing to to first so I can do that in a central place; then I will try the suggestion by Uwe Raabe and get back on what worked.
On the four versions of I: en.wikipedia.org - Dotted and dotless I - Wikipedia
ReplyDeleteForgive me my naivety but isn't SQL case insensitive?
ReplyDeleteStefan Glienke apparently not all parts of SQL.
ReplyDeleteSome parts of Delphi are case sensitive as well.
Jeroen Wiert Pluimers And which ones would that be?
ReplyDeleteP.S. Aha, well SQL itself is case insensitive but its up to the dbms to be case sensitive or not about the identifiers (tables, columns, ...) which seems to be the case with your setup.
FWIW: http://www.firebirdfaq.org/faq76/
Stefan Glienke unit/filename, Register procedure and maybe by now some other pieces.
ReplyDeleteThanks for the link. I'd forgotten they actually documented that.
PostgreSQL has case sensitive db entities, too.
ReplyDeleteFortunately, MS SQL has not.
Still, non ascii chars in Entity names sounds like a problem waiting to jump you.
Lars Fosdal That depends on the server or database collation AFAIK. You can also turn an MSSQL database into being case sensitive.
ReplyDeleteLars Fosdal What I understand is that somewhere on the way from the query in source to the server appears an upper case conversion with Turkish locale. This would change a lower i to the Turkish upper dotted İ, which is not ASCII anymore. The problem is not the query text itself, but the handling later on.
ReplyDeleteStefan Glienke That is true, but you need your head examined if you turn case sensitivty on.
ReplyDeleteUwe Raabe The problem lies in the use of non-ascii characters in entity names. Since there are so many layers to db access (String -> FireDAC -> DB -> virtual view or table mapped through sys.synonyms and a linked server - which again may have a different collation - using non-ascii chars anywhere here, is a risk.
Lars Fosdal in this case, I think it's an i18n issue where either the case conversion results in a non-ASCII character (i becomes İ or I becomes ı), or a comparison issue where the ASCII characters i and I in the entity names are deemed unequal.
ReplyDeleteLars Fosdal I beg to disagree here: a lower case "i" or #$69 is a valid character for a database entity. Making it uppercase on a Turkish locale turns it into the non ASCII character "İ" or #$0130 instead of the normal "I" or #$49. I don't know where this conversion is actually done, though (may be IBX does that somewhere). The problem is that the Turkish language disagrees about an uppercase "i" with almost the rest of the world. BTW, it is the same the other way round: Given a plain ASCII capital "I" will also give a non-ASCII lower case character in Turkish.
ReplyDeleteJeroen Wiert Pluimers It should be fairly easy to figure out which one of the two it is? ASCII letter i (lower or upper) should not convert into a non-ASCII char through the use of lower/upper casing, afaik?
ReplyDeleteIs the problem in a select? Is it the parameter data which are converted incorrectly, or the table data?
Can you step a stored proc in a debugger to look at the actual values after a case conversion?
I think there is a method in the string helper for this
ReplyDeleteIs it an absolute comparison - or a like comparison?
ReplyDeleteUwe Raabe
ReplyDeleteCP857 has i/I and the various native variants
https://www.ascii-codes.com/cp857.html
ISO 8859-9 has i/I and the various native variants
en.wikipedia.org - ISO/IEC 8859-9 - Wikipedia
Are you saying that an ASCII i/I converts to a non ASCII character for lower/upper case? That would be an interesting design!
On the other hand...
ReplyDeletestackoverflow.com - Problems with Turkish SQL Collation (Turkish "I")
This little program shows the problem:
ReplyDeleteprogram Project211;
{$APPTYPE CONSOLE}
{$R *.res}
uses
System.SysUtils;
var
S: string;
begin
S := 'i';
S := S.ToUpper(1055); // Turkish locale
if S = 'I' then begin
Writeln('upper case "i" is "I" on locale 1055');
end
else begin
Writeln('upper case "i" is not "I" on locale 1055');
end;
Readln;
end.
That's... just wrong...
ReplyDeleteJeroen Wiert Pluimers Yes, str.ToUpperInvariant is what you are looking for I think
ReplyDeleteStefan Glienke it's very server and OS dependent: http://www.alberton.info/dbms_identifiers_and_case_sensitivity.html
ReplyDeleteLars Fosdal the problem is in statements like this:
ReplyDeleteInsert into MyTable (Id, ExternalID, DoubleValue, TextValue) values (:Id, :ExternalID, :DoubleValue, :TextValue)
It throws Exception EIBClientError at $0089F942: Field "id" not found
I'm working on an integration test app that does some permutations on these and get that to run in Turkey.
Jeroen Wiert Pluimers Perhaps another argument for the use of stored procedures for inserts and updates, since parameters can be passed by order instead of by name?
ReplyDeleteEdit: or not, since parameterized queries would have the same issues.
David Heffernan thanks; that one got introduced in XE3: docwiki.embarcadero.com - System.SysUtils.TStringHelper.ToUpperInvariant - XE3 API Documentation
ReplyDeleteIt's a bug in about a 100 places of UpperCase and ToUpper usage inside IBX that has been there since the Unicode migration.
ReplyDeleteThe reproduction of just one place: gist.github.com - IBDataSetTestsUnit.pas
ReplyDeleteCC ***** please have someone QP this.
ReplyDeleteDone! RSP-17061
ReplyDeletehttps://quality.embarcadero.com/browse/RSP-17061
It's not limited to getting parameters. At least a 100 upper and lower case conversions are affected. I've not yet checked comparison operationsike starts with.
ReplyDeleteIt seems I forgot to put the diff link here: gist.github.com - diff to make Delphi XE8 case conversions compatible with Turkish locale
ReplyDelete