Count Number of Rows in a SQLite Database

Question

I'm trying the following code to count the number of rows in my SQLite database table, but it throws an exception. Is these a simpler way to do this?

- (void) countRecords {
    int rows = 0;
    @try {
        NSString *dbPath = [self getDBPath];

        if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {

            NSString *strSQL;
            strSQL = @"SELECT COUNT(*) FROM MYTABLE";
            const char *sql = (const char *) [strSQL UTF8String];
            sqlite3_stmt *stmt;

            if (sqlite3_prepare_v2(database, sql, -1, &stmt, NULL) == SQLITE_OK) {

                // THIS IS WHERE IT FAILS:
                if (SQLITE_DONE!=sqlite3_step(stmt) ) {

                    NSAssert1(0,@"Error when counting rows  %s",sqlite3_errmsg(database));

                } else {
                    rows = sqlite3_column_int(stmt, 0);
                    NSLog(@"SQLite Rows: %i", rows);
                }
                sqlite3_finalize(stmt);
            }
            sqlite3_close(database);
        }
    }
    @catch (NSException * e) {
        NSLog(@"Error Counting");
    }
}

Answer 1

I came across a solution, using my code above, just replacing the step statement with the code below:

if (sqlite3_step(stmt) == SQLITE_ERROR) {
    NSAssert1(0,@"Error when counting rows  %s",sqlite3_errmsg(database));
} else {
    rows = sqlite3_column_int(stmt, 0);
    NSLog(@"SQLite Rows: %i", rows);
}

Answer 2

This usually works for me

- (NSInteger )numberRecordsForTable:(NSString *)table {
NSInteger numTableRecords = -1;
if (sqlite3_open([self.dbPath UTF8String], &database) == SQLITE_OK) {
    NSString *sqlStatement = [NSString stringWithFormat: @"select count(*) from %@", table];
    const char *sql = [sqlStatement cStringUsingEncoding:NSUTF8StringEncoding];
    if(sqlite3_prepare_v2(database, sql, -1, &sqlClause, NULL) == SQLITE_OK) {          
        while(sqlite3_step(sqlClause) == SQLITE_ROW) {
            numTableRecords = sqlite3_column_int(sqlClause, 0);
        }
    }
    else {
        printf("could not prepare statement: %s\n", sqlite3_errmsg(database));
    }
}
else {
    NSLog(@"Error in Opening Database File");
}
sqlite3_close(database);
return numTableRecords; 

}

HTH