QSqlQuery error with translated text

[SPlatten]

I am using Qt 5.9.2 with Qt Creator 4.4.1, so far development has been in English, today I have added a french language using the Qt Linguist.

I can see from messages during my application start-up that the translation is working. My application connects to an instance of MariaDB and logs messages in a table called auditlog, adding content to this table is managed by a stored procedure addAuditEntry.

I think whilst writing this post I have come to a conclusion of what the problem is. When I call the stored procedure:

QSqlQuery query;
query.prepare("CALL addAuditEntry(?);");
query.addBindValue(cpszEntry);

I can see the string cpszEntry contains:

"Serveur de formation connect� � la base de donn�es" 

I'm guessing there are some non ASCII characters in the translation that is breaking the query as the Error I get is:

Error:
Incorrect string value: '\xEF\xBF\xBD \xEF\xBF...' for column ``.``.`_txtActivity` at row 1 QMYSQL3: Unable to execute statement

This is annoying and something I hadn't factored for, the parameter _txtActivity is a TEXT type. What can I do to fix this? I thought the benefit of using ? to add parameters is to handle these sort of issues.

[KroMignon]

@SPlatten said in QSqlQuery error with translated text:

where would I use SET NAMES 'utf8umb4';
In the ini file or console ? I need a solution that works when the system starts or restart.

You should send it to server after each new connection:

QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setHostName("myhostname");
db.setDatabaseName("databasename");
db.setUserName("username");
db.setPassword("pasword");
bool ok = db.open()
if(ok)
{
    QSqlQuery query(db);
    query.exec("SET NAMES 'utf8umb4';");
}

[SGaist]

Hi,

Using prepared query has nothing to do with UTF8 management. It's a way to avoid SQL injection by stringing together your query from various input.

One thing you should check is whether your database setup supports the charset you want to use.

On a side note, you might want to take into account that translating that kind of messages may also force you to write tools that are able to extract useful data from them in several languages as well. Showing translated messages to a user is one thing, doing that as part of your system logging might be counter-productive.

[SPlatten]

@SGaist , on start-up the splash screen displays what its doing, this is the text that is being translated, it goes ok until it gets to:

Training Server connected to database

The French translation I have for this message is:

Serveur de formation connecté à la base de données

How do I know what charset is required for this language?

[KroMignon]

@SPlatten said in QSqlQuery error with translated text:

How do I know what charset is required for this language?

AFAIK, Qt internally always use UTF-8. If it is in Qt application, there is nothing to do.
The question is how to do get the message to display?

[SPlatten]

@KroMignon , the actual error is occurring not in the display of the error, which is displayed, but in trying to add the parameter to the stored procedure.

[JonB]

@SPlatten
I would start by Googling, say, MariaDB French text. I do not know the ins & outs, but Accented characters in mySQL table seems related to your behaviour. What about In MySQL, never use “utf8”. Use “utf8mb4”?

[SPlatten]

@JonB , is there a similar issue with TEXT parameters? I've modified the database/table creating procedure replacing:

COLLATE=''uft8_general_ci'

with:

COLLATE='utf8mb4_unicode_ci'

I've then dropped the database and recreated. Still getting the exact same error. Now though, where as before the error message was being logged, now that doesn't work either.

[KroMignon]

@SPlatten said in QSqlQuery error with translated text:

I've then dropped the database and recreated. Still getting the exact same error. Now though, where as before the error message was being logged, now that doesn't work either.

I think you also have to set the default charset for the SQL statements to UTF8.
You can do I with SET NAMES 'utf8'; for each connection or via server configuration:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

[SPlatten]

@KroMignon , these are the settings I need:

[client]
 default-character-set = utf8mb4

 [mysqld]
 character-set-server  = utf8mb4
 collation-server      = utf8mb4_unicode_ci
 character_set_server  = utf8mb4
 collation_server      = utf8mb4_unicode_ci

Just waiting for IT to unlock the laptop so I can implement.

[KroMignon]

@SPlatten said in QSqlQuery error with translated text:

Just waiting for IT to unlock the laptop so I can implement.

Have you tried to use SET NAMES 'utf8'; ?

[SPlatten]

@KroMignon , what does that do?

[KroMignon]

@SPlatten said in QSqlQuery error with translated text:

what does that do?

The same without having to change the server settings ;) ==> https://dev.mysql.com/doc/refman/8.0/en/set-names.html

[SPlatten]

@KroMignon , why utf8 and not utf8mb4 ?

[KroMignon]

@SPlatten said in QSqlQuery error with translated text:

why utf8 and not utf8mb4 ?

Sorry, you are right utf8mb4 is better. utf8 seems to be only a subset of UTF-8 charset! (cf https://mathiasbynens.be/notes/mysql-utf8mb4)

[SPlatten]

@KroMignon , where would I use SET NAMES 'utf8umb4';

In the ini file or console ? I need a solution that works when the system starts or restart.

[KroMignon]

@SPlatten said in QSqlQuery error with translated text:

where would I use SET NAMES 'utf8umb4';
In the ini file or console ? I need a solution that works when the system starts or restart.

You should send it to server after each new connection:

QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setHostName("myhostname");
db.setDatabaseName("databasename");
db.setUserName("username");
db.setPassword("pasword");
bool ok = db.open()
if(ok)
{
    QSqlQuery query(db);
    query.exec("SET NAMES 'utf8umb4';");
}

[SPlatten]

Thank you, will give it a go now.

[SPlatten]

@KroMignon , having implemented that, I'm still getting the same issue.

[KroMignon]

@SPlatten said in QSqlQuery error with translated text:

having implemented that, I'm still getting the same issue.

Out of curiosity, the SQL command you want to execute is CALL addAuditEntry("Serveur de formation connecté à la base de données");, right?

Did you try to do it by hand, on command line or with phpMyAdmin/MySQL Workbench?

[SPlatten]

@KroMignon , I'm using HeidiSQL, will try now.