QPSQL + QT4 (Postgresql driver bug)

[l2succes]

Hi everyone,
So basically, I've been have some problems with QSQL code so i tested it with both the mysql driver and postgresql driver. It works perfectly with the mysql database however when i run the same thing with the postgresql driver i get the following error. And I have to use postgresql it's for my senior project



QPSQL: Unable to create query"
query failed: "ERROR: syntax error at or near "("
LINE 1: EXECUTE ('Don''t Say No', '21:03', 'Twenty One O Three', '/..
^


I don't know where that EXECUTE command comes from, and i check the error log on the server and the same query shows when i'm doing an INSERT

has an anyone seen this before ??
thanks in advance



QSqlQuery query("INSERT INTO tracks (title, artist, album, path, duration) VALUES ( ?, ?, ?, ?, ? )");

query.addBindValue(QString(f.tag()->title().toCString()));
query.addBindValue(QString(f.tag()->artist().toCString()));
query.addBindValue(QString(f.tag()->album().toCString()));
query.addBindValue(it.filePath());
query.addBindValue(QString(f.audioProperties()->length()));
if(query.exec()) {
qDebug() << query.lastQuery();
} else {
qDebug() << "query failed: " << query.lastError().text();
}

[schnitzel]

Doesn't the query execute right away if the string given as part of the constructor is not empty?
I think you need to use the syntax as shown here:
http://doc.qt.nokia.com/latest/qsqlquery.html#details

look for the section on approach to binding values.

A good example of binding can be seen in the querymodel example. Look at EditableSqlModel::setFirstName.

[l2succes]

If you mean doing this


SqlQuery query;
query.prepare("INSERT INTO person (id, forename, surname) "
"VALUES (:id, :forename, :surname)");
query.bindValue(":id", 1001);
query.bindValue(":forename", "Bart");
query.bindValue(":surname", "Simpson");
query.exec();

I've tried using both binding syntaxes, however I'm getting the same error when the query is actually executed

[schnitzel]

If you mean doing this


SqlQuery query;
query.prepare("INSERT INTO person (id, forename, surname) "
"VALUES (:id, :forename, :surname)");
query.bindValue(":id", 1001);
query.bindValue(":forename", "Bart");
query.bindValue(":surname", "Simpson");
query.exec();

I've tried using both binding syntaxes, however I'm getting the same error when the query is actually executed

Does a simple case work, i.e. without binding - just hard coding the values?
Also try from psql command line client to take Qt out of the loop.

[Lesiok]

What is a version of PostgreSQL server and Qt ?

[wysota]

So where is this "bug"? You have an unescaped appostrophe character so it doesn't work. Since psql uses apostrophes for string delimiters, the query syntax you use is irrelevant, you'll run into the same situation. EXECUTE is used by postgresql to well... execute prepared statements.

By the way, you can see how the driver encodes your query using QSqlDriver::formatValue() for the right driver (psql in your case). The docs say a single quote should be escaped by surrounding them with single quotes. See if QSqlDriver returns escaped data. Note it might not work if your field is of a wrong type.

[schnitzel]

So where is this "bug"? You have an unescaped appostrophe character so it doesn't work. Since psql uses apostrophes for string delimiters, the query syntax you use is irrelevant, you'll run into the same situation. EXECUTE is used by postgresql to well... execute prepared statements.

By the way, you can see how the driver encodes your query using QSqlDriver::formatValue() for the right driver (psql in your case). The docs say a single quote should be escaped by surrounding them with single quotes. See if QSqlDriver returns escaped data. Note it might not work if your field is of a wrong type.

well apparantly, the second example (without any quotes) was giving him the same error.

[wysota]

I still don't see a bug. We don't even know if query preparation was successful because the first two syntaxes used are incorrect (one needs to use prepare() to have a prepared query).

[l2succes]

I still don't see a bug. We don't even know if query preparation was successful because the first two syntaxes used are incorrect (one needs to use prepare() to have a prepared query).

Sorry, for taking so long to respond, never got a notice of your reply also I ended up using mysql for testing purposes. So I just got back to postgresql and I admit I spoke too soon, get your point. Anyway thanks for the help