Managing multiple queries at a time on sqlite3 database in iOS

Question

I have created two tables(vcards, viewdownloads) in sqlite3 database in iOS. At the beginning of my app starts Iam inserting my address book contacts to the sqlite3 database in to "vcards" table using background process.

- (void)applicationDidBecomeActive:(UIApplication *)application
{
    // Restart any tasks that were paused (or not yet started) while the application was inactive. If the application was previously in the background, optionally refresh the user interface.
    [self setNotificationForReachability];
    dispatch_queue_t loadQueue = dispatch_queue_create("Image loader", NULL);
    dispatch_async(loadQueue, ^{
        // Your code to run in the background here

        GVDBManager *objDB = [GVDBManager getSharedInstance];
        [objDB getContactsFromAddBook];
        [objDB syncPhoneBookWithVcardsTable];
    });
}
in the middle of this process of inserting contacts into "vcards" table, I have called the another background process to update some values using update query in same "vcards" table

after this process completion again Iam calling one more background process to insert the some contacts to the "viewdownloads" table and displaying the contacts using UI. my questions: 1.After click on one button, Reading the contacts from database and this is taking some time to load into array(aim reading 800 contacts). Iam using background process to do this. 2.Every time iam opening the database and closing it while inserting and updating the tables using background process.This causes my app crash and this is the major issue iam facing. can any explain clearly how to handle multiple actions(inserting, updating and searching) on same database. thanks in Advance.

Luca Iaco Luca Iaco · Accepted Answer · 2014-01-21T08:30:59

1) Are you sure your method doesn't need to work on main thread? maybe some instruction need it.

2) Detect specific exeption ( or EXC_BAD_ACCESS ). I suggest you to enable All breakpoints. see image below:

enter image description here

3) Here main functions to open query and close db with sqlite. I use something like these without any problem

Sample function for open db connection

- (NSError *) openDatabase {

    NSError *error = nil;

        sqlite3* db = self.db; // temp ref

    const char *dbpath = NULL;
        dbpath = [self.databaseName UTF8String]; // disk-based db

    // DB opening
    int result = sqlite3_open(dbpath, &db);
    if (result != SQLITE_OK) {
        // const char *errorMsg = sqlite3_errmsg(db);
        // ....
    }else
        self.db = db;

    return error;
}

Sample function for close db connection

- (NSError *) closeDatabase {

    NSError *error = nil;

    if (self.db != nil) {
        if (sqlite3_close(self.db) != SQLITE_OK){
            // const char *errorMsg = sqlite3_errmsg(self.db);
            // ...
        }           
        self.db = nil;
    }

    return error;
}

Sample function for query DB using PDO (not SELECT clause)

- (NSError *)doPDOQuery:(NSString *)sql withParams:(NSArray *)params {

    //NSError *closeError = nil;
    __block NSError *errorQuery = nil;

    dispatch_sync(self.fetchQueue, ^{

        NSError *openError = nil;

        //Check if database is open and ready.
        if (self.db == nil) {
            openError = [self openDatabase];
        }

        if (openError == nil) {
            sqlite3_stmt *statement;
            const char *query = [sql UTF8String];
            sqlite3_prepare_v2(self.db, query, -1, &statement, NULL);

            // BINDING
            int count =0;
            for (id param in params ) {
                count++;
                if ([param isKindOfClass:[NSString class]] )
                    sqlite3_bind_text(statement, count, [param UTF8String], -1, SQLITE_TRANSIENT);
                if ([param isKindOfClass:[NSNumber class]] ) {

                    const char* paramObjType = [param objCType];

                    if (!strcmp(paramObjType, @encode(float))) // f
                        sqlite3_bind_double(statement, count, [param doubleValue]);
                    else if (!strcmp(paramObjType, @encode(double))) // d
                        sqlite3_bind_double(statement, count, [param doubleValue]);
                    else if (!strcmp(paramObjType, @encode(int))) // i
                        sqlite3_bind_int(statement, count, [param intValue]);
                    else if (!strcmp(paramObjType, @encode(unsigned int))) // I
                        sqlite3_bind_int(statement, count, [param unsignedIntValue]);
                    else if (!strcmp(paramObjType, @encode(char))) // c (eg: 1,0)
                        sqlite3_bind_int(statement, count, [param intValue]);
                    else if (!strcmp(paramObjType, @encode(long))) // q
                        sqlite3_bind_int64(statement, count, [param longLongValue]);
                    else if (!strcmp(paramObjType, @encode(unsigned long))) // Q
                        sqlite3_bind_int64(statement, count, [param unsignedLongLongValue]);
                    else
                        NSLog(@"SQLite binding - unknown NSNumber");
                }
            }

            int result = sqlite3_step(statement);
            if (result == SQLITE_ERROR ||
                result == SQLITE_CONSTRAINT) {
                // const char *errorMsg = sqlite3_errmsg(self.db);
                // ...
            }
            sqlite3_finalize(statement);

        }
        else
            errorQuery = openError;

    });

    return errorQuery;
}

Managing multiple queries at a time on sqlite3 database in iOS

1 Answers