Multiple Queries Not Running in FMDB

Question

I am using FMDB to create a SQLite database on an iPhone. I have initial.sql which has the form

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

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

 NSString * str = // string from file initial.sql [db executeUpdate: str]; 

This succeeds, but later I get a crash:

 no such table: Def 

Clearly, the second statement is not called. How can I do this so that all requests are called?

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

So, everything should work.

Answer 1

This one also bit me; it took me a whole morning going through FMDatabase and reading the sqlite3 API documentation to find it. I'm still not quite sure about the root cause of the problem, but according to this error in PHP , you need 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 behavior will occur, hence our confusion, and I would like someone with more experience with sqlite to come up 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 can rewrite this into a category rather than 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; } 

Note that executeBatch lacks many functions that make it unsuitable for many purposes. In particular, it does not check if the database is locked, it does not guarantee that FMDatabase itself is not locked, it does not support caching of statements.

If you need it, the above is a good starting point for the code. Happy hack!

Answer 2

FMDB v2.3 now has a built-in 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 an option that uses 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 }]; 

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