How to get the row count using FMDB with SELECT statements and JOINs?

[SpokaneDude]

This is my SQL to get the row count of OrderData. Using straight SQL
(in SQLite Database Browser) it gives me the correct value; when I use
this select statement in my app with FMDB, I get a count of zero (0).

// get count of line items for each order
FMResultSet *rs2 = [fmdb executeQuery:@"select
count(orderdata.order_id) from orderdata "
"join custdata on custdata.customer_id =
orderinfo.cust_id "
"join orderinfo on orderdata.order_id =
orderinfo.order_id "
"where custdata.Bus_name = '?'",
globalBusinessName];

while([rs2 next]) {
globalItemCount = [rs intForColumnIndex: 0];
}


Is there something else I need to do in the "while" bit?

[Gus Mueller]

On Jun 2, 2012, at 11:34 AM, SpokaneDude wrote:

> while([rs2 next]) {
> globalItemCount = [rs intForColumnIndex: 0];
> }
>
> Is there something else I need to do in the "while" bit?

You're calling [rs2 next], and then [rs intForColumnIndex:] … so you're using two different instances, which is likely to give you bad results.

-gus

--

August 'Gus' Mueller
Flying Meat Inc.
http://flyingmeat.com/

[SpokaneDude]

Hi Gus... I didn't see that... however, I changed the code, and still
get zero...

[Gus Mueller]

Well, if you can reproduce the problem in the FMDBReportABugFunction function ( https://github.com/ccgus/fmdb#reporting-bugs ) - that would help us figure out what's going on.

-gus

[SpokaneDude]

Hi Gus... well, I bypassed FMDB, used straight SQLite3, and got the
same results. Therefore, it's not FMDB... thanks for your time
anyway...

Regards,
Rolf (SpokaneDude)

On Jun 2, 12:20 pm, Gus Mueller <g...@flyingmeat.com> wrote:
> Well, if you can reproduce the problem in the FMDBReportABugFunction function (https://github.com/ccgus/fmdb#reporting-bugs) - that would help us figure out what's going on.