QSqlQuery, QMYSQL driver, and "multiple statements"
-
This is a question about the behaviour of
QSqlQuery
, the QMYSQL driver, and "multiple statement handling" in the context of a MySQL (not SQLite) back-end. It probably requires some knowledge/understanding of MySQL behaviour, which is not strictly a Qt question, but may depend on the implementation ofQSqlQuery
.http://doc.qt.io/qt-5/qsqlquery.html:
It can be used to execute DML (data manipulation language) statements, [...], as well as DDL (data definition language) statements, such as CREATE TABLE. It can also be used to execute database-specific commands which are not standard SQL (e.g. SET DATESTYLE=ISO for PostgreSQL).
http://doc.qt.io/qt-5/qsqlquery.html#exec:
For SQLite, the query string can contain only one statement at a time. If more than one statement is given, the function returns false.
[The implication of the second quote: for MySQL, the string can contain multiple statements.]
I develop a large, and ever-growing, MySQL script file (for schema upgrading) in MySQL Workbench. An extract of the kind of content is:
-- This script is usually run from within Jinn, which has selected the correct Jinn database. -- It can be run manually from within MySQL Workbench if desired - select the correct schema first. -- Set MySQL statement delimiter to allow for multi-line statements/stored procedures DELIMITER $$ DROP FUNCTION IF EXISTS `jfn_upgrade_SchemaVersion` $$ CREATE FUNCTION `jfn_upgrade_SchemaVersion` () RETURNS int BEGIN -- Return the current schema/database version, stored in the `parameters` table DECLARE databaseVersion int; SELECT ParameterValue FROM parameters WHERE ParameterKey = 'database_version' INTO databaseVersion; RETURN IFNULL(databaseVersion, 0); END $$ -- Restore default MySQL statement delimiter DELIMITER ; CALL jsp_upgrade_UpdateSchemaVersion(0);
Note that this contains things like: comments,
DELIMITER
, DDL statements (CREATE FUNCTION
), multiple DML statements separated by;
(e.g. in function body).This script file can be read & executed from within MySQL Workbench, or via
mysql ... < scriptfile.sql
command-line.However, at end-user runtime, I wish to be able to execute the script file content from within my Qt app, via
QSqlQuery::exec(const QString &query)
, not via themysql
client program.Now, the question is: will my script file as shown (or possibly with very minor modification) be executable via
QSqlQuery
? Particular pitfalls include the use ofDELIMITER
, and whether I can send the whole file content intoQSqlQuery::exec()
and let it/QMYSQL driver handle all the multiple "statements" etc.?I have found it hard by Googling/reading to understand which parts of the script are acceptable to be interpreted by the actual MySQL back-end versus what the
mysql
client is doing/interpreting before sending stuff off to the back-end.My initial attempts, from Python, tried the standard
mysql.connector
low-level module/library. It soon became clear this simply could not handle the code (mis-parsed it, unable to cope with delimiters).I am about to start trying out
QSqlQuery
. However, it would not surprise me if there are errors I need to understand/restrictions on what I can pass to it, which will make it difficult to recognise whether I need to make slight tweaks or whether the approach is basically not doable. So I'd appreciate a heads-up on what I need to do or whether I'm wasting my time! -
This is a question about the behaviour of
QSqlQuery
, the QMYSQL driver, and "multiple statement handling" in the context of a MySQL (not SQLite) back-end. It probably requires some knowledge/understanding of MySQL behaviour, which is not strictly a Qt question, but may depend on the implementation ofQSqlQuery
.http://doc.qt.io/qt-5/qsqlquery.html:
It can be used to execute DML (data manipulation language) statements, [...], as well as DDL (data definition language) statements, such as CREATE TABLE. It can also be used to execute database-specific commands which are not standard SQL (e.g. SET DATESTYLE=ISO for PostgreSQL).
http://doc.qt.io/qt-5/qsqlquery.html#exec:
For SQLite, the query string can contain only one statement at a time. If more than one statement is given, the function returns false.
[The implication of the second quote: for MySQL, the string can contain multiple statements.]
I develop a large, and ever-growing, MySQL script file (for schema upgrading) in MySQL Workbench. An extract of the kind of content is:
-- This script is usually run from within Jinn, which has selected the correct Jinn database. -- It can be run manually from within MySQL Workbench if desired - select the correct schema first. -- Set MySQL statement delimiter to allow for multi-line statements/stored procedures DELIMITER $$ DROP FUNCTION IF EXISTS `jfn_upgrade_SchemaVersion` $$ CREATE FUNCTION `jfn_upgrade_SchemaVersion` () RETURNS int BEGIN -- Return the current schema/database version, stored in the `parameters` table DECLARE databaseVersion int; SELECT ParameterValue FROM parameters WHERE ParameterKey = 'database_version' INTO databaseVersion; RETURN IFNULL(databaseVersion, 0); END $$ -- Restore default MySQL statement delimiter DELIMITER ; CALL jsp_upgrade_UpdateSchemaVersion(0);
Note that this contains things like: comments,
DELIMITER
, DDL statements (CREATE FUNCTION
), multiple DML statements separated by;
(e.g. in function body).This script file can be read & executed from within MySQL Workbench, or via
mysql ... < scriptfile.sql
command-line.However, at end-user runtime, I wish to be able to execute the script file content from within my Qt app, via
QSqlQuery::exec(const QString &query)
, not via themysql
client program.Now, the question is: will my script file as shown (or possibly with very minor modification) be executable via
QSqlQuery
? Particular pitfalls include the use ofDELIMITER
, and whether I can send the whole file content intoQSqlQuery::exec()
and let it/QMYSQL driver handle all the multiple "statements" etc.?I have found it hard by Googling/reading to understand which parts of the script are acceptable to be interpreted by the actual MySQL back-end versus what the
mysql
client is doing/interpreting before sending stuff off to the back-end.My initial attempts, from Python, tried the standard
mysql.connector
low-level module/library. It soon became clear this simply could not handle the code (mis-parsed it, unable to cope with delimiters).I am about to start trying out
QSqlQuery
. However, it would not surprise me if there are errors I need to understand/restrictions on what I can pass to it, which will make it difficult to recognise whether I need to make slight tweaks or whether the approach is basically not doable. So I'd appreciate a heads-up on what I need to do or whether I'm wasting my time!OK, after experimentation, to answer my own question:
Everything in the whole "script-to
QSqlQuery
" principle works, except for theDELIMITER
business.As explained in https://stackoverflow.com/questions/10259504/delimiters-in-mysql, and as I suspected,
DELIMITER
is not a MySQL statement/keyword, it is 100% purely required bymysql
command-line client but forbidden in the MySQL back-end to whichQSqlQuery
sends the string. Irritatingmysql
requirement :(If I completely remove the
DELIMITER
lines, change all the$$
occurrences back to plain;
MySQL statement delimiter, and pass the complete file content as a string viaQSqlQuery
and on to the back-end, it all works as it should.So, I'd either have to remove all the
DELIMITER
stuff --- in which case I can't design using Workbench/mysql
and the script could never be run via those by the end-user if so desired --- or I'd have to delete-and-replace in the string from the source file content before sending toQSqlQuery
--- and I don't trust the required parsing to be robust enough to guarantee compatibility withmysql
.So, reluctantly, I've decided the safest/best option is to give up on
QSqlQuery
for this and allow my app to invokemysql
command-line at runtime to execute scripts.Should anyone wonder about this at a future date, the above should explain.
-
Very useful topic i spent about 7 hours searching for the proper way to know why my (MySQL workbench working code) does not work on QT APPS. thank you. I also want to ask you a question, how to ask a question like you asked here?
-
Very useful topic i spent about 7 hours searching for the proper way to know why my (MySQL workbench working code) does not work on QT APPS. thank you. I also want to ask you a question, how to ask a question like you asked here?
@Ahmed000001
Thank you for your thank you!I also want to ask you a question, how to ask a question like you asked here?
Not sure what you mean by this. If you just mean "how do you start a thread here to ask a question", you just click the New Topic button at the top of the page where you see a list of all threads, and off you go. People here are very helpful at answering.
If you mean "how[/why] did I put my [second] post in a green box": after posting my question I did my own further investigations and found my own answer to my issue. So I came back and typed that in, in case someone else (like you) found my discoveries useful. I then marked my own post as "solving" the question (via clicking on the vertical " ... " button at the bottom-right of the post, which put it in the green box and marked the thread as "solved". Somewhat unusually (at least compared to, say, stackoverflow, you can only mark your own post as solving your own question, not someone else's (though you could quote theirs and then mark that solved. It's nice for everyone if you mark a topic as "solved" when there is a good answer.