SQL GET DATA Statement

SQL GET DATA
Statement

< > Query Statements Example Flow Chart Table of Contents

The SQL GET DATA statement transfers result set column data values for one or more columns in the currently fetched row of an InstantSQL query to COBOL data items.

QueryHandle (input). This argument must specify a numeric integer value with at least six digits of precision. The value identifies the query for which data values from result set columns are to be transferred 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 transferred. 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 transferred. The argument specifies the data item where the column data value is to be transferred. 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 specified column is to be transferred. 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 column result value may be zero for zero length character data in the database. This argument must be present, but the word OMITTED may be specified as a placeholder to explicitly ignore the length indicator value, for example, when NULL column values are not expected.

An SQL GET DATA statement can only be successfully executed when the query status has been set to sql-StatFetching by a prior SQL FETCH ROW statement. (The current status of a query can be obtained using the SQL DESCRIBE QUERY statement.)

An SQL GET DATA statement can be used to transfer the data from any of the columns of the result set of the currently fetched row. However, it is redundant and inefficient to use the SQL GET DATA statement to transfer data from columns that have been bound using the SQL BIND COLUMN statement since the data is already available in COBOL data items.

For those readers familiar with embedded SQL, the SQL GET DATA 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 SQL PREPARE QUERY statement.)

SQL GET DATA Statement Example:

            SQL GET DATA
               sql-QueryHandle
               1, ColOneData,   OMITTED,
               2, ColTwoData,   OMITTED,
               3, ColThreeData, ColThreeLenInd.

           IF ColThreeLenInd = sql-Null-Data
             MOVE ALL "#" TO ColThreeData   *>"#" is COBOL null value
           END-IF.                          *>   for this program.