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)

FMDB query isn't acting right with LIKE

I am using FMDB, which is a wrapper for SQLite.

Here is my query line:

FMResultSet *athlete = [db executeQuery:@"SELECT * FROM athletes WHERE athlete_name LIKE ?", search_text];

Using this I can get a result back if I type in the exact name of the athlete. But, I'm trying to use LIKE so I can search on the name. But, when I add %?% instead of just ? ... nothing returns. And there are no errors.

Has anyone ran into this before and know what I'm doing wrong?

Thanks everyone!

Source: (StackOverflow)

iOS SQLite FMDB Transactions.. Correct usage?

I'm just going to try out using transactions with the FMDB SQLite iOS wrapper.

The documentation is a little vague on transactions but from having a quick look at some functions I have come up with the following logic:

[fmdb beginTransaction];
    // Run the following query
    BOOL res1 = [fmdb executeUpdate:@"query1"];
    BOOL res2 = [fmdb executeUpdate:@"query2"];

if(!res1 || !res2) [fmdb rollback];
else [fmdb commit];

Source: (StackOverflow)

How to delete a row in a sqlite database table?

I am using fmdb for managing my database. I could not find any example for deleting a row from a table in fmdb. I tried

  NSString *sqlStat=@"DELETE from tableName WHERE id=3";    
  FMResultSet *rs = [database executeQuery:sqlStat];

but its not working because when I checked the total number of entries in table, I am getting the same number as before executing the above statement. So, what is a proper way to delete a row from a table using fmdb?

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)

FMDB ios no such table

I have a problem with a sqlite project that i'm doing, I'm using FMDB, I follow a simple example, but doesn´t work. And I can't find the error. I did my database schema from the terminal, I put some data on it. I'm very new to ios dev, so I don't know exactly if I did the steps ok. This is what I did:

1 - I made my Database schema and add some fields. 2 - I copied the database.db into my project folder in xcode. 3 - I add the FMDB files. 4 - I add the sqlite3.dylib 5 - I put this code:

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions
    self.dbname = @"database.db";
    NSArray * docPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString * docDIr = [docPath objectAtIndex:0];
    self.dbpath = [docDIr stringByAppendingPathComponent:dbname];

    [self checkDB];

    [self getQ];

    return YES;
-(void) getQ
    FMDatabase * db = [FMDatabase databaseWithPath:dbpath];
    [db open];

    FMResultSet * result = [db executeQuery:@"SELECT * FROM table1"];

    NSLog(@"last Error: %@",[db lastErrorMessage]);
    NSLog(@"result: %@", result);
-(void) checkDB
    BOOL success;
    NSFileManager * fm = [NSFileManager defaultManager];
    success = [fm fileExistsAtPath:dbpath];
    NSError * error = [[NSError alloc] init];
    if (success) return;
    NSString * dbPathFromApp = [[[NSBundle mainBundle] resourcePath]stringByAppendingPathComponent:self.dbname];

    [fm copyItemAtPath:dbPathFromApp toPath:dbpath error:&error];           

Apparently the database it's empty, so what happened? why I can't find table1 ? If I open the file with any sqlite gui, the table appears just fine. Thank's for any help The console show me the next lines: 2013-03-02 14:03:31.839 myApp[21433:c07] last Error: no such table: table1 2013-03-02 14:03:31.841 myApp[21433:c07] result: (null)

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)

FMDatabaseQueue How To Return A Value

I'm using FMDatabaseQueue in my iOS application. I'm stuck in understanding how to return the value upon creating the queue. Appreciate your help!!

 FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:aPath]; 

[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]) {
// I want value to be returned from here

Source: (StackOverflow)

Passing an array to sqlite WHERE IN clause via FMDB?

Is it possible to pass an array to a SELECT … WHERE … IN statement via FMDB? I tried to implode the array like this:

NSArray *mergeIds; // An array with NSNumber Objects
NSString *mergeIdString = [mergeIds componentsJoinedByString:@","];

NSString *query = @"SELECT * FROM items WHERE last_merge_id IN (?)";
FMResultSet *result = [database executeQuery:query, mergeIdString];

This only works if there is exactly 1 object in the array, which leads me to believe that FMDB adds quotes around the whole imploded string.

So I tried passing the array as is to FMDB's method:

NSArray *mergeIds; // An array with NSNumber Objects
NSString *query = @"SELECT * FROM items WHERE last_merge_id IN (?)";
FMResultSet *result = [database executeQuery:query, mergeIds];

Which doesn't work at all.

I didn't find anything about it in the README or the samples on FMDB's github page.

Thanks, Stefan

Source: (StackOverflow)

Core Data VS Sqlite or FMDB....?

Now this might look like a duplicate thread, but my question is that I have read a lot of questions like.. Core Data vs SQLite 3 and others but these are 2-3 years old. I have also read that FMDB was developed as core data was not supported on iOS, So it should not be used any more. And on the other hand I have read that one should not use core data as a database.

So I am seriously confused,whether I should use core data for object storage or not. I mean on what basis I should decide which to use? Are there any guidelines provided by apple or someone else.. or is it something that will come to me with time.?

Source: (StackOverflow)

FMDBBlockSQLiteCallBackFunction Crash in app that's not using makeFunctionNamed

I'm working on an app that's in the app store, which uses FMDB for interacting with its sqlite database. We've received some crash reports with stack traces like this:

Thread : Crashed: NSOperationQueue 0x170239c20 :: NSOperation 0x17024d7d0 (QOS: LEGACY)
0  libobjc.A.dylib                0x000000019701c0b4 objc_retain + 20
1  MyApp                          0x00000001002bdff4 FMDBBlockSQLiteCallBackFunction
2  MyApp                          0x00000001002bdb1c FMDBBlockSQLiteCallBackFunction
3  MyApp                          0x00000001002b66b4 FMDBBlockSQLiteCallBackFunction
4  MyApp                          0x00000001002980fc FMDBBlockSQLiteCallBackFunction
5  MyApp                          0x000000010029f20c FMDBBlockSQLiteCallBackFunction
6  CFNetwork                      0x00000001851475a4 __49-[__NSCFLocalSessionTask _task_onqueue_didFinish]_block_invoke + 300
7  Foundation                     0x00000001866bf1c4 __NSBLOCKOPERATION_IS_CALLING_OUT_TO_A_BLOCK__ + 16
8  Foundation                     0x0000000186610604 -[NSBlockOperation main] + 96
9  Foundation                     0x00000001866001cc -[__NSOperationInternal _start:] + 636
10 Foundation                     0x00000001866c1f28 __NSOQSchedule_f + 228
11 libdispatch.dylib              0x0000000197655954 _dispatch_client_callout + 16
12 libdispatch.dylib              0x00000001976600a4 _dispatch_queue_drain + 1448
13 libdispatch.dylib              0x0000000197658a5c _dispatch_queue_invoke + 132
14 libdispatch.dylib              0x0000000197662318 _dispatch_root_queue_drain + 720
15 libdispatch.dylib              0x0000000197663c4c _dispatch_worker_thread3 + 108
16 libsystem_pthread.dylib        0x000000019783522c _pthread_wqthread + 816

However, from reading the FMDB code it looks like FMDBBlockSQLiteCallBackFunction is only called as the callback for sqlite functions created using FMDatabase's makeFunctionNamed:maximumArguments:withBlock: method, which we're not using at all.

Any ideas what could be causing crashes like this?

Source: (StackOverflow)