How You Can Use Query Objects
To allow users to change, delete, or add data to a database, use a query object rather than an SQLSelect object. Unlike the process for creating an SQLSelect object, which requires you to provide the SQL select statement and the target information, you cannot create the select statement for a query object (QueryObject.Query.Value is read-only).
Creating a query object requires you to specify data to be retrieved and manipulated. The query object builds the select statement for you.
Creating a query object requires setting attributes and invoking methods of the same system classes involved in creating an SQLSelect object. The system classes used in creating both SQLSelect and QueryObject objects are:
• DataStream
• QueryCol
• QueryParm
• SQLSelect
In addition, creating a query object involves the following two system classes:
• QueryObject
• QueryTable
For a description of these system classes, see the Language Reference Guide online help.
Moreover, setting the Targets attribute for a query object is somewhat more complicated than setting it for an SQLSelect object because there are more relevant attributes of the Targets array to be set.
The Targets attribute, which is an array of type QueryParm, contains the following attributes, all of which are required for creating a query object:
Expression
Contains the name of the target field or variable, such as “field1” or “tbl[i].col1”
Default: None
IsFileHandleField
Set to TRUE if the column's value is to be retrieved as a FileHandle and the target expression evaluates to a bitmap object or string object to be loaded using the FileHandle
Default: FALSE
IsDBHandleField
Set to TRUE if the column's value is to be retrieved as a DBHandle and the target expression evaluates to a bitmap object or string object to be loaded using this DBHandle
Default: FALSE
IsInsertTarget
Set to TRUE if the column is used as a target for an insert statement
Default: FALSE
IsSelectTarget
Set to TRUE if the column is used as a target for a select statement
Default: FALSE
IsUpdateTarget
Set to TRUE if the column is used as a target for an update statement
Default: FALSE
IsDeleteWhere
Set to TRUE if the column is used as a where clause element for a delete statement
Default: FALSE
IsUpdateWhere
Set to TRUE if the column is used as a where clause element for an update statement
Default: FALSE
Required Operations
The following operations are required to create a query object:
1. Provide the parameters for building the select clause:
a. Load the name of each database column into the ColumnName attribute of DataStream's Columns array attribute.
b. Supply the name of the target field or variable to the Expression attribute of the Targets attribute of the Columns array attribute (for every column selected from a database).
c. Set the value of IsSelectTarget to TRUE for every column selected from the database.
d. Set the value of QueryParm's IsUpdateTarget to TRUE for every database column that allows updates.
e. Set the value of QueryParm's IsInsertTarget to TRUE for every database column that allows insertions.
2. Provide the parameters for building the from clause:
a. Specify the name of the table or tables being accessed in the QueryTable's TableName attribute.
b. Supply tables to the FromTable array attribute of the Columns array attribute (required only if a column appears in more than one table).
3. Provide the parameters for building the where clause (if desired):
a. For update operations, set the value of QueryParm's IsUpdateWhere to TRUE for every column that is to be part of the where clause.
b. For delete operations, set the value of QueryParm's IsDeleteWhere to TRUE for every column (usually PrimaryKey columns) that is to be part of the where clause.
c. For select operations, provide values for QueryObject's RunTimeWhere attribute (if desired).
4. Provide the parameters for building the order by clause (if desired):
a. Set the value of the Columns array attribute's OrderBy attribute to an appropriate sequence number; that is, set the primary column to 1 and the secondary column to 2, and so on. If the sort order for a particular column should be descending, then the sequence number's sign should be changed to negative (for example, -1 or -2).
b. Supply the name or names of database columns to the AsName attribute of the Columns array attribute for all columns to be used in the order by clause.
5. Provide the parameters for building the group by clause (if desired):
a. Set the value of the Columns array attribute's GroupBy attribute to an appropriate sequence number, such as 1 for the primary column and 2 for the secondary column.
b. Supply the name or names of database columns to the AsName attribute of the Columns array attribute for all columns to be used in the group by clause.
6. Provide the parameters for building the having clause by entering an appropriate string into QueryObject's HavingClause attribute (if desired).
To execute the query and manipulate the data, you use most of the same DataStream methods used in running SQLSelect objects. However, because you do not specify the select query, you cannot use the SetCols method in building a query object.
The relevant DataStream methods are:
Open
Opens the query in the specified mode
FetchRow
(Valid only for cache mode.) Moves to a specified row in the cache and puts its data into internal buffers
NextRow
Retrieves the next row of data from the cache into internal buffers
PrevRow
(Valid only for cache mode.) Backs up to the previous row in the cached sequence, positioning that row to be loaded into fields or variables
Load
Moves one row of data from internal buffers into target variables, objects, or fields on the form
Close
Closes the query
In addition to using methods defined for the DataStream system class, using a query object for database inserts, updates, and deletions requires methods defined for the QueryObject system class. These methods are:
CommitToCache
Keeps data in the cache consistent with data in the database when insert, update, or delete operations are performed on cached data
DBDelete
Performs deletions on database tables with columns mapped to variables or fields specified for deletion
DBInsert
Performs insertions on database tables with columns mapped to variables or fields specified for insertion
DBUpdate
Performs updates on database tables with columns mapped to variables or fields specified for update
When running in cache mode, the cached data set is frozen to the state of the data at the time the query was executed. If you make changes to the data in the database tables, you must also update the cache to keep data synchronized. To update the cache after changing data in the database, use the CommitToCache method.
How You Can Update Data with a Query Object
To update underlying database tables whose columns have been specified for update and mapped to the Columns array attribute, use QueryObject's DBUpdate method.
This method takes the following parameters:
RowNumber
Specifies the row of the cache updated by the operation (used only if opened in QY_CACHE mode)
Default: the current row
IsRepeated
Set to TRUE if repeat queries are to be used
Default: FALSE
ZeroRowsIsError
If set to TRUE, a zero row result indicates an error; otherwise OK is returned
Default: FALSE
MaxRows
If nonzero, specifies the maximum number of rows that may be affected by the query. If more rows are affected than is specified by MaxRows, returns an error.
Default: zero (unlimited)
How You Can Delete Data with a Query Object
Deleting data with a query object is more complicated than updating it. After removing the deleted row from the database, the code must check whether any rows remain in the cached data set.
For example, assume the data set originally contained four rows and the user just deleted the third row. After the third row is deleted from the cache, the fourth row becomes the third row and CurRow becomes 3. You can display this row directly by invoking the Load method. Note that you do not want to invoke the NextRow method because the deletion from the cache automatically performs that operation.
Assume instead that the user deleted the fourth and last row in the cache. In this case, you do not want to invoke the Load method because there is no current row. Instead, you want to make the current row point to an actual row.