QSqlQuery error with translated text
-
@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 withSET 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@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_ciJust waiting for IT to unlock the laptop so I can implement.
Kind Regards,
Sy -
@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_ciJust waiting for IT to unlock the laptop so I can implement.
@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';?It is an old maxim of mine that when you have excluded the impossible, whatever remains, however improbable, must be the truth. (Sherlock Holmes)
-
@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';? -
@KroMignon , what does that do?
@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
It is an old maxim of mine that when you have excluded the impossible, whatever remains, however improbable, must be the truth. (Sherlock Holmes)
-
@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
-
@KroMignon , why utf8 and not utf8mb4 ?
@SPlatten said in QSqlQuery error with translated text:
why utf8 and not utf8mb4 ?
Sorry, you are right
utf8mb4is better.utf8seems to be only a subset of UTF-8 charset! (cf https://mathiasbynens.be/notes/mysql-utf8mb4)It is an old maxim of mine that when you have excluded the impossible, whatever remains, however improbable, must be the truth. (Sherlock Holmes)
-
@SPlatten said in QSqlQuery error with translated text:
why utf8 and not utf8mb4 ?
Sorry, you are right
utf8mb4is better.utf8seems to be only a subset of UTF-8 charset! (cf https://mathiasbynens.be/notes/mysql-utf8mb4) -
@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.
@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 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';"); } -
@KroMignon , having implemented that, I'm still getting the same issue.
@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?
It is an old maxim of mine that when you have excluded the impossible, whatever remains, however improbable, must be the truth. (Sherlock Holmes)
-
@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?
@KroMignon , I'm using HeidiSQL, will try now.
-
@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?
@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();Kind Regards,
Sy -
@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();@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();It is an old maxim of mine that when you have excluded the impossible, whatever remains, however improbable, must be the truth. (Sherlock Holmes)
-
@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();@KroMignon , crstrError is:
const QString cstrError(crErr.text());Where crErr is:
const QSqlError& crErrInterestingly 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 QCharIts 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/17Looks like exactly what I'm seeing.
-
@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();@KroMignon , found the problem and fixed. The issue was the QString was being converted back to a const char* which was a bit of old code where it wasn't and shouldn't be converted. Just removed .toLatin1().data() now its perfect.
Thank you everyone for your help on this.
