SQLDA format

 

An SQL descriptor area (SQLDA) consists of four variables followed by an arbitrary number of occurrences of a sequence of six variables collectively named SQLVAR.

The SQLDA in REXX is different.

When an SQLDA is used in OPEN, FETCH, CALL, and EXECUTE, each occurrence of SQLVAR describes a host variable.

The fields of the SQLDA are as follows:

SQLDAID

SQLDAID is as used an 'eyecatcher" for storage dumps. It is a string of 8 characters that have the value 'SQLDA' after the SQLDA is used in a PREPARE or DESCRIBE statement. This variable is not used for FETCH, OPEN, CALL, or EXECUTE.

Byte 7 can be used to determine if more than one SQLVAR entry is needed for each column. Multiple SQLVAR entries may be needed if there are any LOB or distinct type columns. This flag is set to a blank if there are not any LOBs or distinct types.

SQLDAID is not applicable in REXX.

SQLDABC

SQLDABC indicates the length of the SQLDA. It is a 4-byte integer that has the value SQLN*LENGTH(SQLVAR) + 16 after the SQLDA is used in a PREPARE or DESCRIBE statement. SQLDABC must have a value equal to or greater than SQLN*LENGTH(SQLVAR) + 16 before it is used by FETCH, OPEN, CALL, or EXECUTE.

SQLABC is not applicable in REXX.

SQLN

SQLN is a 2-byte integer that specifies the total number of occurrences of SQLVAR. It must be set before being used by any SQL statement to a value greater than or equal to 0.

SQLN is not applicable in REXX.

SQLD

SQLD is a 2-byte integer that specifies the number of occurrences of SQLVAR, in other words, the number of host variables or columns described by the SQLDA. This field is set by SQL on a DESCRIBE or PREPARE statement. In other statements, this field must be set before being used to a value greater than or equal to 0 and less than or equal to SQLN.

SQLVAR

This group of values are repeated once for each host variable or column. These variables are set by SQL on a DESCRIBE or PREPARE statement. In other statements, they must be set before being used. These variables are defined as follows:

SQLTYPE

SQLTYPE is a 2-byte integer that specifies the data type of the host variable or column. See SQLTYPE and SQLLEN for a table of the valid values. Odd values for SQLTYPE show that the host variable has an associated indicator variable addressed by SQLIND.

SQLLEN

SQLLEN is a 2-byte integer variable that specifies the length attribute of the host variable or column.

SQLRES

SQLRES is a 12-byte reserved area for boundary alignment purposes. Note that, in i5/OS®, pointers must be on a quad-word boundary.

SQLRES is not applicable in REXX.

SQLDATA

SQLDATA is a 16-byte pointer variable that specifies the address of the host variables when the SQLDA is used on OPEN, FETCH, CALL, and EXECUTE.

When the SQLDA is used on PREPARE and DESCRIBE, this area is overlaid with the following information:

The CCSID of a character or graphic field is stored in the third and fourth bytes of SQLDATA. For BIT data, the CCSID is 65535. In REXX, the CCSID is returned in the variable SQLCCSID.

SQLIND

SQLIND is a 16-byte pointer that specifies the address of a small integer host variable that is used as an indication of null or not null when the SQLDA is used on OPEN, FETCH, CALL, and EXECUTE. A negative value indicates null and a non-negative indicates not null. This pointer is only used if SQLTYPE contains an odd value.

When the SQLDA is used on PREPARE and DESCRIBE, this area is reserved for future use.

SQLNAME

SQLNAME is a variable-length character variable with a maximum length of 30. After a PREPARE or DESCRIBE, this variable contains the name of selected column, label, or system column name. In OPEN, FETCH, EXECUTE, or CALL, this variable can be used to pass the CCSID of character strings. CCSIDs can be passed for character and graphic host variables.

The SQLNAME field in an SQLVAR array entry of an input SQLDA can be set to specify the CCSID. See CCSID values in SQLDATA or SQLNAME for the layout of the CCSID data in this field.

It is important to remember that the SQLNAME field is only for overriding the CCSID. Applications that use the defaults do not need to pass CCSID information. If a CCSID is not passed, the default CCSID for the job is used.

The default for graphic host variables is the associated double-byte CCSID for the job CCSID. If an associated double-byte CCSID does not exist, 65535 is used.

SQLVAR2

This is the Extended SQLVAR structure that contains 3 fields. Extended SQLVARs are needed for all columns of the result if the result includes any distinct type or LOB columns. For distinct types, they contain the distinct type name. For LOBs, they contain the length attribute of the host variable and a pointer to the buffer that contains the actual length. If locators are used to represent LOBs, these entries are not necessary. The number of Extended SQLVAR occurrences needed depends on the statement that the SQLDA was provided for and the data types of the columns or parameters being described. Byte 7 of SQLDAID is always set to the number of sets of SQLVARs necessary.

If SQLD is not set to a sufficient number of SQLVAR occurrences:

  • SQLD is set to the total number of SQLVAR occurrences needed for all sets.

  • A +237 warning is returned in the SQLCODE field of the SQLCA if at least enough were specified for the Base SQLVAR Entries. The Base SQLVAR entries are returned, but no Extended SQLVARs are returned.

  • A +239 warning is returned in the SQLCODE field of the SQLCA if enough SQLVARs were not specified for even the Base SQLVAR Entries. No SQLVAR entries are returned.

SQLLONGLEN

SQLLONGLEN is a 4-byte integer variable that specifies the length attribute of a LOB (BLOB, CLOB, or DBCLOB) host variable or column.

SQLDATALEN

SQLDATALEN is a 16-byte pointer variable that specifies the address of the length of the host variable. This variable is used for LOB (BLOB, CLOB, and DBCLOB) host variables only. It is not used for DESCRIBE or PREPARE.

If this field is NULL, then the actual length of the data is stored in the 4 bytes immediately before the start of the data, and SQLDATA points to the first byte of the field length. The length indicates the number of bytes for a BLOB or CLOB, and the number of characters for a DBCLOB.

If this field is not NULL, it contains a pointer to a 4-byte long buffer that contains the actual length in bytes (even for DBCLOB) of the data in the buffer pointed to by the SQLDATA field in the matching base SQLVAR.

SQLDATATYPE_NAME

SQLDATATYPE_NAME is a variable-length character variable with a maximum length of 30. It is only used for DESCRIBE or PREPARE. This variable is set to one of the following:

  • For a distinct type column, the database manager sets this to the fully qualified distinct type name. If the qualified name is longer than 30 bytes, it is truncated.

  • For a label, the database manager sets this to the first 20 bytes of the label.

  • For a column name, the database manager sets this to the column name.

 

Parent topic:

Processing SELECT statements and using a descriptor

 

Related tasks


Coding SQL statements in REXX applications

 

Related reference


Example: A SELECT statement for allocating storage for SQLDA