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

Reply via email to