Sqlite Issue with Bool values

Question

I have a table with 3 Bool columns.

1. Unseen Default 1
2. AnsweredCorrect Default 0
3. AnsweredWrong Default 0

Right now all Unseen rows are 1, AnsweredCorrect 0 and AnsweredWrong 0. Now I want to count all 3 columns for their ON values. I am quering

SELECT SUM(Unseen) as Unseen, SUM(AnsweredCorrect) as Correct, SUM(AnsweredWrong) as Wrong FROM table_name 

when I run this query in Sqlite browser, it returns

Unseen 20, Correct 0, Wrong 0

Which is right. But When I use it in code, it returns all values 0.

FMResultSet *query = [db executeQuery:[NSString stringWithFormat:@"SELECT SUM(Unseen) as Unseen, SUM(AnsweredCorrect) as Correct, SUM(AnsweredWrong) as Wrong FROM %@;",tableName]];

[counters addObject:[NSNumber numberWithInt:[query intForColumn:@"Unseen"]] ];
[counters addObject:[NSNumber numberWithInt:[query intForColumn:@"Correct"]] ];
[counters addObject:[NSNumber numberWithInt:[query intForColumn:@"Wrong"]] ];

I am using FMDB for data base operations. What is wrong?

Thanks for help!

Answer 1

If this is the entirety of your code for querying the database and accessing the FMResultSet, I would take a look at the docs for the FMDatabase class. Specifically the discussion on -executeQuery:

In order to iterate through the results of your query, you use a while() loop. You also need to “step” (via [FMResultSet next]) from one record to the other.

I imagine you only have one item in the FMResultSet but "stepping" to it will be required.

Edit:

Rather than using the %@ placeholder in the query string, you may need to use ? instead per the docs.

executeQuery:

Execute select statement

• (FMResultSet *)executeQuery:(NSString *)sql, ... Parameters sql The SELECT statement to be performed, with optional ? placeholders.

... Optional parameters to bind to ? placeholders in the SQL statement. These should be Objective-C objects (e.g. NSString, NSNumber, etc.), not fundamental C data types (e.g. int, char *, etc.).

Answer 2

This does not make much sense, so I would advise that you start by doing some checking.

You need to find out what you are doing.

It is likely that you think you are connect to one database file, but you are connected to another.

Try doing all your creation and adding data steps in code in the app, not creating the database table external to the app.

This means that you are connecting to the same sqlite file you're reading from.

If this works, i.e. the SQL returns 1, 0, 0 then find that new SLQlite database file.

Another way is to delete the SQLite file you think are reading from, if the code still works then you are connected to another SQLite database file.

If you are connected to the correct file, try changing the values in the database, add some values to the other columns.