A Cocoa / Objective-C wrapper around SQLite

FMDB: is It good remaininig open database during the whole life cycle of ios app?

I am confused about database open and close operation for FMDB wrapper class.

Is it creating issue if i open database in applicationDidFinishLoading method of AppDelegate class and do not close until application will terminate ?


Source: (StackOverflow)

FMDB SQLite question: row count of a query?

does anyone know how to return the count of a query when using FMDB? If I executeQuery @"select count(*) from sometable were..." I get an empty FMResultSet back. How can I get the row count of the query? Do I need to do a query like "select * from sometable where.." and iterate through the result set? Or can I use useCount or whats the best way (in terms of performance) to do this?


Source: (StackOverflow)

Best way to get the ID of the last inserted row on SQLite

On iPhone, what's the best way to get the ID of the last inserted row on an SQLite Database using FMDB ?

Is there a better way rather than doing :


Source: (StackOverflow)

Using FMDB on multiple threads and two connections

I'm using two different types of fmdb connections in my app:

FMDatabase for all READ queries and FMDatabaseQueue for all UPDATE queries.

Both are handled by a singleton, which keeps both types open the whole time while the app is running.

Both, read and update queries, are used in different threads as some tasks in my app are progressed in background; like getting data from a server and inserting it in the db via FMDatabaseQueue in an own background thread - while reading some information from db via FMDatabase and updating an ViewController with it on the main thread.

My problem is that after inserting data into the db via FMDatabaseQueue the second connection (FMDatabase) does not return the updated information as it does not find them. But I know the data was inserted as I have checked the db with an db browser tool + no errors occur while inserting it. To avoid this, I have to close the FMDatabase db connection and reopen it to see the changes made by the other connection. Unfortunately when my app starts up there are a many inserts, updates + reads as a lot of new data is loaded from server which needs to be processed - so closing and opening the db every time an update was made occurs in many "database busy" messages.

I have used one single FMDatabaseQueue for all threads and executes (read, update) before but it was quite slow when using read queries with __block variables to get the resultset out of the callback while another thread does some inserts(between 50-100 in a single transaction).

On top of it the database is encrypted via sqlcipher - not sure if it's important but want to mentioned it. So every time i have to close and open the database I'm doing a setKey.

My question: Is it possible to use a setup with two different connection types on multiple threads and if yes, do I have to close and open the FMDatabase connection? Or is there a better solution for this usecase?


My code to perform an insert / update looks like

-(void) create:(NSArray *)transactions
    NSMutableString *sqlQuery = [[NSMutableString alloc] initWithString:STANDARD_INSERT_QUERY];

    [sqlQuery appendString:@"(transaction_id, name, date) VALUES (?,?,?)"];

    FMDBDataSource *ds = [FMDBDataSource sharedManager];
    FMDatabaseQueue *queue = [ds getFMDBQ];
    [queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        [db setKey:[ds getKey]]; // returns the key to decrypt the database
        for (Transaction *transaction in transactions)
            [db executeUpdate:sqlQuery, transaction.transactionId,,];

and a read query

-(Transaction *)read:(NSString *)transactionId
    NSString *sqlQuery = [[NSString alloc] initWithString:STANDARD_SELECT_QUERY];
    Transaction *transaction = nil;

    FMDBDataSource *ds = [FMDBDataSource sharedManager];
    FMResultSet *rs = [[ds getFMDB] executeQuery:sqlQuery];

    while ([rs next]) {
        transaction = [[Transaction alloc] init];
        [transaction setTransactionId:[rs stringForColumn:@"transaction_id"]];
        [transaction setName:[rs stringForColumn:@"name"]];

[rs close];
return transaction;

The FMDBDataSource is a singleton holding both, FMDatabase and FMDatabaseQueue, connections

- (FMDatabaseQueue *)getFMDBQ
    if (self.fmdbq == nil)
        self.fmdbq = [FMDatabaseQueue databaseQueueWithPath:[self getDBPath]];

    return self.fmdbq;

- (FMDatabase *) getFMDB
    if(self.fmdb == nil)
        self.fmdb = [FMDatabase databaseWithPath:[self getDBPath]];
        [self openAndKeyDatabase]; // opens the db and sets the key as the db is encrypted
    return self.fmdb;

As I said, when using this code the FMDatabase connection does not get the information which was inserted via FMDatabaseQueue.

Source: (StackOverflow)

FMDB resultset into dictionary

Is there an easy way to get the FMDB results of an executeQuery:SELECT * ... easily into a dictionary?

FMResultSet *appointmentResults = [[DataClass getDB] executeQuery:@"SELECT * FROM Appointments WHERE date = ?",currDateString];
while ([appointmentResults next]) {
    //Create dictionary
    //Add dictionary to array for later use

I was wondering if there was a way I could make the dictionary keys the column names and the values the column values. Preferably without having to do a loop through every row inside the while.

Source: (StackOverflow)

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)

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)

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 read the whole row data from SQLite with FMDB in IOS dev?

I can read all column Data from code like this ...

FMResultSet *rs = [db executeQuery:@"SELECT Name, Age, FROM PersonList"];

while ([rs next]) {

NSString *name = [rs stringForColumn:@"Name"];

int age = [rs intForColumn:@"Age"];


or find some Data from this way

NSString *address = [db stringForQuery:@"SELECT Address FROM PersonList WHERE Name = ?",@"John"];

But if I want a Array contents the whole row's data(assume all my row data is a simple String)

how can I achieve that...?

thanks for your help!


Source: (StackOverflow)

Problem with FMDB and insert value in the "executeQuery:" from a searchString

While building a Search for my app i ran into a problem whilst using the FMDB SQLite Wrapper (

When I search my database with this SQL Command, everything is fine. 13 objects are returned and I can use them.

FMResultSet *rs = [db executeQuery:@"SELECT * FROM ZARTICLE WHERE ZTITLEDE LIKE '%Daimler%'"];

But when i try to insert the searchQuery from the User Input like this:

FMResultSet *rs = [db executeQuery:@"SELECT * FROM ZARTICLE WHERE ZTITLEDE LIKE (?)", theSearchQuery];

... the value is dont be inserted into SQL Command. And I dont get any returned objects from the DB. even if the String (theSearchQuery) is the same written in the first example.

Additionaly I post a part from the documentation of FMDB for your convinience. :)

Data Sanitization

When providing a SQL statement to FMDB, you should not attempt to "sanitize" any values before insertion. Instead, you should use the standard SQLite binding syntax:

INSERT INTO myTable VALUES (?, ?, ?) The ? character is recognized by SQLite as a placeholder for a value to be inserted. The execution methods all accept a variable number of arguments (or a representation of those arguments, such as an NSArray or a va_list), which are properly escaped for you.

Thus, you SHOULD NOT do this (or anything like this):

[db executeUpdate:[NSString stringWithFormat:@"INSERT INTO myTable VALUES (%@)", @"this has \" lots of ' bizarre \" quotes '"]]; Instead, you SHOULD do:

[db executeUpdate:@"INSERT INTO myTable VALUES (?)", @"this has \" lots of ' bizarre \" quotes '"]; All arguments provided to the -executeUpdate: method (or any of the variants that accept a va_list as a parameter) must be objects. The following will not work (and will result in a crash):

[db executeUpdate:@"INSERT INTO myTable VALUES (?)", 42]; The proper way to insert a number is to box it in an NSNumber object:

[db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:42]]; Alternatively, you can use the -execute*WithFormat: variant to use NSString-style substitution:

[db executeUpdateWithFormat:@"INSERT INTO myTable VALUES (%d)", 42]; Internally, the -execute*WithFormat: methods are properly boxing things for you. The following percent modifiers are recognized: %@, %c, %s, %d, %D, %i, %u, %U, %hi, %hu, %qi, %qu, %f, %g, %ld, %lu, %lld, and %llu. Using a modifier other than those will have unpredictable results. If, for some reason, you need the % character to appear in your SQL statement, you should use %%.

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)

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)