iPhone (and other SmartPhones) App Development in Java

• Write in Java, Translate to ObjC and Compile WITHOUT JailBreaking
• Watch it on YouTube
• A Blog on it

Some of iPhone Icons and Graphics Blogs

* Making iPhone Icons on Cocoia Blog
* How to Create Custom iPhone Icon for Your Website

iPhone Related Business Blogs and Articles, I Just Read

• Indie iPhone developer (owned by John Casasanta and Sophia Teutschler) splits, shops around Where To application
• About FutureTap
• 'Where To' Sales Blog
• FutureTap's Ortwin Gentz Blog About His Buying 'Where To'
• How to Price Your iPhone App out of Existence
  
• Trouble in the (99-cent) App Store
• Free iPhone Icons
• They're Apps to Make Money - Story of Indie iPhone developers Todd Moore and Keith Shepherd in Washington Post.
  

Objective C and SQLite: Query Framework to Separate the Database Layer

After messing around with sqlite3_stmt*, sqlite3_prepare_v2, sqlite3_bind_double, sqlite3_step, sqlite3_column_int and sqlite3_column_text to run each query on sqlite (from my objectiveC code) and separate code for each time gathering the result in an NSMutableArray* and NSDictionary (for each row), I was thinking of a way to have a method that do it autometically by just giving the query, with no need to tell how to store the results.

After some research in sqlite documentation, I found sqlite3_get_table method (http://www.sqlite.org/c3ref/free_table.html), which I used in the following way:

+ (NSMutableArray*) executeQuery: (const char *) sql
{
MyAppDelegate *appDelegate = (MyAppDelegate *)[[UIApplication sharedApplication] delegate];
char** azResult = NULL;
int nRows = 0;
int nColumns = 0;
char* errorMsg; //= malloc(255); // this is not required as sqlite do it itself

sqlite3_get_table(
 [appDelegate getdb],  /* An open database */
 sql,     /* SQL to be evaluated */
 &azResult,          /* Results of the query */
 &nRows,                 /* Number of result rows written here */
 &nColumns,              /* Number of result columns written here */
 &errorMsg      /* Error msg written here */
 );
if(azResult != NULL)
{
NSMutableArray* dataTable = [[[NSMutableArray alloc] init] autorelease];
nRows++; //because the header row is not account for in nRows

for (int i = 1; i < nRows; i++)
{
   NSMutableDictionary* row = [NSMutableDictionary dictionaryWithCapacity:nColumns];
   for(int j = 0; j < nColumns; j++)
   {
      NSString* key = [NSString stringWithUTF8String:azResult[j]];
      NSString*  value = [NSString stringWithUTF8String:azResult[(i*nColumns)+j]];

      [row setValue:value forKey:key] ;
   }
   [dataTable addObject:row];
}

//free(errorMsg); // no need to free, sqlite somehow doing it
sqlite3_free_table(azResult);
return dataTable;
}
else
{
   NSAssert1(0,@"Failed to execute query with message '%s'.",errorMsg);
   return nil;
}
}

Above code is capable of accepting any 'select' query and generate results in a standard data structure consist of NSMutableArray and NSMutableDictionary. Each element in the array represent a row, while each key-value pair in dictionary represent a column name and its value. Column name would be same as given in the query or as altered by 'as' keyword.

Using this method one can totally separate DB layer and application layer, and even in DB layer the method would be very short. Here is an example:

NSString* sql = [NSString stringWithFormat:@"select ID, aText, aFloat, aFloat2, "
"aText2 as myText2, aText3, 'aStringConstant' as myStringConstant "
"from myTable where afloat<=%1.6f and afloat2>=%1.6f Order by aText",
floatVariable, floatVariable2];

return [self executeQuery:[sql UTF8String]];

Related Links
SQLite Version 3 C/C++ API Reference
Convenience Routines For Running Queries