Expressions, Criteria, and Assignments
One of the great advantages of a client/server environment is the ability of the client to defer computation to the server. When a complicated query is requested, the client does not have to execute the query itself; the server does the work. This is illustrated by SQL statements like this:
SELECT *
FROM SomeTable
WHERE COl1 = 17 AND COL2 < COL3 - LENGTH( RTRIM (COL4) )
The
WHERE clause, which is specified by the client but executed by the server, contains a type of expression that is encapsulated in the DB Interface Module by some of the expression classes
RWDBExpr,
RWDBCriterion, and
RWDBAssignment. These classes, and others, can be used to represent the following types of expressions:
• RWDBExpr is the base class for simple or complex expressions. These classes derive from it:
— RWDBAssignment is used to assign new values to columns, as in the
SET clause for updating rows.
The various databases have minor differences in their expression syntax. For example, the Sybase database has a modulus operator, while the Oracle database uses a function call instead. The DB Interface Module reconciles these differences by using standard C++ expression syntax involving columns, literals, and function invocations to encapsulate expressions. The Access module then translates expressions into forms appropriate for the database in use. These translations are described in the guides for the Access Modules.
Instances of the
RWDBExpr,
RWDBCriterion, and
RWDBAssignment classes are usually created anonymously, while
RWDBSimpleCaseExpr and
RWDBSearchedCaseExpr are explicitly created.
The following sections discuss these classes in more detail.
Creating WHERE Clauses and Check Constraints
RWDBCriterion constructs boolean expressions used in
WHERE clauses and check conditions.
This example creates a query based on the criterion that COL1 from some table has a value equal to 17:
Example 3 – Creating a WHERE query
RWDBColumn column1 = aTable["COL1"]; //1
RWDBSelector aSelector = myDbase.selector(); //2
aSelector.where(column1 == 17); //3
The
RWDBSelector instance on
//2 is explained in
“Selecting Data” of this manual. For now, look at
//3. The
where() member function of
RWDBSelector accepts an
RWDBCriterion instance as an argument. A C++ expression involving an
RWDBColumn instance
column1, the
operator ==, and a literal
17 are passed to it. Through automatic type conversions and overloading of the relational
operator==, an
RWDBCriterion instance is provided. Here's what happens:
1. First, the compiler tries to apply
operator== to the instance of the
RWDBColumn and the literal integer
17.
2. Since there is no operator to accomplish this, it tries to cast the column and integer into objects on which it can apply some
operator==. The only action the compiler can take is to cast the
RWDBColumn instance into an
RWDBExpr.
3. The compiler then does the same thing for the literal integer.
4. Once these are cast, the compiler can apply the
operator== for the two instances of
RWDBExpr. Since
operator== for two
RWDBExpr instances returns an instance of
RWDBCriterion, the
where() member function is satisfied.
Here’s an example using an
RWDBCriterion as the check condition in an
RWDBCheckConstraint.
Example 4 – Creating a query based on a check condition
RWDBSchema benefitSchema;
// Define columns for benefits table
benefitSchema.appendColumn("empnum", RWDBValue::Int);
benefitSchema.appendColumn("salary", RWDBValue::Decimal,
RWDB_NO_TRAIT, RWDB_NO_TRAIT, 10, 2);
benefitSchema.appendColumn("life_ins", RWDBValue::Decimal, RWDB_NO_TRAIT,
RWDB_NO_TRAIT, 10, 2);
// ... add other columns
// Define check constraint on the table
RWDBCheckConstraint lifeInsCheck(benefitSchema["life_ins"] < 5 //1
* benefitSchema["salary"], "max_life_ins_check");
benefitSchema.checkConstraint(lifeInsCheck);
// Create table
db.createTable("benefits", benefitSchema);
The expression passed as the first argument to the constructor of
RWDBCheckConstraint in line
//1 forms the condition to be checked by the generated check constraint. Similar to the previous example in this section,
RWDBColumn instances returned by
benefitSchema["life_ins"] and
benefitSchema["salary"] and the constant integer
5 are all implicitly converted to
RWDBExpr.
The multiplication operator then creates another expression combining the expressions from integer
5 and column
benefitSchema["salary"]. The relational operator
< produces an
RWDBCriterion instance from the two expressions on either side of it. The generated SQL will be similar to
CONSTRAINT max_life_ins_check CHECK(life_ins < 5 * salary).
RWDBCriterion and RWDBExpr
Internally,
RWDBExpr instances are data structures in the form of trees. The
RWDBCriterion class derives from
RWDBExpr and, therefore, is similarly structured.
Example 3 results in an
RWDBCriterion instance containing a three-node binary tree. The root node represents the dyadic expression of the
operator ==. The two leaf nodes represent the column and the literal integer
17.
It is important to note that evaluation of
RWDBExpr instances is performed by the library at the time of the execution. Expressions that produce
RWDBExpr or
RWDBCriterion instances might look like any other C++ expression, but their evaluation is deferred to the access library module.
Here is another example, which constructs a more complex
RWDBCriterion instance. Assume that an
RWDBSelector instance already exists:
Example 5 – Creating a complex WHERE clause
aSelector.where((col1 % 5 == 0) || col2.isNull());
The underlying access library module evaluates this expression according to its own syntax. For the Oracle database, the expression would be evaluated as:
WHERE (MOD(COL1, 5) = 0) OR COL2 IS NULL
For Sybase it would be slightly different:
WHERE (COL1 % 5 = 0) OR COL2 IS NULL
This difference arises because the Oracle database has a modulus function, while the Sybase database has a modulus operator.
Assigning New Values to Table Columns
Use
RWDBAssignment to assign new values to columns in tables, in other words, to update. The only way to create an instance of class
RWDBAssignment is through the invocation of the
RWDBColumn::assign() method. An
RWDBAssignment is an encapsulation of the SQL phrase:
SET column = expression
where
column refers to the
RWDBColumn instance whose
assign() method produced the
RWDBAssignment, and
expression refers to its argument. Here is an example:
Example 6 – Assigning a value to a column
RWDBAssignment a = col1.assign(col2 / 3.1415);
This is equivalent to the SQL phrase:
SET COL1 = COL2/3.1415
The compiler interprets the SQL phrase as follows:
• The
assign() member function accepts an
RWDBExpr as an argument.
• The compiler does not find an overloaded
operator / for
RWDBColumn instances and doubles, but it has one for two
RWDBExpr instances.
• Since the compiler can create an
RWDBExpr instance from both an
RWDBColumn and a double, it does.
• It then applies the
operator / to form an
RWDBExpr acceptable to the
assign() function.
• Eventually, the assignment is executed by the underlying access library module.
Creating CASE Expressions
Use
RWDBSimpleCaseExpr and
RWDBSearchedCaseExpr to build SQL
CASE expressions.
Simple CASE Expressions
This section demonstrates how to construct a simple
CASE expression for a
SELECT statement using
RWDBSimpleCaseExpr. This example generates a category abbreviation based on a video category.
Here is the SQL:
select title,
CASE category
WHEN 'Animated' THEN 'Anim'
WHEN 'Comedy' THEN 'Cmdy'
WHEN 'Drama' THEN 'Drma'
WHEN 'Science Fiction' THEN 'SciFi'
ELSE category
END
from videos
order by title
Now let’s look at the code:
Example 7 – Performing a query with a simple CASE expression
#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
);
const RWDBConnection myConnection = myDbase.connection();
const RWDBTable videos = myDbase.table("videos");
RWDBSelector select = myDbase.selector();
const RWDBColumn yearColumn = videos["yr"];
const RWDBColumn titleColumn = videos["title"];
const RWDBColumn categoryColumn = videos["category"];
RWDBSimpleCaseExpr simpleExpr(categoryColumn); //1
simpleExpr.when( "Animated", "Anim" ); //2
simpleExpr.when( "Comedy", "Cmdy" );
simpleExpr.when( "Drama", "Drma" );
simpleExpr.when( "Science Fiction", "SciFi" );
simpleExpr.else_(categoryColumn); //3
select << titleColumn //4
<< simpleExpr; //5
RWDBReader rdr = select.reader(myConnection);
RWCString title, decade;
while(rdr()) {
rdr >> title >> decade;
std::cout << title << "\t" << decade << std::endl;
}
return 0;
On
//1, create an instance
simpleExpr of
RWDBSimpleCaseExpr, using the column "category" as the input expression against which to compare.
On
//2 and following, use the
when() method to specify WHEN/THEN statements for the
CASE expression. The first argument to
when() defines an
RWDBExpr that is compared against the input expression with which the
RWDBSimpleCaseExpr was constructed. If the comparison is true, then the second argument is used. This pattern is repeated until all required WHEN/THEN statements are defined.
On //3, add an ELSE statement for the CASE expression to specify a fall-through value.
In //4 and //5, define the select list to include the "title" and the CASE expression.
Searched CASE Expressions
The example in this section uses
RWDBSearchedCaseExpr to construct a searched
CASE expression for a
SELECT statement that generates a decade string for each video release date.
Here is the SQL:
select title,
CASE
WHEN 1970 <= yr AND yr <= 1979 THEN '1970s'
WHEN 1980 <= yr AND yr <= 1989 THEN '1980s'
WHEN 1990 <= yr AND yr <= 1999 THEN '1990s'
ELSE 'Unknown Decade'
END
from videos
order by title
Now let’s look at the code:
Example 8 – Performing a query with a searched CASE expression
#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
);
const RWDBConnection myConnection = myDbase.connection();
const RWDBTable videos = myDbase.table("videos");
RWDBSelector select = myDbase.selector();
const RWDBColumn yearColumn = videos["yr"];
const RWDBColumn titleColumn = videos["title"];
RWDBSearchedCaseExpr searchedExpr; //1
searchedExpr.when( (1970 <= yearColumn) && (yearColumn <= 1979), "1970s" ); //2
searchedExpr.when( (1980 <= yearColumn) && (yearColumn <= 1989), "1980s" );
searchedExpr.when( (1990 <= yearColumn) && (yearColumn <= 1999), "1990s" );
searchedExpr.else_("Unknown Decade"); //3
select << titleColumn //4
<< searchedExpr; //5
select.orderBy(titleColumn);
RWDBReader rdr = select.reader(myConnection);
RWCString title, decade;
while(rdr()) {
rdr >> title >> decade;
std::cout << title << "\t" << decade << std::endl;
}
return 0;
On
//1, create an instance of
RWDBSearchedCaseExpr.
On
//2 and following, use the
when() method to specify WHEN/THEN statements for the CASE expression. The first argument to
when() defines an
RWDBCriterion that, if true, uses the second argument. This pattern is repeated until all required WHEN/THEN statements are defined.
On //3, add an ELSE statement for the CASE expression to specify a fall-through value.
On //4 and //5, define the select list to include the "title" and the CASE expression.
Summary
Class
RWDBExpr and its derived classes
RWDBCriterion,
RWDBAssignment,
RWDBSimpleCaseExpr, and
RWDBSearchedCaseExpr serve as encapsulations of expressions to be evaluated by the DB Interface Module and executed at the database. They allow the use of the familiar C++ expression syntax, while offering the advantage of database portability.