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

Question

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 = [rs2 intForColumnIndex: 0];
}

Is there something I'm missing in the WHILE statement?

Answer 1

I believe the problem here is surrounding the replacement query with apostrophes. They're unnecessary (and detrimental) when doing a query with an argument replacement, so you want to just end the executeQuery with

"where custdata.Bus_name = ? ", globalBusinessName];

My guess is that you're either getting a nil back for rs2 (which you should check for and then evaluate the error state), or the query is failing to find any results, and thus returning a valid rs2, but with a 0 count.