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.
5.3.1 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 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(
"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 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.
5.3.2 Joins
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
// Example Program 4 - Performing a join
#include <rw/rstream.h>
#include <rw/db/db.h>
int
main() {
RWDBDatabase myDbase = RWDBManager::database()
"ODBC", // Access Module name
"odbc_dsn // server name
"user", // user name
"pwd", // 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
int purchaseOrderNumber;
int supplierID;
RWCString title;
while(rdr()) {
rdr >> purchaseOrderNumber >> supplierID >> title;
std::cout << purchaseOrderNumber << "\t"
<< title << "\t" << supplierID << std::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.
5.3.3 Outer Joins
The SQL construct for outer joins varies widely among database vendors. For this reason, the DB Interface Module provides a variety of methods to generate vendor-specific outer join constructs. The major difference between these methods is whether or not they comply with the ANSI SQL 92 syntax. ANSI-compliant outer joins are written as part of the SQL FROM clause; ANSI-noncompliant joins are written as part of the SQL WHERE clause.
You must use ANSI syntax, and write outer joins in the FROM clause, if you use any of the following DB Access Modules:
DB2 CLI
Microsoft SQL Server
PostgreSQL
ODBC
MySQL
With the DB Access Modules listed below, you can use either ANSI or non-ANSI syntax. If you use non-ANSI syntax, you must write outer joins in the WHERE clause.
Oracle OCI
Sybase Open Client
If you use ODBC or Oracle OCI access modules, please note their unique features:
The ODBC interface can support either ANSI or non-ANSI syntax, depending on the compliance of the backend database. However, you must use ANSI syntax with the Rogue Wave ODBC access module.
Oracle OCI supports both ANSI-compliant outer joins and ANSI-noncompliant outer joins using the Oracle join operator (
+). Oracle recommends using the ANSI-compliant outer joins in the
FROM clause rather than using the join operator in the
WHERE clause.
See the appropriate guide for your DB Access Module to determine the supported syntax and other particulars of outer joins. The following two sections demonstrate how an application can generate appropriate outer join syntax for ANSI-compliant databases in the FROM clause, and for ANSI-noncompliant databases in the WHERE clause.
5.3.3.1 Outer Join Constructs in the FROM Clause (ANSI-Compliant)
To generate a SQL 92 outer join construct as part of the FROM clause, use the following procedure:
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.
The following three examples show how to implement three kinds of outer joins in ANSI-compliant syntax: the simple outer join, the nested outer join, and the multiple outer join.
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.
A Simple Outer Join
// This example shows a right outer join of two tables,
// emp and dept.
#include <rw/rstream.h>
#include <rw/db/db.h>
int
main()
{
RWDBDatabase myDbase = RWDBManager::database(
"msq<ver>12d.dll", // Access Module name
"server_name", // server name
"user_name", // user name
"pass_word", // password
"db_name" // database name
);
RWDBConnection myConnection = myDbase.connection();
RWDBTable employee = myDbase.table("emp");
RWDBTable dept = myDbase.table("dept");
RWDBSelector selector = myDbase.selector();
selector << employee["empnum"] << employee["ename"]
<< dept["dname"];
RWDBJoinExpr join = rwdbRightOuter(employee, dept); //1
join.on(employee["deptno"] == dept["deptno"]); //2
selector.from(join); //3
RWDBReader rdr = selector.reader(myConnection);
int empnum;
RWCString ename, dname;
while (rdr())
{
rdr >> empnum >> ename >> dname;
std::cout << empnum << "\t" << ename << "\t"
<< dname
<< std::endl;
}
return 0;
}
On //1, an isolated right outer join is constructed between the tables emp and dept using the global function rwdbRightOuter().
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 //3, the join is explicitly attached to the FROM clause of the selector, completing the join procedure. Join expressions must be explicitly joined to the selector. They are not implicitly attached.
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.
A Nested Outer Join
// The join of emp and dept, from the previous example,
// joins itself to a third table, loc.
#include <rw/rstream.h>
#include <rw/db/db.h>
int
main()
{
RWDBDatabase myDbase = RWDBManager::database(
"msq<ver>12d.dll", // Access Module name
"server_name", // server name
"user_name", // user name
"pass_word", // password
"db_name" // database name
);
RWDBConnection myConnection = myDbase.connection();
RWDBTable employee = myDbase.table("emp");
RWDBTable dept = myDbase.table("dept");
RWDBTable locate = myDbase.table("loc");
RWDBSelector selector = myDbase.selector();
selector << employee["empnum"] << employee["ename"]
<< dept["dname"]
<< locate["lname"];
RWDBJoinExpr join1 = rwdbRightOuter(employee, dept); //1
join1.on(employee["deptno"] == dept["deptno"]); //2
RWDBJoinExpr join2 = rwdbLeftOuter(join1, locate); //3
join2.on(dept["locno"] == locate["locno"]); //4
selector.from(join2); //5
RWDBReader rdr = selector.reader(myConnection);
int empnum;
RWCString ename, dname, lname;
while (rdr())
{
rdr >> empnum >> ename >> dname
>> lname;
std::cout << empnum << "\t" << ename << "\t"
<< dname << "\t" << lname
<< std::endl;
}
return 0;
}
On //1, an isolated right outer join is constructed between the tables emp and dept using the global function rwdbRightOuter().
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 //5, this join is explicitly attached to the FROM clause of the selector, completing the nested join procedure. Join expressions must be explicitly joined to the selector; they are not implicitly attached.
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.
A Multiple Outer Join
// This example shows the use of multiple joins in a query.
// More than one join are included in the FROM clause.
#include <rw/rstream.h>
#include <rw/db/db.h>
int
main()
{
RWDBDatabase myDbase = RWDBManager::database(
"msq<ver>12d.dll", // Access Module name
"server_name", // server name
"user_name", // user name
"pass_word", // password
"db_name" // database name
);
RWDBConnection myConnection = myDbase.connection();
RWDBTable employee = myDbase.table("emp");
RWDBTable salary = myDbase.table("salary");
RWDBTable dept = myDbase.table("dept");
RWDBTable locate = myDbase.table("loc");
RWDBSelector selector = myDbase.selector();
selector << employee["empnum"] << employee["ename"]
<< dept["dname"] << locate["lname"]
<< salary["base_salary"];
RWDBJoinExpr join1 = rwdbLeftOuter(employee, salary); //1
join1.on(employee["empnum"] == salary["empnum"]); //2
RWDBJoinExpr join2 = rwdbLeftOuter(dept, locate); //3
join2.on(dept["locno"] == locate["locno"]); //4
selector.from(join1); //5
selector.from(join2); //6
selector.where(employee["deptno"] == dept["deptno"]);
RWDBReader rdr = selector.reader(myConnection);
int empnum;
RWCString ename, dname, lname;
RWDecimalPortable salary;
while (rdr())
{
rdr >> empnum >> ename >> dname >> lname >> salary;
std::cout << empnum << "\t" << ename << "\t"
<< dname << "\t" << lname << "\t"
<< salary << std::endl;
}
return 0;
}
On //1, an isolated left outer join is constructed between the tables emp and salary using the global function rwdbLeftOuter().
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.
5.3.3.2 Outer Join Constructs In the WHERE Clause (ANSI-Noncompliant)
If your database does not support SQL 92 syntax, you can generate an outer join construct as part of the WHERE clause. Use the following procedure:
2. Specify the criterion, combined with any other criterions, in the WHERE clause.
The following code shows how to implement the procedure:
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.
An Outer Join in the WHERE Clause
#include <rw/rstream.h>
#include <rw/db/db.h>
int
main() {
RWDBDatabase myDbase = RWDBManager::database(
"ctl<ver>12d.dll", // Access Module name
"sybserver", // server name
"tutor", // user name
"passwd", // password
"db" // 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(purchases["videoID"].leftOuterJoin(videos["ID"])); //1
RWDBReader rdr = selector.reader(myConnection);
int purchaseOrderNumber, supplierID;
RWCString title;
while (rdr()) {
rdr >> purchaseOrderNumber >> supplierID >> title;
std::cout << purchaseOrderNumber << "\t"
<< supplierID << "\t" << title
<< std::endl;
}
return 0;
}
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.
5.3.4 Composing FROM clause
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.
5.3.5 Ordering and Grouping
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.
5.3.6 Aggregates, Expressions, and Functions
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 an expression (see
Table 7). 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 7 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.
Table 7 – Predefined functions of the DB Interface Module
Function | Description |
rwdbAvg | The AVG aggregate |
rwdbCast | Type conversion |
rwdbCharLength | Length of character data |
rwdbCount | The COUNT aggregate |
rwdbCountDistinct | The COUNT DISTINCT aggregate |
rwdbCurrentUser | The application's database user name |
rwdbExists | The SQL EXISTS function |
rwdbLower | Converts a string to lower case |
rwdbMax | The MAX aggregate |
rwdbMin | The MIN aggregate |
rwdbName | Assigns a name to a column |
rwdbPosition | Finds the index in one string of another string |
rwdbSessionUser | The application's database login name |
rwdbSubString | Extracts a substring from a string |
rwdbSum | The SUM aggregate |
rwdbSystemDateTime | The current date and time, according to the database |
rwdbSystemUser | The application's login name |
rwdbTrimBoth | Trims both leading and trailing characters from a string. Default character is blank. |
rwdbTrimLeading | Trims leading characters from a string. Default character is blank. |
rwdbTrimTrailing | Trims trailing characters from a string. Default character is blank. |
rwdbUpper | Converts a string to upper case |
5.3.7 Self-Joins
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.
5.3.8 Subqueries
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)
Notice that in the previous example no tables 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 mark the reference to the primary table as external in the subquery, since the table definition is supplied from the outer query. 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.fromExtern(primary); //1
select << primary["title"];
select.where(primary["onHand"] >= avg);
By using the fromExtern() method in line //1, we specify that primary table is defined externally to avg. The definition is supplied elsewhere, and hence primary table should not be defined in the FROM clause of avg.
5.3.9 Summary
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.