5.3 Selecting Data

One way to select data from a database is to use an RWDBReader to read a database table (See Section 4.4.2, "Reading Tables: Class RWDBReader.") In SQL terms, this is equivalent to:

SELECT * FROM table-name

This technique works well as long as we want to read all the rows and all the columns of a single table. Naturally, your applications must do much more than this very simple form of SELECT. You need an encapsulation of the relational concepts of projection, restriction, and join, as well as special features like ordering and grouping. You may also want to select things other than simple column values. In SQL these features are supported by the SELECT statement. In the DB Interface Module, the equivalent is the RWDBSelector.

Each RWDBSelector instance is an encapsulated SELECT statement. Its methods provide your application with explicit control over the SELECT statement's select list, as well as its FROM, ON, HAVING, WHERE, ORDER BY, and GROUP BY clauses. The set operators +, *, and -, which represent union, intersection, and difference, respectively, may be applied to several RWDBSelector instances in order to achieve the semantics of the UNION, INTERSECTION, and DIFFERENCE operations as defined by standard SQL. Because RWDBSelector instances may be used as expressions, subqueries are also supported.

Class RWDBSelector supports the full range of functionality built into the SQL SELECT statement. This means it has many member functions and operators, which are explained in detail in the SourcePro C++ API Reference Guide. This chapter covers the basic concepts.

This section shows you how to select data from a single table.

Example 3: 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( "liboci8012d.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 3 is much like Example 2 on reading a database table in Section 4.4.2, "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.

---

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.

---

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.

The previous section explained how to use an RWDBSelector to express the relational concepts of restriction (the WHERE clause in SQL) and projection (the select list). In this section, we combine these abilities to produce a join. Actually, it's quite simple: we just mention columns from more than one table.

Example 4: Performing a join

On //5, an expression that resolves to an RWDBCriterion is specified. In it, we are restricting the resulting table to rows in which the videoID column in the purchase table is equal to the ID column in the videos table.

Beginning on //6, an RWDBReader is used to read the selected table. Despite the fact that this table cannot be associated with any particular physical storage, since it is generated from the given expression, the mechanism for reading it is the same as for any other table.

1. Create an RWDBJoinExpr using any of the related global functions, such as rwdbOuter().

2. Set any join condition or indicate the join columns using the overloaded method RWDBJoinExpr::on(). If only the join columns are specified, the join condition defaults to equality on common columns.

3. Add the join expression to the FROM clause using RWDBSelector::from(), or add it to another join expression to form a nested join expression.

On //2, the join criterion is specified on the outer join using the on() method of RWDBJoinExpr. The join criterion specifies that the rows in both the tables have to be joined with their respective deptno fields matching. This makes the join expression complete and ready to be attached to the FROM clause of the selector.

On //2, the join criterion is specified on the outer join using the on() method of RWDBJoinExpr. The join criterion specifies that the rows in both emp and dept must be joined with their respective deptno fields matching. This makes the current join expression complete and ready to be attached to another join expression.

On //3, another join expression is created by joining the previously constructed outer join and table loc using the global function rwdbLeftOuter(). Using a join expression instead of table creates a nested join. You can have any level of nesting in the join expression.

On //4, the join criterion is attached to the second outer join using the on() method on RWDBJoinExpr. The join criterion specifies that the rows in both dept and loc have to be joined with their respective locno fields matching. This makes the nested join expression complete and ready to be attached to the FROM clause of the selector.

On //2, the join criterion is specified on the outer join using the on() method of RWDBJoinExpr. The join criterion specifies that the rows in emp and salary must be joined with their respective empnum fields matching. This makes the first join expression complete and ready to be attached to the FROM clause of the selector.

On //3, another join expression is created by joining the tables dept and loc using the global function rwdbLeftOuter().

On //4, the join criterion is specified on the second outer join using the on() method on RWDBJoinExpr. The join criterion specifies that the rows in both dept and loc must be joined with their respective locno fields matching. This makes the second join expression complete and ready to be attached to the FROM clause of the selector.

On //5 and //6, both these joins are explicitly attached to the FROM clause of the selector, completing the multiple join procedure. Join expressions must be explicitly joined to the selector; they are not implicitly attached.

1. Create an RWDBCriterion outer join condition using the leftOuterJoin() or rightOuterJoin() methods on either the RWDBColumn or the RWDBExpr class.

2. Specify the criterion, combined with any other criterions, in the WHERE clause.

On //1, a left outer join expression is constructed and set in the WHERE clause. The criterion says that the two tables purchase and videos should be joined such that the videoID column in purchase should match the ID column in videos. In this case, the tables list for the FROM clause is implicitly derived from the selection list. The rest of the code is similar to Example 4, "Performing a join."

Please note that different databases may impose restrictions on generating outer join constructs in the WHERE clause:

• Some databases do not support outer joins in which one table is outer to more than one other table; other databases give unexpected results under these circumstances. All other joins besides outer joins should work consistently across all databases.

Please see the appropriate DB Access Module guide for more details.

In most cases, it is not necessary to deal explicitly with FROM entries while using class RWDBSelector, as they can be deduced from the columns to be selected and those referenced in the instance of RWDBCriterion set for the WHERE clause. However, in certain circumstances it might be desirable to manipulate the FROM clause. For example, when using RWDBJoinExpr, correlated sub-queries, or when adding an unreferenced table. RWDBSelector provides methods — from(), fromClear(), fromGeneration(), fromExtern(), and fromExternClear() — to manipulate the FROM clause generation.

The list of the FROM entries deduced from the select column list and the WHERE criterion forms the implicit FROM entries list. Use of from() methods forms the explicit FROM entries list. Each invocation of a from() method adds an entry to the explicit FROM entries list. Method fromClear() may be used to clear the explicit FROM entries list.

The enum FromGeneration defines the behavior used to compose the FROM clause from the implicit and explicit FROM entries lists. The two values the enum defines are ExplicitOrImplicit and ExplicitAndImplicit. The enum ExplicitOrImplicit composes the FROM clause based on the explicit FROM entries list if there are any entries; otherwise it uses the implicit FROM entries list. Thus, it results in the use of entries in either explicit or implicit lists, but not both. The enum ExplicitAndImplicit composes the FROM clause as a union of the implicit and explicit FROM entries lists. It combines entries from both the lists, removing duplicate entries. It also removes the table entries that are a part of a join expression included in the explicit list. The fromGeneration()methods act as get and set methods for defining the behavior. Default behavior is ExplicitOrImplicit.

If a table is in the implicit FROM list, but should not be included in the FROM clause, it should be marked external by using the method fromExtern(). A table marked external using fromExtern() is assumed to have been defined outside the query and hence will not be included in the FROM clause. For example, in a correlated sub-query, a table defined in the outer SQL is used inside the sub-query. The sub-query should not define the table, as the table definition is external to the sub-query. (See Section 5.3.8, "Subqueries."). Every call to fromExtern() adds a table to the extern table list. Method fromExternClear() may be used to clear the extern table list.

In the following example, myDbase is a valid RWDBDatabase instance, t1 through t6 are valid RWDBTable instances, and j1 is a valid RWDBJoinExpr instance joining tables t5 and t6.

RWDBTable t1, t2, t3, t4, t5, t6; ... // Define valid RWDBTables t1, t2, t3, t4, t5, t6 RWDBJoinExpr j1 = rwdbLeftOuter(t5, t6); ... // Define the rest of the RWDBJoinExpr j1 RWDBSelector sel1 = myDbase.selector(); sel1 << t1["col"] << t2["col"] << t3["col"]; sel1.fromExtern(t2); ... // Define rest of the RWDBSelector sel1 RWDBSelector sel2 = myDbase.selector(); sel2 << t4["col"] << t5["col"] << t6["col"]; sel2.from(j1); sel2.from(t4); ... // Define the rest of the RWDBSelector sel2 RWDBSelector sel3 = myDbase.selector(); sel3.fromGeneration(RWDBSelector::ExplicitAndImplicit); sel3 << t1["col"] << t2["col"] << t3["col"]; sel3 << t5["col"] << t6["col"]; sel3.from(j1); sel3.from(t4); sel3.externFrom(t2); ... // Define the rest of the RWDBSelector sel3

RWDBSelector sel1 has by default the FROM clause generation behavior of ExplicitOrImplicit. Since no explicit from() entries are added, the FROM clause is generated using the implicit list, consisting of tables t1, t2 and t3 from the select column list. However since table t2 is marked as external, the FROM clause will have tables t1 and t3 only.

RWDBSelector sel2 also has by default the FROM clause generation behavior of ExplicitOrImplicit. Since explicit from() entries are added, the FROM clause is generated using the explicit list, consisting of join expression j1 and table t4.

RWDBSelector sel3 has the FROM clause generation behavior of ExplicitAndImplicit. The implicit list has entries for tables t1, t2, t3, t5 and t6 from the select column list. The from() calls have created the explicit list of join expression j1 and table t4. The union of the two will result in the join expression j1 and tables t1, t2, t3 and t4. Note that tables t5 and t6 are removed as they are part of the join expression j1. Since table t2 is marked as external, the FROM clause will consist of join expression j1 and tables t1, t3 and t4.

In the previous sections, we covered the basics of selecting data from single and multiple tables. In this section and the ones that follow, we look briefly at ways to use class RWDBSelector to construct more complex queries, starting with ordering and grouping.

In SQL, ordering and grouping are specified with the ORDER BY and GROUP BY clauses, respectively. The DB Interface Module models these with the orderBy() and groupBy() methods of RWDBSelector. There are variants to support ordering or grouping by column name, column number, or an expression. The following fragment assumes we have the RWDBSelector instance declared in Section 5.3.2.

select.orderBy(videos["title"]); //1 select.orderBy(1); //2

Line //1 says that the result should be sorted according to the videos.title column. Line //2 says to order by the first item in the select list, which is purchases.orderNum. C++ indexing starts at 0 but SQL indexing starts at 1.

Calls to orderBy() and groupBy() cause items to accumulate within a selector. If we included //1 and //2 in our program, we would order by both the videos.title and column number 1.

---

To replace a selector's order by items, first use the orderByClear() method, then call orderBy() once for each new item. The same is true of groupBy() and groupByClear().

---

Some database implementations place restrictions on ordering and grouping. In particular, ordering or grouping by a complex expression, rather than a simple column reference, may not be supported. It may also be impossible to order or group by an item that doesn't appear in the select list. These restrictions are typically not detectable until runtime. In these cases, the DB Interface Module catches any errors reported by the database and passes them back via your application's error handler. The application may need to be changed to allow its use with the given database. See Tutorial 6 in Chapter 22, "A Complicated Query," for a good example of this.

Using an RWDBSelector to select arbitrary expressions is simple: just insert an RWDBExpr representing the desired expression. For example, to select the sum of two columns, use this type of statement:

select << table1["numericColumn1"] + table1["numericColumn2"];

Functions and aggregates are handled with a uniform functional notation by providing predefined functions that accept an expression (see Table 6). The DB Interface Module then uses RWDBPhraseBook anonymously to hide database-dependent details:

select << rwdbMax(table1["column1"]); //1 select << rwdbUpper(table1["column2"]); //2 select << rwdbSystemDateTime(); //3

Line //1 represents an SQL aggregate MAX, line //2 represents a function that converts its argument to upper case, and //3 represents a function that returns the current date and time as known to the database. Table 6 summarizes the predefined functions of the DB Interface Module. See the entry for RWDBExpr in the DB Interface Module Reference Guide for the syntax used in these functions, and the Access Module guides for the database-dependent expansion of functions. Note that there is also an extensible method that allows you to define your own functions.

Experienced users of SQL know there are times when you want to join a table with itself. To accomplish this with an RWDBSelector, declare a second instance of the RWDBTable of interest. (This example is derived from A Guide to the SQL Standard by C.J. Date.)

RWDBTable first = myDbase.table("supplier"); //1 RWDBTable second = myDbase.table("supplier"); //2 RWDBSelector select = myDbase.selector(); select << first["ID"] << second["ID"]; select.where(first["city"] == second["city"]); //3

From the supplier table, the example selects pairs of IDs representing suppliers in the same city. The RWDBTable instances declared on //1 and //2 both refer to the same database table, supplier. Recall from Section 4.4, "Tables," that these table references are inexpensive, since they don't require any database access. The resulting selector represents an SQL code fragment of this type:

SELECT first.ID, second.ID FROM supplier first, supplier second WHERE first.city = second.city

---

The SQL code here and in the section below is hypothetical, since actual SQL syntax varies from one database implementation to another. SourcePro DB handles these vendor-specific details from a single programming interface, so you don't have to worry about them.

---

Because an RWDBExpr instance can be constructed from an RWDBSelector instance, it is easy to use selectors to express subqueries. For example:

RWDBTable primary = myDbase.table("primary"); RWDBTable backup = myDbase.table("backup"); RWDBSelector select = myDbase.selector(); RWDBSelector avg = myDbase.selector(); avg << rwdbAvg(backup["onHand"]); select << primary["title"]; select.where(primary["onHand"] >= avg);

This code fragment constructs a selector that is equivalent to the hypothetical SQL statement:

SELECT primary.title FROM primary WHERE primary.onHand >= ( SELECT AVG(onHand) FROM backup)

Class RWDBSelector is used to specify data to be read from a database. It encapsulates all aspects of the SELECT statement used in SQL, including the relational concepts of projection, restriction, and join. Since selecting data returns a single table of results, it is convenient to view a selector as a short cut to a result table. Consequently, RWDBSelector can be provided wherever RWDBTable representing a result table is used.

RWDBSelector supports a lot of functionality, so it has many member functions and operators. A complete listing of these is in the SourcePro C++ API Reference Guide. Section 5.3 covered these important concepts:

• The insertion operator << is used to specify items to be selected, that is, a SELECT list. Use it multiple times to specify more than one item to select.

• In general, selectors can deduce the tables from which to select by examining the column references in the selected items and the WHERE criterion. For complex queries, manipulation of the FROM clause may be needed, and is provided by methods from(), fromClear(), fromGeneration(), fromExtern(), and fromExternClear().

• The where() method is used to specify a selection criterion, that is, the WHERE clause. A selector can have only a single selection criterion, but the criterion may be complex.

• RWDBSelector has an execute() method that returns an RWDBResult, which is expected to contain one table. It is usually more convenient to read the selected data by obtaining an RWDBReader directly from the selector.

• Outer joins can be specified either through the FROM method or the WHERE method of RWDBSelector, based on the capabilities of the database.

• Methods such as orderBy() and groupBy() apply SQL semantics to selectors, such as ORDER BY and GROUP BY clauses.

• To express self-joins, declare multiple instances of the same table. This is not expensive.

• A selector may be converted into an expression in order to express subquery semantics. When using subqueries, it may be necessary to use the fromExtern() method to declare tables as externally defined, as for correlated subqueries.

Copyright © Rogue Wave Software, Inc. All Rights Reserved.

The Rogue Wave name and logo, and SourcePro, are registered trademarks of Rogue Wave Software. All other trademarks are the property of their respective owners.
Provide feedback to Rogue Wave about its documentation.