Getting query results

Obviously not all queries are commands -- the more common kind actually returns useful data. Result data in libpqxx are encapsulated in a result object, which acts as a container.

	result R = T.exec("SELECT firstname FROM employee WHERE lastname='Ng'");
      

This executes a query on the database, collects all matching data, and stores it in the form of a result.

There are several ways to get at the individual rows in a result. You can index the result with the array index operator [], or call the at member function. There are also random-access iterators, and you can iterate the rows in a result. Each of these will give you a row object. You again get the same options for accessing the fields in the row. [2].

Thus, R[0] will return the first ("zeroth") row in R. You can get the same row as *R.begin(). What you'll usually do is index the row directly to get at the field you want, e.g. R[0][0] to get the first field of the first row.

Of course you can also iterate the rows in a result, whether with C++ iteration syntax or a classic C-style loop:

        for (auto const row: my_result) process(row);
      
        for (auto row = std::begin(my_result); row != my_result.end(); row++)
          process(*row);
      

Once you have a row, you'll want to access its fields. You can do that in all the same way you accessed rows in a result. But in addition, you can also index a row by name instead of by number:

	// Process employees' names one by one.  process_names() doesn't know
        // exactly what columns are going to be in R, but there must be one
        // called "lastname".
	void process_names(result R)
	{
	  for (result::size_type i = 0; i != R.size(); ++i)
	  process(R[i]["lastname"]);
	}
      

There's one more trick for both result iterators and row iterators. You can index them directly using the array index operator, just like C++-native pointers can work a lot like arrays: R.begin()[0] gets you the first field of R's first row, just like R[0][0] would[3].

There is also more than one way to read a field's value. Let's start out with the easy one, c_str, which reads the value as a C string:

	cout << "Name: " << F.c_str() << endl;
      

This will return the empty string ("") if field F has the null value. Use is_null to see if this is the case:

	if (!F.is_null())
	cout << "Name: " << F.c_str() << endl;
      


[2] The difference between [] and at is that the latter is guaranteed to perform bounds-checking, throwing an exception if you pass it an illegal index value. With the array index operator you may get slightly better performance, but attempting to address a nonexistent row or field will result in undefined behaviour such as program crashes or inexplicably strange results.

[3] Or (*R.begin())[0]. It may seem quirky to have R.begin() and *R.begin() mean the same thing, but it makes practical use of iterators a lot easier.