Objective-C: SQLite Wrapper
November 28, 2008 Matteo Bertozzi | Filed Under Mac OS X, iPhone | 8 CommentsI’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];
[...] bookmarks tagged objective Objective-C: SQLite Wrapper : Th30z – Coding on th… saved by 5 others justkidding19 bookmarked on 12/07/08 | [...]
Pingback by Pages tagged "objective" — December 7, 2008 #
Hi Matteo,
I’ve found that your code has some problem with accented characters, so I’ve modified the columnData routine:
if (columnType == SQLITE_TEXT) {
// const unsigned char *text = sqlite3_column_text(stmt, index);
// return [NSString stringWithFormat:@"%s", text];
char *str = (char *)sqlite3_column_text(stmt, index);
return (str) ? [NSString stringWithUTF8String:str] : @”";
}
Thanks for posting your code.
Ste
Comment by Stefano Falda — April 1, 2009 #
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!
Comment by Michelle — July 18, 2009 #
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
Comment by Tom — September 15, 2009 #
Thank you! This saves me at least an hour of coding!
Comment by Randy — November 11, 2009 #
Hey,
thanks for the great Wrapper.
Is it possible to display characters like german umlaute (ä, ö, ü) correctly?
thanks!
Comment by jacky — November 25, 2009 #
Follow the above “Stefano Falda” comment, that contains the UTF8 fix.
Comment by Matteo Bertozzi — November 25, 2009 #
Sorry, i have not seen that
Thanks for you work!
Comment by jacky — November 26, 2009 #