Qt parsing sql statement

[Hanses]

Hi everyone,

I'm relatively new to Qt and sql. Been building my own Qt driver/plugin for a relatively unknown database. Everything is working and going all fine. However, I have run into a weird problem.

Qt parses my statements. This meaning when I prepare the statement:

QVERIFY(query.prepare("insert into tab_char values(?,?,?)"));

The actual statement being sent to the database is:

"insert into tab_char values(:a,:bb,:bc)"

That is usually fine, however can sometimes be problematic.

This would not be stange if it wasn't for the fact that in the "hasFeature" function for the driver both NamedPlaceholders and PositionalPlaceholders are set to be true and if you look at whats written on, http://doc.qt.io/qt-5/sql-sqlstatements.html, is says the following:

"Qt supports two placeholder syntaxes: named binding and positional binding. Here's an example of named binding. Both syntaxes work with all database drivers provided by Qt. If the database supports the syntax natively, Qt simply forwards the query to the DBMS; otherwise, Qt simulates the placeholder syntax by preprocessing the query."

I'm therefore a bit confused why Qt seem to be, per default, using namedPlaceholders. When I'm using "null indicators" it is problomatic that Qt parses my statements.

I'm aware of the hasFeature as i said before, so i know I can turn off namedPlaceholders etc. However i want both of them and I just don't understand why Qt parses the statement, the essential part of hasFeature looks like this:

bool QMSQLDriver::hasFeature(DriverFeature f) const {
switch (f) {
case NamedPlaceholdercans:
case PositionalPlaceholders:
return true;
}
}

Is there any other way to set the options for Qt to parse the sql statements? Would be very grateful for some help, and if the situation isn't clear just ask questions and I will explain it further.

Regards

[SGaist]

Hi and welcome to devnet,

You should also return false for all the other cases.

By the way, what about PreparedQueries ?

[Hanses]

Hi and thanks for replying SGaist.

Didn't post the whole function, just part of it. The whole hasFeature function looks like this:

bool QMSQLDriver::hasFeature(DriverFeature f) const {
switch (f) {
case LastInsertId:
case EventNotifications:
return false;
case Transactions:
case QuerySize:
case BLOB:
case Unicode:
case PreparedQueries:
case PositionalPlaceholders:
case NamedPlaceholders:
case BatchOperations:
case SimpleLocking:
case LowPrecisionNumbers:
case FinishQuery:
case MultipleResultSets:
case CancelQuery:
return true;
default:
return false;
}
}

Where all three of PreparedQueries, PositionalPlaceholders, NamedPlaceholders are true, which i thought they should be since the database has those feature. Most other cases are also true since they will be implemented in the database, also assumed that they wouldn't matter for the problem at hand.

Should I be setting any of the cases to false to be able to achieve no parsing of my statement?

Thanks again for replying I really appreciate you taking the time :)

[VRonin]

What I don't understand is: is this causing you any problem?

For what I understand Qt uniforms all the named bindings into positional bindings and then converts them to named binding if hasFeature(NamedPlaceholders)==true. This allows users to mix the two up query.prepare("insert into tab_char values(?,:namedVar,?)"). The fact that the driver receives only named binding is expected.

Do you have a test case where this normalisation causes a break in functionality?

[Hanses]

Hi and thanks for replying,

Well yes this is causing me problem.. When you bind using "?" and Qt parses the statement to ":aa" or something similar, this has an impact on how the database interprets the sql statement. For my datebase you need to use the syntax ":aa:bb" in the binding if you want to be able to set a variable to null, (using a null indicator). When then trying to set a value to null but the statement the database retrieves doesn't contain a null indicator name for that specific field it results in an error.

Ex on statements that are acceptable:

(1)
create table TAB (C1 integer);
insert into TAB (values ?);

(2)
create table TAB (C1 integer);
insert into TAB (values :a:i1);

Ex. of statements that are not acceptable:

(3)
create table TAB (C1 integer not null);
insert into TAB (values ?);

(4)
create table TAB (C1 integer primary key); -- primary key implicates not null
insert into TAB (values ?);

(5)
create table TAB (C1 integer);
insert into TAB (values :a);

(And then continuing by setting/binding the value to null.. )

Nr. 5 is the one causing me problem...
When you write a statement like nr 1, the database receives nr 5 and then when you try to bind the variable to null you get error cause the statement doesn't contain a null indicator name.

If it is expected that the driver receives only named binding, there is't much to do i guess... The problem is practically solved by turning off namedPlaceholders --> the database receives "?" which is better cause the database doesn't have as much restrictions for that case.

I just figured that there should be a way to make Qt not parse/ do anything with my sql statement.

But really appreciate you taking the time to answer :)

[SGaist]

Which version of Qt are you using ?

[Hanses]

Using Qt version 5.9.5

[SGaist]

I’d check with a more recent version to see if you still have the same behavior.