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 similar
to the STL's vector
template.
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
.
Two ways are provided to get at individual rows in a
result
: first, through indexing with the array
index operator []
or the at
member function, and second, through random-access iterators. Either
will give you a row
object that in turn can be
addressed with the array index operator (or the
at
member function) or using iterators to get at
its individual fields
[3].
Thus, R[0]
will return the first ("zeroth") row
in R. You won't normally want to bother with
row
s though; they don't contain the actual data,
but rather serve as placeholders to later tell
result
which fields to fetch when field values
from the row are requested. The class exists mostly for technical
reasons related to the array index operators
[4].
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.
Array indexing of rows also works with the fields' names instead of their numbers, eg.:
// Process employees' names one by one. ProcessNames() doesn't know exactly // what columns are going to be in R, but there must be one called "lastname". void ProcessNames(result R) { for (result::size_type i = 0; i != R.size(); ++i) Process(R[i]["lastname"]); }
As for the alternative, accessing the result rows through an iterator,
only const
iterators are provided so the contents of
the result cannot be modified. Use these iterators as random-access
iterators like with any STL-like container:
for (result::const_iterator i = R.begin(); i != R.end(); ++i) Process(*i);
Iterators may be incremented or decremented (whether pre- or post-),
they may be added to or subtracted from to jump through the result
rows; their positions may be compared (provided they point into the
same result
), and they may be dereferenced through
the *
or ->
operators.
Finally, the iterated rows' fields may be addressed using the array
index operator on the iterator directly, eg.
R.begin()[0]
gets you the first field of
R
's first row, just like
R[0][0]
would
[5].
Either way, once you've indexed the row
you get a field
--which is another
placeholder, but this time encapsulates an actual field value in our
query result. A field
F
also
knows its column name, which can be obtained as
F.Name()
.
Again, there is more than one way to read the 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;
In practice of course, not all data is going to consist of strings.
Many fields will be integer values, or decimals, or Booleans. To
convert the field's value to one of these, use its
to
method. This adapts itself to the type of
variable you pass it, expecting the field value to be of an
appropriate form for that type. For convenience,
to
returns false
if the field
had the null value, and true
otherwise. In the
former case, the variable will retain the value it had before the
call.
// Pay an employee his salary. Add bonus for managers. // The employee row must contain the fields void PaySalary(result::const_iterator empl) { long id; float salary; bool is_manager=false; // Get id. Must never be null. if (!empl[0].to(id)) throw runtime_error("No id!"); // Get salary. If this employee has no salary, skip payment. if (!empl[1].to(salary)) return; // Get manager status. If field is null, variable's original value (false) // will be preserved. empl[2].to(is_manager); if (is_manager) salary += Bonus; TransferMoney(id, salary); }
If conversion fails, e.g. when trying to convert a floating-point
value to an integer variable, to
will throw a
runtime_error
reporting the problem in its
what
message.
[3]
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.
[4]
This is an instance of the Proxy
implementation pattern, needed to allow a result
to be indexed as if it were a two-dimensional array. C++'s array
index operator doesn't allow this usage directly, but the operator
can be applied to the result of a previous use of the same operator.
The "result in the middle" needs to have a type of its own, though,
and that's what row
is for.
[5]
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. In fact it matches how
arrays and pointers work in C and C++.