How to insert multiple rows of data into sqlite database using iphone programming

Question

I am new to iphone development. I want to insert several values ​​into my sqlite3 database and display the contents in a table. I can insert one row of data into my database and get it and display the data, but I cannot do with inserting several rows 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 rows of data into my table. Please help me. Thanks.

Answer 1

Try specifying sprintf statement below,

use this statement inside a loop with variable i .

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

Answer 2

This is the usual procedure that I usually 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; } 

Answer 3

SQLite does not support multi-row insertion; see Can I insert multiple rows at once into a SQLite database? .

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

(Also, use SQLite formatted string functions and the %q / %q specifier to avoid SQL injection, even if it is a local database.)

(And someone will suggest you use Core Data.)

Answer 4

You can use the following query to insert massive rows into a table.

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

This will add three entries at a time, while you can extend this time to n-time. Dynamically create your insert request as above, and the use and the rest of the code is the same as we usually use to insert a row.