 SourcePro® 2023.1 |
SourcePro® API Reference Guide |
Encapsulates the SQL SELECT statement.
More...
#include <rw/db/select.h>
Public Types | |
| enum | FromGeneration { ExplicitOrImplicit, ExplicitAndImplicit } |
Related Functions | |
(Note that these are not member functions.) | |
| RWDBCompoundSelector | operator* (const RWDBSelectorBase &left, const RWDBSelectorBase &right) |
| RWDBCompoundSelector | operator+ (const RWDBSelectorBase &left, const RWDBSelectorBase &right) |
| RWDBCompoundSelector | operator- (const RWDBSelectorBase &left, const RWDBSelectorBase &right) |
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 RWDBSelector objects 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 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 RWDBExpr objects, which may be any combination of constants, column references, predefined functions, or RWDBSelector objects combined by arithmetic or functional operators. The WHERE clause is encapsulated by an RWDBCriterion, which is some number of RWDBExpr objects combined with logical operators.
The result of an SQL SELECT statement execution is an SQL table expression. The DB Interface Module represents this concept as a ResultTable. Hence, the following are equivalent:
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:
Example1
Assuming that dBase is a valid RWDBDatabase, we can encapsulate the SQL statement:
as follows:
Notice that it is not necessary to specify the FROM clause. The DB Interface Module 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(), fromGeneration(), and fromExtern() member functions.
Assuming that the name and city columns mentioned above are strings, we would read the results of our query like this:
Example 2
To introduce multiple instances of a table into an encapsulated SELECT statement, declare additional RWDBTable instances. The fragment:
can be captured as in the following example:
Note that the instantiations of aTable and bTable do not require database access, so this technique does not incur excessive overhead.
Example 3
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:
A class-scoped enum presents different behaviors that can be used to compose the FROM clause. It is used in the fromGeneration() methods. Please refer to the Section "Composing FROM clause" in the Chapter "The Data Manipulation Classes" in the DB Interface Module User's Guide, for detailed information.
| Enumerator | |
|---|---|
| ExplicitOrImplicit |
If there are any explicit from() entries, uses only those to generate the |
| ExplicitAndImplicit |
Combines the implicit and explicit entries to generate the |
| RWDBSelector::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 RWDBSelector objects. Usable RWDBSelector instances are obtained from RWDBDatabase.
| RWDBSelector::RWDBSelector | ( | const RWDBSelector & | select | ) |
Copy constructor. The created object shares an implementation with select.
|
inline |
Returns the SQL equivalent of:
This method returns an SQL statement that would be produced by executing self with an implicit RWDBConnection object. An implicit RWDBConnection object inherits the time zone setting from the producer RWDBDatabase instance. Hence, the time zone setting of the producer RWDBDatabase object will be used for creating the string representations of any RWDateTime instances in self.
The behavior of this method depends upon the RWDBDatabase::verboseAsString() setting in the producer RWDBDatabase instance.
false, the SQL returned is the same as that passed to the database for execution. This is the default.true, any placeholders in the returned SQL are replaced with their bound values.true, the SQL returned by this method may not be a valid SQL statement. However, this method's return value is not necessarily the same SQL that is sent to the database for execution. For example, if an RWDBBlob object is bound, calling this method with RWDBDatabase::verboseAsString() set to true will result in a string with blob data returned as hex numbers, such as 0x0A32F5.
|
inline |
Returns the SQL equivalent of:
This method returns an SQL statement that would be produced by executing self with conn. The time zone setting of conn will be used for creating string representations of any RWDateTime instances in self.
The behavior of this method depends upon the RWDBDatabase::verboseAsString() setting in the producer RWDBDatabase instance.
false, the SQL returned is the same as that passed to the database for execution. This is the default.true, any placeholders in the returned SQL are replaced with their bound values.true, the SQL returned by this method may not be a valid SQL statement. However, this method's return value is not necessarily the same SQL that is sent to the database for execution. For example, if an RWDBBlob object is bound, calling this method with RWDBDatabase::verboseAsString() set to true will result in a string with blob data returned as hex numbers, such as 0x0A32F5.
|
inline |
Returns the SQL equivalent of:
This method returns an SQL statement that would be produced by executing self with an implicit RWDBConnection object. An implicit RWDBConnection object inherits the time zone setting from the producer RWDBDatabase instance. Hence, the time zone setting of the producer RWDBDatabase object will be used for creating the string representations of any RWDateTime instances in self.
The behavior of this method depends on the value of verbose, and is independent of the RWDBDatabase::verboseAsString() setting.
false, the SQL returned is the same as that passed to the database for execution.true, any placeholders in the returned SQL are replaced with their bound values although the SQL passed to the database for execution will not be affected.true may not be a valid SQL statement. However, this is not necessarily the same SQL sent to the database for execution. For example, if an RWDBBlob object is bound, calling this method with verbose as true will result in a string with blob data returned as hex numbers, such as 0x0A32F5.
|
inline |
Returns the SQL equivalent of:
This method returns an SQL statement that would be produced by executing self with conn. The time zone setting of conn will be used for creating string representations of any RWDateTime instances in self.
The behavior of this method depends on the value of verbose, and is independent of the RWDBDatabase::verboseAsString() setting.
false, the SQL returned is the same as that passed to the database for execution.true, any placeholders in the returned SQL are replaced with their bound values although the SQL passed to the database for execution will not be affected.true may not be a valid SQL statement. However, this is not necessarily the same SQL sent to the database for execution. For example, if an RWDBBlob object is bound, calling this method with verbose as true will result in a string with blob data returned as hex numbers, such as 0x0A32F5.
|
inline |
Returns an RWDBBulkReader able to read the result set associated with the rows of self. The RWDBBulkReader executes using the supplied connection.
|
inline |
Clears self's clauses, selection lists, and internal controls.
|
inline |
Returns a deep copy of the RWDBColumn in self's schema at the given index. 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.
|
inline |
Returns a deep copy of the first RWDBColumn in self's schema whose name matches the given name. 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.
|
inline |
Returns a deep copy of the first RWDBColumn in self's schema whose name matches the given name. 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.
|
inline |
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.
|
inline |
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.
|
inline |
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.
|
inline |
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.
|
inline |
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.
|
virtual |
Returns an RWDBCompoundSelector that represents the SQL selector:
select-stmt DIFFERENCE selector
where select-stmt is the SELECT statement represented by self.
Reimplemented from RWDBSelectorBase.
| RWDBSelector& RWDBSelector::distinct | ( | bool | logic = true | ) |
By default, adds the DISTINCT SQL keyword to the select statement encapsulated by self. If logic is specified as false, removes the DISTINCT keyword if it is present. Returns a reference to self.
|
inline |
Uses a default database connection to submit the SQL SELECT statement encapsulated by self for execution. There will always be one or more RWDBTable objects in the returned RWDBResult.
|
inline |
Uses the supplied connection to submit the SQL SELECT statement encapsulated by self for execution. There will always be one or more RWDBTable objects in the returned RWDBResult. This function can behave asynchronously if executed using an asynchronous connection.
|
inline |
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. This function returns true if successful.
To obtain schema information, the DB Interface Module uses a default database connection to query the database. If no schema can be obtained, this function returns false.
|
inline |
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. This function returns true if successful.
To obtain schema information, the DB Interface Module uses the supplied connection to query the database. If no schema can be obtained, this function returns false.
| RWDBSelector& RWDBSelector::from | ( | const RWCString & | tableName | ) |
Adds tableName to self's explicit FROM list. 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, you can manipulate default FROM clause generation and cause an explicit FROM list to be maintained. Each call to from() adds an entry to the explicit FROM list. The explicit FROM list can be used by itself or combined with the default implicit list, depending on the fromGeneration() setting. This is explained in detail in the Section "Composing FROM clause" in the Chapter "The Data Manipulation Classes" in the DB Interface Module User's Guide. Returns a reference to self.
| RWDBSelector& RWDBSelector::from | ( | const RWDBTable & | table | ) |
Adds name of table to self's explicit FROM list. 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, you can manipulate default FROM clause generation and cause an explicit FROM list to be maintained. Each call to from() adds an entry to the explicit FROM list. The explicit FROM list can be used by itself or combined with the default implicit list, depending on the fromGeneration() setting. This is explained in detail in the Section "Composing FROM clause" in the Chapter "The Data Manipulation Classes" in the DB Interface Module User's Guide. Returns a reference to self.
| RWDBSelector& RWDBSelector::from | ( | const RWDBJoinExpr & | jExpr | ) |
Adds jExpr to self's explicit FROM list. This method is used for adding outer join constructs to the FROM clause. Each call to from() adds an entry to the explicit FROM list. See the Section on "Outer Joins" in the Chapter "The Data Manipulation Classes" in the DB Interface Module User's Guide and the RWDBJoinExpr entry in this Reference Guide for information on generating appropriate outer join constructs. The FROM clause generation is explained in detail in the Section "Composing FROM clause" in the Chapter "The
Data Manipulation Classes" in the DB Interface Module User's Guide. Returns a reference to self.
| RWDBSelector& RWDBSelector::fromClear | ( | ) |
Clears self's explicit FROM list. See from() for an explanation of implicit and explicit FROM clauses. This is also explained in detail in the Section "Composing FROM clause" in the Chapter "The
Data Manipulation Classes" in the DB Interface Module User's Guide. Returns a reference to self.
| RWDBSelector& RWDBSelector::fromExtern | ( | const RWDBTable & | table | ) |
Declares table as an externally defined table and hence to not be included in the self's FROM clause. Each call to fromExtern() adds an entry to self's extern table list. This method is especially useful in forming correlated SQLs in which the correlated subquery references a table defined in the outer SQL. The FROM clause generation and correlated subqueries are explained in detail in the Section "Composing FROM clause" and the Section "Subqueries" in the Chapter "The Data Manipulation Classes" in the DB Interface Module User's Guide. Returns a reference to self.
| RWDBSelector& RWDBSelector::fromExternClear | ( | ) |
Clears self's extern table list. See fromExtern() for an explanation of the extern table list. The FROM clause generation is explained in detail in the Section "Composing FROM clause" in the Chapter "The
Data Manipulation Classes" in the DB Interface Module User's Guide. Returns a reference to self.
| RWDBSelector& RWDBSelector::fromGeneration | ( | FromGeneration | flag | ) |
Sets self's FROM clause generation behavior. Class-scoped enum FromGeneration defines the behavior in generating self's FROM clause from the implicit and explicit FROM entries. The default behavior is ExplicitOrImplicit. The FROM clause generation is explained in detail in the Section "Composing FROM clause" in the Chapter "The Data Manipulation Classes" in the DB Interface Module User's Guide. Returns a reference to self.
| FromGeneration RWDBSelector::fromGeneration | ( | ) | const |
Returns self's FROM clause generation behavior. Class-scoped enum FromGeneration defines the behaviors in which to generate self's FROM clause from the implicit and explicit FROM entries. The default behavior is ExplicitOrImplicit. The FROM clause generation is explained in detail in the Section "Composing FROM
clause" in the Chapter "The Data Manipulation Classes" in the DB Interface Module User's Guide.
| RWDBSelector& RWDBSelector::groupBy | ( | const RWDBColumn & | column | ) |
| RWDBSelector& RWDBSelector::groupBy | ( | const RWDBExpr & | expr | ) |
| RWDBSelector& RWDBSelector::groupBy | ( | int | columnNumber | ) |
| RWDBSelector& RWDBSelector::groupByClear | ( | ) |
Clears self's GROUP BY clause. Returns a reference to self.
| RWDBSelector& 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.
|
virtual |
Returns an RWDBCompoundSelector that represents the SQL selector:
select-stmt INTERSECTION selector
where select-stmt is the SELECT statement represented by self.
Reimplemented from RWDBSelectorBase.
| RWDBSelector& 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, the DB Interface Module does not attempt to emulate this functionality. In such cases this method sets self's status to RWDBStatus::notSupported. Returns a reference to self.
|
inline |
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& RWDBSelector::on | ( | const RWDBCriterion & | criterion | ) |
Attaches the criterion as an SQL ON clause to the first RWDBJoinExpr attached to self using the from() method. If the first RWDBJoinExpr already has an ON clause, this method replaces it. Returns a reference to self.
| RWDBSelector& RWDBSelector::on | ( | const RWDBColumn & | column | ) |
USING clause of a specific RWDBJoinExpr.Adds the column to the SQL USING clause of the first RWDBJoinExpr attached to self using the from() method. Returns a reference to self.
| RWDBSelector& RWDBSelector::operator<< | ( | RWDBValueManip | manip | ) |
The insertion operator adds manip to self's select list. Returns a reference to self.
| RWDBSelector& 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 RWDBSelector instances, combined with arithmetic or functional operators. See RWDBExpr. Returns a reference to self.
| RWDBSelector& RWDBSelector::operator<< | ( | const RWDBTable & | table | ) |
Use this method to specify SELECT * FROM table. Returns a reference to self.
| RWDBSelector& RWDBSelector::operator= | ( | const RWDBSelector & | select | ) |
Assignment operator. Self shares an implementation with select. Returns a reference to self.
|
inline |
Returns a deep copy of the first RWDBColumn in self's schema whose name matches the given name. 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.
|
inline |
Returns a deep copy of the RWDBColumn in self's schema whose position matches the given position. 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.
| RWDBSelector& 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() and orderByDescending() add ORDER BY columns to self. The order of orderBy() and orderByDescending() calls is retained. Returns a reference to self.
| RWDBSelector& RWDBSelector::orderBy | ( | const RWDBExpr & | expr | ) |
Specifies expr as an ORDER BY <expression> for self. A select statement may have multiple ORDER BY expressions; successive calls to orderBy() and orderByDescending() add ORDER BY expressions to self. The order of orderBy() and orderByDescending() calls is retained. Returns a reference to self.
| RWDBSelector& 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() and orderByDescending() add ORDER BY columns to self. The order of orderBy() and orderByDescending() calls is retained. Returns a reference to self.
| RWDBSelector& RWDBSelector::orderByClear | ( | ) |
Clears self's ORDER BY clause. Returns a reference to self.
| RWDBSelector& 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() and orderByDescending() add ORDER BY columns to self. The order of orderBy() and orderByDescending() calls is retained. Returns a reference to self.
| RWDBSelector& 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() and orderByDescending() add ORDER BY columns to self. The order of orderBy() and orderByDescending() calls is retained. Returns a reference to self.
|
inline |
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.
0, an acceptable default is selected by your Access Module. Please check your Access Module documentation for limitations on the cache size.
|
inline |
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.
0, an acceptable default is selected by your Access Module. Please check your Access Module documentation for limitations on the cache size.
|
inline |
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& RWDBSelector::select | ( | const RWDBExpr & | expr | ) |
This is a synonym for *this << expr. Returns a reference to self.
| RWDBSelector& RWDBSelector::select | ( | const RWDBTable & | table | ) |
This is a synonym for *this << table. Returns a reference to self.
| RWDBSelector& RWDBSelector::selectClear | ( | ) |
Clears self's select list. Returns a reference to self.
|
inline |
Installs handler as self's error handler. The supplied handler is inherited by all objects produced by self. By default, an RWDBStatus::ErrorHandler is inherited from the object that produced self; this method overrides the default.
|
inline |
Returns the status of self.
|
virtual |
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.
Reimplemented from RWDBSelectorBase.
|
virtual |
Returns an RWDBCompoundSelector which represents the SQL selector:
select-stmt UNION ALL selector
where select-stmt is the SELECT statement represented by self.
Reimplemented from RWDBSelectorBase.
| RWDBCriterion RWDBSelector::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& 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.
|
related |
Intersection. Equivalent to
|
related |
Union. Equivalent to
|
related |
Difference. Equivalent to
|
Copyright © 2023 Rogue Wave Software, Inc., a Perforce company. All Rights Reserved. |