All DB Access Modules are implemented through the same interface. However, there are differences in the parameters that must be passed to certain expressions. The following section outlines the differences in the DB Access Module for Microsoft SQL Server.
The DB Interface Module provides a predefined set of global functions which allow an application to build portable SQL expressions. Table 4 lists restrictions on the global functions associated with RWDBExpr.
| Function | Restrictions |
rwdbAvg(const RWDBExpr&) |
None. Corresponds to: AVG(expr) |
rwdbCast(const RWDBExpr&, |
None. Corresponds to:{fn CONVERT(expr, val)} |
rwdbCast(const RWDBExpr&, |
Same as the two-expression form, as Microsoft SQL Server does not support this form. |
rwdbCast(const RWBDExpr&, |
Same as the two-expression form, as Microsoft SQL Server 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: USER_NAME() |
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&, |
None. Corresponds to: expr AS str |
rwdbPosition(const RWDBExpr&, |
None. Corresponds to:{fn LOCATE(expr0, expr1)} |
rwdbSessionUser() |
None. Corresponds to: USER_NAME() |
rwdbSubString(const RWDBExpr&, |
None. Corresponds to:{fn SUBSTRING (expr0, expr1, fn LENGTH(expr0))} |
rwdbSubString(const RWDBExpr&, |
None. Corresponds to:{fn SUBSTRING (expr0, expr1, {fn LENGTH(expr0)} )} |
rwdbSum(const RWDBExpr&) |
None. Corresponds to: SUM(expr) |
rwdbSystemDateTime() |
None. Corresponds to: { fn NOW() } |
rwdbSystemUser() |
None. Corresponds to: SUSER_SNAME() |
rwdbTrimLeading(const RWDBExpr&, |
None. Corresponds to: LTRIM(expr1) |
rwdbTrimTrailing(const RWDBExpr&, |
None. Corresponds to: RTRIM(expr1) |
rwdbTrimBoth(const RWDBExpr&, |
None. Corresponds to: LTRIM(RTRIM(expr1)) |
rwdbUpper(const RWDBExpr&) |
None. Corresponds to: {fn UCASE(expr)} |
Microsoft SQL Server supports ANSI SQL 92 syntax, so outer joins for SQL Server should be written in the FROM clause. 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.
The following example shows an outer join program for SQL Server. You can assume that myDbase is a valid instance of RWDBDatabase.
An Outer Join for MS SQL Server in 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 the 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 join1 and table loc, forming a nested join.
RWDBJoinExpr join2 = rwdbLeftOuter(join1, locate);
// 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.