Solved QSqlQuery, QMYSQL driver, and "multiple statements"

[JonB]

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 of QSqlQuery.

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 the mysql 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 of DELIMITER, and whether I can send the whole file content into QSqlQuery::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!

[JonB]

OK, after experimentation, to answer my own question:

Everything in the whole "script-toQSqlQuery" principle works, except for the DELIMITER 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 by mysql command-line client but forbidden in the MySQL back-end to which QSqlQuery sends the string. Irritating mysql 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 via QSqlQuery 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 to QSqlQuery --- and I don't trust the required parsing to be robust enough to guarantee compatibility with mysql.

So, reluctantly, I've decided the safest/best option is to give up on QSqlQuery for this and allow my app to invoke mysql command-line at runtime to execute scripts.

Should anyone wonder about this at a future date, the above should explain.

[JonB]

OK, after experimentation, to answer my own question:

Everything in the whole "script-toQSqlQuery" principle works, except for the DELIMITER 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 by mysql command-line client but forbidden in the MySQL back-end to which QSqlQuery sends the string. Irritating mysql 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 via QSqlQuery 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 to QSqlQuery --- and I don't trust the required parsing to be robust enough to guarantee compatibility with mysql.

So, reluctantly, I've decided the safest/best option is to give up on QSqlQuery for this and allow my app to invoke mysql command-line at runtime to execute scripts.

Should anyone wonder about this at a future date, the above should explain.

[Ahmed000001]

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?

[JonB]

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