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

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.