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;
}
}


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") ) )


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;
}
}


Noting that:


The argument number, according to the debug output, is exactly the same than the number of placeholders
The type of such arguments is the same in both cases
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);

[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 :o