CODEWITHSUNDEEP
iphoneobjective-cinsertsqlite
Warrior
Warrior

Reputation: 39374

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

Upvotes: 4

Views: 13582

Answers (5)

Paki
Paki

Reputation: 63

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

func bulkInsert(products: [[String: Any]]) {
    var insertStatement: OpaquePointer? = nil
    var 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? = nil

    if sqlite3_prepare_v2(self.sqliteManager.sqliteDB, statement, -1, &compiledStatement, nil) == SQLITE_OK {
        for productDict in products {
            let id = productDict["SupplierID"] as! NSString
            let 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))

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

            sqlite3_reset(compiledStatement);
        }
    }

    // COMMIT
    statement = "COMMIT TRANSACTION";
    var commitStatement: OpaquePointer? = nil

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

Upvotes: 0

DivineDesert
DivineDesert

Reputation: 6954

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

Upvotes: 28

mhrrt
mhrrt

Reputation: 977

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

insert or replace into <TableName> ([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.

Upvotes: 0

Pugalmuni
Pugalmuni

Reputation: 9390

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

Upvotes: 0

kennytm
kennytm

Reputation: 523214

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

Upvotes: 1

Related Questions