4.5 Expressions, Criterions, and Assignments

Before we move on to a discussion of how to select data from a database, we need to take a look at expressions, criterions, 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 - MIN (COL4)

The WHERE clause, which is specified by the client but executed by the server, contains a type of expression that is encapsulated in DBTools.h++ by the RWDBExpr, RWDBCriterion, and RWDBAssignment classes. In DBTools.h++, these expressions are represented as follows:

RWDBExpr is the base class for simple or complex expressions.

RWDBCriterion is used in the context of a boolean expression, as in the WHERE clause above.

RWDBAssignment is used in assigning new values to columns, as in the SET clause for updating rows.

The various databases have minor differences in their expression syntax. For example, Sybase has a modulus operator, but Oracle uses a function call instead. DBTools.h++ uses standard C++ expression syntax involving columns, literals, and function invocations to encapsulate expressions. DBTools.h++ then translates the expression into forms appropriate for the database in use. These translations are described in the access library guides.

Instances of the RWDBExpr, RWDBCriterion, and RWDBAssignment classes are usually created anonymously. The remainder of this section shows how RWDBExpr, RWDBCriterion, and RWDBAssignment objects are created and used. The next example creates a query based on the criterion that COL1 from some table has a value equal to 17:

RWDBColumn column1 = aTable["COL1"];             //1
RWDBSelector aSelector = myDbase.selector();     //2
aSelector.where (column1 == 17);                 //3

The RWDBSelector instance on //2 is explained in Section 4.6 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 takes place:

First, the compiler tries to apply the == operator to the instance of the RWDBColumn and the literal integer 17.
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.
The compiler then does the same thing for the literal integer.
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.

Internally, RWDBExpr instances are data structures in the form of trees. The RWDBCriterion class derives from RWDBExpr and, therefore, is similarly structured. The example above 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 done 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.

Here is another example, which constructs a more complex RWDBCriterion instance. Assume that an RWDBSelector instance already exists:

aSelector.where ((col1 % 5 == 0) || col2.isNull());

The underlying access library evaluates this expression according to its own syntax. For Oracle, this would be:

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 Oracle has a modulus function, while Sybase has a modulus operator.

RWDBAssignment also derives from RWDBExpr. It is used in assigning new values to columns in tables, in other words, updating. 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:

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 have 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.
The instance of RWDBAssignment can then be used with the RWDBUpdater class. See Section 4.8.
Eventually, the assignment is executed by the underlying access library.

In summary, the family of classes derived from RWDBExpr serves as encapsulations of expressions to be evaluated by the library and executed at the database. They allow the use of the familiar C++ expression syntax while offering the advantage of database portability.