SQL PREPARE QUERY Statement

< > Query Statements Connection Statements Example Flow Chart Table of Contents

The SQL PREPARE QUERY statement prepares an SQL text string for execution and returns an InstantSQL query handle value that identifies the resulting query. A cursor concurrency for the prepared query may also be specified.

QueryHandle (output). This argument must refer to a numeric integer data item with at least six digits of precision. The argument specifies the data item where the query handle value that identifies the prepared query is to be stored.

ConnectionHandle (input). This argument must specify a numeric integer value with at least six digits of precision. The value identifies the connection on which to prepare the query. This value must have been returned by a successful SQL CONNECT DATASOURCE statement.

SqlTextString (input). This argument must specify a nonnumeric value. The value must be the SQL statement text string that is to be prepared.

CursorConcurrency (input). This argument must specify a numeric integer value. The value identifies the cursor concurrency for the prepared statement as follows:

sql-Concur-Read-Only ‑ Cursor is read-only. No updates are allowed.

sql-Concur-Lock ‑ Cursor causes the lowest level of locking sufficient to ensure that the row can be updated.

sql-Concur-RowVer ‑ Cursor uses optimistic concurrency control, comparing row versions, such as SQLBase ROWID or Sybase TIMESTAMP.

sql-Concur-Values ‑ Cursor uses optimistic concurrency control, comparing values.

If the specified cursor concurrency is not supported by the data source, the driver substitutes a different cursor concurrency and returns SQLSTATE 01S02, option value changed. This argument is optional. The default value is configurable; see QueryConcurrency in the topic InstantSQL Configuration for information on configuring the default. Setting the cursor concurrency to anything other than sql-Concur-Read-Only is meaningful only when the prepared SQL statement is to be executed within a transaction. When in auto-commit mode, any cursor concurrency other than sql-Concur-Read-Only results in temporary locking that may cause the application to unnecessarily wait on locks held by other connections and unnecessarily delay queries by other connections.

The SQL CONSTRUCT QUERY statement can be used to build the value of SqlTextString. However, simple queries may also be specified with a nonnumeric literal or data item directly in the SQL PREPARE QUERY statement without having used an SQL CONSTRUCT QUERY statement.

After the SQL statement is prepared, it can be executed one or more times by use of the SQL START QUERY statement. When the prepared query is no longer needed, the application program should execute the SQL END QUERY statement with the sql-Drop option specified or implied.

The SQL statement is not validated by InstantSQL, but if the ODBC driver cannot parse or execute the statement an ODBC error will be returned either by the SQL PREPARE QUERY statement or the SQL START QUERY statement, depending on the nature of the error and the specific driver.

Valid query handle values returned by InstantSQL are in the range 1 through 9999. The application program may use the value zero in a query handle data item to indicate that the query has not been prepared or has been dropped.

Cursor concurrency only applies to result set queries, that is, queries using the SELECT statement. UPDATE, INSERT, and DELETE statements executed within a transaction automatically obtain locks if necessary to maintain the transaction isolation level. Locks used to maintain the transaction isolation level may lock more data than just the affected rows. For example, a data source that uses page level locking will lock the entire page for each page that contains an affected row. The locking scheme may also be affected by whether the search criteria are based on columns with the unique constraint or on columns that form an index of the table. Locking schemes may escalate a lock to the entire index, table, or database if the lock manager deems it more efficient or necessary to maintain the specified isolation level. Please see the documentation for the data source(s) you plan to use with your application for details on the locking scheme used by that data source.

Not all drivers honor the sql-Concur-Lock cursor concurrency option. For example, the DB2 driver requires that the SQL statement specify the FOR UPDATE clause in a SELECT statement to obtain a lock on the selected rows. Since the FOR UPDATE clause is extended ODBC SQL syntax, the application may not be interoperable among DBMSs if this clause is used. For example, SQL Server 7.0 does not support this clause in the SELECT statement. An application can use the data item sql-ConDBMSName returned by the SQL DESCRIBE CONNECTION statement to obtain the DBMS product name and, based on this information, build the appropriate SQL statement. In this way, the application can be interoperable among a known set of DBMS products.

A successful SQL PREPARE QUERY statement sets the type of the query identified by the value of the QueryHandle argument to sql-QryPrepared. (The type of a query can be obtained using the SQL DESCRIBE QUERY statement.)

A successful SQL PREPARE QUERY statement sets the status of the query identified by the value of the QueryHandle argument to sql-StatPrepared. (The current status of a query can be obtained using the SQL DESCRIBE QUERY statement.)

SQL PREPARE QUERY Statement Example:

       01 ws-SelectQueryHandle        USAGE ISqlHandle.
       01 ws-UpdateQueryHandle        USAGE ISqlHandle.

           MOVE "select LastName from Employee where EmpID = ?"
               TO sql-QrySQL.
           PERFORM ISQL-PREPARE-QUERY.

           MOVE sql-QueryHandle TO ws-SelectQueryHandle.

           MOVE "update Employee set FirstName = ? where EmpId = ?"
               TO sql-QrySQL.
           PERFORM ISQL-PREPARE-QUERY.

           MOVE sql-QueryHandle TO ws-UpdateQueryHandle.

        ISQL-PREPARE-QUERY.
           IF sql-ConnectionHandle = ZERO
             PERFORM ISQL-CONNECT-DATASOURCE
           END-IF.
           SQL PREPARE QUERY
               sql-QueryHandle,
               sql-ConnectionHandle,
               sql-QrySQL.