Yeah, I know I can do that, and that's my backup option. I just wasn't
sure how much performance suffers in this situation because this has to
keep re-compiling the SQL statements (whereas right now, all my SQL
statements are only prepared once at runtime and then the appropriate
values are simply bound). I was hoping that I could bypass figuring out
the precise performance hit if I could figure out a clever way of doing
this (right now sqlite only accounts for about 16% of the time spent).
Thanks anyways
Dennis Cote wrote:
Vitali Lovich wrote:
Maybe there's something else I can do to get around it for my
purposes. What I have for instance is a table representing ID3
tags. What I want to do is select from the table any existing tags
that match a variable number of fields - for instance, I can say I
want to match using title & artist only or I using all the fields
such as title, artist, album etc... The LIKE comparison allowed me
to use the % wildcard to bind to fields I don't care about - is there
any other way using some other comparison operator (going under the
restriction that I don't want to modify the values I'm using to query).
Vitali,
If I understand you correctly, you have a table with columns like this:
CREATE TABLE id3_tags (
title text,
artist text,
album text,
...
);
And you want to do queries that match one or more of these columns.
I would suggest that you build your SQL query based on the columns the
user selected to match. Then you can bind the user supplied text to
match only those columns and you don't even look at the columns that
are not supposed to match (instead of matching everything with a
wildcard). Something like this:
//build the query
int columns = 0;
string sql = "SELECT * from id3_tags ";
if (user_selected_title) {
sql += columns++ ? "AND " : "WHERE ";
sql += "title = :title ";
}
if (user_selected_artist) {
sql += columns++ ? "AND " : "WHERE ";
sql += "artist = :artist ";
}
if (user_selected_album) {
sql += columns++ ? "AND " : "WHERE ";
sql += "album = :album ";
}
... // repeat for all columns
// prepare the query
sqlite3_stmt* query;
int rc = sqlite3_prepare(db, sql.c_str(), -1, &query, NULL);
// bind parameters to the query
if (user_selected_title) {
int idx = sqlite3_bind_parameter_index(query, ":title");
sqlite3_bind_text(query, idx, users_title, -1, SQLITE_STATIC);
}
if (user_selected_artist) {
int idx = sqlite3_bind_parameter_index(query, ":artist");
sqlite3_bind_text(query, idx, users_artist, -1, SQLITE_STATIC);
}
if (user_selected_album) {
int idx = sqlite3_bind_parameter_index(query, ":album");
sqlite3_bind_text(query, idx, users_album, -1, SQLITE_STATIC);
}
... // repeat for all columns
// execute the query
do {
rc = sqlite3_step(query);
if (rc == SQLITE_ROW) {
// use sqlite3_column_* to retrieve results
}
} while rc != SQLITE_DONE
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
