RWDBSelectorRWDBSelectorBase
#include <rw/db/select.h> RWDBSelector select = myDbase.selector();
RWDBSelector is an encapsulation of an SQL SELECT statement. Its methods provide an application with explicit control over the SELECT statement's select list, as well as its FROM, WHERE, ORDER BY, and GROUP BY clauses. The set operators +, *, and - (union, intersection, and difference) may be applied to RWDBSelectors in order to achieve the semantics of the SQL UNION, INTERSECTION, and DIFFERENCE operations. See the entry for RWDBCompoundSelector. An RWDBSelector may be used to instantiate an RWDBExpr, so subqueries are also supported. See the entry for RWDBExpr.
The insertion operator << is used to add items to an RWDBSelector select list; the where() method is used to specify a WHERE clause. The items which are inserted into an RWDBSelector are RWDBExprs, which may be any combination of constants, column references, predefined functions, or RWDBSelectors combined by arithmetic or functional operators. The WHERE clause is encapsulated by an RWDBCriterion, which is some number of RWDBExprs combined with logical operators.
The result of an SQL SELECT statement is an SQL table expression. DBTools.h++ represents this concept as a ResultTable. Hence, the following are equivalent:
(1) RWDBReader rdr = selector.execute().table().reader(); (2) RWDBReader rdr = selector.reader();
RWDBSelector also derives from RWDBSelectorBase so that RWDBSelector and RWDBCompoundSelector may be handled in a uniform manner.
RWDBSelector is designed around the Interface/Implementation paradigm. An RWDBSelector instance is an interface to a reference-counted implementation; copy constructors and assignment operators produce additional references to a shared implementation. An RWDBSelector implementation is a base class from which a family of database-specific selector implementations is derived.
Most RWDBSelector methods return a reference to self. This allows calls to be stacked, as in:
select.where(...).orderBy(...).groupBy(...), etc.
Assuming that dBase is a valid RWDBDatabase, we can encapsulate the SQL statement:
SELECT myTable.name, yourTable.city FROM myTable, yourTable WHERE myTable.key = yourTable.key
as follows:
RWDBTable myTable = dBase.table("myTable");
RWDBTable yourTable = dBase.table("yourTable");
RWDBSelector select = dBase.selector();
select << myTable["name"] << yourTable["city"];
select.where(myTable["key"] == yourTable["key"]);
Notice that it is not necessary to specify the FROM clause. DBTools.h++ deduces what tables to select from the column references in the select list; it generates a member of the FROM clause for each unique table reference. You can override this behavior by using the from() member function.
Assuming that the name and city columns mentioned above are strings, we would read the results of our query like this:
RWDBReader rdr = select.reader();
RWCString name;
RWCString city;
while(rdr()) {
rdr >> name >> city;
// process the name and city
}
To introduce multiple instances of a table into an encapsulated SELECT statement, declare additional RWDBTable instances. The fragment:
SELECT a.name, b.type from myTable a, myTable b
can be captured as in the following example:
RWDBTable aTable = myDbase.table("myTable");
RWDBTable bTable = myDbase.table("myTable");
RWDBSelector select = myDbase.selector();
select << aTable["name"] << bTable["type"];
Note that the instantiations of aTable and bTable do not require database access, so this technique does not incur excessive overhead.
This example introduces the binding of application variables within a WHERE clause of a SELECT statement. This allows an application to repeatedly execute the RWDBSelector without clearing and constructing the WHERE clause each time. The following example uses an AutoParts table in the database, with text column name and integer column id:
RWDBTable partsTable = myDbase.table("AllParts");
RWDBConnection connection = myDbase.connection();
RWDBSelector select = myDbase.selector();
int id = 1001;
select << partsTable["name"];
select.where( partsTable["id"] == RWDBBoundExpr(&id) );
RWDBReader reader = select.reader(connection);
// process reader...
id = 2001;
reader = select.reader(connection);
// process reader...
RWDBSelector();
The default constructor creates an RWDBSelector whose status is RWDBStatus::notInitialized. This constructor is provided as a convenience, for example, for declaring an array of RWDBSelectors. Usable RWDBSelectors are obtained from RWDBDatabase.
RWDBSelector(const RWDBSelector& select);
Copy constructor. The created object shares an implementation with select.
RWDBSelector& operator=(const RWDBSelector& select);
Assignment operator. Self shares an implementation with select. Returns a reference to self.
RWDBSelector& operator<<(RWDBValueManip& manip);
The insertion operator adds manip to self's select list. Returns a reference to self.
RWDBSelector& operator<<(const RWDBExpr& expr);
The insertion operator adds an item to self's select list. The supplied expr may be made up of constants, column references, predefined functions, or RWDBSelectors, combined with arithmetic or functional operators. See RWDBExpr. Returns a reference to self.
RWDBSelector& operator<<(const RWDBTable& table);
Use this method to specify SELECT * FROM table. Returns a reference to self.
RWDBColumn operator[](const RWCString& name) const;
Inherited from RWDBSelectorBase. Returns a deep copy of the first RWDBColumn in self's schema whose name matches the given name. The RWDBColumn returned is associated with self, that is, the table() method of the returned RWDBColumn returns this selector. If no matching column is found, returns an RWDBColumn whose status is RWDBStatus::ColumnNotFound. A deep copy is made so that applications can modify the returned RWDBColumn without modifying self's schema.
RWDBColumn operator[](size_t position) const;
Returns a deep copy of the RWDBColumn in self's schema whose position matches the given position. The RWDBColumn returned is associated with self. If no matching column is found, returns an RWDBColumn whose status is RWDBStatus::ColumnNotFound. A deep copy is made so that the application can modify the returned RWDBColumn without modifying self's schema.
virtual RWCString asString() const;
Inherited from RWDBSelectorBase. Returns the SQL equivalent of SELECT ... FROM ... WHERE ...
RWDBBulkReader bulkReader(const RWDBConnection& conn);
Returns an RWDBBulkReader able to read the result set associated with the execution of self. The RWDBBulkReader executes using the supplied connection.
RWDBStatus clear();
Clears self's clauses, selection lists, and internal controls.
RWDBColumn column(const RWCString& name) const;
Inherited from RWDBSelectorBase. Returns a deep copy of the first RWDBColumn in self's schema whose name matches the given name. The RWDBColumn returned is associated with self, that is, the table() method of the returned RWDBColumn returns this selector. If no matching column is found, returns an RWDBColumn whose status is RWDBStatus::columnNotFound. A deep copy is made so that applications can modify the returned RWDBColumn without modifying self's schema.
RWDBColumn
column(const RWCString& name,
RWCString::caseCompare casecompare);
Inherited from RWDBSelectorBase. Returns a deep copy of the first RWDBColumn in self's schema whose name matches the given name. The RWDBColumn returned is associated with self, that is, the table() method of the returned RWDBColumn returns this selector. If no matching column is found, returns an RWDBColumn whose status is RWDBStatus::columnNotFound. A deep copy is made so that applications can modify the returned RWDBColumn without modifying self's schema. The casecompare argument toggles case sensitivity when comparing column names. Valid arguments are RWCString::exact and RWCString::ignoreCase.
RWDBColumn column(size_t index) const;
Inherited from RWDBSelectorBase. Returns a deep copy of the RWDBColumn in self's schema at the given index. The RWDBColumn returned is associated with self, that is, the table() method of the returned RWDBColumn returns this selector. If index is out of range, returns an RWDBColumn with status RWDBStatus::invalidPosition. A deep copy is made so that applications can modify the returned RWDBColumn without modifying self's schema.
RWDBCursor
cursor(RWDBCursor::CursorType type = RWDBCursor::Sequential,
RWDBCursor::CursorAccess access = RWDBCursor::Read)
const;
Inherited from RWDBSelectorBase. Produces an RWDBCursor for execution of the SQL select statement encapsulated by self. The cursor is created on a default database connection, using the type and access specifications provided. The connection is held by the RWDBCursor until the RWDBCursor is destroyed.
RWDBCursor
cursor(const RWDBConnection& connection,
RWDBCursor::CursorType type = RWDBCursor::Sequential,
RWDBCursor::CursorAccess access = RWDBCursor::Read)
const;
Inherited from RWDBSelectorBase. Produces an RWDBCursor for execution of the SQL statement encapsulated by self. The cursor is created on the supplied database connection, using the type and access specifications provided. The connection is held by the RWDBCursor until the RWDBCursor is destroyed. This function can behave asynchronously if executed using an asynchronous connection.
RWDBCursor
cursor(const RWDBSchema& updateCols,
RWDBCursor::CursorType type = RWDBCursor::Sequential,
RWDBCursor::CursorAccess access = RWDBCursor::Read)
const;
Inherited from RWDBSelectorBase. Produces an RWDBCursor for execution of the SQL select statement encapsulated by self. The argument updateCols is used in building a clause:
FOR UPDATE OF column-name, column-name,...
Some SQL dialects require this form. The cursor is created on a default database connection, using the type and access specifications provided. The connection is held by the RWDBCursor until the RWDBCursor is destroyed.
RWDBCursor
cursor(const RWDBSchema& updateCols,
const RWDBConnection& connection,
RWDBCursor::CursorType type = RWDBCursor::Sequential,
RWDBCursor::CursorAccess access = RWDBCursor::Read)
const;
Inherited from RWDBSelectorBase. Produces an RWDBCursor for execution of the SQL statement encapsulated by self. The cursor is created on the supplied database connection, using the type and access specifications provided. The connection is held by the RWDBCursor until the RWDBCursor is destroyed. The argument updateCols is used in building a clause:
FOR UPDATE OF column-name, column-name,...
Some SQL dialects require this form. This function can behave asynchronously if executed using an asynchronous connection.
RWDBDatabase database();
Inherited from RWDBSelectorBase. Returns the RWDBDatabase that produced this selector. If there is no such object, for example, if this selector was created using the default constructor, returns an RWDBDatabase with a status of RWDBStatus::notInitialized.
RWDBCompoundSelector& difference(const RWDBSelectorBase& selector) const;
Inherited from RWDBSelectorBase. Returns an RWDBCompoundSelector that represents the SQL selector:
select-stmt DIFFERENCE selector
where select-stmt is the SELECT statement represented by self.
RWDBSelector& distinct(RWBoolean Distinct = TRUE);
By default, adds the DISTINCT SQL keyword to the select statement encapsulated by self. If Distinct is specified as FALSE, removes the DISTINCT keyword if it is present. Returns a reference to self.
RWDBResult execute();
Inherited from RWDBSelectorBase. Uses a default database connection to submit the SQL SELECT statement encapsulated by self for execution. There will always be one or more RWDBTables in the returned RWDBResult.
RWDBResult execute(const RWDBConnection& connection);
Inherited from RWDBSelectorBase. Uses the supplied connection to submit the SQL SELECT statement encapsulated by self for execution. There will always be one or more RWDBTables in the returned RWDBResult. This function can behave asynchronously if executed using an asynchronous connection.
virtual RWBoolean fetchSchema();
Executes the SQL statement associated with self, then populates self's internal schema with the result set, that is, the columns in the table that resulted from the execution of the SQL statement. The function fetchSchema() returns TRUE if successful.
To obtain schema information, DBTools.h++ uses a default database connection to query the database. If no schema can be obtained, fetchSchema() returns FALSE.
virtual RWBoolean fetchSchema(const RWDBConnection& connection);
Executes the SQL statement associated with self, then populates self's internal schema with the result set, that is, the columns in the table that resulted from the execution of the SQL statement. The function fetchSchema() returns TRUE if successful.
To obtain schema information, DBTools.h++ uses the supplied connection to query the database. If no schema can be obtained, fetchSchema() returns FALSE.
RWDBSelector& from(const RWDBJoinExpr& jexpr);
Adds jexpr to self's explicit FROM clause. This method is used for adding outer join constructs to the FROM clause. Each call to from() adds an entry to the explicit FROM clause. The tables that need not be part of the outer join can be added through the other from() methods. Use the function fromClear() to discard the explicit FROM clause and revert to the default behavior. However, the default behavior does not add any outer join constructs to the tables in the FROM clause. See Section 4.6.3 in the User's Guide and the RWDBJoinExpr entry in the Class Reference for information on generating appropriate outer join constructs. If outer join constructs have been added to the FROM clause, the join columns may have to be indicated through the on() or using() methods of RWDBSelector.
RWDBSelector& from(const RWCString& tableName);
Adds tableName to self's explicit FROM clause. Normally, RWDBSelector has no explicit FROM clause. It can deduce which tables to query from the column references in its select list. By using the from() method, an application programmer overrides default FROM clause generation and causes an explicit FROM clause to be maintained. Each call to from() adds an entry to the explicit FROM clause. Note that explicit and implicit FROM clause generation cannot be mixed; if from()is used, it must be used to specify all tables required by the select. Use fromClear() to discard the explicit FROM clause and revert to the default behavior. Returns a reference to self.
RWDBSelector& from(const RWDBTable& table);
Adds table's name to self's explicit FROM clause. Normally, RWDBSelector has no explicit FROM clause. It can deduce which tables to query from the column references in its select list. By using the from() method, an application programmer overrides default FROM clause generation and causes an explicit FROM clause to be maintained. Each call to from() adds an entry to the explicit FROM clause. Note that explicit and implicit FROM clause generation cannot be mixed; if from()is used, it must be used to specify all tables required by the select. Use fromClear() to discard the explicit FROM clause and revert to the default behavior. Returns a reference to self.
RWDBSelector& fromClear();
Clears self's explicit FROM clause and enables implicit FROM clause generation. See from() for an explanation of implicit and explicit FROM clauses. Returns a reference to self.
RWDBSelector& groupBy(const RWDBColumn& column);
Specifies column as a GROUP BY column for self. A select statement may have multiple GROUP BY columns; successive calls to groupBy() add GROUP BY columns to self. The order of groupBy() calls is retained. Returns a reference to self.
RWDBSelector& groupBy(int columnNumber);
Specifies columnNumber as a GROUP BY column for self. A select statement may have multiple GROUP BY columns; successive calls to groupBy() add GROUP BY columns to self. The order of groupBy() calls is retained. Returns a reference to self.
RWDBSelector& groupByClear();
Clears self's GROUP BY clause. Returns a reference to self.
RWDBSelector& having(const RWDBCriterion& criterion);
Specifies criterion as self's HAVING clause. If self already has a HAVING clause, this method replaces it. This method is most often used in conjunction with groupBy(). Returns a reference to self.
RWDBCompoundSelector& intersection(const RWDBSelectorBase& selector) const;
Inherited from RWDBSelectorBase. Returns an RWDBCompoundSelector that represents the SQL selector:
select-stmt INTERSECTION selector
where select-stmt is the SELECT statement represented by self.
RWDBSelector& into(const RWCString& tableName);
Specifies the INTO clause of a select statement. If self already has an INTO clause, this method replaces it. A blank tableName removes the clause from self. If self's database does not support SELECT INTO, DBTools.h++ does not attempt to emulate this functionality. In such cases this method sets self's status to RWDBStatus::notSupported. Returns a reference to self.
RWBoolean isValid();
Inherited from RWDBSelectorBase. Returns TRUE if self's status is RWDBStatus::ok, otherwise returns FALSE. Does not return FALSE if the previous executed statement failed. You must check the status of the RWDBResult returned from execute() instead of the status of the RWDBSelector object.
RWDBSelector& on(const RWDBColumn& column);
This adds the column to self's SQL USING clause. The SQL USING clause specifies the join columns for outer join constructs. Returns a reference to self.
RWDBSelector& on(const RWDBCriterion& criterion);
This specifies the join condition for outer join constructs as self's SQL ON clause. If self already has an ON clause, this method replaces it. Returns a reference to self.
RWDBSelector& orderBy(const RWDBColumn& column);
Specifies column as an ORDER BY column for self. A select statement may have multiple ORDER BY columns; successive calls to orderBy() add ORDER BY columns to self. The order of orderBy() calls is retained. Returns a reference to self.
RWDBSelector& orderBy(int columnNumber);
Specifies columnNumber as an ORDER BY column for self. A select statement may have multiple ORDER BY columns; successive calls to orderBy() add ORDER BY columns to self. The order of orderBy() calls is retained. Returns a reference to self.
RWDBSelector& orderByClear();
Clears self's ORDER BY clause. Returns a reference to self.
RWDBSelector& orderByDescending(const RWDBColumn& column);
Specifies column as an ORDER BY DESCENDING column for self. A select statement may have multiple ORDER BY columns; successive calls to orderBy() add ORDER BY columns to self. The order of orderBy() calls is retained. Returns a reference to self.
RWDBSelector& orderByDescending(int columnNumber);
Specifies columnNumber as an ORDER BY DESCENDING column for self. A select statement may have multiple ORDER BY columns; successive calls to orderBy() add ORDER BY columns to self. The order of orderBy() calls is retained. Returns a reference to self.
RWDBReader reader(size_t cache=0) const;
Inherited from RWDBSelectorBase. Produces an RWDBReader that can be used to read data from self one row at a time. Uses a default database connection which is held by the RWDBReader until the RWDBReader is destroyed.
NOTE:When cache is 0, an acceptable default is selected by your access library. Please check your access library documentation for limitations on the cache size.
RWDBReader
reader(const RWDBConnection& connection,
size_t cache=0) const;
Inherited from RWDBSelectorBase. Produces an RWDBReader that can be used to read data from self one row at a time. Uses the supplied connection, which is held by the RWDBReader until the RWDBReader is destroyed. This function can behave asynchronously if executed using an asynchronous connection.
NOTE:When cache is 0, an acceptable default is selected by your access library. Please check your access library documentation for limitations on the cache size.
RWDBSchema schema() const;
Inherited from RWDBSelectorBase. Returns a deep copy of self's RWDBSchema. The copy is made so that an application can modify the returned RWDBSchema without modifying self's schema. The schema is empty until self's fetchSchema() method is called.
RWDBSelector& select(const RWDBExpr& expr);
This is a synonym for *this << expr. Returns a reference to self.
RWDBSelector& select(const RWDBTable& table);
This is a synonym for *this << table. Returns a reference to self.
RWDBSelector& selectClear();
Clears self's select list. Returns a reference to self.
RWDBStatus status();
Returns the status of self.
RWDBCompoundSelector& union_ (const RWDBSelectorBase& selector) const;
Inherited from RWDBSelectorBase. Returns an RWDBCompoundSelector that represents the SQL selector:
select-stmt UNION selector
where select-stmt is the SELECT statement represented by self. The trailing underscore is to avoid conflict with the C++ union keyword.
RWDBCompoundSelector& unionAll(const RWDBSelectorBase& selector) const;
Inherited from RWDBSelectorBase. Returns an RWDBCompoundSelector which represents the SQL selector:
SELECT select-stmt UNION ALL
where select-stmt is the SELECT statement represented by self.
RWDBCriterion where() const;
Returns a copy of self's RWDBCriterion, an encapsulated SQL WHERE clause. Returns an empty RWDBCriterion if self has no WHERE clause.
RWDBSelector& where(const RWDBCriterion& criterion);
Specifies criterion as self's SQL WHERE clause. If self already has a WHERE clause, this method replaces it. Returns a reference to self.
RWDBCompoundSelector
operator+(const RWDBSelectorBase& left,
const RWDBSelectorBase& right);
Union. Equivalent to left.union_(right). Inherited from RWDBSelectorBase.
RWDBCompoundSelector
operator-(const RWDBSelectorBase& left,
const RWDBSelectorBase& right);
Difference. Equivalent to left.difference(right). Inherited from RWDBSelectorBase.
RWDBCompoundSelector
operator*(const RWDBSelectorBase& left,
const RWDBSelectorBase& right);
Intersection. Equivalent to left.intersection(right). Inherited from RWDBSelectorBase.
©Copyright 1999, Rogue Wave Software, Inc.
Contact Rogue Wave about documentation or support issues.