Although all DB Access Modules are implemented through the same interface, there are still differences in the parameters that must be passed to certain expressions. The following section outlines the differences in the DB Access Module for ODBC.
The DB Interface Module does not restrict the length of table names or column names, but, for optimum portability, object names should be chosen to meet the constraints of all ODBC drivers with which the application is likely to run. In the tutorials shipped with the DB Interface Module, for example, all table names are 8 letters or less and all column names are 10 letters or less. This allows them to comply with the requirements of certain single tier drivers.
The DB Interface Module provides a predefined set of global functions, which allow an application to build portable SQL expressions.
Whenever possible, the DB Access Module for ODBC implements each global function through an escape sequence provided by the ODBC API. If the ODBC API does not supply an escape sequence analogous to the DB Interface Module global function, default syntax from the DB Interface Module is used. This default syntax may or may not be accepted by the underlying ODBC driver.
Table 4 lists the ODBC escape sequences or the DB Interface Module default syntax.
| Function | Restrictions |
| rwdbAvg(const RWDBExpr&) | None. Corresponds to: AVG(expr) |
rwdbCast(const RWDBExpr&,
const RWDBValue&)
|
None. Corresponds to:{fn CONVERT(expr, val)} |
rwdbCast(const RWDBExpr&,
const RWDBValue&,
const RWDBExpr&)
|
Same as the two-expression form, as ODBC does not support this form. |
rwdbCast(const RWBDExpr&,
const RWDBValue&,
const RWDBExpr&,
const RWDBExpr&)
|
Same as the two-expression form, as ODBC does not support this form. |
| rwdbCharLength(const RWDBExpr&) | None. Corresponds to: {fn LENGTH(expr)} |
| rwdbCount() | None. Corresponds to: COUNT(*) |
| rwdbCount(const RWDBExpr&) | None. Corresponds to: COUNT(expr) |
| rwdbCountDistinct(const RWDBExpr&) | None. Corresponds to: COUNT(DISTINCT expr) |
| rwdbCurrentUser() | None. Corresponds to: { fn USER()} |
| rwdbExists(const RWDBSelectorBase&) | None. Corresponds to: EXISTS(sel) |
| rwdbLower(const RWDBExpr&) | None. Corresponds to: {fn LCASE(expr)} |
| rwdbMax(const RWDBExpr&) | None. Corresponds to: MAX(expr) |
| rwdbMin(const RWDBExpr&) | None. Corresponds to: MIN(expr) |
rwdbName(const RWCString&
const RWDBExpr&)
|
None. Corresponds to: expr AS str |
rwdbPosition(const RWDBExpr&,
const RWDBExpr&)
|
None. Corresponds to:{fn LOCATE(expr0, expr1)} |
| rwdbSessionUser() | None. Corresponds to: {fn USER()} |
rwdbSubString(const RWDBExpr&,
const RWDBExpr&)
|
None. Corresponds to:{fn SUBSTRING(expr0, expr1, {fn LENGTH(expr0)} )} |
rwdbSubString(const RWDBExpr&,
const RWDBExpr&,
const RWDBExpr&)
|
None. Corresponds to:{fn SUBSTRING(expr0, expr1, expr2)} |
| rwdbSum(const RWDBExpr&) | None. Corresponds to: SUM(expr) |
| rwdbSystemDateTime() | None. Corresponds to: { fn NOW() } |
| rwdbSystemUser() | None. Corresponds to: {fn USER()} |
rwdbTrimLeading(const RWDBExpr&,
const RWDBExpr&)
|
None. Default from the DB Interface Module:{ fn LTRIM(expr1)} |
rwdbTrimTrailing(const RWDBExpr&,
const RWDBExpr&)
|
None. Default from the DB Interface Module:{fn RTRIM(expr1) |
rwdbTrimBoth(const RWDBExpr&,
const RWDBExpr&)
|
None. Default from the DB Interface Module:{fn LTRIM(RTRIM(expr1))} |
| rwdbUpper(const RWDBExpr&) | None. Corresponds to: {fn UCASE(expr)} |
For the DB Access Module for ODBC, the DB Interface Module supports the writing of outer joins using ANSI SQL 92 syntax only. See the examples on constructing outer joins in Section 5.3.3.1, "Outer Join Constructs In the FROM Clause (ANSI-Compliant)," in the DB Interface Module User's Guide.
Because of a restriction in ODBC syntax, you may not specify a nested join as a first parameter in a global function. For example, specifying a nested join as a first parameter in rwdbLeftOuterJoin() when instantiating an RWDBJoinExpr object results in an error.
The following example shows how you would write an outer join program for ODBC. You can assume that myDbase is a valid instance of RWDBDatabase.
The DB Access Module for ODBC generates SQL using the ODBC outer join escape sequence. It encloses the join using the syntax: {oj ... }, where ... represents the join expression. If you read the generated SQL, you should know that this is simply a normal part of the code.
An Outer Join for ODBC Using ANSI-Compliant Syntax
RWDBTable employee = myDbase.table("emp");
RWDBTable depart = myDbase.table("dept");
RWDBTable locate = myDbase.table("loc");
RWDBSelector selector = myDbase.selector();
selector << employee["empnum"] << employee["ename"]
<< employee["deptno"] << depart["deptno"]
<< depart["dname"] << depart["locno"]
<< locate["locno"] << locate["lname"];
// Define and declare join1 as a right outer join
// between the tables emp and dept.
RWDBJoinExpr join1 = rwdbRightOuter(employee, depart);
// Attach the join criterion to join1 using
// the on() method. This criterion joins the two tables
// emp and dept by their deptno columns.
join1.on(employee["deptno"] == depart["deptno"]);
// Define and declare join2 as a left outer join
// between the table loc and join1, forming a nested join.
// Note that the nested join must be specified as second
// parameter and not first.
RWDBJoinExpr join2 = rwdbLeftOuter(locate, join1);
// Attach the join criterion to the join2 using
// the on() method. This criterion joins the two tables
// dept and loc by their locno columns.
join2.on(depart["locno"] == locate["locno"]);
// Attach join2 explicitly to the selector FROM clause
// using the from() method.
selector.from(join2);
|
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.