4.6 Selecting Data

We have already seen one way to select data from a database: by using a reader to read a database table. In SQL terms, we have learned how to:

SELECT * FROM  table-name

This is fine as long as we wish to read all the rows and all the columns of a single table. Naturally, your applications need to 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. The DBTools.h++ 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 DBTools.h++ Class Reference. This chapter covers the basic concepts, and introduces some advanced ones as well.

This section shows you how to select data 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(
          "libora4d.so",    // access library name
          "oracle7host",    // server name
          "dbxx",           // user name
          "demodb",         // password
          "DEMO_DB"         // 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
  while(rdr()) {
    int purchaseOrderNumber;
    int supplierID;
    rdr >> purchaseOrderNumber >> supplierID;
    cout << purchaseOrderNumber << "\t" << supplierID << endl;
  }
  return 0;
}

This example is much like Example Program 2 on reading a database table in Section 4.4.2. The difference is that in this example 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() and fromClear() 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 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.

---

For example:

select.where(table1["col1"] == x && table1["col2"] <= y);

Here 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 it. Our expectation is that the result of the execution is going to be a single table consisting of two columns. Consequently, we might do this:

RWDBResult result = select.execute(myConnection);
RWDBTable resultTable = result.table();
RWDBReader rdr = resultTable.reader();
// etc.

This syntax is completely valid, but a bit tedious. 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 three lines of code with one:

RWDBReader rdr = select.reader(myConnection);

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). How can we combine these abilities to produce a join? Actually, it's quite simple: we just mention columns from more than one table.

// Example Program 4 - Performing a join
#include <rw/rstream.h>
#include <rw/db/db.h>

int 
main() {
  RWDBDatabase myDbase = RWDBManager::database()
            "ODBC",        // access library name
            "odbcserver",  // user name
            "demodb",      // password
            "DEMO_DB"      // database name
        );
    
  RWDBConnection myConnection = myDbase.connection();

  RWDBTable purchases = myDbase.table("purchase");            //1
  RWDBTable videos = myDbase.table("videos");                 //2
  RWDBSelector select = myDbase.selector();
  select << purchases["orderNum"]                             //3
         << purchases["supplierID"] << videos["title"];       //4
  select.where(purchases["videoID"] == videos["ID"]);         //5
  RWDBReader rdr = select.reader(myConnection);               //6
  while(rdr()) {
    int purchaseOrderNumber;
    int supplierID;
    RWCString title;
    rdr >> purchaseOrderNumber >> supplierID >> title;
    cout << purchaseOrderNumber << "\t" 
         << title << "\t" << supplierID << endl;
  }
  return 0;
}

On //1 and //2, references to the database tables purchase and videos are obtained. On //3 and //4, the insertion operator << is used to specify the columns we wish to select: the orderNum and supplierID columns from the purchase table, and the title column from the videos table. As in previous examples, the tables to select FROM can be deduced from the column references.

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.

DBTools.h++ provides a variety of methods that generate appropriate outer join constructs for different vendors. The SQL construct for outer joins varies widely among database vendors. The major difference is whether the outer join is part of the SQL FROM clause or part of the SQL WHERE clause.

The following two sections demonstrate how an application can generate appropriate outer join syntax for databases that support SQL 92, and for those that do not. See the DBTools.h++ access library guides to determine which syntax is supported.

If your database supports SQL 92 syntax, you can generate an outer join construct as part of the FROM clause. Use the following procedure:

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

2. Add this join expression to the FROM clause using RWDBSelector::from().

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

The following code shows how to implement the procedure:

#include <rw/rstream.h>
#include <rw/db/db.h>

int 
main() {
  RWDBDatabase myDbase = RWDBManager::database( 
                         "inf15d.dll",      // access library name
                         "server_name",     // server name
                         "user_name",       // user name
                         "pass_word",       // password
                         "db_name"          // database name
                         );

  RWDBConnection myConnection = myDbase.connection();

  RWDBTable purchases = myDbase.table("purchase");
  RWDBTable videos = myDbase.table("videos");

  RWDBSelector selector = myDbase.selector();
  selector << purchases["orderNum"] 
           << purchases["supplierID"] 
           << videos["title"];

   selector.from(rwdbLeftOuter(purchases, videos));            // 1

   selector.on(purchases["videoID"] == videos["ID"]);          // 2
   selector.where( ( purchases["videoID"] == 
         videos["ID"] ) && ( purchases["videoID"] > 30 ));     // 3

   RWDBReader rdr = selector.reader(myConnection);
   int purchaseOrderNumber, supplierID;
   RWCString title;
   while (rdr()) {
     rdr >> purchaseOrderNumber >> supplierID >> title;
     cout << purchaseOrderNumber << supplierID << title << endl;
   }
   return 0;
}

Note: Informix does not provide for an explicit ON clause as that is an implicit part of the WHERE clause. Therefore, //2 and //3 above should be replaced by the following statement:

   selector.where( ( purchases["videoID"] == 
         videos["ID"] ) && ( purchases["videoID"] > 30 )); // 2 & 3

On line //1, a left outer join expression is constructed by rwdbLeftOuter() between the purchases table and the videos table, and set as part of the FROM clause. Line //2 specifies the ON clause indicating the join condition to be used for the outer join. Line //3 specifies the criterion for the WHERE clause. The rest of the code in the example is similar to Example Program 4.

If your database does not support SQL 92 syntax, you may generate an outer join construct as part of the WHERE clause. Use the following procedure:

1. Create an RWDBCriterion outer join condition using either the RWDBColumn or the RWDBExpr left/right outer join methods.

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

The following code shows how to implement the procedure:

#include <rw/rstream.h>
#include <rw/db/db.h>

int 
main() {
  RWDBDatabase myDbase = RWDBManager::database( 
                         "SYBASE",      // access library name
                         "sybcserver",  // server name
                         "tutor",       // user name
                         "passwd",      // password
                         "base"         // database name
                         );

  RWDBConnection myConnection = myDbase.connection();
  RWDBTable purchases = myDbase.table("purchase");
  RWDBTable videos = myDbase.table("videos");
  RWDBSelector selector = myDbase.selector();
  selector << purchases["orderNum"] 
           << purchases["supplierID"] << videos["title"];

  selector.where(purchase["videoID"]
                            .leftOuterJoin(videos["ID"])); //1
  RWDBReader rdr = selector.reader(myConnection);
  int purchaseOrderNumber, supplierID;
  RWCString title;
  while (rdr()) {
    rdr >> purchaseOrderNumber >> supplierID >> title;
    cout << purchaseOrderNumber << supplierID << title << endl;
  }
  return 0;
}

On //1, a left outer join expression is constructed and set in the WHERE clause. 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 Program 4.

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. DBTools.h++ models these with the orderBy() and groupBy() methods of RWDBSelector. There are variants to support ordering or grouping by column name or by column number. The following fragment assumes we have the RWDBSelector instance declared in the previous section.

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 second item in the select list, which is purchases.supplierID.C++ indexing starts at 0.

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.

---

NOTE: 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, DBTools.h++ 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 20 for a good example of this.

Relational database engines support the notion of selecting more than simple column references. There are aggregate functions such as MIN, MAX, SUM and AVG, which apply to entire columns that may be grouped with a GROUP BY clause. There are also arithmetic and string operations that apply to scalar values, and there are often built-in functions such as USER or DATE.

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 any expression (see Table 7). DBTools.h++ 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 7 summarizes the DBTools.h++ predefined functions. See the entry for RWDBExpr in the DBTools.h++ Class Reference for the syntax used in these functions, and the access library guides for the database-dependent expansion of functions. Note that there is also an extensible method that allows you to define your own functions.

Table 7 -- DBTools.h++ predefined 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 Date (see the Bibliography):

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 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

---

NOTE: The SQL code here and in the section below is hypothetical, since actual SQL syntax varies from one database implementation to another. DBTools.h++ 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 which is equivalent to the hypothetical SQL statement:

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

Notice that in the example above there are no tables that are referenced in both the outer query and the subquery. Special care is required when table references are shared between the outer query and the subquery. Consider the following example:

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

A reasonable interpretation of this code fragment could be expressed this way in SQL:

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

However, we might also mean this:

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

In other words, we might want a correlated subquery. To get it, we remove the reference to the primary table from the FROM list of the subquery. Here's how:

RWDBTable primary = myDbase.table("primary");
RWDBTable backup = myDbase.table("backup");
RWDBSelector select = myDbase.selector();
RWDBSelector avg = myDbase.selector();
avg << rwdbAvg(backup["onHand"]);
avg.where(backup["ID"] == primary["ID"]);
avg.fromClear();                                               //1
avg.from(backup);                                              //2
select << primary["title"];
select.where(primary["onHand"] >= avg);

By using the fromClear() and from() methods on //1 and //2, we override the mechanism by which RWDBSelector deduces the tables to select from, and specify exactly what we want.

Please note that this does not work for RWDBUpdaters and RWDBDeleters. For these classes, you do not have to clear your FROM clause; however, you do need to specify explicitly all table names to be used in the subquery's FROM clause. For example:

RWDBTable primary = myDbase.table("primary");
RWDBTable backup = myDbase.table("backup");
RWDBDeleter deleter = primary.deleter();
RWDBSelector avg = myDbase.selector();
avg << rwdbAvg(backup["onHand"]);
avg.where(backup["ID"] == primary["ID"]);
avg.from(primary);                                             //1
avg.from(backup);                                              //2
deleter.where(primary["onHand"].in(avg));

By using the from() methods on //1 and //2, we explicitly specify the tables for our subquery.

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 is used.

RWDBSelector supports a lot of functionality, so it has many member functions and operators. A complete listing of these is in the DBTools.h++ Class Reference. Section 4.6 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.

• 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-like 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 fromClear() and from() methods to specify the tables to select from, as for correlated subqueries.

©Copyright 2000, Rogue Wave Software, Inc.
Contact Rogue Wave about documentation or support issues.