0

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?

SpokaneDude
  • 4,856
  • 13
  • 64
  • 120

1 Answers1

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.

gaige
  • 17,263
  • 6
  • 57
  • 68
  • "globalBusinessName" has a space between two words... apostrophes are required in that case.. – SpokaneDude Jun 04 '12 at 16:55
  • It Shouldn't be when it is being replaced as an argument in a statement. Did you follow up on the nil return value and check the error? – gaige Jun 04 '12 at 18:06
  • Yes, I did... no errors. The interesting thing is that I get the correct answer using SQLite Database Browser, and the SQLite shell tool, but not in the program, all using the same select statement. – SpokaneDude Jun 05 '12 at 14:57
  • Curious, which is why I think the problem is not with the select statement per se, but with the replacement using e variable. Two other things to try if you haven't already: remove the where clause altogether and make sure you get a non-zero response; and try creating the query using a manually-generated string. In other words use `[NSString stringWithFormat:...]` to create your query and then pass that to `executeQuery` with no parameters. – gaige Jun 05 '12 at 16:40
  • Removing the where clause still gave me 0, should have been 2. Using NSString gives the same result, 0. – SpokaneDude Jun 05 '12 at 17:06
  • At this point, I'd start stepping the query from `select count(orderdata.order_id) from orderdata` and add clauses until it fails. I assume you already verified that the correct file is being opened. – gaige Jun 05 '12 at 18:45
  • Yes, correct file is being opened... your latest suggestion won't work because it has to have all the joins to get to the correct data, otherwise the result is always zero. – SpokaneDude Jun 05 '12 at 19:03
  • I understand that it isn't useful as a solution, but it would be as a diagnostic. – gaige Jun 06 '12 at 01:47