QSqlQuery error with translated text

[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.

[SPlatten]

@KroMignon , Now I'm confused, having put into HeidiSQL:

CALL addAuditEntry("Serveur de formation connecté à la base de données");

It works with no error reported, so why doesn't the same work when I do:

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