Comparing strings with contents of columns

Question

I wrote the following lines of code to compare some strings with contents of columns (string) in SQLite database:

void PaymentTransaction::setAttributesBySqliteStmt(sqlite3_stmt * statement)
{
    ZF_LOGD("setAttributesBySqliteStmt\n");
    int columnCnt = sqlite3_column_count(statement);
    for (int i = 0; i < columnCnt; i++) {
        if (strcmp("id", sqlite3_column_name(statement, i)) == 0)
            id = sqlite3_column_int(statement, i);
        else if (strcmp("gas_station_id", sqlite3_column_name(statement, i)) == 0)
            gas_station_id = sqlite3_column_int(statement, i);
        else if (strcmp("nozzle_id", sqlite3_column_name(statement, i)) == 0)
            nozzle_id = sqlite3_column_int(statement, i);
            ................
            //SAME CODES FOR OTHER COLUMNS 
            ................
            else {
        ZF_LOGW("Invalid column name(%s) in table name Transaction",
                sqlite3_column_name(statement, i));
           }
      }
  }

   void PaymentTransaction::bindValueToSqliteStmt(sqlite3_stmt* statement)
   {
int paramCnt = sqlite3_bind_parameter_count(statement);
for (int i = 1; i <= paramCnt; i++) {
    if (i == sqlite3_bind_parameter_index(statement, "@id"))
        sqlite3_bind_int(statement, i, id);
    else if (i == sqlite3_bind_parameter_index(statement, "@gas_station_id"))
        sqlite3_bind_int(statement, i, gas_station_id);
    else if (i == sqlite3_bind_parameter_index(statement, "@nozzle_id"))
        sqlite3_bind_int(statement, i, nozzle_id);
    else if ............
     //SAME CODES FOR OTHER COLUMNS 
     ..................
     else {
        ZF_LOGW("Invalid param name(%s) in table name Transaction",
                sqlite3_bind_parameter_name(statement, i));
     }
  }
}

Since there are 30 columns in total, there are lots of if else statements and I was wondering whether there is better/shorter way of writing these lines of code?

Answer 1

The obvious approach here would be to use a map or unordered_map to convert from a column name to a pointer (or reference) to the actual column. This will typically reduce the line count by about 2:1. More importantly, however, it makes most of that simple initialization, and the code that executes at run time is about 3 lines or so. An implementation would look something like this:

struct PaymentTransaction {
    int id;
    int gas_station_id;
    int nozzle_id;
    // more members here
public:
    void setAttributes(sqlite3_statement const *);
};
void PaymentTransaction::setAttributes(sqlite3_statement const *statement) {
    for (int i = 0; i < statement->sqlite3_column_count(); i++) {
        std::string name = sqlite3_column_name(statement, i);

        static const std::map<std::string, int &> int_members{
            { "id", id},
            {"gas_station_id", gas_station_id},
            {"nozzle_id", nozzle_id}
            // ...
        };

        auto pos = int_members.find(name);
        if (pos != int_members.end()) {
            pos->second = sqlite3_column_int(statement, i);
        }
    }
    // Possibly similar code for members of other types
}

Note that this requires a separate map for each type of column (integer vs. floating point vs. string) but can have a single map for all the columns of one type.

You could separate the map from the function, and make the function a template to avoid duplicating that code (but this may be more trouble than it's worth, depending on how many types you need to deal with--all you've shown are ints, but I don't know how accurately that reflects you real use.