QSqlQuery::bindValue does not work

[l0ud]

Hi.

Here is the code:

QSqlQuery insQuery(*webDb);
insQuery.prepare("INSERT INTO crawl_virtuemart_categories (virtuemart_category_id, virtuemart_vendor_id, category_layout, category_product_layout, products_per_row, limit_list_start, limit_list_step, limit_list_max, limit_list_initial, ordering, finished) VALUES ( :id , 1, \"default\", \"default\", 3, 0, 10, 0, 10, 1, 1)");
insQuery.bindValue(":id",100);

QSqlQuery insQuery(*webDb);
insQuery.prepare("INSERT INTO crawl_virtuemart_categories (virtuemart_category_id, virtuemart_vendor_id, category_layout, category_product_layout, products_per_row, limit_list_start, limit_list_step, limit_list_max, limit_list_initial, ordering, finished) VALUES ( :id , 1, \"default\", \"default\", 3, 0, 10, 0, 10, 1, 1)");
insQuery.bindValue(":id",100);

To copy to clipboard, switch view to plain text mode 

Unfortunately, after execution I have syntax error, because of remaining :id instead of 100. But why? Shouldn't I mix "hardcoded" values with "dynamic" ones?

Thanks in advance.

[ChrisW67]

Sql string values are enclosed by single quotes not double quotes: this is the syntax error.

[l0ud]

It's not! I forgot to add this is mysql.

But anyway, the following code gives the same error:

QSqlQuery insQuery(*webDb);
               insQuery.prepare("INSERT INTO crawl_virtuemart_categories (virtuemart_category_id, virtuemart_vendor_id, category_layout, category_product_layout, products_per_row, limit_list_start, limit_list_step, limit_list_max, limit_list_initial, ordering, finished) VALUES ( :id , 1, 'default', 'default', 3, 0, 10, 0, 10, 1, 1)");
               insQuery.bindValue(":id",100);

QSqlQuery insQuery(*webDb);
               insQuery.prepare("INSERT INTO crawl_virtuemart_categories (virtuemart_category_id, virtuemart_vendor_id, category_layout, category_product_layout, products_per_row, limit_list_start, limit_list_step, limit_list_max, limit_list_initial, ordering, finished) VALUES ( :id , 1, 'default', 'default', 3, 0, 10, 0, 10, 1, 1)");
               insQuery.bindValue(":id",100);

To copy to clipboard, switch view to plain text mode 

you have error in your syntax near ':id , 1, 'default', ...

[ChrisW67]

It's not! I forgot to add this is mysql.

Well, since you didn't actual bother to specify either the actual error message or the database engine I assumed ANSI SQL, which does not allow double-quotes as literal string delimiters. MySQL will not accept double quotes if ANSI mode is turned on, so if you do not have complete control of the MySQL database settings and expect portability then you should use single quotes. Quite apart from that, single quotes are both easier to type and read in C++ code.

But anyway, the following code gives the same error:
...

You don't say what your table definition actually is. This, for example, works just fine:

#include <QtCore>
#include <QtSql>
#include <QDebug>
 
int main(int argc, char **argv)
{
    QCoreApplication app(argc, argv);
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("newton");
    db.setDatabaseName("test");
    db.setUserName("test");
    db.setPassword("password");
    bool ok = db.open();
    qDebug() << "Opened:" << ok;
 
    db.exec(
        "CREATE TABLE IF NOT EXISTS crawl_virtuemart_categories ("
        "  virtuemart_category_id int(11) NOT NULL,"
        "  virtuemart_vendor_id int(11) NOT NULL,"
        "  category_layout varchar(10) NOT NULL,"
        "  category_product_layout varchar(10) NOT NULL,"
        "  products_per_row int(11) NOT NULL,"
        "  limit_list_start int(11) NOT NULL,"
        "  limit_list_step int(11) NOT NULL,"
        "  limit_list_max int(11) NOT NULL,"
        "  limit_list_initial int(11) NOT NULL,"
        "  ordering int(11) NOT NULL,"
        "  finished int(11) NOT NULL,"
        "  PRIMARY KEY (virtuemart_category_id)"
        ") "
    );
 
// Your code
QSqlQuery insQuery;
insQuery.prepare("INSERT INTO crawl_virtuemart_categories (virtuemart_category_id, virtuemart_vendor_id, category_layout, category_product_layout, products_per_row, limit_list_start, limit_list_step, limit_list_max, limit_list_initial, ordering, finished) VALUES ( :id , 1, 'default', 'default', 3, 0, 10, 0, 10, 1, 1)");
insQuery.bindValue(":id",100);
// end of your code
 
    ok = insQuery.exec();
    qDebug() << "Insert:" << ok;
    if (!ok) 
        qDebug() << insQuery.lastError();
 
    return 0;
}

#include <QtCore>
#include <QtSql>
#include <QDebug>

int main(int argc, char **argv)
{
    QCoreApplication app(argc, argv);
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("newton");
    db.setDatabaseName("test");
    db.setUserName("test");
    db.setPassword("password");
    bool ok = db.open();
    qDebug() << "Opened:" << ok;

    db.exec(
        "CREATE TABLE IF NOT EXISTS crawl_virtuemart_categories ("
        "  virtuemart_category_id int(11) NOT NULL,"
        "  virtuemart_vendor_id int(11) NOT NULL,"
        "  category_layout varchar(10) NOT NULL,"
        "  category_product_layout varchar(10) NOT NULL,"
        "  products_per_row int(11) NOT NULL,"
        "  limit_list_start int(11) NOT NULL,"
        "  limit_list_step int(11) NOT NULL,"
        "  limit_list_max int(11) NOT NULL,"
        "  limit_list_initial int(11) NOT NULL,"
        "  ordering int(11) NOT NULL,"
        "  finished int(11) NOT NULL,"
        "  PRIMARY KEY (virtuemart_category_id)"
        ") "
    );

// Your code
QSqlQuery insQuery;
insQuery.prepare("INSERT INTO crawl_virtuemart_categories (virtuemart_category_id, virtuemart_vendor_id, category_layout, category_product_layout, products_per_row, limit_list_start, limit_list_step, limit_list_max, limit_list_initial, ordering, finished) VALUES ( :id , 1, 'default', 'default', 3, 0, 10, 0, 10, 1, 1)");
insQuery.bindValue(":id",100);
// end of your code

    ok = insQuery.exec();
    qDebug() << "Insert:" << ok;
    if (!ok) 
        qDebug() << insQuery.lastError();

    return 0;
}

To copy to clipboard, switch view to plain text mode 

(provided a row with id == 100 does not already exist)