QSqlQuery::bindValue() : Is possible to bound values to multiple locations?

PetrS82

Hello,
I have following problem. I need to bind values in more location than one and it doesn't work.
In QT 4.8 documentation is note that

"Values cannot be bound to multiple locations in the query, eg:
INSERT INTO testtable (id, name, samename) VALUES (:id, :name, :name)
Binding to name will bind to the first :name, but not the second."

But in the documentation for newer versions of QT this note does not appear. Is it because it is fixed there?
But why it does not work in my Qt 5.10.1 ?

Thank you for the answer.

Christian Ehrlicher

It's still not possible

PetrS82

I' sorry exactly this example ( INSERT INTO testtable (id, name, samename) VALUES (:id, :name, :name) ) works.
But some exaples deos not work.

Christian Ehrlicher

@PetrS82 said in QSqlQuery::bindValue() : Is possible to bound values to multiple locations?:

But some exaples deos not work.

Which example does not work? 'Some' is a little bit imprecise.

SGaist

Hi,

What SQL backend are you using ?
Some have changed over time to implement that feature. IIRC, the SQLite driver recently was updated because the SQLite version used had that feature implemented.

PetrS82

Oracle 12.1.0

PetrS82

Hello,
I made following table (using Oracle SQL Developer):
create table testtable (KEY VARCHAR(255), VALUE VARCHAR(255), ID VARCHAR(255));

In C++ I have folloving code:

QSqlQuery query(db);

query.prepare("declare "
            "x NUMBER; "
            "begin "
            "insert into testtable(KEY, VALUE, ID) "
            "values ('INIT_TEST', :INIT_ID, :INIT_ID); "
            "insert INTO testtable(ID, KEY, VALUE) "
            "select :RESULT_ID, 'TEST_INIT_ID', VALUE "
            "from testtable "
            "where ID = :INIT_ID; "
            "end; ");


query.bindValue(":INIT_ID", 1000);
query.bindValue(":RESULT_ID", 2000);


if (query.exec()) {
    cout << "query.exec: OK" << endl;
} else {
    cout << "query.exec: error " << query.lastError().text().toStdString() << endl;
}

The expected result is:

 ID     |       KEY         |  VALUE
1000    |   INIT_TEST       |  1000
2000    |  TEST_INIT_ID     |  1000

But the real result is:

 ID     |        KEY       |  VALUE
1000    |   INIT_TEST      |  1000
1000    |  TEST_INIT_ID    |  1000

Both IDs are 1000 (and console output is "query.exec: OK").
Note that if I run this statement in SQL Developer, the result is as expected.

Christian Ehrlicher

As @SGaist and I already said - don't use it. It may work (with the Qt Sqlite driver) but obviously not with the Qt ODBC driver (which you seem to use, but did not yet told us).

PetrS82

This post is deleted!

PetrS82

@Christian-Ehrlicher QOCI

PetrS82

@Christian-Ehrlicher
Is QOCI what you asked me? I don't know more. Here is one line (edited) form config.xml file we use for connection

connection user="user_foo" password="password_foo" host="localhost" port="port_number" typ="QOCI" name="DATABASE_FOO" sid="ORCL" db_name="ORCL"

Christian Ehrlicher

QOCI is even older - it has not been touched since Qt4 times so if it did not work with Qt4 it will not with Qt5.

SGaist

IIRC, you can check if the driver provided that feature.

PetrS82

@Christian-Ehrlicher

cout << "NamedPlaceholders: " << db.driver()->hasFeature(QSqlDriver::NamedPlaceholders) << endl;
cout << "PositionalPlaceholders: " << db.driver()->hasFeature(QSqlDriver::PositionalPlaceholders) << endl;

OUTPUT:
NamedPlaceholders: 1
PositionalPlaceholders: 0

SGaist

@PetrS82 said in QSqlQuery::bindValue() : Is possible to bound values to multiple locations?:

query.prepare("declare "
"x NUMBER; "
"begin "
"insert into testtable(KEY, VALUE, ID) "
"values ('INIT_TEST', :INIT_ID, :INIT_ID); "
"insert INTO testtable(ID, KEY, VALUE) "
"select :RESULT_ID, 'TEST_INIT_ID', VALUE "
"from testtable "
"where ID = :INIT_ID; "
"end; ");

Isn't that set of queries invalid ?
Your second insert is missing the values part and your select clause explicitly asks to show the value of :RESULT_ID first and then the content of the other two columns. Which makes the output you show even more surprising.