QSqlQuery works with composed QString, but not with bound values

[i92guboj]

Hello.

I have subclassed QSqlQueryModel. While reimplementing setData() I created a method which uses a QSqlQuery with some bound values to update the relevant row in the database.

bool EditableSqlModel::setFirstName(int table, int id, const QString &firstName)
{
    QSqlQuery query;
    query.prepare("UPDATE :table SET desc = :string WHERE id = :id;");
 
    QString table_str;
    switch(table) {
    case 1:
        table_str = "presupuestados_cascos_y_accesorios";
        break;
    case 2:
        table_str = "presupuestados_complementos";
        break;
    case 3:
        table_str = "presupuestados_puertas_y_cajones";
        break;
    case 4:
        table_str = "presupuestados_electrodomesticos";
        break;
    default:
        return false;
    }
 
    query.bindValue(":table", table_str);
    query.bindValue(":string", firstName);
    query.bindValue(":id", id);
 
    if(query.exec())
    {
        return true;
    }
    else
    {
        qDebug() << Q_FUNC_INFO << query.lastQuery();
        qDebug() << Q_FUNC_INFO << query.lastError();
        qDebug() << Q_FUNC_INFO << query.boundValues();
        return false;
    }
}

bool EditableSqlModel::setFirstName(int table, int id, const QString &firstName)
{
    QSqlQuery query;
    query.prepare("UPDATE :table SET desc = :string WHERE id = :id;");

    QString table_str;
    switch(table) {
    case 1:
        table_str = "presupuestados_cascos_y_accesorios";
        break;
    case 2:
        table_str = "presupuestados_complementos";
        break;
    case 3:
        table_str = "presupuestados_puertas_y_cajones";
        break;
    case 4:
        table_str = "presupuestados_electrodomesticos";
        break;
    default:
        return false;
    }

    query.bindValue(":table", table_str);
    query.bindValue(":string", firstName);
    query.bindValue(":id", id);

    if(query.exec())
    {
        return true;
    }
    else
    {
        qDebug() << Q_FUNC_INFO << query.lastQuery();
        qDebug() << Q_FUNC_INFO << query.lastError();
        qDebug() << Q_FUNC_INFO << query.boundValues();
        return false;
    }
}

To copy to clipboard, switch view to plain text mode 

This, outputs:

bool EditableSqlModel::setFirstName(int, int, const QString&) "UPDATE :table SET desc = :string WHERE id = :id;" 
bool EditableSqlModel::setFirstName(int, int, const QString&) QSqlError(-1, "Parameter count mismatch", "") 
bool EditableSqlModel::setFirstName(int, int, const QString&) QMap((":id", QVariant(int, 2) ) ( ":string" ,  QVariant(QString, "3719000000, PUERTA ESP., 117.96") ) ( ":table" ,  QVariant(QString, "presupuestados_puertas_y_cajones") ) )

bool EditableSqlModel::setFirstName(int, int, const QString&) "UPDATE :table SET desc = :string WHERE id = :id;" 
bool EditableSqlModel::setFirstName(int, int, const QString&) QSqlError(-1, "Parameter count mismatch", "") 
bool EditableSqlModel::setFirstName(int, int, const QString&) QMap((":id", QVariant(int, 2) ) ( ":string" ,  QVariant(QString, "3719000000, PUERTA ESP., 117.96") ) ( ":table" ,  QVariant(QString, "presupuestados_puertas_y_cajones") ) )

To copy to clipboard, switch view to plain text mode 

But, if I use this instead, it just works!

bool EditableSqlModel::setFirstName(int table, int id, const QString &firstName)
{
    QSqlQuery query;
    //query.prepare("UPDATE :table SET desc = :string WHERE id = :id;");
 
    QString table_str;
    switch(table) {
    case 1:
        table_str = "presupuestados_cascos_y_accesorios";
        break;
    case 2:
        table_str = "presupuestados_complementos";
        break;
    case 3:
        table_str = "presupuestados_puertas_y_cajones";
        break;
    case 4:
        table_str = "presupuestados_electrodomesticos";
        break;
    default:
        return false;
    }
 
/*  query.bindValue(":table", table_str);
    query.bindValue(":string", firstName);
    query.bindValue(":id", id);
*/
    if(query.exec(QString("update %1 set desc = '%2' where id = %3")
                  .arg(table_str)
                  .arg(firstName)
                  .arg(id)))
    {
        return true;
    }
    else
    {
        qDebug() << Q_FUNC_INFO << query.lastQuery();
        qDebug() << Q_FUNC_INFO << query.lastError();
        qDebug() << Q_FUNC_INFO << query.boundValues();
        return false;
    }
}

bool EditableSqlModel::setFirstName(int table, int id, const QString &firstName)
{
    QSqlQuery query;
    //query.prepare("UPDATE :table SET desc = :string WHERE id = :id;");

    QString table_str;
    switch(table) {
    case 1:
        table_str = "presupuestados_cascos_y_accesorios";
        break;
    case 2:
        table_str = "presupuestados_complementos";
        break;
    case 3:
        table_str = "presupuestados_puertas_y_cajones";
        break;
    case 4:
        table_str = "presupuestados_electrodomesticos";
        break;
    default:
        return false;
    }

/*  query.bindValue(":table", table_str);
    query.bindValue(":string", firstName);
    query.bindValue(":id", id);
*/
    if(query.exec(QString("update %1 set desc = '%2' where id = %3")
                  .arg(table_str)
                  .arg(firstName)
                  .arg(id)))
    {
        return true;
    }
    else
    {
        qDebug() << Q_FUNC_INFO << query.lastQuery();
        qDebug() << Q_FUNC_INFO << query.lastError();
        qDebug() << Q_FUNC_INFO << query.boundValues();
        return false;
    }
}

To copy to clipboard, switch view to plain text mode 

Noting that:

1. The argument number, according to the debug output, is exactly the same than the number of placeholders
2. The type of such arguments is the same in both cases
3. The table is the same, and the database is always the default one

Can someone enlighten me on why this is failing?

Thank you.

[Lesiok]

What database ? Probably the name of the table must be given directly. This is not a feature of Qt but the database.

[ChrisW67]

Bind variables in Sql can only be used to specify values used in the WHERE (and possibly GROUP BY) clauses. Bind variables cannot be used to change the fundamental structure of the query. Bind variables are not the same as literal string substitutions.

If you find you need to run the same query over many identically structured tables then your database can probably be better designed.

[Lesiok]

Bind variables in Sql can only be used to specify values used in the WHERE (and possibly GROUP BY) clauses. Bind variables cannot be used to change the fundamental structure of the query. Bind variables are not the same as literal string substitutions.

False. You can bind most values except for the names of tables, functions etc. This is working

query.prepare("UPDATE m_table SET first_col=:new_value WHERE test_col BETWEEN :min_value AND :max_value");
query.bindValue(":new_value",123);
query.bindValue(":min_value",10);
query.bindValue(":max_value",19);

query.prepare("UPDATE m_table SET first_col=:new_value WHERE test_col BETWEEN :min_value AND :max_value");
query.bindValue(":new_value",123);
query.bindValue(":min_value",10);
query.bindValue(":max_value",19);

To copy to clipboard, switch view to plain text mode 

[i92guboj]

I see. I just assumed that bindValue() would do a regular string substitution.

Anyway, as suggested, I ended using a different approach here, both at db level as well as conceptually at model/view level.

To sum it up, bindValue() doesn't work when it comes to substitute table names, right?

Thank you for the answers, and forgive me for replying so late. I guess I really need to check my profile settings and see why the notification didn't arrive