Transferring values between host variables and a descriptor area

The rules governing the transfer of values between host variables an a descriptor area are more strict than those for transferring values between host variables (or descriptor area) and table columns:

• The following applies to all fields except NAME and DATA: The SQL data type of the host variable in which the value of a field is stored or from which a value is read must be SMALLINT.

• If the value of the NAME field is read, the host variable must be of the type CHAR(n) or VARCHAR(n) where n >= 128.

• If the value of the DATA field is stored in a host variable or read from a host variable, the SQL data type of the host variable must match the data type described by the fields TYPE, DATETIME_INTERVAL_CODE, LENGTH, PRECISION and SCALE of the same item descriptor. The rules are contained below in accordance with the data type.

Strings

The length of the host variable must be the same as the value in the item descriptor field LENGTH for the SQL data types CHAR and NCHAR.

In the case of the SQL data types VARCHAR and NVARCHAR, the maximum length of the host variable must be the same as the value of the item descriptor field LENGTH if the value is to be transferred from the host variable to the descriptor area. If the value is transferred from the descriptor area to the host variable, the maximum length of the host variable must be at least as big as the value of the item descriptor field LENGTH.

Numeric values

For the SQL data type NUMERIC or DECIMAL, the total number of significant digits of the host variable must be the same as the value of the item descriptor field PRECISION and the number of digits to the right of the decimal point the same as the value of the item descriptor field SCALE.

Time values

The SQL data type of the host variable must correspond to the data type of the item descriptor field DATETIME_INTERVAL_CODE.
In the case of the SQL data types TIME and TIMESTAMP, the item descriptor field PRECISION must contain the value 3.

Recommended procedure

The following procedure is recommended if you do not want to have to define host variables for every possible data type:

1. Use DESCRIBE to store the data type description for the value in the DATA field of the item descriptor.

2. Query the data type of the item descriptor with GET DESCRIPTOR.

3. Change the data type of the item descriptor to match the data type of the host variable with SET DESCRIPTOR.

4. Transfer the value from DATA to or from the host variable.

Example

You want to prepare the following dynamic statement:

SELECT street, country, zip, city FROM customers WHERE company='Siemens'

After executing DESCRIBE OUTPUT, GET DESCRIPTOR will provide you with the following data type descriptions:

VALUE	REPETITIONS	TYPE	LENGTH	PRECISION	SCALE	Corresponding data type
1	1	1	40	0	0	CHAR(40)
2	1	1	3	0	0	CHAR(3)
3	1	2		5	0	NUMERIC(5,0)
4	1	1	40	0	0	CHAR(40)

If you want to use host variables of the type CHAR(100) and NUMERIC(15,5) for storing values, use SET DESCRIPTOR to set the item descriptor fields to the following values:

VALUE	REPETITIONS	TYPE	LENGTH	PRECISION	SCALE
1	1	1	100
2	1	1	100
3	1	2		15	5
4	1	1	100

You can now execute the prepared statement with EXECUTE. The values are stored in the descriptor area. STREET, COUNTRY and CITY are padded on the right with blanks until their length is 100. Five leading zeros and five zeros after the decimal point are added to ZIP.

You can use GET DESCRIPTOR to transfer the values to the appropriate host variables and process them.