A Cocoa / Objective-C wrapper around SQLite

Fastest way to insert many rows into sqlite db on iPhone

Can someone explain what the best way to insert a lot of data on the iPhone using FMDB is? I see things like using the beginTransaction command. I'm honestly not sure what this or setShouldCacheStatements do. I followed what code my coworker did so far, and this is what it looks like:

BOOL oldshouldcachestatements = _db.shouldCacheStatements;
[_db setShouldCacheStatements:YES];
[_db beginTransaction];
NSString *insertQuery = [[NSString alloc] initWithFormat:@"INSERT INTO %@ values(null, ?, ?, ?, ?, ?, ?, ?);", tableName];
[tableName release];
BOOL success;

bPtr += 2;
int *iPtr = (int *)bPtr;
int numRecords = *iPtr++;

for (NSInteger record = 0; record < numRecords; record++) {
    NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init];
    // Some business logic to read the binary stream  

    NSNumber *freq = aFreq > 0 ? [NSNumber numberWithDouble:100 * aFreq / 32768]: [NSNumber numberWithDouble:-1.0];

    // these fields were calculated in the business logic section
    success = [_db executeUpdate:insertQuery,
               [NSNumber numberWithInt:position],                                                                       
               [NSString stringWithFormat:@"%@%@", [self stringForTypeA:typeA], [self stringForTypeB:typeB]],     // methods are switch statements that look up the decimal number and return a string
               [NSString stringWithFormat:@"r%i", rID],                                                               
               [self stringForOriginal:original],                                                                    
               [self stringForModified:modified],                                                                    

    [pool drain];
[outerPool drain];

[_db commit];
[_db setShouldCacheStatements:oldshouldcachestatements];

Is this the fastest I can do? Is the writing the limitation of sqlite? I saw this: and wasn't sure if this implementation was the best with fmdb, or if there was any other thing I can do. Some other coworkers mentioned something about bulk inserts and optimziing that, but I'm not honestly sure what that means since this is my first sqlite encounter. Any thoughts or directions I can go research? Thanks!

Source: (StackOverflow)

"Apple Mach-O (id) linker" error when adding external libraries to project

I added FMDB to my project then added the frameworks libsqlite3.dylib and libsqlite3.0.dylib, but I still get the build error. If I remove the FMDB classes from my project then it builds just fine. What other things should I check?

Detailed info on the error:

Ld /Users/gmi/Library/Developer/Xcode/DerivedData/iNROMockUp5->gjmgpakyszrgwbbxnkdxehexacxm/Build/Products/Debug->iphonesimulator/ normal i386 cd /iOSDev/Testing/iNROMockUp5 setenv MACOSX_DEPLOYMENT_TARGET 10.6 setenv PATH >"/Developer/Platforms/iPhoneSimulator.platform/Developer/usr/bin:/Developer/usr/bin:/usr/bi>n:/bin:/usr/sbin:/sbin" /Developer/Platforms/iPhoneSimulator.platform/Developer/usr/bin/llvm-gcc-4.2 -arch i386 >-isysroot >/Developer/Platforms/iPhoneSimulator.platform/Developer/SDKs/iPhoneSimulator4.3.sdk ->L/Users/gmi/Library/Developer/Xcode/DerivedData/iNROMockUp5->gjmgpakyszrgwbbxnkdxehexacxm/Build/Products/Debug-iphonesimulator ->F/Users/gmi/Library/Developer/Xcode/DerivedData/iNROMockUp5->gjmgpakyszrgwbbxnkdxehexacxm/Build/Products/Debug-iphonesimulator -filelist >/Users/gmi/Library/Developer/Xcode/DerivedData/iNROMockUp5->gjmgpakyszrgwbbxnkdxehexacxm/Build/Intermediates/>iphonesimulator/ -mmacosx->version-min=10.6 -Xlinker -objc_abi_version -Xlinker 2 -lsqlite3 -lsqlite3.0 -framework >MapKit -framework UIKit -framework Foundation -framework CoreGraphics -o >/Users/gmi/Library/Developer/Xcode/DerivedData/iNROMockUp5->gjmgpakyszrgwbbxnkdxehexacxm/Build/Products/Debug->iphonesimulator/

ld: duplicate symbol _main in /Users/gmi/Library/Developer/Xcode/DerivedData/iNROMockUp5->gjmgpakyszrgwbbxnkdxehexacxm/Build/Intermediates/>iphonesimulator/ and >/Users/gmi/Library/Developer/Xcode/DerivedData/iNROMockUp5->gjmgpakyszrgwbbxnkdxehexacxm/Build/Intermediates/>iphonesimulator/ for architecture i386 collect2: ld returned 1 exit status Command /Developer/Platforms/iPhoneSimulator.platform/Developer/usr/bin/llvm-gcc-4.2 failed >with exit code 1

ld: duplicate symbol _main in /Users/gmi/Library/Developer/Xcode/DerivedData/iNROMockUp5->gjmgpakyszrgwbbxnkdxehexacxm/Build/Intermediates/>iphonesimulator/ and >/Users/gmi/Library/Developer/Xcode/DerivedData/iNROMockUp5->gjmgpakyszrgwbbxnkdxehexacxm/Build/Intermediates/>iphonesimulator/ for architecture i386

Command /Developer/Platforms/iPhoneSimulator.platform/Developer/usr/bin/llvm-gcc-4.2 failed with exit code 1

Source: (StackOverflow)

sqlite3 and fmdb nested FMResultSet is possible?

I'm trying to iterator through a master detail sort of tables and I'd like to populate the master/detail structures as I go. Apparently when I nest result sets I get a BAD Access exception:

FMDatabase *db = self.database;
[db open];
db.traceExecution = YES;
db.logsErrors = YES;
FMResultSet *rs = [db executeQuery:@"select group_id, label from main.preference_group order by group_id"];
while ([rs next])
    PreferenceGroup *pg = [[PreferenceGroup alloc] init];
    pg.group_id = [rs intForColumn:@"group_id"];
    pg.label = [rs stringForColumn:@"label"];
    pg.translatedLabel = NSLocalizedString(pg.label, nil);
    NSMutableArray * prefs = [[NSMutableArray alloc] init];
    [prefGroups addObject:prefs];
    FMResultSet *rs2 = [db executeQuery:@"select pref_id, label, value from main.preference where group_id = ? order by pref_id", pg.group_id, nil];
        while ([rs2 next])
            Preference * pref = [[Preference alloc] init];
            pref.group_id = pg.group_id;
            pref.pref_id = [rs2 intForColumn:@"pref_id"];
            pref.label = [rs2 stringForColumn:@"label"];
            pref.value = [rs2 stringForColumn:@"value"];
            pref.translatedLabel = NSLocalizedString(pref.value, nil);
            [prefs addObject:pref];
        [rs2 close];
    [rs close];
    [db close];

In the rs2 (second result set) I get the EXEC_BAD_ACCESS within FMDatabase class.

Is this a restriction of sqlite3/fmdb or am I doing something wrong here?

Source: (StackOverflow)

Keeping FMDB thread safe

I see in FMDB 2.0, the author added FMDatabaseQueue for threads. The example is:

// First, make your queue.

FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:aPath];
Then use it like so:

[queue inDatabase:^(FMDatabase *db) {
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:1]];
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:2]];
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:3]];

    FMResultSet *rs = [db executeQuery:@"select * from foo"];
    while ([rs next]) {
// An easy way to wrap things up in a transaction can be done like this:

[queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:1]];
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:2]];
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:3]];

    if (whoopsSomethingWrongHappened) {
        *rollback = YES;
    // etc…
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:4]];

Two questions, what is the databaseQueueWithPath parameter supposed to be? Is that the path of where my database lies?

Second question, I can see how this would work if you have multiple updates and you don't want them trampling on top of each other. But what about if you are inserting data into the database, but also want to access other data in the database so the user can still play with your app while their data is being inserting. Is that possible? Thanks!

Source: (StackOverflow)

EXC_BAD_ACCESS when using SQLite (FMDB) and threads on iOS 4.0

I am using FMDB to deal with my database which works fine. The app uses a background thread which is doing some work and needs to access the database. At the same time the main thread needs to run some quieries on the same database. FMDB itself has a little locking system, however, I added another to my classes.

Every query is only performed if my class indicates that the database is not in use. After performing the actions the database gets unlocked. This works as expected as long as the load is not too high. When I access a lot of data with the thread running on the main thread an EXC_BAD_ACCESS error occurs.

Here is the looking:

- (BOOL)isDatabaseLocked {
    return isDatabaseLocked;

- (Pile *)lockDatabase {
    isDatabaseLocked = YES;
    return self;        

- (FMDatabase *)lockedDatabase {
    @synchronized(self) {
        while ([self isDatabaseLocked]) {
            //NSLog(@"Waiting until database gets unlocked...");
        isDatabaseLocked = YES;
        return self.database;       

- (Pile *)unlockDatabase {
    isDatabaseLocked = NO;
    return self;            

The debugger says that the error occurs at [FMResultSet next] at the line

rc = sqlite3_step(statement.statement);

I double checked all retain counts and all objects do exist at this time. Again, it only occurs when the main thread starts a lot of queries while the background thread is running (which itself always produce heavy load). The error is always produced by the main thread, never by the background thread.

My last idea would be that both threads run lockedDatabase at the same time so they could get a database object. That's why I added the mutex locking via "@synchronized(self)". However, this did not help.

Does anybody have a clue?

Source: (StackOverflow)

How can I use FMDB as a Framework in an iPad application?

I am currently developing an iPad application. At one stage, there is a series of sliders and buttons, when you press the button, it stores the value of the slider into a table. First of all, I need to use SQLite to manage the database. I found a project called FMDB that is a wrapper around SQLite for iPhone/Mac apps. I am pretty new to iOS/Objective-c developpment and want to know how you add FMDB (or an FMDB framework) into an app.


Source: (StackOverflow)

Is there a tutorial about how to use FMDB, the sqlite3 wrapper class? [closed]

The FMDB page just offers the cvs checkout. Maybe someone out there wrote a good tutorial on how to use FMDB with sqlite3 on the iphone?

Source: (StackOverflow)

Error when detaching SQLite database - database is locked

I have a system that is based on the SQLite database. Each client has a local database, and once in a while the update arrives from the main server, just a small delta .db file. The task is to merge to local database with the delta file, the schema is identical in both.

For my database management I use fmdb wrapper that can be found here. In the main thread, I keep the connection to the local database open. The delta file arrives in the background, and I want to do the merge in the background to avoid any user interface freezes that this could cause.

As for the merge itself, the only option that I found is to attach the delta database to the local database, then insert/update the rows, and finally detach the delta. This does not work as smooth as I expected.

Code description:

  • The onDeltaGenerated method is invoked in a background thread whenever delta database is ready to be processed (arrives from the server and is saved in the readable location).
  • The deltaDBPath is the absolute location of the delta database in the filesystem.
  • The db variable references open FMDataBase connection.


- (void)onDeltaGenerated:(NSNotification*)n {
NSString* deltaDBPath = [[n userInfo] objectForKey:@"deltaPath"];
@synchronized(db) {
    [db executeUpdate:@"ATTACH DATABASE ? AS delta", deltaDBPath];
    if ([db hadError]) {
        NSLog(@" ****ERROR*** %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    } else {
        NSLog(@"Delta attached from %@", deltaDBPath);
    [db beginTransaction];
    BOOL update1 = NO;
    BOOL update2 = NO;
    BOOL transaction = NO;
    update1 = [db executeUpdate:@"INSERT OR REPLACE INTO equipment SELECT * FROM"];
    if (!update1) {
        NSLog(@" *** ERROR *** update 1 failed!");
        NSLog(@" ****ERROR*** %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    update2 = [db executeUpdate:@"INSERT OR REPLACE INTO equipmentExt SELECT * FROM delta.equipmentExt"];
    if (!update2) {
        NSLog(@" *** ERROR *** update 2 failed!");
        NSLog(@" ****ERROR*** %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    transaction = [db commit];
    if (!transaction) {
        NSLog(@" *** ERROR *** transaction failed!");
        NSLog(@" ****ERROR*** %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    [db executeUpdate:@"DETACH DATABASE delta"];
    if ([db hadError]) {
        NSLog(@" ****ERROR*** %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    } else {
        NSLog(@"Delta detached");


After this method is invoked for the first time, all seem to be fine until I try to detach the database. When I try to do it, I get the following error:

2012-01-11 12:08:52.106 DBApp[1415:11507] Error calling sqlite3_step (1: SQL logic error or missing database) SQLITE_ERROR
2012-01-11 12:08:52.107 DBApp[1415:11507] DB Query: DETACH delta
2012-01-11 12:08:52.107 DBApp[1415:11507]  ****ERROR*** 1: database delta is locked

I also tried to the same but without putting inserts into transaction, the result is identical. Another thing was to remove the @synchronized clause, but also no luck. My guess is that if fails when trying to access local database connection from the background thread, but then how come it manages to attach and insert? Any help appreciated.


I moved the code to the main thread, so the db is now accessed from the main thread only. The problem remains.


Ok, so after trying everything, I gave up on this for a moment and then came back when the first answer appeared here. Surprisingly, everything seems to work fine now, so my code must be correct. I suspect this was the problem with different threads locking the file, as I used XCode, SQLiteDatabaseBrowser and my application to open the database. Even though the lsof showed that the file was not locked, I think it was wrong and either XCode or SQLiteDatabaseBrowser was locking it. I consider the problem solved, and the lesson taken from this is not to thrust lsof so much and also plan the debugging better next time.

Source: (StackOverflow)

SQLite FMDB creating table - beginner iOS

I think this is a simple question but I didnt find the answer in the FMDB git page. When you use the command:

[database executeUpdate:@"create table T_table(name text primary key, age int)"];

does FMDB or SQLite make some kind of verification to see if the table already exists?

Can I call this method in my class initializer without creating more than one table?

Sorry if stupid question.

Source: (StackOverflow)

Fetching complex data using FMDB

I am using SQLite in an iOS application and I am using FMDB as a wrapper. This is my database schema :


CREATE TABLE OffreMarket (codeOffer TEXT NOT NULL,
codeMarket TEXT NOT NULL,
FOREIGN KEY(codeOffer) REFERENCES Offer(code),
FOREIGN KEY(codeMarket) REFERENCES Market(code));


My model objects :

@interface Offer : NSObject
@property (nonatomic,copy) NSString *code;
@property (nonatomic,copy) NSString *name;
@property (nonatomic,copy) NSArray *markets;

@interface OffreMarket : NSObject
@property (nonatomic,copy) NSString *codeOffer;
@property (nonatomic,copy) NSString *codeMarket;

@interface Market : NSObject
@property (nonatomic,copy) NSString *code;
@property (nonatomic,copy) NSString *name;

For example I am fetching all the offers in the database like this :

- (NSArray *)offers {
// Open database

NSMutableArray *offers = [NSMutableArray new];

FMResultSet *resultSet = [database executeQuery:@"SELECT * FROM Offer"];
while ([resultSet next]){

   Offer *offer = [Offer new];
   offer.code = [resultSet stringForKey:@"code"]; = [resultSet stringForKey:@"name"];

   // Get the markets for each offer
   FMResultSet *marketResultSet = [database executeQuery:@"SELECT * FROM  OffreMarket WHERE codeOffer = ?",offer.code];

   NSMutableArray *offers = [NSMutableArray new];
   while ([marketResultSet next]) {
      OffreMarket *offerMarket = [OffreMarket new];
     [offers addObject:offerMarket];

  market.offers = [offers copy];

return [offers copy]


This is working but it takes time because I am using many SQL requests to fetch all the Offers and the corresponding Markets.

Can i avoid many SQL requests to fetch all the Offers with the corresponding markets? Thanks for your answers

Source: (StackOverflow)

FMDB: add new column and insert data

In my app I'm using FMDatabase. I'm trying to add new column to existing table and then insert data in that column. When I added the column by Alter Table, I got no errors but when I'm trying to insert data in the table, I got error complaining about the newly added column that doesn't exist.

Here is my code

NSString *databasePath = [myDB getPlacesDBPath];

FMDatabase *db = [FMDatabase databaseWithPath:databasePath];

if (![db open])
    return ;

if (![db columnExists:@"placeName" inTableWithName:@"MYPLACES"])
    [db executeQuery:@"ALTER TABLE MYPLACES ADD COLUMN placeName TEXT"];
    // I tried to set the result of this query in "BOOL Success" and print the results and I got YES    

//Note: I have more than 12 columns but I didn't post all of them here
NSString *insertSQL = [NSString stringWithFormat: @"INSERT INTO  MYPLACES  ( placeID ,  placeName)  VALUES (\"%@\", \"%@\")", placeIDValue, placeNameValue ];

[db executeUpdate:insertSQL];

if ([db hadError]) {
    NSLog(@"error : %@",[db lastError]);

[db close];

I got the following error:

error : Error Domain=FMDatabase Code=1 "table MYPLACES has no column named placeName" UserInfo=0xe340920 {NSLocalizedDescription=table MYPLACES has no column named placeName}

What's the problem? how to know why the new column is not added?

Source: (StackOverflow)

Multiple Queries not Running in FMDB

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


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.

Source: (StackOverflow)

Error when trying to use the SQLite wrapper FMDatabase

I'm having trouble when I try to use FMDatabase.

I have added theese files:


..and I have also added the libsqlite3.dylib library and imported FMDatabase.h, but as soon as I uncomment this line:

FMDatabase *db = [FMDatabase databaseWithPath:@"/tmp/tmp.db"];

..I get this error:

Undefined symbols for architecture i386:
  "_OBJC_CLASS_$_FMDatabase", referenced from:
      objc-class-ref in DBWrapper.o
ld: symbol(s) not found for architecture i386

I have also tried changing settings like Deployment target between 4.0 - 5.0 and creating a new, clean project, etc.

I'm using Xcode Version 4.3 (4E109).

What am I doing wrong? :)

// Stefan

Source: (StackOverflow)

ARC error with FMDB class after changes to building settings to compile SQLCipher

I followed the tutorial for compiling SQLCipher on iOS devices using a binary located here:

I've made several changes to build settings, adding header search path and C flags. Then after testing a compile of the sqlcipher, i didn't get any errors with the binary, but errors that did not exist before with FMDB started showing up like this:

FMDatabaseAdditions.m:137:19: Implicit declaration of function 'NSFileTypeForHFSTypeCode' is invalid in C99`

FMDatabaseAdditions.m:137:19: Implicit conversion of 'int' to 'NSString *' is disallowed with ARC`

FMDatabaseAdditions.m:137:15: Incompatible integer to pointer conversion initializing 'NSString *__strong' with an expression of type 'int'`

FMDatabaseAdditions.m:158:96: Values of type 'NSUInteger' should not be used as format arguments; add an explicit cast to 'unsigned long' instead`

FMDatabaseAdditions.m:161:28: Implicit declaration of function 'NSHFSTypeCodeFromFileType' is invalid in C99`

Source: (StackOverflow)

Has apple now enabled FTS in the standard/built-in sqlite library?

I want to use FTS in my iOS project. Through some answers to questions here on SO (like this) and other sources (like this), i understood that i will have to roll out my own built of sqlite3 on iOS, thus replacing the dependency to default libsqlite3.dylib.

But when i directly run the query (in a new project, with just the standard 'libsqlite3.dylib' linked and no custom sqlite build) :

"SELECT rowid FROM pages WHERE textcontent MATCH 'jim';" 

on a table 'pages' created by using query :

"CREATE VIRTUAL TABLE pages USING fts3(textcontent TEXT)", 

I dont get any errors, instead, i get the correct result (rowid of the rows in which the word 'jim' exists) as if the FTS is enabled by defalt in the built-in iOS sqlite library .

So, is this the case? Has apple now enabled FTS in the standard/built-in sqlite library? Or there is something that i am missing here?


PS. I am using FMDB in my project as an sqlite wrapper and here is the code that i use to test the above.

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory ,      NSUserDomainMask, YES);
NSString *documentsDir = [paths objectAtIndex:0];
NSString *dbDocumentsPath = [documentsDir stringByAppendingPathComponent:@"1.db"];

FMDatabase *db = [FMDatabase databaseWithPath:dbDocumentsPath];

if (![db open])
    NSLog(@"Could not open db.");

if([db executeUpdate:@"CREATE VIRTUAL TABLE pages USING fts3(textcontent TEXT)"])
    NSLog(@"Virtual Table Created");

if([db executeUpdate:@"INSERT INTO pages(textcontent) VALUES ('Jack')"])
    NSLog(@"First Insert Done");
if([db executeUpdate:@"INSERT INTO pages(textcontent) VALUES ('jim is jam')"])
    NSLog(@"Second Insert Done");

FMResultSet* resultSet1 = [db executeQuery:@"SELECT rowid FROM pages WHERE textcontent MATCH 'jim';"];

while([resultSet1 next])
    NSLog(@"%@",[resultSet1 objectForColumnName:@"rowid"]);

Source: (StackOverflow)