German Umlaute Encoding Issue

[BastiFantasti]

Hi all,

first of all the versions I'm using:

Delphi 10
zeos testing-7.3
FreeTDS from zeos testing
MS SQL Server 2008 (SP2?)

currently I'm facing an issue with the migration from my BDE database to MS SQL Server with the German Umlaute ä,ö,ü
I know this issue has been discussed in severel threads and a lot of possible solutions have been posted, but none is working for me.
I already found out, that the sybdb.dll is ANSI based and that there are some tricks and hooks needed to correctly convert unicode strings.
I've tried both version of sybdb.dll with and without iconv support. And I also tried to declate the varchar fields with nvarchar - but no luck.

When I insert a SQL statement containing special characters I always get this error;

DBError [2402]: Error converting characters into server's character set. Some characters could not be converted...
Incorrect syntax near 'B'.

When I manually replace the a,ö,ü with ae,oe,ue it works. But that's not the way it should be...

As connection parameters I use:

Code: Select all

ClientCodepage=UTF8,
ControlsCodePage=cCP_UTF16,
AutoEncodeStrings=ON

I've also tried it with

Code: Select all

ClientCodepage=UTF8,
ControlsCodePage=cCP_UTF8,
AutoEncodeStrings=OFF

In addition I've added all UTF8 related tags I could find.

Code: Select all

    // hier das Zeos UTF8 Zeugs
    Properties.Clear;
    Properties.Add('character_set_client=utf8');
    Properties.Add('character_set_connection=utf8');
    Properties.Add('character_set_database=utf8');
    Properties.Add('character_set_results=utf8');
    Properties.Add('character_set_server=utf8');
    Properties.Add('character_set_system=utf8');
    Properties.Add('collation_connection=utf8_general_ci');
    Properties.Add('collation_database=utf8_general_ci');
    Properties.Add('collation_server=utf8_general_ci');
    Properties.Add('Codepage=utf8');

My insert statement looks like this:

Code: Select all

var
  q, PidIn, ArchivIn, PKlassIn : string;
begin
  //Dummy data
  PidIn := 'MyIdentifier';
  ArchivIn := '0001';
  PKlassIn := 'Bööööp'; //<- Problem with this value
  //statement
  q := 'INSERT INTO MyTable(PROJID,ARCHIV,PKLASS) VALUES (:pid,:archiv,:pklass);';
  zQuery.SQL.Clear;
  zQuery.SQL.Add(q);
  //Add values to statement
  zQuery.ParamByName('pid').Value := PidIn;
  zQuery.ParamByName('archiv').Value := ArchivIn;
  zQuery.ParamByName('pklass').Value := PKlassIn; 
  zQuery.ExecSQL;
end;

I also tried a workaround like this:

Code: Select all

var
  q, PidIn, ArchivIn, PKlassIn : string;
begin
  //Dummy data
  PidIn := 'MyIdentifier';
  ArchivIn := '0001';
  PKlassIn := 'Bööööp'; //<- Problem with this value
  //modified statement N:pklass
  q := 'INSERT INTO MyTable(PROJID,ARCHIV,PKLASS) VALUES (:pid,:archiv,N:pklass);';
  zQuery.SQL.Clear;
  zQuery.SQL.Add(q);
  //Add values to statement
  zQuery.ParamByName('pid').Value := PidIn;
  zQuery.ParamByName('archiv').Value := ArchivIn;
  // tested both ways .AsString and .AsWidString
  //also in combination with UTF8Encode(PKlassIn)
  zQuery.ParamByName('pklass').AsString / .AsWideString := PKlassIn; 
  zQuery.ExecSQL;
end;

But nothing was working.

Any help is very appreciated!

Best regards,
Bastian

[miab3]

Try this, but rather with national settings.

32.zip

ftp://ftp.freepascal.org/fpc/contrib/windows/

Michal

[marsupilami]

Hello Bastian,

hmmm - you've got me curious. Usually this should work. Could you supply me with the declaration for your testtable to complete the test case? In that case I will probably have a look into this during the upcoming long easter weekend time ;o) Which protocol do you use for your connection? What are your settings for ClientCodepage and ControlsCodePage?

With best regards,

Jan

[BastiFantasti]

Hi and thanks to both for the quick reply.

this is the table where I wanted to execute the insert query on:


CREATE TABLE Zeichnung (
ProjID VARCHAR(128) NOT NULL ,
Projklas VARCHAR(64) ,
merkmn1 FLOAT ,
merkmn2 FLOAT ,
merkms1 VARCHAR(64) ,
bearbeit VARCHAR(32) ,
edatum DATE ,
aedatum DATE ,
bemerk VARCHAR(255) ,
archiv VARCHAR(8) ,
ncprogrm VARCHAR(8) ,
PRIMARY KEY(ProjID));

the Insert statement was (original values were string and float variables. I replaced them by the data which comes into the function and raises the error) :

Code: Select all

var q : string;

q := 'INSERT INTO Zeichnung(ProjID, Projklas,merkmn1,merkmn2,mkerms1,bearbeit,edateum,aedatum,bemerk,archiv,ncprogrm) VALUES (:pid,:pklas,:mn1, :mn2, :ms1, :bearb, :edat, :adat, :bemerk, :archiv, :ncpr);';

zQuery.SQL.clear;
zQuery.SQL.Add(q);
zQuery.ParamByName('pid').Value := '12345-1';
zQuery.ParamByName('archiv').Value := 'v0001';
zQuery.ParamByName('ncp').Value := 'd0001';
zQuery.ParamByName('pkl').Value := 'Böck';
zQuery.ParamByName('mn1').Value := 0.0;
zQuery.ParamByName('mn2').Value := 0.0;
zQuery.ParamByName('ms1').Value := '';
zQuery.ParamByName('bearb').Value := 'operator';
zQuery.ParamByName('edat').Value := '14.01.2015';
zQuery.ParamByName('adat').Value := '';
zQuery.ParamByName('bemerk').Value := '';
zQuery.ExecSQL;

I was using the "mssql" identifier and

sybdb.dll : 0.95.87.0

and I don't know if this is needed any longer:
ntwdblib.dll : 8.00.194 (file version: 2000 80.194.0


Thanks again for your help.

Best regards,
Bastian

[marsupilami]

Hello Bastian,

mssql is the driver for using microsofts quite aged ntwdblib.dll. Character set conversion is a hassle with that driver. It also has other shortcomings.

I strongly suggest to use the FreeTDS_MsSQL>=2005 driver. This one uses the sybdb.dll. If you are in doubt, which dll gets used, just point the LibraryLocation to the desired dll. Use UTF-8 as the ClientCodepage. Then everything should work out ok.

Best regards and have a nice easter weekend,

Jan

[BastiFantasti]

Hi,

sorry I mixed it all up

I just internally call it MSSQL for my INI file.
In the INI section there is:

DriverIdent=FreeTDS_MsSQL>=2005
DriverPath=drivers\mssql\sybdb.dll

Best regards and have a great easter weekend, too
Bastian

[marsupilami]

Hello Bastian,

one more question. Which sybdb.dll do you use? The one with iconv support (should be around 1MB in size) or the one that is built without iconv suport (should be about 300kB in size).

Also you should use UTF-8 when specifying UTF-8 as the ClientCodepage. Specifying UTF8 is definitly not supported by sybdb.dll without iconv support. FreeTDS might fall back to ASCII in a case like that - which doesn't suport Umlauts. I am not sure how the sybdb.dll with iconv support behaves in that case. But even if sybdb.dll with iconv behaves correctly, there could be issues with Zeos because Zeos does some internal adjustments based on the character set you use and UTF8 is not a valid identifier in that case...

With best regards,

Jan

PS: So - und jetzt ab zur Eisdiele

[BastiFantasti]

Thanks for the replies,

I used both sybdb.dlls with and without iconv support. And set all UTF8 related property tags i came across.
The result was the same

By now I had no time to test the posted files because I'm quite in a hurry with my current project.
I've implemented ADO as an alternative way to talk to the MSSQL server.
As soon as I have some more time I'll do the tests and changes as suggested.
And post my result in this thread accordingly.

Best regards,
Bastian