SQLite Problem

[natbobc]

Hi!!

Basically I want to do a simplified keyword search using the following SQL query;

SELECT body,created_on FROM entries WHERE body LIKE '%term%' ORDER BY created_on;

In the SQLite command line I receive the result set I expect. When I transfer it to Qt it simply does not work. I have tried the following;

QSqlQuery query;
query.prepare( "SELECT body,created_on FROM entries WHERE body LIKE '%?%' ORDER BY created_on" );
query.addBindValue( term );
query.exec();

QSqlQuery query;
query.prepare( "SELECT body,created_on FROM entries WHERE body LIKE '%?%' ORDER BY created_on" );
query.addBindValue( term );
query.exec();

To copy to clipboard, switch view to plain text mode 

As well as named bindings;

QSqlQuery query;
query.prepare( "SELECT body,created_on FROM entries WHERE body LIKE '%:term%' ORDER BY created_on" );
query.bindValue( ":term", term );
query.exec();

QSqlQuery query;
query.prepare( "SELECT body,created_on FROM entries WHERE body LIKE '%:term%' ORDER BY created_on" );
query.bindValue( ":term", term );
query.exec();

To copy to clipboard, switch view to plain text mode 

The QSqlError I get in both cases is reported as 2. The error text is "parameter count mismatch" in both cases.

Note a similar query applied without a conditional works fine in QT. The query that works is as follows;

query.prepare( "SELECT body,created_on FROM entries ORDER BY created_on" );

query.prepare( "SELECT body,created_on FROM entries ORDER BY created_on" );

To copy to clipboard, switch view to plain text mode 

Hard coding a value also works;

query.prepare( "SELECT body,created_on FROM entries WHERE body LIKE '%term%' ORDER BY created_on" );

query.prepare( "SELECT body,created_on FROM entries WHERE body LIKE '%term%' ORDER BY created_on" );

To copy to clipboard, switch view to plain text mode 

Obviously there is something I am missing. Are % not allowed when used with a bound value? I've skimmed through the documentation and haven't seen anything that would imply such limitations.

Any help would be greatly appreciated.

Nate

System Info:
~~~~~~~~
Mac OS X Tiger
QT 4.3.2
g++ v4.0.1

[DeepDiver]

Searching the Qt sources I did find the error message in the SQLite driver.
Looks like your query is not compliant with the SQLite definitions.

Let's have a look at the SQLite documentation ...

Good luck,

Tom

[elcuco]

"like" in sqlite3 works in a funky way, take a look into it's definition in that DB (or do the filtering in c++ code).

[natbobc]

"like" in sqlite3 works in a funky way, take a look into it's definition in that DB (or do the filtering in c++ code).

I'm pretty sure it's not at the DB level because the last example I gave works in QT which includes a like clause, it just doesn't use bound values. From what I've read in the documentation I'm left with the impression that sometimes bound values are deferred to the database to handle and other times implemented directly in QT dependent on what the database can handle. I'll try wading through the source, I think that's going to be my best bet at this point.

Thanks for the replies,

Nate

[DeepDiver]

In case of SQLite bound values are NOT handled by Qt but by the driver.
That's why the error message comes from the driver sources.

You need to have a look at the SQLite docs on host variable definition.
Maybe you ask in a SQLite forum ....


Good luck,

Tom

[SSS]

I had the same problem. To solve the problem, use the string with wildcards as your bindValue. However, do not use the apostrophes. I also used the ":" syntax. Here is an example that should work.

QString qsBind = "%" + qsYourString + "%";

query.prepare("SELECT * FROM People WHERE Name LIKE :contains;");
query.bindValue(":contains", qsBind);
query.exec();

Hope this helps.