UTF8 and ADO-Connection

[pengels]

Hi all,

I am using ZeosLib 7.1.3a with Delphi 2010 and want to write some UTF8 characters into a Memo-Field of an Access-DB connected via ADO. But I cannot get this working. E.g. the character Ĉ will be stored as C, while the german Umlauts äüöß are correct. Can anybody tell me, how TZConnection must be configured to get this working? Currently I use the following settings:

Connection.Properties.Text := 'codepage=utf8';
Connection.ControlsCodePage := cCP_UTF8;
Connection.ClientCodepage := 'utf8';
Connection.Protocol := 'ado';
Connection.Port := 0;
Connection.Database := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + dbFile + ';Persist Security Info=False;Jet OLEDB:Database Password=' + sDecrypt(AccessPW) + ';Mode=Share Deny None';
Connection.HostName := '';
Connection.User := '';
Connection.Password := '';
...
Connection.ExecuteDirect(t)

Where t is 'update table set col=''äöüßĈ'' where id=1234'

Does the Memo already contain some UTF8-chars, I can read same with this connection without problems, but I cannot write them. Any Ideas?

PS: The program works well with a MySQL-Database, but not Access via ADO.

Has anybody an idea how to solve this problem?

Best regards

Peter

[EgonHugeist]

D2010 is Unicode based, ADO is pure WideString/UnicodeString based.
So keep ControlsCodePage cCP_UTF16. Zeos doesn't accept it and always switches back to cCP_UTF16 btw.
AND IF you write UTF8 Chars into a T(DB)Memo than you mean you're writing accendentual characters or do you really try to write native UTF8-encoded chars?

ARE you sure ACCESS supports characters which do not exists in you locale settings? -> IMHO this is the troublemaker..

[pengels]

Hi Michael,

my application shall become a helper to an existing commercial application, also written in Delphi. I think, that this application uses TADO-components, since every connection is made via ODBC and a corresponding UDL-File. Supported are ACCESS, Firebird, MySQL and MSSQL Databases. Because my helper-application shall not use ODBC (the users have to install this too), my attempt was to use ZEOS and direct connections. We are using a MySQL-Database and with this, everything works fine. Now I wanted to add support for the other types, ACCESS first.

If I write something like Ĉ or Č as part of a surname into the database, my helper will display it correctly - it can read it. But it cannot write it, the sign is broken to a simple C.

Regards Peter

[EgonHugeist]

Hi Peter,

Just to be clean: If this character already persists in your Access db than you can reading works as expected. But if you want to post this char than the ASCII7 char is posted, right?

Man this is wird, we just link the data UnicodeString in the ADO statement. AFAIK do we NO A2U and U2A conversions for the Unicode IDE's. Did you set the ControlsCodepage to cCP_UTF16 again?

[pengels]

Hi Michael,

yes, you got it clear: In the case the Access-DB already contains a char like Ĉ in a memo it is displayed correctly by ZEOS, but when I write it back into the DB it will become a simple C. Here are my current settings:

Connection.Protocol := 'ado';
Connection.ControlsCodePage := cCP_UTF16;
Connection.AutoEncodeStrings := true;
connection.ClientCodepage := 'cp1251';
Connection.Port := 0;

Perhaps the following hint may help to figure out, what's going wrong:

The update-string is correctly TAbstractStatement.GetEncodedSQL, where it is parsed. This routine calls TZCodepage.ZPlainstring in ZCompatibility.pas. Line 802 contains the statement

UseEncoding := FConSettings.ClientCodePage^.Encoding

and the value of UseEncoding is ceAnsi, which seems to be wrong. In the following conversions the string will be corupted.

Kind regards Peter

[EgonHugeist]

Hi Peter,

sorry for the delay.
ADO doesn't have client codepages. AFAIR did a set ado to IsAnsiDriver = False. So the Ansi stuff isn't handled there.
Forget about ClientCodePage + ADO. There is no one -> as i wrote ADO is a pure WideString based connection.

Just an idea/suggestion
if you want to update such fields use parameters and a possible 'N' suffix (does Access support NCHAR fields?) like:

Code: Select all

insert into foo(id, fld1) values (1, N:?)

This "could" help to get the Encoding running made by ADO.