[SOLVED] zeoslib and character set issue lazarus / 16-Databases ---------------------------------------------------------------------- tintinux · 2024-10-08 11:17 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 · 2024-10-08 11:43 First... how did you create the database itself? Did you specify the character set at that point? ( like "create database character set utf8mb4 collate utf8mb4_bin" ) You can check the current character set with: Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i USE db_name;SELECT @@character_set_database, @@collation_database; https://dev.mysql.com/doc/refman/8.4/en/charset-database.html ---------------------------------------------------------------------- tintinux · 2024-10-08 12:39 Quote from: rvk on October 08, 2024, 01:43:48 pmFirst... how did you create the database itself? Did you specify the character set at that point? ( like "create database character set utf8mb4 collate utf8mb4_bin" ) As I have written it in my first message : Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE SCHEMA IF NOT EXISTS test CHARACTER SET UTF8 COLLATE utf8_general_ci; and Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2024-10-08 13:18 Quote from: tintinux on October 08, 2024, 01:17:23 pmI'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. Quote from: tintinux on October 08, 2024, 01:17:23 pmI 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 · 2024-10-08 13:30 Quote from: rvk on October 08, 2024, 03:18:24 pmQuote from: tintinux on October 08, 2024, 01:17:23 pmI'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 · 2024-10-09 08:56 Quote from: rvk on October 08, 2024, 03:18:24 pmMaybe 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. Quote from: rvk on October 08, 2024, 03:18:24 pmBTW. 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 · 2024-10-09 09:03 Quote from: tintinux on October 09, 2024, 10:56:29 amI 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: QuoteERROR 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 · 2024-10-09 09:05 Quote from: dseligo on October 08, 2024, 03:30:35 pmQuote from: rvk on October 08, 2024, 03:18:24 pmQuote from: tintinux on October 08, 2024, 01:17:23 pmI'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. Quote from: dseligo on October 08, 2024, 03:30:35 pmAnd 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. Quote from: dseligo on October 08, 2024, 03:30:35 pmAnd 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 · 2024-10-09 09:10 Quote from: rvk on October 09, 2024, 11:03:07 amDoes 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