Error in function TSQLTableToDataSet

sjerinic · 2013-04-17 16:15:12

Hi!

I found error in function TSQLTableToDataSet when translates UTF8 strings. In my case any Cyrilic letter translated into "?" because UTF8 fields treated like strings.
For solution I added two lines in unit mORmotVCL.pas:

with result.FieldDefs do begin
   Types[F].SQLType := aTable.FieldType(F,@Types[F].EnumType);
   case Types[F].SQLType of
      sftBoolean:
         Add(aFieldName,ftBoolean);
      sftInteger:
         Add(aFieldName,ftLargeint); // LargeInt=Int64
      ...
      ...
      ...
      sftUTF8Text:           //NEW DEFINITION - LINE 140
         Add(aFieldName,ftWideString,aTable.FieldLengthMax(F,true));
      else
         Add(aFieldName,ftString,aTable.FieldLengthMax(F,true));
      end;
   end;

// and changed line 172:
      sftUTF8Text:
        result.Fields[F].AsString := UTF8ToString(aTable.GetU(i,F));
//        result.Fields[F].AsString := String(aTable.GetS(i,F));

Should I create a new ticket for this bug?

ab · 2013-04-17 18:30:52

IMHO this is not a bug, but a feature.

Using string does make sense since it is the default kind of text for the VCL.
If Windows is set in WinAnsi1251 code, Cyrillic letters will be translated as expected.

With Delphi 2009 and up, this does not make sense to use WideString.

We made add a parameter for older versions, to force WideString instead of AnsiString.
But this ticket will be a feature request, not a bug report.

sjerinic · 2013-04-17 22:28:11

However, the bug or feature, I guess I'm not the only one who needs to see letters from several code pages at the same time. Thanks!

ab · 2013-04-18 08:49:45

So please create a ticket for it!

sjerinic · 2013-04-18 20:20:41

Ticket ID is: 2970335e406c1feb64d206972371d265b6ad79c9

Thanks!

ab · 2013-04-19 06:33:03

Implemented as an optional aForceWideString parameter.

See http://synopse.info/fossil/info/61d5bf3b2d

sjerinic · 2013-04-20 21:43:27

Unfortunately the problem is still present.

I'll try explain with sample:

In MSSQL database I have nvarchar fields which have data for few languages. For example, field can has values:

English: "Company SDCCZ"
Serbian Latin: "Kompanija ŠĐČĆŽ"
Serbian Cyrillic: "Компанија ШЂЧЋЖ"

My regional settings are English USA - for customers I don't know.
With string field in DataSet and aTable.GetS(i,F) in DBGrid data look like:

English: "Company SDCCZ"
Serbian Latin: "Kompanija Š???Ž"
Serbian Cyrillic: "????????? ?????"

If I remove IFDEF, IFNDEF directives and set aForceWideString = true, in your last update, everything is ok and in DBGrid I have:
English: "Company SDCCZ"
Serbian Latin: "Kompanija ŠĐČĆŽ"
Serbian Cyrillic: "Компанија ШЂЧЋЖ"

Probably Delphi xe3 DataSet strings are not real unicode
Could you remove IFDEF/IFNDEF directives?

ab · 2013-04-21 07:42:00

XE3 is real Unicode from the ground up, with its string=UnicodeString.

The error was in the mORMotVCL code.
There were some warnings at compilation.
We used aTable.GetS() instead of aTable.GetString().

Now it is working: http://synopse.info/fossil/info/4e8cd5a87a

Sorry for the inconvenience.

sjerinic · 2013-04-21 20:53:06

And I must say sorry, but still it is not OK. Last time I forgot to wrote to you that in test I used GetString instead GetS function. Now from mORMot side everything is OK, but problem is in Delphi.

Result.Fields[F].AsString call DataDB.pas procedure TStringField.SetAsString(const Value: string)

procedure TStringField.SetAsString(const Value: string);
begin
SetAsAnsiString(AnsiString(Value));
end;

AnsiString(Value) call System.pas procedure _LStrFromPWCharLen(var Dest: _AnsiStr; Source: PWideChar; Length: Integer; CodePage: Word);

_LStrFromPWCharLen variable Source is ok, but in line CharFromWChar(Pointer(Dest), DestLen, Source, Length, CodePage); variable Dest is not like Source. Probably because of CodePage 1252.
In this line Serbian Latin is: "Kompanija ŠDCCŽ" instead "Kompanija ŠĐČĆŽ" and Serbian Cyrillic: "????????? ?????" instead "Компанија ШЂЧЋЖ".

From that reason I told you that Delphi probably is not "pure" unicode. Maybe it is normal behavior but problem exist. DataSet in string fields can not show all unicode letters without changed code page. Wide string may be outdated, but it doing the job.

At this point my solution is:
Line 157: Add(aFieldName,ftString,aTable.FieldLengthMax(F,true));
Changed with line: Add(aFieldName,ftWideString,aTable.FieldLengthMax(F,true));

or

remove {$ifndef UNICODE} directives.

How is going, JSON field UTF8 must be datset wide string field.

ab · 2013-04-22 05:17:05

You are right... in fact DB.pas is broken, or at least, its nomenclature is broken.

With Delphi 2009+, the AsString method is a synonymous for AsAnsiString, and AsWideString means in fact AsUnicodeString.
Since string=UnicodeString in Delphi 2009+, this naming does not make any sense!

So I've fixed the code to let Delphi 2009+ always use the TWideString / AsWideString version.
It won't use WideString withing, but plain UnicodeString.
See http://synopse.info/fossil/info/c6ef9c1d0f

The people how wrote the DB.pas migration at Embarcadero did just a bad job, IMHO.
So much confusing, in respect to the rest of the VCL.
I'm quite sure we are not the only one to have this misunderstanding!
See for instance http://stackoverflow.com/questions/9459186
I wrote a blog article about this.
http://blog.synopse.info/post/2013/04/2 … m-confused

Thanks for your feedback and patience!

sjerinic · 2013-04-23 17:58:06

Thank you!

It was a pleasure to participate in solving this problem.

mORMot Open Source

#1 2013-04-17 16:15:12

Error in function TSQLTableToDataSet

#2 2013-04-17 18:30:52

Re: Error in function TSQLTableToDataSet

#3 2013-04-17 22:28:11

Re: Error in function TSQLTableToDataSet

#4 2013-04-18 08:49:45

Re: Error in function TSQLTableToDataSet

#5 2013-04-18 20:20:41

Re: Error in function TSQLTableToDataSet

#6 2013-04-19 06:33:03

Re: Error in function TSQLTableToDataSet

#7 2013-04-20 21:43:27

Re: Error in function TSQLTableToDataSet

#8 2013-04-21 07:42:00

Re: Error in function TSQLTableToDataSet

#9 2013-04-21 20:53:06

Re: Error in function TSQLTableToDataSet

#10 2013-04-22 05:17:05

Re: Error in function TSQLTableToDataSet

#11 2013-04-23 17:58:06

Re: Error in function TSQLTableToDataSet

Board footer