How to insert multiple row of data to sqlite datab

问题:

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','ash'),('3','karna'),('2','kumar'),('5','siva')";
const char *sql1 = "INSERT INTO user (id,name) VALUES ('0','xxx')";
 int result=sqlite3_prepare_v2(db, sql1, -1, &insert_MyObj_statement, NULL);
    NSAssert1(result == SQLITE_OK, @"addMyObjectIntoDatabase: failed to prepare statement with err '%s'", sqlite3_errmsg(db));
}
 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.

回答1:

Try with sprintf statement given below,

use this statement inside the loop with variable i.

sprintf(buffer,"INSERT INTO user (name) VALUES ('%s');",[[names objectAtIndex:i] UTF8String]);

回答2:

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(beginStatement);
    sqlite3_finalize(compiledStatement);
    sqlite3_finalize(commitStatement);
    return YES;
}

return YES;
}

回答3:

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.)

回答4:

You can use following query to insert bulk rows to table.

insert or replace into ([Column1],[Column2]) select Col1Val1,Col2Val1 union select ColVal2,Col2Val2 union select Col1Val3,Col2Val3

This will add three record at a time you can extend this upto n-time. Dynamically create your insert query like above and use and rest of code is same as we generally use for inserting a row.