How to insert data to sqlite database in iphone

Question

I am new to iphone development.I want to insert certain data into my database and retrieve it and display it in a table.I have created Database data.sqlite with table 'user' .The table has two values id(varchar) and name(varchar).I have inserted 3 rows of data in table via "insert into user value('1','xxxx');" in terminal window.I have created a navigation based applicaton named "testDb".In appdelegate i have two methods

- (void)createEditableCopyOfDatabaseIfNeeded {

NSLog(@"Creating editable copy of database");
// First, test for existence.
BOOL success;
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"data.sqlite"];
success = [fileManager fileExistsAtPath:writableDBPath];
if (success) return;
// The writable database does not exist, so copy the default to the appropriate location.
NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"data.sqlite"];
success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
if (!success) {
    NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
}
}

and

+(sqlite3 *) getNewDBConnection{
sqlite3 *newDBconnection;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"data.sqlite"];
// Open the database. The database was prepared outside the application.
if (sqlite3_open([path UTF8String], &newDBconnection) == SQLITE_OK) {

    NSLog(@"Database Successfully Opened :)");

} else {
    NSLog(@"Error in opening database :(");
}
return newDBconnection;
}

In rootviewcontroller

-(void)initializeTableData{
sqlite3 *db=[DatabaseTestAppDelegate getNewDBConnection];
sqlite3_stmt *statement=nil;
const char *sql="select * from user";
if(sqlite3_prepare_v2(db, sql, -1, &statement, NULL)!=SQLITE_OK)
    NSAssert1(0,@"error in preparing staement",sqlite3_errmsg(db));
else{
    while(sqlite3_step(statement)==SQLITE_ROW)
        [tableData addObject:[NSString stringWithFormat:@"%s",(char*)sqlite3_column_text(statement,1)]];
}
    sqlite3_finalize(statement);


}

- (void)viewDidLoad {
[super viewDidLoad];
tableData=[[NSMutableArray alloc]init];
[self addMyObjectIntoDatabase];
[self initializeTableData];
self.title=@"Database test";


}

To display the contents

- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath {

static NSString *CellIdentifier = @"Cell";

UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier];
if (cell == nil) {
    cell = [[[UITableViewCell alloc] initWithStyle:UITableViewCellStyleDefault reuseIdentifier:CellIdentifier] autorelease];
}
NSLog(@"before displaying data");
cell.textLabel.text=[tableData objectAtIndex:indexPath.row];
NSLog(@"after displaying data");
// Configure the cell.

return cell;
}

This is working fine to display the content of table.But now i want to insert another row to my table and display the inserted content along with previous content.Since i am new to iphone i learnt from tutorials to read the database and display its contents.Please guide me how to proceed further to my task of inserting and displaying again.Thanks.

Answer 1

If you're really interested in learning how to use SQLite to manage data in an iPhone app, I would recommend you to complete the following tutorial, as it's a very good introduction:

http://icodeblog.com/2008/08/19/iphone-programming-tutorial-creating-a-todo-list-using-sqlite-part-1/

The tutorial handles Selecting, Updating, Inserting and Deleting data in an iPhone app, using SQLite.

Answer 2

You basically have two recommended options (and using the C class is not one of them). FMDB is used by many any developers as a wrapper to Sqlite. http://code.google.com/p/flycode/source/browse/trunk/fmdb FMDB is lightweight and pretty easy to use.

Here is an example call:

NSString *path = [documentsDirectory stringByAppendingPathComponent:@"mysqlite.db"];    
appSettings.dao = [FMDatabase databaseWithPath:path];
FMResultSet *rs = [[appSettings dao] executeUpdate:@"SELECT * FROM mytable"];
while ([rs next]){
  // do stuff ...
}

This assumes am ApplicationSettings instance inside your AppDelegate .m file.

ApplicationSettings *appSettings = [ApplicationSettings sharedApplicationSettings];

Inserts are easy too:

sql = [NSString stringWithFormat:@"INSERT INTO table (intCol1, strCol2) VALUES ('%d','%@')", myObj.theNumber, myObj.theString];
[appSettings.dao executeUpdate:sql];

The other option is to use Core Data (available as of SDK 3.0). Core Data is meant to be quite fast and optimized, although it does limit you from doing non-standard stuff. Although it can lead to slower SQL queries unless you give due consideration to how you use it.

There's a tutorial on the iPhone Dev Center here