Proper use of callback function of sqlite3 in C++

Question

I have the following C++ code for testing purposes in conjunction with SQLite3. It's a class called customer with a callback function declared. This callback function is called whenever sqlite3_exec() returns results (records) from the SQLite database.

What I don't like about this construction is that source code to process the results is located in a call back function outside of the class rather than the results being processed by the class method from which sqlite3_exec() is called.

I could use global variables that will be used in the class method after the callback function has finished extracting the values from the SQL query results. But what if there is more than one record and the call back function is called several times. Then I need to work with arrays unless I make sure that I will only have single results.

Do I need to forget about the callback function and go into deeper calls of the SQLite API?

Or do I need to go to a C++ wrapper, I suppose that there is no call back mechanism there and the results being passed back to the class method itself?

// customer
#include "Customer\customer.h"
//## begin module%50E6CCB50119.additionalDeclarations preserve=yes
static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
  int i;
  char* columnName;
  char* columnValueString;
  short int columnValueShortInt = 0;
  int columnValueInt = 0;

  cout << "begin of callback function\n";

  for(i=0; i<argc; i++)
  {
    columnName = azColName[i];
    if (strcmp(columnName, "FirstName")==0 || strcmp(columnName, "LastName")==0)
    {
      columnValueString = argv[i];
      cout << "columnName = " << columnName << "; value = " << columnValueString <<"\n";
    }
    else
    {
      if(strcmp(columnName, "Age")==0)
      {
        stringstream(argv[i]) >> columnValueShortInt;
        cout << "columnName = " << columnName << "; value = " << columnValueShortInt <<"\n";
      }
      else // strcmp(columnName, "Id")==0)
      {
        stringstream(argv[i]) >> columnValueInt;
        cout << "columnName = " << columnName << "; value = " << columnValueInt <<"\n";
      }
    }
  }
  cout << "end of call back function \n";
  return 0;
}

//## end module%50E6CCB50119.additionalDeclarations


// Class customer

customer::customer ()
  //## begin customer::customer%50F969EE01E4.hasinit preserve=no
  //## end customer::customer%50F969EE01E4.hasinit
  //## begin customer::customer%50F969EE01E4.initialization preserve=yes
  //## end customer::customer%50F969EE01E4.initialization
{
  //## begin customer::customer%50F969EE01E4.body preserve=yes
  customerId = 0;
  zErrMsg = 0;

  customerDataBaseRc = sqlite3_open("customerdb", &customerDataBase);
  if(customerDataBaseRc)
  {
    fprintf(stderr, "Can't open database %s\n", sqlite3_errmsg(customerDataBase));
    sqlite3_close(customerDataBase);
  }

  const char * pSQL[6];
  const char * sqlStatement;

  pSQL[0] = "create table customerTable (Id int, FirstName varchar(30), LastName varchar(30), Age smallint)";

  // execute all the sql statements
  for(int i = 0; i < 1; i++)
  {
    customerDataBaseRc = sqlite3_exec(customerDataBase, pSQL[i], callback, 0, &zErrMsg);

    if( customerDataBaseRc !=SQLITE_OK )
    {
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
      break; // break the loop if error occur
    }
  }
  //## end customer::customer%50F969EE01E4.body
}


customer::~customer ()
{
  //## begin customer::~customer%50F93279003E.body preserve=yes
  const char *pSQL[6];

  // Remove all data in customerTable
  pSQL[0] = "delete from customerTable";

  // Drop the table from database
  pSQL[1] = "drop table customerTable";

  // execute all the sql statements
  for(int i = 0; i < 2; i++)
  {
    customerDataBaseRc = sqlite3_exec(customerDataBase, pSQL[i], callback, 0, &zErrMsg);
    if( customerDataBaseRc !=SQLITE_OK )
    {
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
      break; // break the loop if error occur
    }
  }
  cout << "destructor";
  //## end customer::~customer%50F93279003E.body
}



//## Other Operations (implementation)
unsigned int customer::createCustomer (char  iCustomerFirstName[20], char  iCustomerLastName[20], unsigned short iCustomerAge)
{
  //## begin customer::createCustomer%50EBFFA3036B.body preserve=yes
  const char *sqlStatement;

  string result;          // string which will contain the result

  ostringstream convert;   // stream used for the conversion

  convert << "insert into customerTable (Id, FirstName, LastName, Age) values (" << customerId << ", '" << iCustomerFirstName << "', '" << iCustomerLastName << "', " << iCustomerAge << ")";
  result = convert.str(); // set 'Result' to the contents of the stream

  sqlStatement = result.c_str();

  // Execute sql statement
  customerDataBaseRc = sqlite3_exec(customerDataBase, sqlStatement, callback, 0, &zErrMsg);
  // Check for errors
  if(customerDataBaseRc !=SQLITE_OK )
  {
    fprintf(stderr, "SQL error: %s\n", zErrMsg);
    sqlite3_free(zErrMsg);
  }

  return customerId++;
  //## end customer::createCustomer%50EBFFA3036B.body
}

char * customer::getCustomer (unsigned int iCustomerId)
{
  //## begin customer::getCustomer%50ED3D700186.body preserve=yes
  const char *sqlStatement;

  char *tmp ="blabla";

  string result;          // string which will contain the result

  ostringstream convert;   // stream used for the conversion

  convert << "select * from customerTable where Id = " << iCustomerId;
  result = convert.str(); // set 'Result' to the contents of the stream

  sqlStatement = result.c_str();

  // Execute the sql statement
  customerDataBaseRc = sqlite3_exec(customerDataBase, sqlStatement, callback, 0, &zErrMsg);
  // Check for errors
  if(customerDataBaseRc !=SQLITE_OK )
  {
    fprintf(stderr, "SQL error: %s\n", zErrMsg);
    sqlite3_free(zErrMsg);
  }

  return tmp;
  //## end customer::getCustomer%50ED3D700186.body
}

// Additional Declarations
  //## begin customer%50E6CCB50119.declarations preserve=yes
  //## end customer%50E6CCB50119.declarations

//## begin module%50E6CCB50119.epilog preserve=yes
//## end module%50E6CCB50119.epilog

Not really I suppose, I am new to this forum, will try to minimise code next times, thanks for the hint. — Michel de Man
– Michel de Man, Commented Jan 21, 2013 at 12:47

CL. · Accepted Answer · 2013-01-21 12:15:07Z

28

What one typically does in this case is take advantage of the void * (which you call NotUsed) parameter of the callback -- a parameter you define when you install the callback. For C++, you would typically set that parameter to the this pointer to your interested object, and you would make the callback (an extern "C" function in a c++ source file) a friend method to your class (if necessary).

This would look like this:

class customer
{
    ...
public:
    int callback(int argc, char **argv, char **azColName);
};

static int c_callback(void *param, int argc, char **argv, char **azColName)
{
    customer* cust = reinterpret_cast<customer*>(param);
    return cust->callback(argc, argv, azColName);
}

char* customer::getCustomer(int id)
{
    ...
    rc = sqlite3_exec(db, sql, c_callback, this, &errMsg);
    ...
}

int customer::callback(int argc, char **argv, char **azColName)
{
    ...
}

edited Jan 21, 2013 at 12:15

CL.

182k18 gold badges241 silver badges282 bronze badges

answered Jan 21, 2013 at 11:19

mah

40.1k9 gold badges79 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Michel de Man Over a year ago

This goes byond my current c++ knowledge, not to say experience, have to work on this one. Do you mean, if I would pass the this pointer, the call back funtion would then be able to fill the attributes of the class instance (object) and then process the values within the method itself after the sqlite3_exec call has been finished?

mah Over a year ago

Yes. You would need to cast the incoming void * to be of your object pointer type -- perhaps with something like MyClass *object = (MyClass *)NotUsed;. From that point you can access everything public about your object; if you want to access private (or protected) things, you need to declare (in your class) that callback() is a friend function. You can easily find examples of friend functions in a google search.

Michel de Man Over a year ago

Thanks, I will try to implement your suggestions,

Michel de Man Over a year ago

I have studied the example given above. So it looks like the c_callback funtion is passing on the results of the sql query to the class method callback where extraction of the query results then can take place, i.e. filling the this pointer object attributes. Then within getCustomer further processing can take place, am I correct?

CL. Over a year ago

Typically, you'd use one separate callback for each sqlite3_exec call.

|

CL. · Accepted Answer · 2013-01-21 12:06:09Z

Using sqlite3_exec has the disadvantages that you have to convert some values back from a string to a number, and that it needs to allocate memory for all result records (which can lead to problems when reading large tables). Furthermore, the callback always is a separate function (even if it's in the same class).

For your example query, using the sqlite3_prepare/sqlite3_step/sqlite3_finalize API would look like this:

void one_customer::readFromDB(sqlite3* db, int id)
{
    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db, "SELECT FirstName, LastName, Age"
                                    " FROM customerTable"
                                    " WHERE Id = ?", -1, &stmt, NULL);
    if (rc != SQLITE_OK)
        throw string(sqlite3_errmsg(db));

    rc = sqlite3_bind_int(stmt, 1, id);    // Using parameters ("?") is not
    if (rc != SQLITE_OK) {                 // really necessary, but recommended
        string errmsg(sqlite3_errmsg(db)); // (especially for strings) to avoid
        sqlite3_finalize(stmt);            // formatting problems and SQL
        throw errmsg;                      // injection attacks.
    }

    rc = sqlite3_step(stmt);
    if (rc != SQLITE_ROW && rc != SQLITE_DONE) {
        string errmsg(sqlite3_errmsg(db));
        sqlite3_finalize(stmt);
        throw errmsg;
    }
    if (rc == SQLITE_DONE) {
        sqlite3_finalize(stmt);
        throw string("customer not found");
    }

    this->id         = id;
    this->first_name = string(sqlite3_column_text(stmt, 0));
    this->last_name  = string(sqlite3_column_text(stmt, 1));
    this->age        =        sqlite3_column_int(stmt, 2);

    sqlite3_finalize(stmt);
}

(This code handles errors by just throwing a string with the error message.)

I am really impressed by the speed of people giving such usefull and detailed solutions, very appreciated.

Collectives™ on Stack Overflow

Proper use of callback function of sqlite3 in C++

2 Answers 2

8 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related