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:

In addition, creating a query object involves the following two system classes:

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:

Contains the name of the target field or variable, such as “field1” or “tbl[i].col1”

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

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

Set to TRUE if the column is used as a where clause element for a delete statement

Set to TRUE if the column is used as a where clause element for an update statement

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.

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).

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).

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.

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:

(Valid only for cache mode.) Moves to a specified row in the cache and puts its data into internal buffers

(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

Moves one row of data from internal buffers into target variables, objects, or fields on the form

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:

Keeps data in the cache consistent with data in the database when insert, update, or delete operations are performed on cached data

Performs deletions on database tables with columns mapped to variables or fields specified for deletion

Performs insertions on database tables with columns mapped to variables or fields specified for insertion

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.

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:

Specifies the row of the cache updated by the operation (used only if opened in QY_CACHE mode)

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.

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.