[SOLV]Firebird 2.5.3 COLLATE Unicode_CI_AI error when saving accented characters

[lazguy]

Hello Everybody, who can help me?

I am using Firebird embedded 2.5.3, Lazarus 1.2.4, ZeosLib 7.0 and DB components. Developping on Windows 7.

My VARCHAR fields with Charset UTF8 and Collate UNICODE_CI_AI

I can not insert or update fields with accented characters like á à ã ó ô, etc. I am getting error -303 Malformed string

When I try to insert or update fields without any accented characters, everything goes ok. No problem.


Thank you very much in advance.

[Zoran]

I tried to compile your example program, but unit LibGRSoftware is missing.
Anyway, would you try to create simpler example, as simple as possible, just to show your problem.

[lazguy]

Thank you Very Much,

Excuse me for my fault.  Now the missing library is there. The attachment is now complete.

[marcov]

Probably you need to reimport the datasources so that wide versions of the controls (TWideMemo, TWideStringfield) are generated.

Add       'codepage=utf8'
      'client_encoding=utf8'
to theconnection properties

[lazguy]

Probably you need to reimport the datasources so that wide versions of the controls (TWideMemo, TWideStringfield) are generated.

Add       'codepage=utf8'
      'client_encoding=utf8'
to theconnection properties

Thank You, Marcov,

I don´t know if I made something wrong to apply your tip, but did not worked. I am having the same problem. Part of the error message is in the attachment.

Please, anyone knows how to help me?

Thank you Very Much in advance.

[marcov]

The error gives you the table, which can give you an idea which form it is. Make sure that all field types used in that form (dbstringfield, dbmemo are of the WIDE variant).

I did this long ago once in Delphi zeos7 beta and postgresql, this is all what I remember of it of what I had to do on the ZEOS side to make it support unicode.

I can remember that the main thing was that I got issues for days because if you change the collation in the DB, the grids and datasets that already had imported fields wouldn't automatically change accordingly, and I had to reimport those fields in all relevant forms and datamodules.

[lazguy]

The error gives you the table, which can give you an idea which form it is. Make sure that all field types used in that form (dbstringfield, dbmemo are of the WIDE variant).

I did this long ago once in Delphi zeos7 beta and postgresql, this is all what I remember of it of what I had to do on the ZEOS side to make it support unicode.

I can remember that the main thing was that I got issues for days because if you change the collation in the DB, the grids and datasets that already had imported fields wouldn't automatically change accordingly, and I had to reimport those fields in all relevant forms and datamodules.

Thank you, Marcov, for your attention.

In fact, I have to create these fields as WideString by hand, because at the fields list windows of my ZQuery component, when I click on the + button, the fields inserted automatically are only TStringField.

Now I can insert and update, but....

now I have other problem: The accents at my TDBmemo component are appearing as ?, and some other characters simply do not appear.


Please, how can I solve this ?


Thank you very much in advance.

[marcov]

Hmm, I guess that Zeos can't read the collation from Firebird then as it can for postgressql. There when I changed the collation on the fields in the DB (via the postgresql manager), afterwards I had to reimport the fields.

[EgonHugeist]

Hi,

Hmm, I guess that Zeos can't read the collation from Firebird then as it can for postgressql. There when I changed the collation on the fields in the DB (via the postgresql manager), afterwards I had to reimport the fields.

Thanks for support him, Marco. Nope Zeos can do that.

Probably you need to reimport the datasources so that wide versions of the controls (TWideMemo, TWideStringfield) are generated.

Add       'codepage=utf8'
      'client_encoding=utf8'
to theconnection properties

It should be 'ClientCodePage=UTF8'
or
set TZConnection.ClientCodePage to 'UTF8' (look to the property editor of TZConnection)

The collation he is talking about is NOT detectable since it's a collation but not the CharacterSet encdoding which is send or retrieved by the Server/client.

To keep control about the default Character field types set TZConnection.ControlsCodePage := cCP_UTF8;
So the client-encoding and LCL are 100% compatible for FB.

Anyway a suggestion to lazguy:
It looks to me like your default CharacterSet of your created database is <> UTF8. It's better to recreate the DB whith UTF8 -> just to avoid character encoding conversions which decreases the performance.

[lazguy]

Hmm, I guess that Zeos can't read the collation from Firebird then as it can for postgressql. There when I changed the collation on the fields in the DB (via the postgresql manager), afterwards I had to reimport the fields.

Well, in that case, I think that there isn't much to do, unless I change the DB.

So, to close this topic I have one more question:

If I use on my Firebird embedded charset WIN1252 and some other collate that works for those fields, will I be able to run the same program on Linux or Android without problems, or, for that, it Must be UTF8 ?

Thank you Very Much in advance.

[lazguy]

Hi,

Hmm, I guess that Zeos can't read the collation from Firebird then as it can for postgressql. There when I changed the collation on the fields in the DB (via the postgresql manager), afterwards I had to reimport the fields.

Thanks for support him, Marco. Nope Zeos can do that.

Probably you need to reimport the datasources so that wide versions of the controls (TWideMemo, TWideStringfield) are generated.

Add       'codepage=utf8'
      'client_encoding=utf8'
to theconnection properties

It should be 'ClientCodePage=UTF8'
or
set TZConnection.ClientCodePage to 'UTF8' (look to the property editor of TZConnection)

The collation he is talking about is NOT detectable since it's a collation but not the CharacterSet encdoding which is send or retrieved by the Server/client.

To keep control about the default Character field types set TZConnection.ControlsCodePage := cCP_UTF8;
So the client-encoding and LCL are 100% compatible for FB.

Anyway a suggestion to lazguy:
It looks to me like your default CharacterSet of your created database is <> UTF8. It's better to recreate the DB whith UTF8 -> just to avoid character encoding conversions which decreases the performance.

Thank You, EgonHugeist,

I was answering marcov while you posted your answer, so I did not see it on time. I will try your tip tomorrow and I will answer you.

[EgonHugeist]

See my previous reply:

NO it must NOT be UTF8 but in all cases its faster if you want to work with GUI+LCL+DB-access. Because LCL assumes all strings are UTF8 (not my idea, my dear)

It doesn't matter which characterset except characterset "NONE" -> Here my english ends (:

AGAIN just set ClientCodePage to "UTF8"

[marcov]

NO it must NOT be UTF8 but in all cases its faster if you want to work with GUI+LCL+DB-access. Because LCL assumes all strings are UTF8 (not my idea, my dear)

For the record: not mine either 

[lazguy]

Hi,

Hmm, I guess that Zeos can't read the collation from Firebird then as it can for postgressql. There when I changed the collation on the fields in the DB (via the postgresql manager), afterwards I had to reimport the fields.

Thanks for support him, Marco. Nope Zeos can do that.

Probably you need to reimport the datasources so that wide versions of the controls (TWideMemo, TWideStringfield) are generated.

Add       'codepage=utf8'
      'client_encoding=utf8'
to theconnection properties

It should be 'ClientCodePage=UTF8'
or
set TZConnection.ClientCodePage to 'UTF8' (look to the property editor of TZConnection)

The collation he is talking about is NOT detectable since it's a collation but not the CharacterSet encdoding which is send or retrieved by the Server/client.

To keep control about the default Character field types set TZConnection.ControlsCodePage := cCP_UTF8;
So the client-encoding and LCL are 100% compatible for FB.

Anyway a suggestion to lazguy:
It looks to me like your default CharacterSet of your created database is <> UTF8. It's better to recreate the DB whith UTF8 -> just to avoid character encoding conversions which decreases the performance.

Thank you Very Much, Marcov and EgonHugeist

Problem Solved!!!

EgonHugeist, your tips was Very Valuable !!!  I deleted the database and recreated it again with characterset = UTF8 and collate = UNICODE_CI_AI
I also set the Zconnection properties as you indicated, and now everything works perfectly.


Thank you Very Very Much, guys  !!!!  Have a nice weekend !!!!