Weird issue with some characters in a script

[tintinux]

Hi

I'm using 7.2.6 with Lazarus 2.0.10, connected to a MariaDb 10 Database, utf-8 charset selected everywhere.

I get a syntax error when I execute in a ZSqlProcessor this :

Code: Select all

CREATE OR REPLACE TABLE Countries( CountryName VARCHAR(50) NOT NULL)  DEFAULT CHARSET=utf8 ENGINE=InnoDB;

INSERT INTO Countries (CountryName) VALUES ('CAÏMANES, ÎLES');

But with this one (only one "E" has been removed in the country name), for example : no problem !

Code: Select all

CREATE OR REPLACE TABLE Countries(CountryName VARCHAR(50) NOT NULL) DEFAULT CHARSET=utf8 ENGINE=InnoDB;

INSERT INTO Countries (CountryName)  VALUES ('CAÏMANS, ÎLES');

The first script run without error in another SQL client (SquirrelSQL), or when executed in a mysql command line with input from files.

I have attached a zip containing a small project reproducing the problem, and the 2 script files

Any idea about this strange issue ? Could someone try with Delphi ?

[aehimself]

I remember there were issues on MySQL with the default UTF-8 codepage and everyone advised to use UTF8MB4 instead. I don't know if MariaDB still has this, but it worths to try...?

I converted your application to Delphi and the only error I get is that the table does not exist; so I suppose parsing was successful. This is not going to be too much of a help, as
- I'm using Delphi, you are using Lararus
- I'm using Zeos 8.0, you are using 7.2
- I'm connecting to MySQL while you are connecting to MariaDB...

Anyway, I'd try updating to Zeos 8. I remember @Egonhugeist reworked the character encoding, maybe it will solve your issues too.

[marsupilami]

Another option might me that the files are read in the wrong encoding. Maybe it is worth a try to see if the SQL loads correctly? Display it in a TMemo or something like that and if it is correct, assign the Memo lines to the TZSqlProcessor SQL:
SqlProcessor.SQL.Assign(Memo.Lines);
Or:
SqlProcessor.SQL. Text := Memo.Lines.Text.

[EgonHugeist]

Hello tintinux,

i hade a look to Classes.pas of FPC3.2. They added an overload for loadfromstream/loadfromfile having a TEncoding overload.
Whithout checking your example it feels correct to add this parameter to our TSQLProcessor.LoadFrom(File/Stream).

See https://sourceforge.net/p/zeoslib/code-0/7316/ Trunk only.
Yet i'm out of time to checkyour example, next week propably. But i'm starting from the premisse your DB is utf8 encoded and the file you use use ansi encoded. Try to add an Ansi-TEncoding to the TSQLProcessor loaders.. Does it help?

[tintinux]

With some delay, I confirm the issue has disappeared with ZeosLib 8.0 and no code modification.

It is certainly better to use the new signature of LoadFromFile, with the parameter TEncoding.UTF8 , but as far as I can see, in my configuration, it doesn't appear to solve or change anything by itself.

Thanks