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

[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.