Fetching Query Results

First read about how to prepare statements

Fetching a Results

The result is represented by cppdb::result class. It stores the query result. The prepared statement result can be fetched using statement::query() function.

cppdb::statement st = sql << "SELECT name,age FROM students";
cppdb::result r = st.query();

Note:

statement can be converted to result automatically that actually causes calling query() function.

So the same code above can be written using syntactic sugar as following:

cppdb::result r = sql << "SELECT name,age FROM students";

Fetching Meta-data on the Result:

Following meta-data can be fetched about the result:

• Number of columns using cols() member function.
• The name of columns using name() member function. These names can be used for retrieval of the data using fetch() member functions.

Getting the Result Data

In order to iterate over rows of the result you should use next() function that returns true if the next row exits and false otherwise. Once next() returned true you can fetch the values using fetch() family of functions.

There are 3 kinds of prototypes for fetch() functions.

1. bool fetch(int column,type &value) - fetch the value from column (index starts from 0) returning false if the value in NULL.
2. bool fetch(std::string const &column_name,type &value) - fetch the value from column using its name returning false if the value in NULL.
3. bool fetch(type &value) - fetch the value from the next column in current row (starting from 0) returning false if the value in NULL.

Where type is one of C++ numeric types, std::string for text, std::tm for date-time types and std::ostream for Blob types. Fetching a value would try to do the best in casting between result type and the type you provide, for example fetching numeric or date-time types would convert them to string representation, it would try to do the casting between string and std::tm and numeric types if possible.

If conversion fails, cppdb::bad_value_cast is thrown.

For example:

cppdb::result r = sql << "SELECT name,age FROM users WHERE age > ?" << 18.0;
while(r.next()) {
  std::string name;
  double age;
  r.fetch(0,name);
  r.fetch(1,age);
  std::cout << name << " is " << age << " years old" << std::endl;
}

Another way to get values directly is by using cppdb::result::get(int) or cppdb::result::get(std::string const &) template member functions that allow to fetch values from columns directly using column index or column name:

cppdb::result r = sql << "SELECT name,age FROM users WHERE age > ?" << 18.0;
while(r.next()) {
  std::cout << r.get<std::string>("name") << " is " << r.get<double>("age") << " years old" << std::endl;
}

Unlike fetch() function, get() functions throw cppdb::null_value_fetch if the value was null.

Syntactic Sugar

There is also overloaded operator ">>" that provide syntactic sugar for fetching data and the example above an be written as:

cppdb::result r = sql << "SELECT name,age FROM users WHERE age > ?" << 18.0;
while(r.next()) {
  std::string name;
  double age;
  r >> name >> age;
  std::cout << name << " is " << age << " years old" << std::endl;
}

Note:

Operators >> function remain variable unchanged when the result is NULL value.

In order to fetch both meta-data and the value you can use into() manipulator that passes both reference to the value and a tag. For example

cppdb::null_tag_type age_tag,name_tag;
std::string name;
double age;
r >> cppdb::into(name,name_tag) >> cppdb::into(age,age_tag);

Fetching a Single Row

Sometimes it is useful to fetch a single row of data and not iterate over it. This can be done using cppdb::statement::row() function that works like query() but also calls next() first time and checks if more then one rows had been fetched (in which case it throws multiple_rows_query exception).

For example:

cppdb::statement st = sql<<"SELECT password WHERE username=?" << user
cppdb::result r = st.row();

if(!r.empty()) {
        if(pass == r.get<std::string>(0)) {
                // ok
        }
        else {
                // wrong password
        }
}
else {
        // no such user
}

You can also use cppdb::row manipulator to make it shorter:

cppdb::result r = sql<<"SELECT password WHERE username=?" << user << cppdb::row
if(!r.empty()) 
        ...
else 
        ...

You can also use operator >> right after row manipulator, but in case the empty result was fetched a empty_row_access exception would be thrown.

double age;
sql<<"SELECT age WHERE username=?" << user << cppdb::row >> age;