[SOLVED] zeoslib and character set issue

[tintinux]

Hi
I'm using Zeos 7.2 on a MySql 5 database.

The full project for test is attached.

This is a part of the code :

Code: [Select]

  if not ZConnection1.Connected then
  begin
    ZConnection1.Protocol:= 'mysql';
    ZConnection1.ClientCodepage:= 'utf8';
    ZConnection1.LibraryLocation:= Edit1.Text;
    ZConnection1.HostName:= Edit2.Text ;
    ZConnection1.User:= Edit3.Text ;
    ZConnection1.Password:= Edit4.Text ;
    ZConnection1.Database:= '';
    ZConnection1.Connect;
    ShowMessage ('Connected');
  end;
  ZSQLProcessor1.Script.Clear;
  ZSQLProcessor1.Script.AddStrings(Memo1.Lines);
  ZSQLProcessor1.Execute;
  ShowMessage ('Ok'); 
When I load and execute these instructions :

Code: [Select]

SET NAMES Utf8;
CREATE SCHEMA IF NOT EXISTS test CHARACTER SET UTF8 COLLATE utf8_general_ci;
USE test;
CREATE TABLE IF NOT EXISTS test ( teststring VARCHAR(100) ) DEFAULT CHARSET=utf8 ENGINE=InnoDB;
INSERT INTO test  VALUES ( 'spécifications FEC, il faut que cette requête');
 
I get a MySql syntax error.

But when I just remove the comma, or some other characters in the constant string, it works fine !

Both files are found UTF8 by Lazarus.

Same issue with a ZQuery (and only one instruction at a time)

I guess the character set is not detected correctly by the mysql driver and that maybe I should set it as UTF8 in the ZSqlProcessor. But I don't know how.
Or if someone has a better idea ?

Thanks for your help !

Edit : the varchar length where too short in my example (but it was not the cause of the issue)

[rvk]

First... how did you create the database itself?
Did you specify the character set at that point?
( like "create database <database_name> character set utf8mb4 collate utf8mb4_bin" )

You can check the current character set with:

Code: SQL  [Select][+][-]

1. USE db_name;
2. SELECT @@character_set_database, @@collation_database;

https://dev.mysql.com/doc/refman/8.4/en/charset-database.html

[tintinux]

First... how did you create the database itself?
Did you specify the character set at that point?
( like "create database <database_name> character set utf8mb4 collate utf8mb4_bin" )

As I have written it in my first message :

Code: Pascal  [Select][+][-]

1. CREATE SCHEMA IF NOT EXISTS test CHARACTER SET UTF8 COLLATE utf8_general_ci;

and

Code: Pascal  [Select][+][-]

1. SELECT @@character_set_database, @@collation_database

returns : utf8mb3, utf8mb3_general_ci


PS : I have omitted to say that, using SquirreL Sql client, it works fine, I have no error.

[rvk]

I'm using Zeos 7.2 on a MySql 5 database.

I wonder how old that one is.

If I install Zeos via the Online Package Manager, the latest version is 8.0.0.
And if I load your project, it gives an error that "AutoEncodeStrings" is an unknown property.

Maybe there is a lot of work done to make it more compatible with UTF8.

You code works for me on Windows 10 with Lazarus trunk, Zeos 8.0.0 and libmysql.dll 5.7.24 with 10.5.19-MariaDB as back-end.

I get a MySql syntax error.

BTW. did you mention what error you got and I overlook this? Or did you just not mention it?

[dseligo]

I'm using Zeos 7.2 on a MySql 5 database.

I wonder how old that one is.

If I install Zeos via the Online Package Manager, the latest version is 8.0.0.
And if I load your project, it gives an error that "AutoEncodeStrings" is an unknown property.

And MySQL 5.0 was released in 2005. Problem could be there, too.

[tintinux]

Maybe there is a lot of work done to make it more compatible with UTF8.
You code works for me on Windows 10 with Lazarus trunk, Zeos 8.0.0 and libmysql.dll 5.7.24 with 10.5.19-MariaDB as back-end.

Well, the issue disappear also with my sample and Zeos 8 on Linux (and MySql5). I'm going to make more tests with larger files.

BTW. did you mention what error you got and I overlook this? Or did you just not mention it?

I mentioned : a I get a MySql syntax error.

[rvk]

I mentioned : a I get a MySql syntax error.

It's normally good practice to copy and paste the actual error message. Just to avoid confusion.
Does it actually say "a MySql syntax error" occured? And nothing else?

Normally it looks something like:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id=101 SET name='foo'' at line 1

Glad it's solved with Zeos 8.

[tintinux]

I'm using Zeos 7.2 on a MySql 5 database.

I wonder how old that one is.
If I install Zeos via the Online Package Manager, the latest version is 8.0.0.

Zeos 8 stable if available since 3 months, that is short.

And if I load your project, it gives an error that "AutoEncodeStrings" is an unknown property.

Because the property has been removed in Zeos 8. There is a button to remove it quickly.

And MySQL 5.0 was released in 2005. Problem could be there, too.

No, because it works with others Sql Clients, like SquirreL SQL.
However I must keep MySql 5 because the newer versions are not compatible with some compiled applications I have.

[tintinux]

Does it actually say "a MySql syntax error" occured? And nothing else?

Ok, sorry ! next time I will copy the message.
It was nearly this one :
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id=101 SET name='foo'' at line 1