Qt Forum

    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search
    • Unsolved

    Solved QSqlQuery, QMYSQL driver, and "multiple statements"

    General and Desktop
    2
    4
    2301
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • JonB
      JonB last edited by

      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 1 Reply Last reply Reply Quote 0
      • JonB
        JonB @JonB last edited by 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.

        1 Reply Last reply Reply Quote 1
        • JonB
          JonB @JonB last edited by 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.

          1 Reply Last reply Reply Quote 1
          • A
            Ahmed000001 last edited by 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 1 Reply Last reply Reply Quote 0
            • JonB
              JonB @Ahmed000001 last edited by

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

              1 Reply Last reply Reply Quote 0
              • First post
                Last post