Inserting multiple rows in SQLite3

Question

How could I programmatically insert several rows into an sqlite3 table for iOS? This is a code snippet of my current method:

sqlite3 *database;

if(sqlite3_open([filePath UTF8String], &database) == SQLITE_OK) {
    const char *sqlStatement = "insert into TestTable (id, colorId) VALUES (?, ?)";
    sqlite3_stmt *compiledStatement;

    if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
    {
        for (int i = 0; i < colorsArray.count; i++) {
            sqlite3_bind_int(compiledStatement, 1, elementId);
            long element = [[colorsArray objectAtIndex:i] longValue];
            sqlite3_bind_int64(compiledStatement, 2, element);
        }
    }

    if(sqlite3_step(compiledStatement) == SQLITE_DONE) {
        sqlite3_finalize(compiledStatement);
    }
    else {
        NSLog(@"%d",sqlite3_step(compiledStatement));
    }
}
sqlite3_close(database);

This way I only get the first row inserted, how can I tell sqlite that I want each 'for' loop to be a row insertion? I couldn't find any example of this...

Thanks!

edzio27 · Accepted Answer · 2012-11-04 15:19:01Z

1

You have to run this statement:

sqlite3_step(compiledStatement) == SQLITE_DONE

after each insertion, and in your code i see that you run it only once, on the end.

answered Nov 4, 2012 at 15:19

edzio27

4,1207 gold badges35 silver badges48 bronze badges

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

1 Comment

edzio27 Over a year ago

If my answer help you with your problem, please mark it as correct.

Andr Devs · Accepted Answer · 2012-11-04 17:40:03Z

1

I got it working, this is now my code:

sqlite3 *database;

if(sqlite3_open([filePath UTF8String], &database) == SQLITE_OK) {
    const char *sqlStatement = "insert into TestTable (id, colorId) VALUES (?, ?)";
    sqlite3_stmt *compiledStatement;

    if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
    {
        for (int i = 0; i < colorsArray.count; i++) {
            sqlite3_bind_int(compiledStatement, 1, elementId);
            long element = [[colorsArray objectAtIndex:i] longValue];
            sqlite3_bind_int64(compiledStatement, 2, element);

            if (sqlite3_step(compiledStatement) == SQLITE_DONE) {
                if (i == (colorsArray.count - 1))
                    sqlite3_finalize(compiledStatement);
                else
                    sqlite3_reset(compiledStatement);
            }
            else {
                NSLog(@"row insertion error");
            }
        }
    }
}
sqlite3_close(database);

answered Nov 4, 2012 at 17:40

Andr Devs

2151 gold badge5 silver badges14 bronze badges

2 Comments

CL. Over a year ago

This code forgets to call sqlite3_finalize/reset if the last insert fails. Please not that calling sqlite3_reset immediately before sqlite3_finalize is harmless.

Sandeep Khade Over a year ago

Hi for (int i = 0; i < colorsArray.count; i++) this for loop is wrong totally please check it is inserting only one record.

Collectives™ on Stack Overflow

Inserting multiple rows in SQLite3

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related