sqlite3 and fmdb nested FMResultSet is possible?

Question

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?

I don't see anything wrong in your code except one little *nil* in the second query definition. You need to narrow down the problem by investigating what exactly causes the exception. One way to do it is to go to breakpoints navigator and place the breakpoint on all exceptions by pressing the small + button on the bottom left. This should stop the execution in the exact line that causes the exception. — lawicko, Jun 06 '12 at 13:11
Yep, I was doing that but it took me a while to figure the 'int' thing as the error was a bit misleading (from those who come from Java world like me)... Anyway, thanks for the reply! — Aurelio Calegari, Jun 07 '12 at 11:46

score 2 · Accepted Answer · answered Jun 07 '12 at 11:45

I just found what I did wrong. I was passing a int as part of the second query. I had to convert it to NSNumber:

            FMResultSet *rs2 = [db executeQuery:@"select pref_id, label, value from main.preference where group_id = ? order by pref_id", [NSNumber numberWithInt:pg.group_id], nil];

So that means, YES, sqlite3/fmdb does support nested queries! :-)

score 0 · Answer 2 · answered Mar 20 '13 at 19:28

I'm using FMDB and SQLITE3 as well, and I find nested queries work:

(I'm not claiming the code hint below is good, never mind the format, please)

Both Master and Detail tables have a column called 'id'

FMResultSet *rso = [database executeQuery:@"select * from master order by id"];
while ([rso next])
{
    NSInteger masterId = [rso intForColumn:@"id"];
    NSString *q3 = [[NSString alloc] initWithFormat:
               @"select * from detail where masterid = %d order by id", masterId, nil ];

    FMResultSet *rsa = [database executeQuery:q3 ];

    while ([rsa next])
    {
        NSInteger detailId   = [rsa intForColumn:@"id"];
        //
        // here do something with masterId and detailId 
    }
}

This was a pleasant surprise, actually. I was halfways expecting to have to first query all master records, and then loop through them in App memory to pick up the details from SQlite3. But the above construct works fine.

sqlite3 and fmdb nested FMResultSet is possible?

2 Answers2