QSqlError is empty when binding parameter that does not exist

[the_]

Hi,

just not sure how to deal with that:

I prepare a query with some parameters, binding values to these parameters, the count matches but the names do not.

Create statement may look like like this (sqlite table)
create table mytable (col1 text, col2 varchar, col3 varchar)
When I do this on an insert query

q.prepare("insert into mytable values(:val1,:val2,:val3)

and then do a

q.bindValue(":val1",value1);
q.bindValue(":val2",value2);
q.bindValue(":Val3",value3);
q.exec();
qDebug() << q.lastError();

it prints out QSqlError("","","") but col3 is a NULL value in the table.

Any ideas why this happens that way and does not throw any errors?
(Maybe wrong sub forum :) )

[kshegunov]

@the_
Hi,
For bind to work the column name have to match, and since you don't bind anything to :val3 you get QVariant() as value (the default) which in turn is put as NULL in table. On the other hand you bind a value to :Val3, but you don't have a placeholder in the prepared query for that, so it's silently ignored. You have no errors in your SQL and QSqlQuery::lastError() returns nothing sensible, as expected.

just not sure how to deal with that

Well, I suggest binding values to existing placeholders, aside from that there's insufficient information to advise anything more. You can also switch to indexed bindings, i.e. QSqlQuery::addBindValue, if that suits you better.

Kind regards.

[the_]

Hi @kshegunov ,

I just came across this when having a typo in a large insert query with many parameters.

The thing is when I do same with for example PDO in PHP it gives me a warning
again a simple and stuipd example

$con = new PDO("mysql:host=localhost;dbname=test","userpassword');
$con->prepare("insert into test2 values (:id,:content)");
$req = $con->prepare("insert into test2 values (:id,:content)");
$req->bindValue(":id",1234);
$req->bindValue(":Contents",0);
$req->execute();

• PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in - on line x if the names do not match
  or
• DOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in - on line x if the number of parameters are not equal

So for me it looks like that QSqlQuery only looks if the number of bound parameters is correct but does not care about their names. QSqlQuery::addBindValue is a solution if someone does not double double check (like me sometimes :) ) the names of the parameters

Thanks

[kshegunov]

@the_

The thing is when I do same with for example PDO in PHP it gives me a warning

Qt's SQL driver should probably throw a warning at you as well, and if it doesn't it may be a minor bug. But in any case this doesn't elevate to an error, much less an SQL error, at least in my opinion. You could try searching in the bugtracker if something's reported about this.

So for me it looks like that QSqlQuery only looks if the number of bound parameters is correct but does not care about their names.

Without looking at the source of the SQL driver, I can't tell if that's the case, and you didn't mention what database you're using as well.

Kind regards.