Here a simple tutorial to use SQLite in Objective-C to make complex iphone|ipad applications that uses a database!
First of all, you NEED to download Firefox, after that you need to download and install SQLite Manager Add On for Firefox from here.
The plugin will be used later…
Now, create a new project and import SQLite library. Generally it is located here:
/Developer/Platforms/iPhoneOS.platform/Developer/SDKs/iPhoneOS.X.X.X.sdk/usr/lib/libsqlite3.dylib
In your header file, you need t add the import and create sqlite3 class in your interface:
[code lang=”java” autolinks=”false” collapse=”false” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”false”]#import <sqlite3 .h>
[…]
sqlite3 *database;[/code]
I show only three method that uses the db, SELECT, DELETE, INSERT.
READ DATA (like SELECT * FROM XXX)
[code lang=”java” autolinks=”false” collapse=”false” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”false”]- (NSMutableArray*) readDataFromDatabase
{
dataArray = [[NSMutableArray alloc] init];
dataStored = [[NSMutableDictionary alloc] init];
NSString *name;
NSString *address;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"MyDB.sqlite"];
if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
const char *sql = "SELECT * FROM tabella";
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
while (sqlite3_step(statement) == SQLITE_ROW) {
name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
address = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
[dataStored setObject:name forKey:@"name"];
[dataStored setObject:address forKey:@"address"];
[dataArray addObject:[dataStored copy]];
}
}
sqlite3_finalize(statement);
}
sqlite3_close(database);
[dataStored release];
return dataArray;
}[/code]
In this method, that return a NSMutableArray, I used a NSMutableDictionary (dataStored) and a NSMutableArray (dataArray).
Now we have a dataArray with lists of the select query!
DELETE DATA FROM xxx
[code lang=”java” autolinks=”false” collapse=”false” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”false”]- (void) deleteDataFromDatabase:(id) data
{
NSMutableDictionary *d = data;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"MyDB.sqlite"];
sqlite3_stmt *delete_statment = nil;
if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
if (delete_statment == nil) {
const char *sql = "DELETE FROM tabella WHERE name=? AND address=?";
if (sqlite3_prepare_v2(database, sql, -1, &delete_statment, NULL) != SQLITE_OK) {
NSAssert1(0, @"Error: failed to prepare statement with message ‘%s’.", sqlite3_errmsg(database));
}
}
sqlite3_bind_text(delete_statment, 2, [[d objectForKey:@"name"] UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(delete_statment, 3, [[d objectForKey:@"address"] UTF8String], -1, SQLITE_TRANSIENT);
int success = sqlite3_step(delete_statment);
if (success != SQLITE_DONE) {
NSAssert1(0, @"Error: failed to save priority with message ‘%s’.", sqlite3_errmsg(database));
} else {
sqlite3_reset(delete_statment);
}
}
sqlite3_close(database);
}[/code]
Here, you can delete a value from db. (delete from tabella where x=0 and y=0). It accept ad method parameter an object, that is necessary a NSMutableDictionary (first row).
INSERT DATA
[code lang=”java” autolinks=”false” collapse=”false” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”false”]- (void) insertDataInDatabase:(NSString*)name address:(NSString*)address
{
sqlite3_stmt *insert_statement = nil;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"MyDB.sqlite"];
if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
if (insert_statement == nil) {
static char *sql = "INSERT INTO tabella (name,address) VALUES(?,?)";
if (sqlite3_prepare_v2(database, sql, -1, &insert_statement, NULL) != SQLITE_OK) {
NSAssert1(0, @"Error: failed to prepare statement with message ‘%s’.", sqlite3_errmsg(database));
}
sqlite3_bind_text(insert_statement, 2, [name UTF8String], -1, SQLITE_TRANSIENT );
sqlite3_bind_text(insert_statement, 3, [address UTF8String], -1, SQLITE_TRANSIENT );
}
int success = sqlite3_step(insert_statement);
sqlite3_reset(insert_statement);
if (success != SQLITE_ERROR) {
NSLog(@"error");
}
}
sqlite3_finalize(insert_statement);
sqlite3_close(database);
}[/code]
In this method you can add values to the table.
First of all operations, you need call createEditableCopyOfDatabaseIfNeeded that locate (and copy to exec path) the db:
[code lang=”java” autolinks=”false” collapse=”false” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”false”]- (void) createEditableCopyOfDatabaseIfNeeded
{
BOOL success;
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"MyDB.sqlite"];
success = [fileManager fileExistsAtPath:writableDBPath];
if (success) return;
NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"MyDB.sqlite"];
success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
if (!success) {
NSAssert1(0, @"Failed to create writable database file with message ‘%@’.", [error localizedDescription]);
}
}[/code]
It copy current .sqlite file in your device.
Obviously, you can load MyDB.sqlite only a once, open it and mantain it opened until you completed operations, but this is a stupid example.
How works Firefox SQLite Manager plugin?
It’s a simple GUI that is used to create a DB, insert/edit/query values.
You need to save db file and import in your project.
Happy coding!
Rif: albertopasca.it