CODEWITHSUNDEEP
objective-csqlitecount
Lauren Quantrell
Lauren Quantrell

Reputation: 2669

Count Number of Rows in a SQLite Database

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");
    }
}

Upvotes: 12

Views: 23145

Answers (6)

Charles Robertson
Charles Robertson

Reputation: 1820

-(void)databaseRecordCount{
    int rows = 0;
    @try {
        sqlite3 *database;
        NSString *filePath = [self databaseDocumentsFilePath];
        if(sqlite3_open([filePath UTF8String], &database) == SQLITE_OK) {
            NSString *query = @"SELECT * FROM MYTABLE";
            sqlite3_stmt *compiledStatement;
            if(sqlite3_prepare_v2(database, [query UTF8String], -1, &compiledStatement, NULL) != SQLITE_OK)
                NSLog(@"Error while creating detail view statement. '%s'", sqlite3_errmsg(database));
            if(sqlite3_prepare_v2(database, [query UTF8String], -1, &compiledStatement, nil) == SQLITE_OK) {
                while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
                    rows++;
                }
                sqlite3_finalize(compiledStatement);
            }
            sqlite3_close(database);
        }
    }
    @catch (NSException * e) {
        NSLog(@"Error Counting");
    }
    NSLog(@"SQLite Rows: %i", rows);
    NSUserDefaults *userDefaults;
    userDefaults = [NSUserDefaults standardUserDefaults];
    [userDefaults setInteger:rows forKey:@"databaseRecordCount"];
    [userDefaults synchronize];
}

Upvotes: 1

Kit Ramos
Kit Ramos

Reputation: 1869

I thought I'd trow in my two cents here as there is an expression to count rows in a database, I use it when dealing with MySQL databases using php scripts all the time. and I tested it in an ios app it's available in there too behold:

sqlite3 *database;
if(sqlite3_open([dbpath UTF8String], &database) == SQLITE_OK)
{
    NSString *sql = @"select count(*) from today";
    sqlite3_stmt *selectStatement;
    int returnValue = sqlite3_prepare_v2(database, [sql UTF8String], -1, &selectStatement, NULL);
    if (returnValue == SQLITE_OK) 
    {
        if(sqlite3_step(selectStatement) == SQLITE_ROW)
        {
            numrows= sqlite3_column_int(selectStatement, 0);
        }
    }
    sqlite3_finalize(selectStatement);
    sqlite3_close(database);
}

no need for a fancy loop counter thing. btw if your using an auto increment int for the primary key. it works just slightly different then an array's key. where as in an array that is n items long the valid array elements are from 0 to n-1 in a database the key field is from 1 to n simple enough to work around if you just keep that in mind.

Upvotes: 1

Lauren Quantrell
Lauren Quantrell

Reputation: 2669

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);
}

Upvotes: 11

Paul Lefebvre
Paul Lefebvre

Reputation: 6406

You'll have to count of each table individually. Some pseudo code:

sql = "SELECT name FROM sqlite_master" WHERE type = 'table'
tables() = GetRows(sql)

Dim total As Integer
For Each t As String in tables
  sql = "SELECT COUNT(*) FROM " + t
  total = total + GetValue(sql)
Next

Show(total)

Upvotes: 0

Jeremy Bassett
Jeremy Bassett

Reputation: 171

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

Upvotes: 5

Femi
Femi

Reputation: 64700

There is no SQL expression to count rows in a database: you can count rows in a every table and then add them up.

Upvotes: 1

Related Questions