Multiple Queries not Running in FMDB

Question

I'm using FMDB to create a SQLite database on iPhone. I have a initial.sql that is of the form

CREATE TABLE Abc ... ;
CREATE TABLE Def ... ;

I load this by loading the file into an NSString and running it

NSString * str = // string from file initial.sql

[db executeUpdate: str];

This succeeds but later on I get a failure:

no such table: Def

It's clear that the second statement is not being called. How can I do this so that all of the queries will be called?

According to the SQLite documentation: "The routines sqlite3_prepare_v2(), sqlite3_prepare(), sqlite3_prepare16(), sqlite3_prepare16_v2(), sqlite3_exec(), and sqlite3_get_table() accept an SQL statement list (sql-stmt-list) which is a semicolon-separated list of statements."

So, this should all work.

Exceptionable

See github.com/ccgus/fmdb/issues/59 – Archle

Answer 1

I got bitten by this one too; it took me an entire morning of stepping through FMDatabase and reading the sqlite3 API documentation to find it. I am still not entirely sure about the root cause of the issue, but according to this bug in PHP, it is necessary to call sqlite3_exec instead of preparing the statement with sqlite3_prepare_v2 and then calling sqlite3_step.

The documentation does not seem to suggest that this behaviour would happen, hence our confusion, and I would love for someone with more experience with sqlite to come forward with some hypotheses.

I solved this by developing a method to execute a batch of queries. Please find the code below. If you prefer, you could rewrite this into a category instead of just adding it to FMDatabase.h, your call.

Add this to the FMDatabase interface in FMDatabase.h:

- (BOOL)executeBatch:(NSString*)sql error:(NSError**)error;

Add this to the FMDatabase implementation in FMDatabase.m:

- (BOOL)executeBatch:(NSString *)sql error:(NSError**)error
{
    char* errorOutput;
    int responseCode = sqlite3_exec(db, [sql UTF8String], NULL, NULL, &errorOutput);

    if (errorOutput != nil)
    {
        *error = [NSError errorWithDomain:[NSString stringWithUTF8String:errorOutput]
                                     code:responseCode 
                                 userInfo:nil];
        return false;
    }

    return true;
}

Please note that there are many features missing from executeBatch which make it unsuitable for a lot of purposes. Specifically, it doesn't check to see if the database is locked, it doesn't make sure FMDatabase itself isn't locked, it doesn't support statement caching.

If you need that, the above is a good starting point to code it yourself. Happy hacking!

Navigator

I actually ended up just making sure that my batched queries were on different lines and splitting by the newline character. Then I surrounded it with BEGIN/COMMIT to make it all a single transaction. – Exceptionable

Answer 2

FMDB v2.3 now has a native wrapper for sqlite3_exec called executeStatements:

BOOL success;

NSString *sql = @"create table bulktest1 (id integer primary key autoincrement, x text);"
                 "create table bulktest2 (id integer primary key autoincrement, y text);"
                 "create table bulktest3 (id integer primary key autoincrement, z text);"
                 "insert into bulktest1 (x) values ('XXX');"
                 "insert into bulktest2 (y) values ('YYY');"
                 "insert into bulktest3 (z) values ('ZZZ');";

success = [db executeStatements:sql];

It also has a variant that employs the sqlite3_exec callback, implemented as a block:

sql = @"select count(*) as count from bulktest1;"
       "select count(*) as count from bulktest2;"
       "select count(*) as count from bulktest3;";

success = [db executeStatements:sql withResultBlock:^int(NSDictionary *dictionary) {
    NSInteger count = [dictionary[@"count"] integerValue];
    NSLog(@"Count = %d", count);
    return 0;   // if you return 0, it continues execution; return non-zero, it stops execution
}];

Hagiocracy

I guess most people don't realize that if you return non-zero it will stop. Besides, I think it is better to let your block decide whether you want to stop or not. As I suggest here github.com/ccgus/fmdb/issues/428 – Limacine

I guess it's possible that someone might not notice, but it is documented in the quick help in Xcode for this method. I'm not sure what you mean by "it is better to let your block decide whether you want to stop or not", though: That's what the purpose of the return 0 is, to tell it to continue. If you return a non-zero value, it will stop. – Hagiocracy

Answer 3

Split Batch Statement
Add in .h file:
#import "FMSQLStatementSplitter.h"
#import "FMDatabaseQueue.h"

FMSQLStatementSplitter can split batch sql statement into several separated statements, then [FMDatabase executeUpdate:] or other methods can be used to execute each separated statement:

FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:databasePath];
NSString *batchStatement = @"insert into ftest values ('hello;');"
                           @"insert into ftest values ('hi;');"
                           @"insert into ftest values ('not h!\\\\');"
                           @"insert into ftest values ('definitely not h!')";
NSArray *statements = [[FMSQLStatementSplitter sharedInstance] statementsFromBatchSqlStatement:batchStatement];
[queue inDatabase:^(FMDatabase *adb) {
    for (FMSplittedStatement *sqlittedStatement in statements)
    {
        [adb executeUpdate:sqlittedStatement.statementString];
    }
}];

Agenda

This "extra" has been replaced with a native wrapper to sqlite3_exec. Just call executeStatements. – Hagiocracy