SQL BIND COLUMN Statement

SQL BIND COLUMN
Statement

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

The SQL BIND COLUMN statement binds one or more columns in a query result set to COBOL data items.

QueryHandle (input). This argument must refer to a numeric integer data item with at least six digits of precision. Its value identifies the query for which columns in the result set are to be bound to COBOL data items. This value must have been returned from a successful SQL PREPARE QUERY statement or any of the browse statements.

ColNameOrNumberN (input). This argument must specify a nonnumeric value or a numeric integer value. If the argument is nonnumeric, then its value specifies the column name of a column in the result set that is to be bound. If the argument is numeric, then its value specifies the column number of a column in the result set that is to be bound. Columns in the result set are numbered from left to right starting with one (1).

DataItemN (output). This argument may refer to any COBOL data item that is consistent with the data type of the column being bound. The argument specifies the data item to which the column is to be bound. The column value is transferred into the data item each time an SQL FETCH ROW statement is successfully executed. If the column value is NULL in a fetched row, InstantSQL stores binary zeroes into the data item, but an application should use the value stored in the LenIndN argument to detect NULL values.

LenIndN (output). This argument must refer to a signed numeric integer data item with at least nine digits of precision. The argument specifies the data item where the length indicator value for the bound column is to be transferred each time an SQL FETCH ROW statement is successfully executed. The value sql-Null-Data is transferred into the length indicator for NULL column result values. Otherwise, the length of the result value is transferred. The length of the result value may be zero for zero length character or binary data in the database. For other than character and binary data, the length indicator value is generally not meaningful to the COBOL program when it is other than sql-Null-Data. For example, for numeric data, date data, time data, and timestamp data, the length value indicates the length of the C data transferred from the database and not the length of the COBOL data item. This argument must be present, but the word OMITTED may be specified as a placeholder if the length indicator value is not needed, such as when null data is not allowed for a column and the length of character or binary data is not needed by the COBOL program. When OMITTED is specified, the length indicator value for the result column is not transferred to the COBOL program.

The SQL BIND COLUMN statement may generally be done anytime after the SQL PREPARE QUERY statement and before the SQL END QUERY statement. However, in some cases, for example, the Access 97 ODBC driver where the SQL text specifies a procedure call, the result columns are not known until after the SQL START QUERY statement is executed for the first time.

Result column data is transferred to the bound data items after each SQL FETCH ROW statement has successfully fetched another row from the result set. The bound data items are not modified if the SQL FETCH ROW statement is unsuccessful in fetching a row, including the end-of-data case. Errors in storing the column values into the bound data items will cause the SQL FETCH ROW statement to report the errors, but InstantSQL will have attempted to store all the bound columns into their respective COBOL data items before reporting any such errors.

SQL BIND COLUMN may be called any number of times binding different columns. If a column is referenced that has already been bound, the new binding will replace the existing binding. Other than re-binding a column to a different COBOL data item, there is no unbinding support in InstantSQL except to drop the query.

A column may only be bound to a single COBOL data item at any one time, but multiple columns may be bound to the same COBOL data item.

Data items from different programs can be bound to the result columns of the same query, but care must be taken not to cancel programs that contain data items bound to result columns of queries that have not been dropped. Likewise, if result columns are bound to dynamically allocated memory, the memory should not be deallocated until the query is dropped. When result columns are bound to Linkage Section data items, the columns are bound to the data items passed as actual arguments at the time of the SQL BIND COLUMN statement. If different actual arguments are passed at the time of the SQL FETCH ROW statement, the column data will be stored into the data items specified by the actual arguments at the time of the SQL BIND COLUMN statement rather than the data items specified by the actual arguments at the time of the SQL FETCH ROW statement.

Using SQL BIND COLUMN after query has been created eliminates the need to perform SQL GET DATA for each column of each row of a query. This results in significant performance improvements over repeated calls to SQL GET DATA. Columns that are bound may still be extracted using SQL GET DATA, but doing so is redundant and inefficient.

For result columns with the data types sql-LongVarChar or sql-LongVarBinary, the length of the COBOL data item specified by DataItemN is used to establish the maximum length of the data that can be transferred from the data source for the column. Otherwise, the maximum length for these data types is established by the configuration options LongVarCharDataSize and LongVarBinaryDataSize, respectively.

For those readers familiar with embedded SQL, the SQL BIND COLUMN statement is analogous to listing the host variables in the INTO phrase of an embedded SELECT statement. (The INTO phrase is not used in SELECT statements submitted in InstantSQL with the InstantSQL SQL PREPARE QUERY statement.)

SQL BIND COLUMN Statement Examples:

           SQL BIND COLUMN
               sql-QueryHandle,
               1, ColOneData,   ColOneLenInd,
               2, ColTwoData,   ColTwoLenInd,
               3, ColThreeData, ColThreeLenInd.

           SQL BIND COLUMN
               sql-QueryHandle,
               "EmpIDNo", WS-EmpIDNo, OMITTED,
               "EmpName", WS-EmpName, WS-EmpName-Len,
               "EmpDept", WS-EmpDept, OMITTED.