Tag Archive for 'iphone'

/*
 * collect (id, name) pairs from 'list' table by tag_id.
 *   table 'list' consits of (id, name, tag_id) 
 */
void select_list(sqlite3 *handle, int tag_id)
{
   // construct query
   sqlite3_stmt *stmt = nil;
   const char *query = "SELECT id,name from list where tag_id=?";
 
   if (sqlite3_prepare_v2(handle, query, -1, &stmt, NULL) != SQLITE_OK) {
      handle_error([NSString stringWithFormat:
        @"Error: failed to prepare statement with message '%s'.",
        sqlite3_errmsg([db handle])]);
      return;
   }
 
   sqlite3_bind_int(stmt, 1, tag_id);
 
   // send query and collect results
   while (sqlite3_step(stmt) == SQLITE_ROW) {
      NSNumber *iD   = [NSNumber numberWithInt:sqlite3_column_int(stmt, 0)];
      NSString *name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 1)];
   }
 
   sqlite3_finalize(stmt);
}

(NSArray *) select_list:(ToddleDB *) db withTag:(NSNumber *)tag_id
{
   NSDictionary *where = [NSDictionary
      dictionaryWithObject:[NSString stringWithFormat:@"id=%d", [tag_id intValue]]
      forKey:@"WHERE"];
 
   NSArray *keys  = [NSArray arrayWithObjects:@"id", @"name", nil];                     // fields of table
   NSArray *types = [NSArray arrayWithObjects:[NSNumber class], [NSString class], nil]; // types of each fields
   NSDictionary *query = [NSDictionary dictionaryWithObjects:types forKeys:keys];       // run SQL SELECT
   return [toddleDB select:query from:@"list" option:where];
}