SQLite count (*) how to get the result?

Question

I did this code to count line numbers in 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 correct statement to get the number of rows ... is this correct?

Answer 1

Edit: to execute the 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.

By the way, you can 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 index 0.

Therefore you should use

 rows = sqlite3_column_int(countstmt, 0); 

Answer 2

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