InstantSQL Query Statements

Query Statements

< > Query Example Query Flow Chart Table of Contents

InstantSQL activities normally require that an SQL statement text string be provided to a connected data source with the SQL PREPARE QUERY statement (the exceptional activities are the browse statements described in another topic and the SQL DESCRIBE ERROR statement). The SQL statement may be designed to extract data from the data source (for example SELECT Number, Name FROM Employees) or to perform an operation that updates the data source but does not return data (for example UPDATE Employees SET Paid = True).

InstantSQL provides the following statements for querying a connected data source:

Statement	Purpose
SQL CONSTRUCT QUERY	Build an SQL statement text string.
SQL PREPARE QUERY	Submit SQL statement text string to data source for parsing and query plan creation.
SQL START QUERY	Execute or re-execute a query.
SQL FETCH ROW	Fetch the next row of a result set.
SQL GET DATA	Get data from a row of a result set.
SQL END QUERY	Close query cursor or drop query.

SQL statement text strings can be constructed with the SQL CONSTRUCT QUERY statement. This statement only builds the text string, which may then be used to create a query as described below. SQL statement text strings may also be coded as literal values or be obtained from user input to the application. The SQL CONSTRUCT QUERY statement is particularly useful when building an SQL literal, such as a character-string, date, time or timestamp literal, in the SQL statement text string.

SQL SELECT statement queries return data and are called result set queries. Performing an SQL PREPARE QUERY statement that specifies the SQL statement text string to be prepared for execution creates a result set query. This statement submits the SQL statement text string to the data source for parsing and query plan creation. The SQL statement can be generated in a Working-Storage data item before the SQL PREPARE QUERY statement or it can be coded as a literal. The SQL PREPARE QUERY statement returns an InstantSQL query handle value that identifies the created query. This query handle value must be provided to other InstantSQL statements to specify the query to be accessed by the statement.

Once a query has been created, it can be executed with the SQL START QUERY statement. Execution of a result set query makes the result set available. Queries can also be re-executed with the SQL START QUERY statement, without the need to re-submit the SQL statement text string through the SQL PREPARE QUERY statement, for queries where re-execution is appropriate.

The result set can be read like a COBOL file. The SQL FETCH ROW statement moves to the next row in the result set and is similar to a READ NEXT statement in COBOL. When the end of the query has been reached, the SQL FETCH ROW statement sets the InstantSQL return code to the value that makes sql-EndOfData true.

Unlike the READ NEXT statement in COBOL, the SQL FETCH ROW statement does not normally return the data in the columns of the result row to the COBOL application; it simply moves the pointer to the next row. To get the data from the columns of the result row, the SQL GET DATA statement can be used. This returns the specified columns into specified COBOL working-storage data items. The SQL GET DATA statement can be performed any number of times for each row of the result set. (See the topic InstantSQL Advanced Statements for information on a performance enhancement that reduces or eliminates the need for the SQL GET DATA statement.)

Once the query is no longer needed (typically after sql-EndOfData has been set to true), the query should be ended. The SQL END QUERY statement with the sql-Drop option specified or implied drops a query. Once the query is dropped its query handle value can no longer be used. A query may also be ended using the SQL END QUERY statement with the sql-Close option specified, which preserves the prepared query for later re-execution, but closes the cursor and discards any pending results.

For update queries (such as the SQL statements UPDATE, INSERT and DELETE), the query does not return a result set. Thus, for such queries, there is no need to use the SQL FETCH ROW or SQL GET DATA statements, but the query is still created using the SQL PREPARE QUERY, executed with the SQL START QUERY statement, and ended with the SQL END QUERY statement.

The status inquiry statement SQL DESCRIBE QUERY can be used to get information about any query. The description group returned by the SQL DESCRIBE QUERY statement contains sql-QryNoCols, which would have the value zero for an update query (since there is no result set and therefore no columns). A query description also includes sql-QryRowCount, which would have a value of the number of rows affected by an update query after the query is executed; the value is -1 for a result set query.

Click here for a Query Example