How to insert multiple row of data to sqlite database by iphone programming

Question

I am new to iphone development.I want to insert multiple values into my sqlite3 database and display the content in the tableview.I am able to insert single row of data in to my database and retrieve it and display the data but i am not able to do with inserting multiple row of data.Here is my code...

-(void)initializeTableData
{
    sqlite3 *db=[DatabaseTestAppDelegate getNewDBConnection];
    sqlite3_stmt *statement=nil;
    sqlite3_stmt *statement1=nil;

    if (insert_MyObj_statement == nil)
    {
        const char sql2[] = "DELETE FROM user";
        sqlite3_prepare_v2(db, sql2, -1, &statement1, NULL);
        sqlite3_step(statement1);
        const char sql1[] = "INSERT INTO user (id,name) VALUES ('0','xxx')";
        int result=sqlite3_prepare_v2(db, sql1, -1, &insert_MyObj_statement, NULL);
    }
    sqlite3_step(insert_MyObj_statement);

    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);
}

Is there any other way to insert multiple row of data in to my table .Please help me out.Thanks.

Answer 1

This is the routine I generally use to insert data in bulk..

static sqlite3 *masterDB;
static sqlite3_stmt *init_statement = nil;

{
NSString* statement;

statement = @"BEGIN EXCLUSIVE TRANSACTION";

if (sqlite3_prepare_v2(masterDB, [statement UTF8String], -1, &init_statement, NULL) != SQLITE_OK) {
    printf("db error: %s\n", sqlite3_errmsg(masterDB)); 
    return NO;
}
if (sqlite3_step(init_statement) != SQLITE_DONE) {
    sqlite3_finalize(init_statement);
    printf("db error: %s\n", sqlite3_errmsg(masterDB)); 
    return NO;
}

NSTimeInterval timestampB = [[NSDate date] timeIntervalSince1970];
NSDateFormatter *dateFormat = [[NSDateFormatter alloc] init];
[dateFormat setDateFormat:@"MMM dd, yyyy"];
NSDate *now = [NSDate date];
NSString *dateTime  = [dateFormat stringFromDate:now];
[dateFormat release];
statement = @"insert into table(id, name) values(?,?)";
sqlite3_stmt *compiledStatement;

if(sqlite3_prepare_v2(masterDB, [statement UTF8String], -1, &compiledStatement, NULL) == SQLITE_OK)
{
    for(int i = 0; i < [aryList count]; i++){
        NSString *objName = [aryList objectAtIndex:i];
        sqlite3_bind_int(compiledStatement, 1, i );
        sqlite3_bind_text(compiledStatement, 2, [objName UTF8String], -1, SQLITE_TRANSIENT);            
        while(YES){
            NSInteger result = sqlite3_step(compiledStatement);
            if(result == SQLITE_DONE){
                break;
            }
            else if(result != SQLITE_BUSY){
                printf("db error: %s\n", sqlite3_errmsg(masterDB)); 
                break;
            }
        }
        sqlite3_reset(compiledStatement);

    }
    timestampB = [[NSDate date] timeIntervalSince1970] - timestampB;
    NSLog(@"Insert Time Taken: %f",timestampB);

    // COMMIT
    statement = @"COMMIT TRANSACTION";
    sqlite3_stmt *commitStatement;
    if (sqlite3_prepare_v2(masterDB, [statement UTF8String], -1, &commitStatement, NULL) != SQLITE_OK) {
        printf("db error: %s\n", sqlite3_errmsg(masterDB)); 
        return NO;
    }
    if (sqlite3_step(commitStatement) != SQLITE_DONE) {
        printf("db error: %s\n", sqlite3_errmsg(masterDB)); 
        return NO;
    }

    sqlite3_finalize(compiledStatement);
    sqlite3_finalize(commitStatement);
    return YES;
}

return YES;
}

Answer 2

SQLite doesn't support multiple-row insertion, see Is it possible to insert multiple rows at a time in an SQLite database?.

To insert multiple rows at once, you need to issue multiple INSERT statements.

(Also, use SQLite's formatted string functions and the %q/%Q specifier to avoid SQL injection — even if that's a local database.)

(And someone will suggest you to use Core Data.)