SQL START QUERY Statement

SQL START QUERY
Statement

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

The SQL START QUERY statement starts (executes) a query that has just been prepared or re-starts (re-executes) any query that has been previously started.

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

QueryTimeout (input). This argument must specify a numeric integer value. The value indicates the number of seconds to wait for an SQL statement to execute before returning to the application. This argument is optional. The default value is configurable. (See QueryTimeout in the topic InstantSQL Configuration for information on configuring the default.) If the value is 0, then there is no timeout and the execution attempt will wait indefinitely.

If the query identified by the query handle has parameters, all the parameters must have been bound to COBOL data items with one or more SQL BIND PARAMETER statements prior to the SQL START QUERY statement. Also, the COBOL data items for input parameters must be set to the desired parameter values prior to each SQL START QUERY statement.

The SQL START QUERY statement is not necessary following any of the InstantSQL browse statements (SQL QUERY COLUMNS, SQL QUERY DATASOURCES, SQL QUERY INDEXES, SQL QUERY PROCEDURES, SQL QUERY PROCEDURE-COLUMNS, SQL QUERY TABLES, and SQL QUERY TYPES) since these statements start the indicated browse query. If SQL START QUERY statement specifies a query handle value returned by a browse statement, the specified browse query is re-started from the beginning.

An SQL START QUERY statement executed when the query status is set to sql-StatPrepared by an SQL PREPARE QUERY statement starts a query for the first time. (The current status of a query can be obtained using the SQL DESCRIBE QUERY statement.) An SQL START QUERY statement executed when the query status is sql-StatExecuting or sql-StatFetching re-starts the query, after closing the cursor and discarding any pending results. If the query does not include input parameters, then the same query is re-started. If the query includes input parameters, then the new parameter values are transferred from the bound COBOL data items for those parameters before re-starting the query.

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

Output parameter values, if any, are not transferred to the bound data items in the COBOL program until an SQL FETCH ROW statement is executed. It is data source - dependent whether the output values of parameters are valid before all rows of the result set, if any, have been fetched. The configuration options GetOutputParamsEachRow and GetOutputParamsOnStart may be used to modify the default behavior of InstantSQL when using a database product where output parameter values are valid earlier than the general case. If the configuration option GetOutputParamsOnStart is set to "True", output parameter values will be transferred to the bound COBOL data items after the query is started; that is, the values will be available to the COBOL program upon completion of the SQL START QUERY statement.

When an SQL START QUERY is executed within a transaction (see the SQL START TRANSACTION statement for information on transactions), the statement may be terminated if the data source detects a deadlock situation. In this case, an ODBC error with an SQL state of 40001, serialization failure, will be reported. When this occurs, the application should rollback the transaction, and, if desired, start the transaction again from the beginning.

When a query timeout value is specified or a query timeout value is configured, the timeout value applies to the entire query, not just the query execution done by the SQL START QUERY statement. For example, if a nonzero timeout value is specified, the timeout applies to an SQL FETCH ROW statement when it attempts to fetch a row that is locked by another application because the SQL PREPARE QUERY statement specified a cursor concurrency of lock. If the timeout expires before the other application releases the lock, the SQL FETCH ROW statement will terminate with an ODBC error. The SQL state will be S1T00, timeout expired. When a timeout error occurs, the InstantSQL statement may be re-executed if the application chooses to wait again or, in an interactive application, the error situation may be presented to the user for a decision.

Some data sources, for example, Access 97, do not support setting a query timeout value. For such data sources, an ODBC error will occur if a timeout value other than 0 is specified. The SQLSTATE for the error should be S1C00, driver not capable.

When an SQL START QUERY statement is executed outside a transaction after one or more transactions have been started with the SQL START TRANSACTION statement and ended with either an SQL COMMIT TRANSACTION or SQL ROLLBACK TRANSACTION statement, InstantSQL will re-enter auto-commit mode. In this case, InstantSQL will also set the transaction isolation level back to the default for the driver if it is different than the transaction isolation level set for the last transaction. If the InstantSQL changes the transaction isolation level for the connection, any open cursors for the connection will be automatically closed. This is required because some drivers do not allow the transaction isolation level to be changed when there are open cursors for the connection. This automatic closing of cursors occurs before the query statement is re-executed, so if the query opens a cursor, that cursor, and only that cursor for the connection, will be open upon completion of the SQL START QUERY statement.

SQL START QUERY Statement Example:

SQL START QUERY
sql-QueryHandle.