Bulk Database insertion not supported in iOS below 6.0

Question

When inserting 400 row in single sqlite query in iOS 6.0.

e.g:  INSERT INTO MYTAB (NAME ,ADD) values("name1","add1"),("name2","add2"),("name3","add3"),.....

, works perfectly but when running same code in 5.0 it gives error:

syntax error near ",".

This syntax wasn't supported before [SQLite 3.7.11](http://www.sqlite.org/releaselog/3_7_11.html). [It seems that ios 5 uses older version](http://stackoverflow.com/a/14288130/2286990).[Here's other relevant answer](http://stackoverflow.com/a/1609688/2286990). — lifus, Jul 05 '13 at 12:23
I had asked a question that it gives error what is its solution. — shreeji, Jul 05 '13 at 12:45

score 1 · Answer 1 · answered Jul 05 '13 at 11:50

Consider using a transaction and a prepared sqlite statement.

NSString* query;

if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK)
{
    query = @"BEGIN TRANSACTION";
    ret = sqlite3_exec(database, [query UTF8String], NULL, NULL, NULL);

    sqlite3_stmt *compiledStatement;
    NSString* str = @"INSERT INTO MYTAB (NAME, ADD) values (?,?)";
    sqlite3_prepare_v2(database, [str UTF8String], -1, &compiledStatement, NULL);

    for (int i=0; i<50; i++)
    {
        const char *name = "name";
        const char *add = "add";

        sqlite3_bind_text(compiledStatement, 1, name, -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(compiledStatement, 2, add, -1, SQLITE_TRANSIENT);

        ret = sqlite3_step(compiledStatement);
        ret = sqlite3_reset(compiledStatement);
    }

    sqlite3_finalize(compiledStatement);

    query = @"COMMIT TRANSACTION";
    ret = sqlite3_exec(database, [query UTF8String], NULL, NULL, NULL);

    sqlite3_close(database);
}

Thank you Vame . I had done same using sqlite3_exe but it gives error.. also had set "PRAGMA synchronous = OFF" after BEGIN TRANSACTION . but same error... — shreeji, Jul 05 '13 at 12:40
what kind of error? edit your question and post some code so we can see — Vame, Jul 05 '13 at 13:04

score 0 · Answer 2 · edited May 23 '17 at 12:05

This syntax wasn't supported before SQLite 3.7.11:

Enhance the INSERT syntax to allow multiple rows to be inserted via the VALUES clause.

It seems that iOS 5 uses 3.7.7, iOS 6 uses 3.7.13 and that the reason why query failed in iOS 5.

You may still use separate INSERT statements or workaround mentioned here or prepared statements, but if you simply need to pre-populate database efficiently then you may also follow suggestions mentioned here.

Bulk Database insertion not supported in iOS below 6.0

2 Answers2