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.