Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. MySQL procedure not recognized as SELECT statement
Forum Updated to NodeBB v4.3 + New Features

MySQL procedure not recognized as SELECT statement

Scheduled Pinned Locked Moved Solved General and Desktop
4 Posts 2 Posters 354 Views 1 Watching
  • 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.
  • E Offline
    E Offline
    EIntemporel
    wrote on 2 Dec 2024, 11:20 last edited by
    #1

    I got a simple procedure like that:

    CREATE
        PROCEDURE Test()
    BEGIN
        SELECT * FROM myTable;
    END;
    

    But when i want to call it in the code:

    QSqlQuery myQuery;
    myQuery.exec("CALL Test()");
    

    Then the query is not handled like a SELECT

    myQuery.isSelect(); // return false
    

    So every abstract helper around next(), first(), seek() etc... won't work because they start by looking if the query is a SELECT statement by calling the isSelect()
    I know I can use the myQuery.result()->handle() to then work around the MYSQL_STMT*, but this should work without that... I think

    1 Reply Last reply
    0
    • E Offline
      E Offline
      EIntemporel
      wrote on 2 Dec 2024, 15:35 last edited by
      #3

      I've rewriten a little the QMYSQLDriver to handle that case, what I do is simple:

      I've added a private boolean variable to know if a query is a procedure:

      class QMYSQLResultPrivate: public QSqlResultPrivate
      {
          ...
      
          bool isProcedure = false;
      };
      

      Then in the prepare(const QString&) i've added:

      bool QMYSQLResult::prepare(const QString& query)
      {
          Q_D(QMYSQLResult);
          if (!driver())
              return false;
      
          if (query.startsWith("CALL", Qt::CaseInsensitive))
              d->isProcedure = true;
      
          ...
      }
      

      and then in the exec(), I recalculate the bindInValues() if the !d->meta and if d->isProcedure

      bool QMYSQLResult::exec()
      {
          ...
          r = mysql_stmt_execute(d->stmt);
          ...
      
          // recalculate the bindInValues after executing the statement
          if (!d->meta && d->isProcedure)
              d->bindInValues();
      
          //if there is meta-data there is also data
          setSelect(d->meta);
          ...
      }
      

      Like that I can call my procedure and also benefit from the QSqlResult handling is there is any result

      1 Reply Last reply
      0
      • C Online
        C Online
        Christian Ehrlicher
        Lifetime Qt Champion
        wrote on 2 Dec 2024, 13:49 last edited by
        #2

        I don't see what the Qt mysql driver can do here. It's getting the information if it is a select or not from

        int numFields = mysql_field_count(d->drv_d_func()->mysql);
        setSelect(numFields != 0);
        

        So the driver does not tell us that it is a select.

        See https://dev.mysql.com/doc/c-api/8.4/en/mysql-field-count.html

        Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
        Visit the Qt Academy at https://academy.qt.io/catalog

        1 Reply Last reply
        1
        • E Offline
          E Offline
          EIntemporel
          wrote on 2 Dec 2024, 15:35 last edited by
          #3

          I've rewriten a little the QMYSQLDriver to handle that case, what I do is simple:

          I've added a private boolean variable to know if a query is a procedure:

          class QMYSQLResultPrivate: public QSqlResultPrivate
          {
              ...
          
              bool isProcedure = false;
          };
          

          Then in the prepare(const QString&) i've added:

          bool QMYSQLResult::prepare(const QString& query)
          {
              Q_D(QMYSQLResult);
              if (!driver())
                  return false;
          
              if (query.startsWith("CALL", Qt::CaseInsensitive))
                  d->isProcedure = true;
          
              ...
          }
          

          and then in the exec(), I recalculate the bindInValues() if the !d->meta and if d->isProcedure

          bool QMYSQLResult::exec()
          {
              ...
              r = mysql_stmt_execute(d->stmt);
              ...
          
              // recalculate the bindInValues after executing the statement
              if (!d->meta && d->isProcedure)
                  d->bindInValues();
          
              //if there is meta-data there is also data
              setSelect(d->meta);
              ...
          }
          

          Like that I can call my procedure and also benefit from the QSqlResult handling is there is any result

          1 Reply Last reply
          0
          • E Offline
            E Offline
            EIntemporel
            wrote on 2 Dec 2024, 15:47 last edited by
            #4

            For some reason mysql_stmt_result_metadata return a valid MYSQL_RES* for a calling procedure after the execution, this is why I set-up all this things to enable that "re-calculation"

            1 Reply Last reply
            0
            • E EIntemporel has marked this topic as solved on 2 Dec 2024, 15:47
            • E EIntemporel has marked this topic as solved on 2 Dec 2024, 15:48
            • E EIntemporel has marked this topic as solved on 2 Dec 2024, 15:48

            1/4

            2 Dec 2024, 11:20

            • Login

            • Login or register to search.
            1 out of 4
            • First post
              1/4
              Last post
            0
            • Categories
            • Recent
            • Tags
            • Popular
            • Users
            • Groups
            • Search
            • Get Qt Extensions
            • Unsolved