Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

QSqlQuery, MariaDB, error in Insert but I can't see it.



  • I have written a C++ and this uses the QJSEngine to allow me to write JavaScript and then run the scripts without recompiling the C++.

    I've written a series of API routines in C++ that I expose to the JavaScript. I'm currently working on the database API and there is a problem with the demo insert, this is the text:

    INSERT INTO `tblTest` (`dtWhen`, `tmWhen`) VALUES (?,?)
    

    The parameters to along with this are:

    ["2020/7/16", "15:43:52"]
    

    I sent this to the C++ as a JSON object:

         {"op":"insert"
        ,"sql":"INSERT INTO `tblTest` (`dtWhen`, `tmWhen`) VALUES (?,?)"
     ,"fields":["biPri", "dtWhen", "tmWhen"]
     ,"params":[ds, tm]
    ,"success":"onRowResults"
    ,"failure":"onFailure"}
    

    This is part of the C++ that processes the JSON and performs the query:

    QString strSQL = itrSQL->toString();
    QSqlQuery query(strSQL);
    
    if ( itrParams != objDB.end() ) {
        QJsonArray aryParams = itrParams->toArray();
    
        for( int i=0; i<aryParams.count(); i++ )  {
            QVariant varParam(aryParams.at(i).toVariant());
            query.addBindValue(varParam);
        }
    }
    QSqlError err = query.lastError();
    

    The error I'm seeing is:

    INSERT INTO `tblTest` (`dtWhen`, `tmWhen`) VALUES (?,?)
    You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?,?)' at line 1 QMYSQL: Unable to execute query
    

    As far as I can see there is nothing wrong with the syntax and the parameters are valid, this is the table structure:

    CREATE TABLE `tblTest` (
      `biPri` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `dtWhen` date DEFAULT NULL,
      `tmWhen` time DEFAULT NULL,
      PRIMARY KEY (`biPri`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


  • Actually, just spotted the error after I posted the correct code is:

        QString strSQL = itrSQL->toString();
        QSqlQuery query;
        query.prepare(strSQL);
    
        if ( itrParams != objDB.end() ) {
            QJsonArray aryParams = itrParams->toArray();
    
            for( int i=0; i<aryParams.count(); i++ )  {
                QVariant varParam(aryParams.at(i).toVariant());
                query.addBindValue(varParam);
            }
        }
        query.exec();


  • Actually, just spotted the error after I posted the correct code is:

        QString strSQL = itrSQL->toString();
        QSqlQuery query;
        query.prepare(strSQL);
    
        if ( itrParams != objDB.end() ) {
            QJsonArray aryParams = itrParams->toArray();
    
            for( int i=0; i<aryParams.count(); i++ )  {
                QVariant varParam(aryParams.at(i).toVariant());
                query.addBindValue(varParam);
            }
        }
        query.exec();

Log in to reply