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]
-----------------------------------------------------------------------------
