How You Can Run the SQLSelect Query

To execute the query after specifying it, invoke the Open method. If you open the SQLSelect object in QY_ARRAY mode, each column's value goes directly to the Expression attribute of the SQLSelect object's target array. If you mapped the Expression attribute for each column to a specific field on the form, each column's value is displayed on the form.

If, however, you open the SQLSelect object in any mode but array mode, the following additional methods are required to display the data:

Retrieves the data row by row from the database into internal buffers

Moves data from the internal buffers into the target fields on the form

Opening an SQLSelect object requires setting several parameters in addition to specifying the query mode. The Open method has the following parameters:

(Required.) Specifies one of the following modes:

(Optional.) Checks the number of columns specified in the Query text with the number of columns in the Columns array. When FALSE, populates columns in the Columns array if they have not been explicitly set.

(Optional.) Specifies whether to make a repeat query. Set to TRUE to make a repeat query.

(Optional.) Specifies a maximum number of array rows to populate (if greater than 0). Exceeding MaxRows causes an error (applies only to QY_ARRAY mode).

(Required.) Specifies the scope for evaluation of expressions in the Columns array Targets attribute.

Default: Value of the current Scope attribute

It is always necessary to set the scope for an SQLSelect object or QueryObject object. Scope can be set as a parameter to the Open method or by setting the DataStream's Scope attribute directly. The following code sets the SQLSelect object's Scope attribute to the scope of the current frame:

ss.Scope = CurFrame.Scope;
if ss.Scope != CurFrame.Scope then
/* error */
endif;

Note: Because setting the scope causes compilation of expressions in the SQLSelect object, errors during compilation could prevent the scope from setting as expected. It is recommended, therefore, that you check for appropriate setting of the scope while you are developing the application and make the necessary changes to your 4GL code.

Rather than setting the scope by setting the Scope attribute, you can set it using the Open method. The following code opens the SWLSelect object in cache mode, with CheckCols set to TRUE, and specifies the current frame as the scope:

status = ss.Open
        (
            querymode     = QY_CACHE,
            checkcols     = TRUE,
            cope          = CurFrame.Scope
        );

if (status != ER_OK) then
    /* There was an error. */
    rollback;
    /* Notify user of error. */
    ...
    resume;
endif;
commit;

if (ss.ErrorNo != 0 and ss.IsDBError = TRUE) then
    /* There was a database error */
    rollback;
    /* Notify user of error. */
    ...
    resume;
endif;

The value returned by the Open method is ER_OK if no errors occur in executing the query. If no errors have occurred, the example code commits the transaction opened by executing the query.

Like any SQL statement, SQLSelect objects (and QueryObject objects) participate in a transaction. Because closing the SQLSelect object does not close the transaction, your application must explicitly commit or roll back the transaction.

Because all the data retrieval is done at once when the Open method is invoked, the code in the previous example commits the transaction immediately.

After committing the transaction, the sample code checks for errors using the following DataStream attributes:

Provides the error number of the last operation, generated either by:

• The DBMS—for example, a column specified as a target does not exist in the table

• The application—for example, the 4GL code provides an illegal scope or contains expressions that do not compile

Set to TRUE if the error is a DBMS error.

If the SQLSelect has been opened successfully, this frame uses the SQLSelect's MaxRow attribute to determine the next operation. The MaxRow attribute contains the number of rows retrieved. If the value of the MaxRow attribute is not zero, you can invoke the NextRow and Load methods to retrieve data.

The following code performs these operations:

Because the ss SQLSelect object was opened in QY_CACHE mode, data was retrieved from the database table into the cache. The data is not directly displayed on the form. Therefore, the example code invokes the NextRow method to move the next row (in this case the first row) from the cache to internal buffers, and the Load method to move it from internal buffers to fields (or variables) in the frame.