How To Insert Multiple Row Of Data To Sqlite Database By Iphone Programming

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 da

Solution 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)); 
    returnNO;
}
if (sqlite3_step(init_statement) != SQLITE_DONE) {
    sqlite3_finalize(init_statement);
    printf("db error: %s\n", sqlite3_errmsg(masterDB)); 
    returnNO;
}

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;
            }
            elseif(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)); 
        returnNO;
    }
    if (sqlite3_step(commitStatement) != SQLITE_DONE) {
        printf("db error: %s\n", sqlite3_errmsg(masterDB)); 
        returnNO;
    }

    sqlite3_finalize(compiledStatement);
    sqlite3_finalize(commitStatement);
    returnYES;
}

returnYES;
}

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

Solution 3:

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

Solution 4:

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

insertor replace into<TableName> ([Column1],[Column2]) select Col1Val1,Col2Val1 unionselect ColVal2,Col2Val2 unionselect 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.

Solution 5:

I've used DevineDesert's answer. It's working great. Here is the same solution written in swift.

Do have in mind self.sqliteManager.sqliteDB - is an OpaquePointer for DB

funcbulkInsert(products: [[String: Any]]) {
    var insertStatement: OpaquePointer? =nilvar statement ="BEGIN EXCLUSIVE TRANSACTION;"if sqlite3_prepare_v2(self.sqliteManager.sqliteDB, statement, -1, &insertStatement, nil) !=SQLITE_OK {
        print("db error: %s\n", sqlite3_errmsg(self.sqliteManager.sqliteDB) ??"")
        return
    }

    if sqlite3_step(insertStatement) !=SQLITE_DONE {
        sqlite3_finalize(insertStatement)
        print("db error: %s\n", sqlite3_errmsg(self.sqliteManager.sqliteDB) ??"")
        return
    }

    statement ="insert into Products (SupplierID, DescriptFR) values(?,?)";
    var compiledStatement: OpaquePointer? =nilif sqlite3_prepare_v2(self.sqliteManager.sqliteDB, statement, -1, &compiledStatement, nil) ==SQLITE_OK {
        for productDict in products {
            let id = productDict["SupplierID"] as!NSStringlet description = productDict["DescriptFR"] as!NSString

            sqlite3_bind_text(compiledStatement, 1, id.utf8String, -1, unsafeBitCast(-1, to: sqlite3_destructor_type.self))
            sqlite3_bind_text(compiledStatement, 2, description.utf8String, -1, unsafeBitCast(-1, to: sqlite3_destructor_type.self))

            whiletrue {
                let result = sqlite3_step(compiledStatement)
                if result ==SQLITE_DONE {
                    break
                } elseif result !=SQLITE_BUSY {
                    print("db error: %s\n", sqlite3_errmsg(self.sqliteManager.sqliteDB) ??"")
                    break
                }
            }

            sqlite3_reset(compiledStatement);
        }
    }

    // COMMIT
    statement ="COMMIT TRANSACTION";
    var commitStatement: OpaquePointer? =nilif sqlite3_prepare_v2(self.sqliteManager.sqliteDB, statement, -1, &commitStatement, nil) !=SQLITE_OK {
        print("db error: %s\n", sqlite3_errmsg(self.sqliteManager.sqliteDB) ??"")
    }
    if sqlite3_step(commitStatement) !=SQLITE_DONE {
        print("db error: %s\n", sqlite3_errmsg(self.sqliteManager.sqliteDB) ??"")
    }

    sqlite3_finalize(compiledStatement);
    sqlite3_finalize(commitStatement);
}