How to insert data into a SQLite database in iPhon

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 the table via "insert into user value('1','xxxx');" in the 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 into 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. How do I proceed further to my task of inserting and displaying again?

Answer 1

To insert a user with certain values, you need to create an SQL insert statement like. Lets say the User object has a first name and last name then it would look like:

NSString *insertStatement = [NSString stringWithFormat:@"INSERT INTO USER (FIRSTNAME, LASTNAME) VALUES (\"%@\", \"%@\")", user.firstName, user.lastName];

char *error;
if ( sqlite3_exec(databaseHandle, [insertStatement UTF8String], NULL, NULL, &error) == SQLITE_OK)

You can check a full example at: http://www.apptite.be/tutorial_ios_sqlite.php

Answer 2

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:

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 3

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

Answer 4

please check:

dbName = @"db.sqlite";
NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDir = [documentPaths objectAtIndex:0];
dbPath = [documentsDir stringByAppendingPathComponent:dbName];
BOOL success;
NSFileManager *fileManager = [NSFileManager defaultManager];
success = [fileManager fileExistsAtPath:dbPath];
if(success) return;
NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:dbName];
[fileManager copyItemAtPath:databasePathFromApp toPath:dbPath error:nil];

and

 sqlite3 *database ;
[app checkAndCreateDatabase];
// Open the database from the users filessytem
if(sqlite3_open([app.dbPath UTF8String], &database) == SQLITE_OK)
{
    sqlite3_stmt *compiledStatement1;
    NSString* query;

   query=[NSString stringWithFormat:@ " insert into test ( name  ,  company ,  phone ,  email ,  address ,  country ,  state ,  city,  zip , online_id , in_time , flag_delete, flag_update,type_id) values (?, ?, ?,?,?, ?, ?, ?, ?,?,?,? ,?,?)"];



    const char *compiledStatement = [query UTF8String];
    if(sqlite3_prepare_v2(database, compiledStatement, -1, &compiledStatement1, NULL) == SQLITE_OK)
    {
        sqlite3_bind_text(compiledStatement1, 1, [text  UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(compiledStatement1, 2, [text UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(compiledStatement1, 3, [text UTF8String], -1, SQLITE_TRANSIENT);
}
    // Loop through the results and add them to the feeds array
       if(SQLITE_DONE != sqlite3_step(compiledStatement1))
        NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));
    sqlite3_finalize(compiledStatement1);
    sqlite3_close(database);
   }