SQLite count(*) how to get the result?

Question

i've done this code to count the numbers of row in a db

    int rows = 0;
if (sqlite3_open([[SqliteManager getDBPath] UTF8String], &database) == SQLITE_OK) {
    const char *sql = "select count(*) from artheca";
    sqlite3_stmt *countstmt;

    if(sqlite3_prepare_v2(database, sql, -1, &countstmt, NULL) == SQLITE_OK) {              
        NSLog(@"inside");
        rows = sqlite3_column_int(countstmt, 0);
    }
}
else
    sqlite3_close(database);
return rows;

But the result is always 0.

So, I'm not sure if rows = sqlite3_column_int(countstmt, 0); is the right statement to get the number of rows... is it correct?

Answer 1

Edit: To execute a SQL statement you need to call all these 6 functions in order:

sqlite3_open()        Open the database
sqlite3_prepare()     Create the SQL statement
sqlite3_step()        Execute the statement
sqlite3_column()      Fetch the result
sqlite3_finalize()    Destroy the statement
sqlite3_close()       Close the database

You are missing the sqlite3_step and sqlite3_finalize steps.

BTW, you could use sqlite3_get_table() or sqlite3_exec() for these simple queries.

---

From http://www.sqlite.org/c3ref/column_blob.html,

The leftmost column of the result set has the index 0.

Hence you should use

rows = sqlite3_column_int(countstmt, 0);

Answer 2

you missed sqlite3_step(countstmt); before rows = sqlite3_column_int(countstmt, 0); in order to execute the query