Selecting Data from a Single Table
This section shows you how to select data from a single table. To use this code, the <ver> placeholder in any library names would need to be replaced with the actual digits representing the library version number.
Example 9 – Selecting from a single table
// Example Program 3 - Selecting from a single table
#include <rw/rstream.h>
#include <rw/db/db.h>
 
int
main() {
RWDBDatabase myDbase = RWDBManager::database(
"liboci<ver>12d.so", // Access Module name
"OCI_tnsname", // server name
"user", // user name
"pwd", // password
"" // database name
);
RWDBConnection myConnection = myDbase.connection();
RWDBTable purchases = myDbase.table("purchase");
RWDBSelector select = myDbase.selector(); //1
select << purchases["orderNum"] //2
<< purchases["supplierID"]; //3
select.where(purchases["videoID"] == 10); //4
RWDBReader rdr = select.reader(myConnection); //5
int purchaseOrderNumber;
int supplierID;
while(rdr()) {
rdr >> purchaseOrderNumber >> supplierID;
std::cout << purchaseOrderNumber << "\t" << supplierID << std::endl;
}
return 0;
}
 
Example 9 s much like Example 2 on reading a database table in Reading Tables: Class RWDBReader. The difference here is that we are interested in selecting only the orderNum and supplierID columns, rather than the entire table, and we are interested only in the rows that apply to a particular videoID. In relational terms, we want a projection and a restriction.
To limit the search, we use an RWDBSelector, which is obtained from an RWDBDatabase on //1. On //2 and //3, the insertion operator << is used to add the column references of interest to the selector's select list, a projection of the columns selected. Note that each invocation of the insertion operator adds another item to the selector's select list. If you need to clear out the select list and begin again, use the selectClear() method.
NOTE: Use the insertion operator << to specify which columns or expressions to select. Each insertion adds to the select list. You usually don’t need to specify a FROM clause.
The items inserted into an RWDBSelector are instances of RWDBExpr, which may be constructed from constants, column references, variables, or functions, possibly combined by arithmetic operators. In this example, the expressions to be selected are simply references to the orderNum and supplierID columns of the purchase table.
Notice that it is not necessary to deal explicitly with a FROM clause as you would with SQL. The tables to select from can be deduced from the columns to be selected. In the rare case where the table cannot be deduced from the columns to be selected, you may specify a FROM clause explicitly by using the from(), fromClear(), fromGeneration(), fromExtern() and fromExternClear() methods of the class RWDBSelector.
On //4 of the example we see an invocation of the selector's where() method. This is how we specify the selector's WHERE clause, a restriction on the rows to select. The where() method takes a single argument, whose type is RWDBCriterion. The instance of RWDBCriterion is built anonymously from the expression:
 
(purchase[“videoId”] == 10)
In this case, only rows in which the videoID column is equal to the constant 10 are selected. Unlike the insertion operator, whose nature demands that its arguments accumulate in a selector, the where() method replaces any existing criterion with its argument. Multiple criterions are expressed using the C++ logical operators.
NOTE: Use the where() method to specify a selection criterion. Use the logical operators (&&, ||, !) to build up complex criterions.
Here is an example of a complex criterion using the logical operator &&:
 
select.where(table1["col1"] == x && table1["col2"] <= y);
In this example, the rows to be selected from table1 are restricted to those in which col1 is equal to the program variable x, and col2 is less than or equal to the program variable y. The logical or, ||, and not, !, could be used as well. To clear the WHERE clause from a selector, call where() with a default RWDBCriterion, as in:
 
select.where(RWDBCriterion()).
Having told the selector what to select, we are ready to execute, on //5:
 
RWDBReader rdr = select.reader(myConnection);
Of course, we could have accomplished the same task in a different way. Since we expect that the result of the execution is going to be a single table consisting of two columns, we could write:
 
RWDBResult result = select.execute(myConnection);
RWDBTable resultTable = result.table();
RWDBReader rdr = resultTable.reader();
// etc.
This syntax is completely valid, but somewhat tedious and prolonged. Since a selector always produces one table of results, it is convenient to view it as a short cut to a result table. This lets us replace these three lines of code with the one that we actually used.