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.
