FMDB SQLite question: row count of a query?

Question

does anyone know how to return the count of a query when using FMDB? If I executeQuery @"select count(*) from sometable were..." I get an empty FMResultSet back. How can I get the row count of the query? Do I need to do a query like "select * from sometable where.." and iterate through the result set? Or can I use useCount or whats the best way (in terms of performance) to do this?

Thanks!

Potentiate

Answer 1

Shorter code to accomplish the same thing:

NSUInteger count = [db intForQuery:@"SELECT COUNT(field) FROM table_name"];

Make sure to include the FMDatabaseAdditions.h header file to use intForQuery:.

Still

Is there a something like "intForQueryWithFormat"? – Celandine

The regular implementation of intForQuery already takes a variable argument list. Just put question marks where you want variables used, and then put them at the end of the call like this: NSUInteger count = [db intForQuery:@"SELECT COUNT(?) FROM ?", fieldName, tableName]; – Still

Answer 2

try this. It works for me. Iterating all the records is not recommended.

FMResultSet *rs = [db executeQuery:@"select count(FIELD) as cnt from TABLENAME"];
while ([rs next]) {
    NSLog(@"Total Records :%d", [rs intForColumn:@"cnt"]);
}

May be you should check your Where clause.

Jaunita

Answer 3

Swift 2 Example

This code snippet will print the count for you.

if let rs = db.executeQuery("SELECT COUNT(*) as Count FROM TABLE_NAME", withArgumentsInArray: nil) {
    while rs.next() {
        print("Total Records:", rs.intForColumn("Count"))
    }
}

If it did not work, a few suggestions:

a) Look for a line in your project that says let database = or var database =. If you find one then change db to database
b) Did you change the TABLE_NAME in the Select statement to whatever your table is called?

Kragh

Answer 4

The first one is also right but by using this method you can retrieve records and count using the same query , no headache to write another one. Just add count(*) as count to your query.

You could always just run the proper SQL statement. I do something like:

FMResultSet *rs = [database executeQuery:@"select count(*) as count from words"];
[rs next];

wordsThatExist = [rs intForColumn:@"count"];

Setting up the SQL query may be quicker and cheaper then iterating.. I believe counts are cheap.

Beggs

Answer 5

updated for Swift 3 minor change to "int For Column"

if let rs = db.executeQuery("SELECT COUNT(*) as Count FROM TABLE_NAME", withArgumentsInArray: nil) {
while rs.next() {
    print("Total Records:", rs.int(forColumn: "Count"))
    }
}

Mohn

Answer 6

updated for Swift 4 minor change in method parameter name

if let rs = db.executeQuery("SELECT COUNT(*) as Count FROM TABLE_NAME", withArgumentsIn: nil) {
while rs.next() {
    print("Total Records:", rs.int(forColumn: "Count"))
    }
}

Chincapin

Answer 7

Please Try Following Code, this works for me

let objManager = ModelManager.getInstance()

objManager.database?.open()

let resultSet1: FMResultSet! = sharedInstance.database!.executeQuery("SELECT COUNT(Field) FROM TableName”, withArgumentsInArray:nil)

        if (resultSet1 != nil)
        {
            while resultSet1.next()
            {
              countRecord = Int(resultSet1.intForColumn("COUNT(Field)"))
            }

        }

     print(countRecord)

You Will get Count of Field

Padua

Answer 8

If you want to know the count of the rows before make something with the result, you can even do a simple query and ask for the results columnCount that give you the number of rows and you can save one query if you really want to make something with the resultSet

FMResultSet *results = [database executeQuery:@"SELECT * from tableName"];
int numberOfRows = [results columnCount];

while ([results next]){
... do your stuff ...
}

Lickspittle

Doesn't columnCount give you the count of columns and not rows? I don't believe this is a correct answer. – Groping