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.

This is not an iPhone question. I think you first need to figure out the SQLIte part and then come back with a more iPhone specific version. — Stefan Arentz, Feb 04 '10 at 18:16

score 28 · Answer 1 · edited Mar 25 '19 at 13:45

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

I have used this code for adding 600 rows, and wow...its there. Thanks. — Hiren Gujarati, Jun 28 '12 at 13:40
You are a life saver! I guess the trick is behind, that these insertions are done as a Single Transaction right? Please, it would be nice if you can give a short and sweet explanation! Thanks a lot! — Randika Vishman, Mar 28 '14 at 20:42
Yes trick lies between insertions are done in single transaction. Since SQLite consume more time to perform any single transaction. So this works like a charm :) — DivineDesert, Mar 29 '14 at 11:10
Please explain more on this. Is it important to implement COMMIT TRANSACTION? Won't sqlite3_step(compiledStatement) just insert the rows? @drewi — user2955351, Jan 13 '15 at 08:15
Thanks a ton! A perfect answer! Works like a magic. Used this to insert 21K records. — Asawari, Dec 01 '15 at 09:47
@DivineDesert I used your following solution it's work for me but I do one more thing in the background for an update some records which is coming from the server and it's `crash`. can you please give me a solution. — Ilesh P, Oct 13 '17 at 10:54

score 1 · Answer 2 · edited May 23 '17 at 10:29

1

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

edited May 23 '17 at 10:29

Community

1
1

answered Feb 10 '10 at 18:35

kennytm

510,854
105
1,084
1,005

Core data fails if you are to deal with complex queries with joins. – DivineDesert Jun 29 '12 at 04:20

score 0 · Answer 3 · edited Mar 25 '19 at 13:44

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.

score 0 · Accepted Answer · edited Dec 26 '13 at 06:55

0

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

edited Dec 26 '13 at 06:55

Kumar KL

15,315
9
38
60

answered Feb 10 '10 at 18:22

Pugalmuni

9,350
8
56
97

11

And my son's name is `Robert'); DROP TABLE users; --` – kennytm Feb 10 '10 at 18:30
6

Ah little bobby tables we call him. – marcc Feb 10 '10 at 18:34

score 0 · Answer 5 · answered Sep 16 '19 at 14:19

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

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

5 Answers5

Linked