fmdb
A Cocoa / Objective-C wrapper around SQLite
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)
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)
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?
UPDATE
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, transaction.name, transaction.date];
}
}];
}
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)
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)
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 :
SELECT MAX(ID)
Source: (StackOverflow)
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?
Thanks!
Source: (StackOverflow)
I am using FMDB, which is a wrapper for SQLite. http://github.com/ccgus/fmdb
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)
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 ?
Thanks.
Source: (StackOverflow)
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)
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)
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;
return;
}
// 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)
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.
-Tristan
Source: (StackOverflow)
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)
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.
Code:
- (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 delta.equipment"];
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.
Edit
I moved the code to the main thread, so the db is now accessed from the main thread only. The problem remains.
Edit2
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)
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)