Friday, November 28, 2008

Objective-C: SQLite Wrapper

I've written a simple SQLite Wrapper with two Examples one for Mac and one for the iPhone. The Wrapper class has the same source code for both platforms.

Here you can find the Mac Example Source Code and the iPhone Example Source Code.


This is a simple usage example:

Sqlite *sqlite = [[Sqlite alloc] init];

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,
NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *writableDBPath = [documentsDirectory
stringByAppendingPathComponent:@"SQLiteTest.db"];
if (![sqlite open:writableDBPath])
  return;

[sqlite executeNonQuery:@"CREATE TABLE test (key TEXT NOT NULL, value TEXT);"];
[sqlite executeNonQuery:@"DELETE FROM test;"];
[sqlite executeNonQuery:@"INSERT INTO test VALUES (?, ?);",
[Sqlite createUuid], @"PROVA"];
[sqlite executeNonQuery:@"INSERT INTO test VALUES (?, ?);",
[Sqlite createUuid], @"PROVA 2"];
[sqlite executeNonQuery:@"INSERT INTO test VALUES (?, ?);",
[Sqlite createUuid], @"PROVA 3"];

NSArray *results = [sqlite executeQuery:@"SELECT * FROM test;"];
for (NSDictionary *dictionary in results) {
  NSLog(@"Row");
  for (NSString *key in [dictionary keyEnumerator])
    NSLog(@" - %@ %@", key, [dictionary objectForKey:key]);
}

[results release];
[sqlite release];

15 comments:

  1. [...] bookmarks tagged objective Objective-C: SQLite Wrapper : Th30z - Coding on th... saved by 5 others     justkidding19 bookmarked on 12/07/08 | [...]

    ReplyDelete
  2. Wow. I found this about 6 months too late. Could have saved me a lot of effort. I'm new to DB development (laughable in itself for a 20-year veteran programmer) and I keep thinking "There has to be an easier way -- so much duplicate code." And here is the solution -- certainly good enough for my use.

    Thank you!

    ReplyDelete
  3. Neat wrapper. I've noticed a few things missing.

    Most importantly, since the executeQuery method returns an array of dictionary object, the only way to get the column names is to get the keys from one of the dictionary (row) objects (eg using allKeys). Unfortunately, keys are returned from a dictionary in no guaranteed order. So, although your wrapper code adds the cells of the result in order of columns generated by the SQLite query, when we extract the column names from the resulting dictionary rows, the order often changes.

    For simple queries on tables, the order is most often consistent with the query. But when querying a view, for instance, the order is most often changed.

    You need to provide a hook to grab the column names of the query result as an ordered array.

    Similarly, you need to provide a way to grab the declared_types of the result columns.

    Is there any cost to use your library? Do you accept modification submissions?

    Hope this helps,
    Tom

    ReplyDelete
  4. Thank you! This saves me at least an hour of coding!

    ReplyDelete
  5. Hey,
    thanks for the great Wrapper.

    Is it possible to display characters like german umlaute (ä, ö, ü) correctly?

    thanks!

    ReplyDelete
  6. Follow the above "Stefano Falda" comment, that contains the UTF8 fix.

    ReplyDelete
  7. Sorry, i have not seen that :)

    Thanks for you work!

    ReplyDelete
  8. Hi Matteo,

    Thanks for posting your code.
    I’ve found that your code has some problem with french accented characters when I insert data, so I’ve modified the bindObject routine:

    ...
    ...
    } else {
    sqlite3_bind_text(stmt, idx, [[obj description] UTF8String], -1, SQLITE_STATIC);
    }
    //start of added code
    } else if ([obj isKindOfClass:[NSString class]]) {
    sqlite3_bind_text(stmt, idx, [(NSString*)obj cStringUsingEncoding : [NSString defaultCStringEncoding]], -1, SQLITE_STATIC);
    //end of added code
    } else {
    sqlite3_bind_text(stmt, idx, [[obj description] UTF8String], -1, SQLITE_STATIC);
    }

    This work fine for me, on iPhone

    ReplyDelete
  9. [...] directory/bundle. Jmac Hey this code worked for me thanks ... I am using a Wrapper from : Objective-C: SQLite Wrapper : Th30z – Coding on the Fly will this cause any issue ahead [...]

    ReplyDelete
  10. Hi,

    I was just wondering if one could use this code in a commercial app? I'm referring to licensing. You havent released it as open-source.

    Aman

    ReplyDelete
  11. Hi,
    Same question as Aman asked above. Can we use this code in commercial App ? What's the license for this ?

    ReplyDelete
  12. Objective-C SQLite Wrapper is under BSD License

    ReplyDelete
  13. Hi , it seemes that the download link is broken. Can you please send me a another link or the files itselves. My email is: yamil.alburquerque@gmail.com

    ReplyDelete
  14. checkout the github https://github.com/matteobertozzi/blog-code/tree/master/zips

    ReplyDelete