QSqlQuery

[rajko]

I don't know what I am doing wrong, so please help.

My query only works if I use backslash quotes in exec string:



query.exec("INSERT INTO \"tstSchema\".\"testTable\" (\"Name\") "
"VALUES ('John')" );


This is not the way is described in SQL programming guide in Qt Assistant, nor in the Postgre manual and this way is very clumsy. I'm using Win 7 64 bit, Qt 4.8.1, Qt Creator 2.5., Postgres 9.1.Thanks in advance.

[ChrisW67]

If you need to embed literal double-quotes into a C++ string you must escape them. It's not described in the Qt docs because this has nothing to do with Qt or QSqlQuery.

However, SQL typically uses only single quotes for literal strings. Identifiers like table names typically only need to be quoted (in non-standard ways) when they contains spaces of other reserved characters. Since your table and column names do not contain anything unusual your query could probably be written without escaped quotes at all.

[Lesiok]

What do you mean "works only with backslash quotes". What returns QSqlQuery::error and what is in PostgreSQL log ?

[rajko]

This is from SQL programming doc on http://qt-project.org/doc/qt-4.7/sql-sqlstatements.html#id-451de5ac-c061-4e44-89d3-cf2704e240a2



QSqlQuery query;
query.exec("INSERT INTO employee (id, name, salary) "
"VALUES (1001, 'Thad Beaumont', 65000)");


This doesn't work for me , it say QSql error, error near parentheses. If I want that this above works I have to do this;



QSqlQuery query;
query.exec("INSERT INTO employee (\"id\", \"name\", \"salary\") "
"VALUES (1001, 'Thad Beaumont', 65000)");


The same is if I use placeholders.

And it doesn't work with single quotes either. When I look in any object in Postgres every action described in SQL is double quoted, schema.table is double quoted like this "testSchema"."testTable", but in Postgres manual on page http://www.postgresql.org/docs/9.1/static/sql-insert.html is the same as in qt docs above but as I said it doesn't work, I tried several times.

[ChrisW67]

QSqlQuery query;
query.exec("INSERT INTO employee (id, name, salary) "
"VALUES (1001, 'Thad Beaumont', 65000)");


A perfectly good SQL statement. Works fine if the table employee exists.


This doesn't work for me , it say QSql error, error near parentheses. If I want that this above works I have to do this;


QSqlQuery query;
query.exec("INSERT INTO employee (\"id\", \"name\", \"salary\") "
"VALUES (1001, 'Thad Beaumont', 65000)");


Qt just returns the error message your RBDMS is providing. If your RDBMS is expecting all identifiers to be quoted then you must supply them that way. This has not a Qt requirement. The expectation of quoted identifiers lies at the RDBMS end, not the Qt end, of the connection.



The same is if I use placeholders.

You cannot use placeholders to substitute for identifiers in SQL statements. This would be an error that any RDBMS would through at you (if Qt doesn't pick it up first).



And it doesn't work with single quotes either. When I look in any object in Postgres every action described in SQL is double quoted, schema.table is double quoted like this "testSchema"."testTable", but in Postgres manual on page http://www.postgresql.org/docs/9.1/static/sql-insert.html is the same as in qt docs above but as I said it doesn't work, I tried several times.
I did not claim single quotes would work. I said that the quoting of identifiers is inconsistent (across RDBMS systems) but that the query you are using does not require quoted identifiers at all on PostgreSQL (Sqlite, Mysql, or any other system I am aware of).

PostgreSQL only requires quoted identifiers if the identifier would otherwise be interpreted as a keyword, or of you want to have tables/columns with case sensitive naming. Unquoted identifiers are treated as lower-case, so unquoted employee and quoted "employee" refer to the same table, while "Employee" is a distinct table. I know of no way to make PostgresSql require quoted identifiers at all times.


The code below works perfectly well on my fresh PostgreSQL 9.1 install (albeit with Qt 4.7.4).


#include <QtCore>
#include <QtSql>
#include <QDebug>

int main(int argc, char *argv[])
{
QCoreApplication app(argc, argv);

QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
db.setDatabaseName("testdb");
db.setUserName("test");
db.setPassword("password");
if (db.open()) {
QSqlQuery query;
bool ok = query.exec("create table employee (id int, name varchar(100), salary numeric)");
if (!ok) qDebug() << query.lastError();
ok = query.exec("insert into employee (id, name, salary) values (1, 'Fred', 99)");
if (!ok) qDebug() << query.lastError();
ok = query.exec("insert into employee (id, name, salary) values (2, 'George', 199)");
if (!ok) qDebug() << query.lastError();
ok = query.exec("select * from employee");
while (ok && query.next())
qDebug() << query.record();
ok = query.exec("drop table employee");
if (!ok) qDebug() << query.lastError();
}
else
qDebug() << db.lastError();

return 0;
}

[Lesiok]

PostgreSQL has a parameter named quote_all_identifiers. The default value is OFF but for you it has a value ON. Look at postgresql.conf file.

[ChrisW67]

As I understand that option it affects SQL generated by PostgreSql, e.g in EXPLAIN or dumps, not what it accepts in queries.
http://www.postgresql.org/docs/9.1/static/runtime-config-compatible.html#GUC-QUOTE-ALL-IDENTIFIERS

As a quick check I just ran my example with that option turned on. No difference: it works fine.

[rajko]

Thank you for your effort Chris, I made newbie mistake and I didn't test it from scratch. I created scheme and then table with quotes and from then on you have to use quotes no metter what you write. There is a link on http://stackoverflow.com/questions/6331504/omitting-the-double-quote-to-do-query-on-postgresql confirming this. Anyway, now it works, thanks again.