CODEWITHSUNDEEP
sqlite
Dhaval Punjabi
Dhaval Punjabi

Reputation: 41

Get count of rows in sqlite table using c in a program

How to get count of rows in sqlite table using c in a program? Is it only by running "select * from table" ? and incrementing a variable?

sqlQuery = "select count (*) from company;";
rc = sqlite3_prepare_v2(db, sqlQuery, -1, &stmt, NULL);
printf("prepare status for count : %d\n", rc);
rowcount = sqlite3_step(stmt);

sqlQuery = "select * from company;";
rc = sqlite3_prepare_v2(db, sqlQuery, -1, &stmt, NULL);
printf("prepare status for count : %d\n", rc);
while (SQLITE_ROW == sqlite3_step(stmt)) {
   rowcount ++; 
}

I am not getting the row count with 1,

But I do get it when I perform 2.

Upvotes: 4

Views: 5473

Answers (2)

ImCodeInventor
ImCodeInventor

Reputation: 1

My solution is to use sqlite3_prepare with sqlite3_stmt and then I have a result from sqlite3_step. And then I count rows from that like:

while (sqlite3_step(stmt) != SQLITE_DONE) { rows++; }

It gives me the correct rows received from a query.

I use the number to create my array like this:

Tuple *responses = new Tuple[rows];

Finally, I put the received data into my array like this:

int n = 0;
while (sqlite3_step(stmt) != SQLITE_DONE) {
    responses[n].setID(sqlite3_column_int(stmt, 0));
    responses[n].setX1(sqlite3_column_int(stmt, 1));
    responses[n].setX2(sqlite3_column_int(stmt, 2));
    responses[n].setName(sqlite3_column_text(stmt, 3));
    n++;
}

This query returns some rows and in four columns. All rows fits perfectly in my array having a column sized class object (Tuple) for every row returned. I believe the tutorial is already on YouTube.

Upvotes: 0

Mark Benningfield
Mark Benningfield

Reputation: 2892

The sqlite3_step() function does not return the results of the query. It returns SQLITE_ROW if there is another row in the result set, or SQLITE_DONE if there isn't. It also returns an error code if something bad happened.

sqlQuery = "select count(*) from company;";
rc = sqlite3_prepare_v2(db, sqlQuery, -1, &stmt, NULL);
if (rc != SQLITE_OK) {
  // error handling -> statement not prepared
}
rc = sqlite3_step(stmt);
if (rc != SQLITE_ROW) {
  // error handling -> no rows returned, or an error occurred
}
rowcount = sqlite3_column_int(stmt, 0);

To get the values for the current row in the result set, you have to access them by column index, using the prepared statement that is executing the query. Since there is only one column in this query, the index of 0 is used for the left-most, or only, column in the result. And, since the value of that column is an integer, we use the sqlite3_column_int() function, which returns the value as a 32-bit integer.

For most ordinary database operations, you only need to use a few of the functions in the SQLite C API. You should really read and absorb the introduction. It will help you to understand the normal workflow for database operations.

Upvotes: 5

Related Questions