QSqlQuery error with translated text

[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();

[KroMignon]

@SPlatten said in QSqlQuery error with translated text:

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

I suppose the problem is the content of crstrEntry.
Is this working?

QSqlQuery query;
query.prepare("CALL addAuditEntry(?);");
query.addBindValue("Serveur de formation connecté à la base de données");
query.exec();

[SPlatten]

@KroMignon , crstrError is:

const QString cstrError(crErr.text());

Where crErr is:

const QSqlError& crErr

Interestingly in Qt Creator / Debugger, when ** crstrError** is viewed in the debugger contains:

 Locals                 
                             crstrEntry           "Serveur de formation connect� � la base de donn�es"         QString
                                           [0]         'S'          83          0x0053 QChar
                                           [1]         'e'          101        0x0065 QChar
                                           [2]         'r'           114        0x0072 QChar
                                           [3]         'v'          118        0x0076 QChar
                                           [4]         'e'          101        0x0065 QChar
                                           [5]         'u'          117        0x0075 QChar
                                           [6]         'r'           114        0x0072 QChar
                                           [7]         ' '           32          0x0020 QChar
                                           [8]         'd'          100        0x0064 QChar
                                           [9]         'e'          101        0x0065 QChar
                                           [10]       ' '           32          0x0020 QChar
                                           [11]       'f'           102        0x0066 QChar
                                           [12]       'o'          111        0x006f  QChar
                                           [13]       'r'           114        0x0072 QChar
                                           [14]       'm'         109        0x006d QChar
                                           [15]       'a'          97          0x0061 QChar
                                           [16]       't'           116        0x0074 QChar
                                           [17]       'i'           105        0x0069 QChar
                                           [18]       'o'          111        0x006f  QChar
                                           [19]       'n'          110        0x006e QChar
                                           [20]       ' '           32          0x0020 QChar
                                           [21]       'c'           99          0x0063 QChar
                                           [22]       'o'          111        0x006f  QChar
                                           [23]       'n'          110        0x006e QChar
                                           [24]       'n'          110        0x006e QChar
                                           [25]       'e'          101        0x0065 QChar
                                           [26]       'c'           99          0x0063 QChar
                                           [27]       't'           116        0x0074 QChar
                                           [28]       '�'        65533   0xfffd    QChar
                                           [29]       ' '           32          0x0020 QChar
                                           [30]       '�'        65533   0xfffd    QChar
                                           [31]       ' '           32          0x0020 QChar
                                           [32]       'l'           108        0x006c  QChar
                                           [33]       'a'          97          0x0061 QChar
                                           [34]       ' '           32          0x0020 QChar
                                           [35]       'b'          98          0x0062 QChar
                                           [36]       'a'          97          0x0061 QChar
                                           [37]       's'           115        0x0073 QChar
                                           [38]       'e'          101        0x0065 QChar
                                           [39]       ' '           32          0x0020 QChar
                                           [40]       'd'          100        0x0064 QChar
                                           [41]       'e'          101        0x0065 QChar
                                           [42]       ' '           32          0x0020 QChar
                                           [43]       'd'          100        0x0064 QChar
                                           [44]       'o'          111        0x006f  QChar
                                           [45]       'n'          110        0x006e QChar
                                           [46]       'n'          110        0x006e QChar
                                           [47]       '�'        65533   0xfffd    QChar
                                           [48]       'e'          101        0x0065 QChar
                                           [49]       's'           115        0x0073 QChar

Its looking like the storage in QString is the bit thats messed up and requires fixing?
See offsets 28, 30 and 47.

Found this:
https://forum.qt.io/topic/113781/qt-5-14-1-qsqlmodel-and-french-characters/17

Looks like exactly what I'm seeing.