Forum > Databases

UTF8 connection to MySQL 5

(1/2) > >>

Firebird:
I have defined a UTF8 database and table in MySQL 5 and a DSN referring to this database (character set is set to "utf8").
When running the following code, only "???" show up in the database table for the 4th attribute:
  conn := TODBCCOnnection.Create(nil);
  query1 := TSQLQuery.Create(nil);
  transaction := TSQLTransaction.Create(nil);

  conn.DatabaseName := 'odbc_utf8';
  conn.Transaction := transaction;
  conn.CharSet := 'utf8';

  query1.DataBase := conn;

  query1.SQL.Text := 'SET CHARACTER SET utf8';
  query1.ExecSQL;
  query1.SQL.Text := 'SET NAMES ''utf8''';
  query1.ExecSQL;

  query1.SQL.Text := 'REPLACE INTO dischmeasdatatypes VALUES(3,"Test","ru","Инструмент","test data record")';
  query1.ExecSQL;

When I do the same using native Windows functions for odbc access the record is inserted correctly (even without "SET CHARACTER SET" and "SET NAMES" statements).

What am I missing? I would be very grateful for any help.

Zvoni:
Why in blazes are you using ODBC and MySQL5?
Use the dedicated MySQL-Connection-Units/Classes
And why MySQL5? Found nothing older?

To your question: What CharSet has the Column of your Table?
https://stackoverflow.com/questions/15262133/how-to-store-utf8-to-mysql-5-x-via-jdbc
Yes, for Java, but It's a question of the DB-Table/Column

Zath:

--- Quote from: Zvoni on October 26, 2022, 04:44:01 pm ---Why in blazes are you using ODBC and MySQL5?
Use the dedicated MySQL-Connection-Units/Classes
And why MySQL5? Found nothing older?

--- End quote ---

Nice attitude there Zvoni.

Ever consider some older hosting sites still use ver 5 and DB setups might also need it ?
Some versions of Delphi require dlls from earlier versions of MySQL to run the same app.

Using a 2021 shopping cart package, the coders updated to the latest PHP and MySQL versions and caused havoc on the cart forum because tables in use were MySQL 5.x and the update broke a lot of sites.

Simply moving to the latest versions isn't always the brightest move if it applies to established software.
The latest PHP version won't allow zip files to expand. Again, this screwed the next version of another cart software because it needed PHP8.1 to run the software but couldn't be installed on a host due to lack of self extraction.




Firebird, read this page about Connection and character sets as Cyrillic might need a more specific version of utf8.

https://dev.mysql.com/doc/refman/5.6/en/charset-connection.html

Zvoni:
Ok, i apologize if my attitude rubs you the wrong way.

Everything correct you wrote, and yes, i also adhere to the old adage of „if it ain‘t broke don’t fix it“.
BUT: nothing of that is in OP‘s first post. In that case i always assume „new“ software.
If that‘s not the case, and he has to respect „old“ software, then say it up front.

If it‘s „new“ software he‘ll get no pity from me.
If he wants to use steel and flintstone to start a fire in the 21st century, be my guest, but don’t come complaining if it doesn’t work.

And i actually answered his question with a reference

Firebird:
Well, the decision to use odbc and MySQL 5 was not on my side but was taken due to compatibility reasons in the project I am working in. I don't think the issue is on the DB side. The attribute is defined as VARCHAR(50) in the table.
If I go down to basic "odbc structures", then the query works:

ODBC_Connect('odbc_utf8', logfile);
StmtHandle := nil;
if (ODBC_SQLStatAlloc(StmtHandle)) then
begin
  query := 'INSERT INTO dischmeasdatatypes VALUES(3,"Test","ru","Инструмент","test something")';
  ODBC_SQL(query);
  ODBC_SQLStatFree();
end;
ODBC_Disconnect();

Navigation

[0] Message Index

[#] Next page

Go to full version