|
Advanced Statements |
|
< > Query Example Query Flow Chart Transaction Example Transaction Flow Chart Table of Contents
InstantSQL provides the following advanced statements for performance enhancements and transaction support:
|
Statement |
Purpose |
|
Bind result columns to COBOL
data items. |
|
|
Bind query parameters to COBOL
data items. |
|
|
Start a transaction. |
|
|
End a transaction and commit
the changes. |
|
|
End a transaction and roll back
the changes. |
Binding Columns
The simple query facilities require using the SQL GET DATA statement for each column of each row of a result set. This can be inefficient for large result sets. Therefore, InstantSQL provides the SQL BIND COLUMN statement, which reduces or eliminates the need to use the SQL GET DATA statement. The SQL BIND COLUMN statement can be executed once after the query is prepared to bind result columns to COBOL data items. Then, each time an SQL FETCH ROW statement successfully fetches a row, the data values for the bound columns are transferred to the specified COBOL data items automatically. Binding columns also makes it easier to use a prepared SQL statement multiple times since the binding can be done once.
Binding Parameters
There are times when queries need to be repeated many times with different parameter values. For example, where the user is allowed to specify an employee number to retrieve the name and this can be performed many times before the employee is finally selected. In such cases, preparing new queries for each employee number is inefficient. Therefore, InstantSQL provides facilities to create parameterized queries. Parameterized queries enable parameter markers, the ? character, to be included in the SQL statement. The parameter markers can be bound to COBOL data items with the SQL BIND PARAMETER statement. Each time the query is re-started, the parameter values are obtained from the bound COBOL data items.
When the result set of the current query is no longer required, typically after sql-EndOfData has been set to true, another query can be started by setting new parameter values in the bound COBOL data items and re-starting the query. After the query has been started with all the desired parameter values, the query can be dropped by using the SQL END QUERY statement.
Parameterized queries can also be used for updates, for example
UPDATE Employees SET NetPay = ? WHERE Number = ?.
In this example different employees can be selected and their net pay updated. To use prepared queried for updates, the SQL PREPARE QUERY statement must be used to create the query; then, each time the update is to be done, the SQL START QUERY statement is used after setting the desired parameter values into the bound COBOL data items. For update queries, there is no need to use the SQL BIND COLUMN, SQL FETCH ROW, or SQL GET DATA statements.
Parameters must be used when the SQL statement string specifies a stored procedure call and the procedure specified has output parameters. Output parameters include the return value of a stored procedure that returns a value. (See the topic Stored Procedures for more information on executing stored procedures.)
Click here for an Advanced Query Flowchart
Click here for an Advanced Query Example
Transactions
InstantSQL supplies limited support for ODBC transactions. Transactions provide concurrency control through use of transaction isolation levels. When InstantSQL connects to a data source, the connection is in auto-commit mode with the default transaction isolation level for the data source. In auto-commit mode, each SQL statement that is executed is automatically committed. This is sufficient when the application requires only single SQL statements to perform its work.
The SQL START TRANSACTION statement may be used to group a set of SQL statement executions into one transaction. This statement modifies the connection to be in manual-commit mode and can also change the transaction isolation level, if desired. In manual-commit mode, a set of SQL statements may be executed as a single transaction. After the statements have been executed, the application can use the SQL COMMIT TRANSACTION statement to commit the work done or the SQL ROLLBACK TRANSACTION statement to roll back the work done (a rollback may be done after any of the statements, for example, after a statement in the transaction fails). If the transaction isolation level is set to sql-TXN-Serializable, the data source isolates the transaction from other concurrent transactions so that, in effect, it occurs as a single unit with respect to activities by other connections to the data source. This is most often implemented in the data source by a record locking scheme.
Higher levels of transaction isolation offer the most protection for the integrity of database data at the expense of concurrency and performance. Serializable (sql-TXN-Serializable) transactions are guaranteed to be unaffected by other transactions and therefore guaranteed to maintain database integrity. Repeatable read (sql-TXN-Repeatable-Read) transactions are generally the minimum level required to maintain database integrity for transactions that do updates with multiple SQL statements.
However, a higher level of transaction isolation can cause slower performance because it increases the chances that the application will have to wait for locks on data to be released. An application may specify a lower level of isolation to increase performance in the following cases:
When it can be guaranteed that no
other transactions exist that might interfere with an application’s
transactions. This situation occurs
only in limited circumstances, such as when one person in a small company
maintains dBASE files containing personnel data on one computer and does not share
these files.
When speed is more critical than accuracy and any errors are likely to
be small. For example, suppose that a
company makes many small sales and that large sales are rare. A transaction that estimates the total value
of all open sales might safely use the sql-TXN-Read-Uncommitted isolation level. Although the transaction would include orders in the process of
being opened or closed that are subsequently rolled back, these would tend to
cancel each other out and the transaction would be much faster because it is
not blocked each time it encounters such an order.
It is important to note that the transaction isolation level does not affect the ability of a transaction to see its own changes; transactions can always see any changes they make. For example, a transaction might consist of two UPDATE statements, the first of which raises the pay of all employees by 10 percent and the second of which sets the pay of any employees over some maximum amount to that maximum amount. This succeeds as a single transaction only because the second UPDATE statement can see the results of the first.
Note Applications should not commit or roll back transactions by executing COMMIT or ROLLBACK SQL statements with the SQL PREPARE QUERY and SQL START QUERY statements. The effects of doing this are undefined. Possible problems include the driver no longer knowing when a transaction is active and the driver being in auto-commit mode (the COMMIT or ROLLBACK statement may succeed without doing anything because there is no outstanding work). Applications should use the SQL COMMIT TRANSACTION or SQL ROLLBACK TRANSACTION statements instead, after using an SQL START TRANSACTION statement.
Click here for a Transaction Flowchart
Click here for a Transaction Example
© Copyright 2000-2020 Micro Focus or one of its affiliates.