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.

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!