Databases, continued.

The main problem about databases lies in how one can fetch the data from the db. Simply put, storing the result of a SELECT statement is a pain.

Consider this simple table:

CREATE TABLE user (id INTEGER, login TEXT, password TEXT);

Now, a very simple select statement:

SELECT * FROM user;

The C++ typical code to deal with the result of this query is something along the line of:

if (teh_sql_api_exec_function("SELECT * FROM user;")) {  int rowcount = teh_sql_api_getrowcount();  int colcount = teh_sql_api_getcolcount();  for (int r=0; r    for (int c=0; c      std::string s = teh_sql_api_getstringvalue(c, r);      my_clever_query_object.setdata(c, r, s);    }  }}

We just have lost the type of our different fields - our id is not more an integer, it is a string. You can avoid this by doing:

  teh_sql_type t = teh_sql_api_getdatatype(c);  if (t == teh_sql_integer) {    int i = teh_sql_api_getintvalue(c, r);    my_clever_query_object.setintdata(c, r, i);  } else if (t == teh_sql_string) {    std:string s = teh_sql_api_getstringvalue(c, r);    my_clever_query_object.setstringdata(c, r, s);  } else {     // ... other cases    }

We now know the type of our objects but we can't say anymore that the code is generic. We need to have one method per SQL data type in my_clever_query_object (MySQL has many many different types). We also have to store the values in different places, because a string is not a date and an integer is not a float - C++ is bad at mixing oranges and apples. Thus, you'll need one container per type. It means that you'll have to cleverly store your informations in my_clever_query_object to be able to fetch them back when needed (you'll be able to rename it to my_uber_clever_query_object if you succeed).

There is obviously a better solution to this problem - and you already know its name: variants.

You can implement variant in numerous ways. Qt variant is a basic one - everything is done without using templates (at least, it was true in Qt3. I haven't checked this in Qt4). The Qt variant is very easy to use - and believe it or not but they use it to store database results from their SQL drivers, as shown here.

Another solution is to use typelists[1]. Both the Tiny Template Library variant and the boost.variant use this clever technique.

With variants, we are able to store any kind of data in a single container. Our code becomes:

  teh_sql_type t = teh_sql_api_getdatatype(c);  my_cOOl_variant v;  if (t == teh_sql_integer) {    v = teh_sql_api_getintvalue(c, r);  } else if (t == teh_sql_string) {    v = teh_sql_api_getstringvalue(c, r);  } else {     // ... other cases    }  my_clever_query_object.setdata(c, r, v);

That's still a pita, but it's probably the best we can do in C++ :)

---

[1] typelists are described by A. Alexandrescu in his Modern C++ Design book.