SQLite in C. sqlite3_exec: parameter set in callback function prints incomplete data when called in main()

Question

I am trying to achieve something very basic. But unfortunately I am unable to get the expected result.

I have a sqlite table named emp_infohaving 9 employee records in following fields:

SR_NO[0], NAME[1], AGE[2], SEX[3], ADDRESS[4], EMPID[5], CARDID[6] and SALARY[7].

To access the table and fetch the employee record as per sql query, I have following program in C:

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> 
#include <string.h>

static int callback(void *param, int argc, char *argv[], char **azColName){
    int i=0;

    if(argc == 0) return 0;
    char **res = (char **)param;
    *res = (char *)realloc(*res, sizeof(*res));

  //for(i=0; i<argc; i++){
  //printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");

    strcpy(*res, argv[1], sizeof(*res));
    printf("%s\n", *res);
 // }
return 0;
}

int main(int argc, char* argv[])
{
sqlite3 *db;
char *zErrMsg = 0;
int rc;
char *sql;
char *param;

rc = sqlite3_open("/DBsqlite3/foreign_key.db", &db);

    if( rc )
    {
       fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
       exit(0);
    }
    else
    {
       fprintf(stderr, "\nOpened database successfully\n\n");
    }

sql="select * from emp_info;"

rc = sqlite3_exec(db, sql, callback, &param, &zErrMsg);

    if( rc != SQLITE_OK )
    {
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
    }
    else
    {
       fprintf(stdout, "Operation done successfully\n\n");
       printf("the value of data is %s \n\n", param);
    }


free(param);
sqlite3_close(db);
return 0;
}

The output is as follows: ` Opened database successfully

AAA
BBB
CCC
DDD
EEE 
FFF
GGG
HHH
III
Operation done successfully

the value of data is III

I expected that all the AAA,BBB,CCC,.... will get stored in param and will get printed. But that's not happening. param takes only the last record only.

Why is this happening ? I suspect strcpy() is creating the problem.

Kindly help.

Apart from this, my real aim is to get all the valid employee record (as per sql query) to get stored in param and it should display all the fields(SR_NO[0], NAME[1], AGE[2], SEX[3], ADDRESS[4], EMPID[5], CARDID[6] and SALARY[7]). say for example if i provide following query to sql

sql="SELECT * FROM emp_info WHERE AGE>40";

then the param should display result as follow

7           GGG         41          MALE        G1G2G3G4    307         7777GGG     77777     

8           HHH         42          FEMALE      H1H2H3H4    308         8888HHH     88888

9           III         43          FEMALE      I1I2I3I4    309         9999IIII    99999

And not just a column.How can I achieve this ?

Thank you in advance.

Answer 1

working on the suggestions suggested by CL. I started working on writing the code on cursor-based API. And I got the desired output.

#include <iostream>
#include <sqlite3.h>
#include <stdlib.h>
#include <list>
#include <iterator>
#include <algorithm>

using namespace std;

class sqliteDB
{

private:
  int rc;
  sqlite3 *db;
  char *zErrMsg;
  const char *sql;
  sqlite3_stmt * stmt;
  list<string> answer;


public:

//================================XX=====================================//
  bool connectDB()
  {

      rc = sqlite3_open("/DBsqlite3/foreign_key.db", &db);
      if( rc )
      {
        cerr << "Can't open database: " << sqlite3_errmsg(db) << endl;
        sqlite3_close(db);
        exit(1);
      }
      else
      {
            std::cout<<"\n\nDatabase opened successfully\n\n";
      }
   }

//===============================XX=====================================//

  void allEmp()
  {
     int AGE;

     //Preparing the statement
     rc=sqlite3_prepare(db,"SELECT * FROM emp_info WHERE AGE>40;", -1, &stmt, NULL ); 
     if (rc != SQLITE_OK){
     throw string(sqlite3_errmsg(db));
     }
     else{
     cout<<"\n\nThe statement was prepared successfully\n\n";
     }

    // Creating List Container //
    cout << "\n\nList answer was created successfully\n\n";
    while(sqlite3_step(stmt) == SQLITE_ROW) {
    cout << "Rows" << endl;

    int column = sqlite3_column_count(stmt);

       for(int i = 0; i < column; i++)
       {
          cout << "Columns" << endl;
          answer.push_back(string((const char *) sqlite3_column_text(stmt, i)));
       }
     }

    sqlite3_finalize(stmt);

    cout << "\n\nDatabase was closed successfully\n\n";

 }

//==============================XX=====================================//

 void printList(){
    int s = answer.size();
    for( list<std::string>::const_iterator it = answer.begin(); it != answer.end(); it++)
      cout << it->c_str() << endl;
  } 


//===================================XX===============================//    

};


int main()
{
  sqliteDB object1;
  object1.connectDB();
  object1.allEmp();
  object1.printList();
  cout << "\n\nAll the statement were executed properly\n\n";

 return 0;
}

//========================END OF CODE===========================//

The Output is as follows

 Database opened successfully



 The statement was prepared successfully



 List answer was created successfully

 Rows
 Columns
 Columns
 Columns
 Columns
 Columns
 Columns
 Columns
 Columns
 Columns
 Columns
 Columns
 Columns


 Rows
 Columns
 Columns
 Columns
 Columns
 Columns
 Columns
 Columns
 Columns
 Columns
 Columns
 Columns
 Columns


 Rows
 Columns
 Columns
 Columns
 Columns
 Columns
 Columns
 Columns
 Columns
 Columns
 Columns
 Columns
 Columns

 Database was closed successfully

 7
 GGG
 41
 MALE
 G1G2G3G4
 307
 7777GGG
 77777
 9833446677
 abi@gmail.com
 07:08:1947
 NONE
 8
 HHH
 42
 FEMALE
 H1H2H3H4
 308
 8888HHH
 88888
 9833446688
 abj@gmail.com
 08:09:1947
 NONE
 9
 III
 43
 FEMALE
 I1I2I3I4
 309
 9999IIII
 99999
 9833446699
 abk@gmail.com
 09:10:1947
 NONE


 All the statement were executed properly

The major problem I faced was with the overloading of "<<" operator.

Thank you CL.

Answer 2

You took the code from an answer where the goal was to read a single result row. The strcpy overwrites the previous value of the same variable.

You should use the cursor-based API, and call sqlite3_step in a loop:

...
while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
    my_object *o = new ...;
    o->set_field(sqlite3_column_xxx(stmt, 1);
    ...
    my_list.add(o);
}
...

Answer 3

I'm no expert in this, but a simple google search shows, the syntax of sqlite3_exec() is

int sqlite3_exec(
  sqlite3*,                                  /* An open database */
  const char *sql,                           /* SQL to be evaluated */
  int (*callback)(void*,int,char**,char**),  /* Callback function */
  void *,                                    /* 1st argument to callback */
  char **errmsg                              /* Error msg written here */
);

Note the 4th parameter, it is suppossed to be a void *.

As per your definition of variables and calling convention,

char *param;

and

rc = sqlite3_exec(db, sql, callback, &param, &zErrMsg);

you're getting the 4th param wrong. You may want to rewrite it as

rc = sqlite3_exec(db, sql, callback, (void *)param, &zErrMsg);

Note: [If not being taken care already by sqlite3_exec()] You may need to allocate memory to param before being passed to sqlite3_exec(). Also, instead of directly passing argv to the callback, you may want to have some sanity and validity check of the same.