PDA

View Full Version : QSqlQuery and single quotes using bindValue



Luc4
26th September 2010, 15:31
Hi! I created some tables using sqlite with Qt. My problem is that I noticed that using bindValue this way:

query.prepare("INSERT INTO monitored_files "
"VALUES (NULL, :fileName, :original_relative_dir_path, :backupped_relative_dir_path, :directory_id)");
query.bindValue(":fileName", entries.at(i));
query.bindValue(":original_relative_dir_path", processRelativeDirPath);
query.bindValue(":backupped_relative_dir_path", processRelativeDirPath);
query.bindValue(":directory_id", directoryId);
query.exec();
where entries.at(i) is P9040479.JPG and processRelativeDirPath is "." (without "s), I get this when listing the records in sqlite:

1|'P9040479.JPG'|'.'|'.'|1
this seems to be wrong as, if I write the query directly in sqlite:

insert into monitored_files values (null, 'P9040479.JPG', '.', '.', 1);
I get:

2|P9040479.JPG|.|.|1
The schema of my table is:

CREATE TABLE monitored_files (file_id INTEGER PRIMARY KEY,file_name VARCHAR(32767),original_relative_dir_path VARCHAR(32767),backupped_relative_dir_path VARCHAR(32767),directory_id INTEGER);
So it seems those Qt instructions are placing single quotes that shouldn't be there... My variables don't have those single quotes. Any idea where they come from?
Thanks!

wysota
26th September 2010, 16:14
If you select the data back, do you have the quotes in the result?

Luc4
26th September 2010, 16:29
If I understand correctly your question then yes, I get the single quotes.

I queried the table with a:

select * from monitored_files
and I printed all the file_name's. The result is the field with the quotes.
My problem started when I tried to query according to the value of the field. Using a simple:

select * from monitored_files where file_name='something'
returns not records even if that string is in the table. The same is for "... LIKE '%something'" and "... LIKE 'something%'", but not "... LIKE '%something%'". I suppose this is because the single quotes are stored in the database. Any idea why bindValue is placing the single quotes twice?
Thanks!

wysota
26th September 2010, 19:42
This works for me:

#include <QtSql>
#include <QtGui>

int main(int argc, char **argv){
QApplication app(argc, argv);
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(":memory");
db.open();
QSqlQuery qry("CREATE TABLE test (id integer, path varchar)");
qry.prepare("INSERT INTO test VALUES(1, :path)");
qry.bindValue(":path", "somedata");
qry.exec();
qry.exec("SELECT * FROM test");
while(qry.next()){
qDebug() << qry.value(0) << qry.value(1);
}

}

Result: QVariant(qlonglong, 1) QVariant(QString, "somedata")

Luc4
27th September 2010, 00:34
It works for me too, indeed. I just tried to run my code in release mode and it's working... I suspect I have some problems not with the code, but with the way the new Qt Creator manages the builds. Thank you very much.