Page 1 of 1

Delphi XE and SQLite and Chinese character

Posted: 07.01.2012, 14:47
by mmvisual
Hello,

I have load the last SVN 966 and I want use SQLite.
But the problem, the database store the chinese characters not.

I use Delphi XE, in the Label can I see a text whith chinese characters.

When I edit a table and insert a chinese text and post it, then I see the chinese text, too.

But when I close the conection and reopen the table, then I see only "???" characters.

When I use MySQL, then it works fine, but not with SQLite.

When I use this characters ążł then zeos convert it to azl.

Can you help me, please :?:
I have search in this forum, but no solution found.

PS: I use WinXP and have install a font "Arial Unicode MS" with the included characters.

Regards, Markus.

Posted: 07.01.2012, 22:02
by EgonHugeist
mmvisual,

could you be so kind and post your How-It-Works-with-MySQL-way too?

Posted: 07.01.2012, 23:12
by mmvisual
With phpMyAdmin, create an new database with kollation "utf8_unicode_ci" and then make your tables.

In Delphi:
Use my demo, last posting, and set User, Passwort, Port=3306, Database, Host.
Set the protocol to"mysql-5".

Finish.

Posted: 07.01.2012, 23:45
by EgonHugeist
Found "Bezeichnung": aha du bist ein Deutscher, grins...


mmvisual, i've check your example-project:

First:

procedure TForm1.FormCreate(Sender: TObject);
begin

ZQuery1.SQL.Text := 'PRAGMA encoding = "UTF-8";'; -> use this in your TZConnection.Poperties...
ZQuery1.ExecSQL;

Second: These paramteters you also have to set in mysql... So i wonder why it works. In your example-project the Field-assigning of your Label1.Caption is missed...

Um dir auf die Sprünge zu helfen.. (Brain.Upgrade...)

schau mal hier (look here ) http://zeos.firmos.at/viewtopic.php?t=3367
and http://zeos.firmos.at/viewtopic.php?p=13640#13640

Some of your chinese letters work and others actually not....

The workaround you need := SQL := SQL + String(Ansistring(UTF8Encode(YourParam))); or use Params :s which solve this yet..

Posted: 08.01.2012, 00:38
by mmvisual
Ja, ich bin ein deutscher und mein englisch ist eher denglisch.

Yes, sorry. No extra parameter for MySQL. Use a new TZConnection and set this parameters. Then a TZQuery with "SELECT * FROM <your_table>".
No extra Execute, no extra parameters and properties in TZConnection and TZQuery.

The Zeos/SQLite working only right with chinese charactes when I using Lazarus and Linux!
So, it is not a problem from SQLite, only from Zeos with Delphi.

The Zeos/SQLite/Lazarus/Windows can store the caracters ąłż, and Delphi cannot (the result is then: alz).

I think there is a problem with the convertions between PAnsiChar and PWideChar and Delphi make automaticly a conversion, at storing in the database. SQLite have all DLL routines with "Ansi" and "WideString", I think there must be used the Wide vatiant.

I have see the other (old) threads, but, there gives no "easy to use" solution.

Posted: 08.01.2012, 00:56
by EgonHugeist
mmvisual,
mein englisch ist eher denglisch.
muhaha handshake!

look at
http://zeos.firmos.at/viewtopic.php?t=2 ... c&start=45

read carefully... It exactly discribes the problem, and what has to be done. You are right in case of delphi-using. But this problem accours only since version 2009 and it seems you are using XE or am i wrong?

It's a known problem resulting of mixing 1-Byte-Chars(the dll's Zeos is using for DB-accessing) and Delphi12_up varying-chars(up to 4 Byte per Char on some special chars)

So what can you do to work with 7.0.x Alpha in a save way: use Parameter-Queries. The parameter automalicaly do that utf8-Encoding..

regards der Egon

Posted: 08.01.2012, 02:48
by mmvisual
No, I want not use Parameter Queries. I want use a Query with DataSource and a Grid. Then edit / write my texts into the grid and post the changes. (One code line TZQuery.Post;)

I have set the Properties "codepage=UTF8" in TZConnection and TZQuery, but it do not work, have no result.

Yes, I have DelphiXE.
Your links showing for a description for MySQL, but my problem is with SQLite.

Can you store with my EXE (Test, see top) this characters "言順要レ" into the SQLite database?
When yes, and you have make a change, can you post the new sources, please?

Posted: 08.01.2012, 11:29
by EgonHugeist
These are Parameter-Statement-Queries, so i wonder why it happens. Did somebody forgot the UTF8Encodung of the vtUnicodeString in SQLLite? so check your pn i'll help you in german.

Posted: 09.01.2012, 01:41
by EgonHugeist
Yesterday, me and mmvisual,

did some debugging to solve his problem. We've some findings:

function ConvertSQLiteTypeToSQLType(TypeName: string; var Precision: Integer;
var Decimals: Integer): TZSQLType;

else if TypeName = 'VARCHAR' then
Result := {$IFDEF DELPHI12_UP}stUnicodeString{$ELSE}stString{$ENDIF} was missed so

the GetUnicodeString(..): WideString function for the IZResultSet/RowAccesor was never called.

So using stString insteat of stUnicodeString means that the TRowAccessor lost the Character-informations resulting of SetString(Value: AnsiString).

So w've changed the line in ZdbcSQLLiteUtils like above. The RowAccessor and ResultSets now uses the right Setter&Getter-Functions. Now we've the next Problem: ZQuery.Insert fails. I've checked out what happens:

InternalPost:
the Metadata lost now his informations. So the GenericResolver could not assamble the right insert-query. No more fieldnames or table-name where aviable now.

Is there somebody who knows why this happens? Reverting to Result := stString; and everything is fine, but like i said by using chinese or russian letters the Character-informations where lost and each "special"-Char changes to "?";

Regards