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
– Stefan Arentz, Commented Feb 4, 2010 at 18:16

jww · Accepted Answer · 2019-03-25 13:45:51Z

28

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

edited Mar 25, 2019 at 13:45

jww

104k107 gold badges453 silver badges975 bronze badges

answered Feb 29, 2012 at 10:24

DivineDesert

6,9641 gold badge32 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Hiren Gujarati Over a year ago

I have used this code for adding 600 rows, and wow...its there. Thanks.

drewi Over a year ago

I use this for 7000 rows. Works like a charm

Randika Vishman Over a year ago

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!

DivineDesert Over a year ago

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

Asawari Over a year ago

Thanks a ton! A perfect answer! Works like a magic. Used this to insert 21K records.

|

Community · Accepted Answer · 2017-05-23 10:29:28Z

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, 2017 at 10:29

CommunityBot

11 silver badge

answered Feb 10, 2010 at 18:35

kennytm

526k110 gold badges1.1k silver badges1k bronze badges

1 Comment

DivineDesert Over a year ago

Core data fails if you are to deal with complex queries with joins.

Kumar KL · Accepted Answer · 2013-12-26 06:55:40Z

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, 2013 at 6:55

Kumar KL

15.3k9 gold badges40 silver badges62 bronze badges

answered Feb 10, 2010 at 18:22

Pugalmuni

9,3908 gold badges60 silver badges97 bronze badges

2 Comments

kennytm Over a year ago

And my son's name is Robert'); DROP TABLE users; --

marcc Over a year ago

Ah little bobby tables we call him.

jww · Accepted Answer · 2019-03-25 13:44:43Z

0

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.

edited Mar 25, 2019 at 13:44

jww

104k107 gold badges453 silver badges975 bronze badges

answered Sep 6, 2013 at 16:11

mhrrt

9779 silver badges18 bronze badges

Comments

Paki · Accepted Answer · 2019-09-16 14:19:30Z

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

Collectives™ on Stack Overflow

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

5 Answers 5

9 Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

9 Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related