SQL END QUERY Statement

SQL END QUERY
Statement

< > Query Statements Example Flow Chart Table of Contents

The SQL END QUERY statement terminates an InstantSQL query either by dropping the query or by only closing the cursor for the query based on an option specified in the statement.

QueryHandle (input). This argument must specify a numeric integer value with at least six digits of precision. The value identifies the query to be ended. This value must have been returned from a successful SQL PREPARE QUERY statement or any of the browse statements.

Option (input). This argument must specify a numeric integer value. The value identifies whether the query is to be dropped (sql-Drop) or only the cursor for the query closed (sql-Close). This argument is optional; when the argument is omitted, the default is to drop the query as if sql-Drop had been specified.

The SQL END QUERY statement, with the sql-Drop option specified or implied, should be used at the point the query is no longer needed. When the query is dropped, InstantSQL and ODBC resources necessary for supporting the query are released. After an SQL END QUERY statement is executed with the sql-Drop option, the query handle value in the QueryHandle argument is no longer valid for use in statements that use an input query handle argument. If the application needs the query after it is dropped, the application must use the SQL PREPARE QUERY statement or one of the browse query statements to recreate the query and obtain a new valid query handle value.

The application should set the data item that contains the query handle value to zero to indicate that the query handle is no longer valid. InstantSQL does not modify the data item used in the SQL END QUERY statement, since it might not be the appropriate data item. For example, the statement might be contained in a common paragraph that is performed after moving the query handle value to a temporary data item specified in the statement.

The SQL END QUERY statement with the sql-Close option should be used at the point in the application when the cursor is no longer needed, that is, after the last SQL FETCH ROW statement is executed on the query handle. In this case the query handle value is still valid for use, but an SQL START QUERY statement is required to re-open the cursor. Closing the cursor when the application is done fetching result rows releases resources used to support the cursor and discards any pending results. Closing the cursor may also prevent SQLSTATE S1010, function sequence error, for subsequent InstantSQL statements with some ODBC drivers.

InstantSQL automatically closes the cursor when the SQL FETCH ROW statement sets sql-EndOfData to true, so it is not necessary to explicitly close the cursor when the application executes SQL FETCH ROW statements until sql-EndOfData is true. Also, a cursor only exists for prepared and executed SELECT SQL statements; it is not necessary to explicitly close the cursor for UPDATE, DELETE, or INSERT SQL statements.

A successful SQL END QUERY statement with the sql-Close option sets the status of the query identified by the value of the QueryHandle argument to sql-StatPrepared. (Other than when a query is dropped, the current status of a query can be obtained using the SQL DESCRIBE QUERY statement.) This indicates that the statement is again in the prepared state and may be re-executed with the SQL START QUERY statement.

SQL END QUERY Statement Examples:

       01 ws-Option              PIC 9(2) BINARY.
       01 ws-SelectQueryHandle   USAGE ISql-Handle.

       CLOSE-SELECT-QUERY.
           MOVE ws-SelectQueryHandle TO sql-QueryHandle.

           MOVE sql-Close TO ws-Option.
           PERFORM ISQL-END-QUERY.

       DROP-SELECT-QUERY.
           MOVE ws-SelectQueryHandle TO sql-QueryHandle.
         MOVE sql-Drop TO ws-Option.
           PERFORM ISQL-END-QUERY.
           MOVE ZERO TO ws-SelectQueryHandle.

       ISQL-END-QUERY.
           SQL END QUERY
               sql-QueryHandle,
               ws-Option.
           IF ws-Option = sql-Drop
             MOVE ZERO TO sql-QueryHandle
           END-IF.