In previous uses of the SQLDA for input or output, SQLTYPE always described the data type of the storage area pointed to by SQLDATA. In the following example, the type code 500 (originally obtained with a DESCRIBE of the select-statement) describes the data type of the main variable.
Figure 67. The FETCH Using Descriptor
In previous sections, the select_list item, the type code, and the data type of the storage area allocated for holding query results are all equivalent. That is, in the above example, PARTNO is a SMALLINT column (with no nulls permitted), 500 is the type code meaning SMALLINT NOT NULL, and the area allocated is a binary integer halfword. To force a data conversion, you must allocate a storage area having a different data type and then change SQLTYPE in the SQLDA. Suppose that you wanted to select the SMALLINT part numbers into an integer area. Here is the sequence of instructions needed:
EXEC SQL PREPARE S1 FROM :STRING EXEC SQL DESCRIBE S1 INTO SQLDA Allocate a binary integer fullword of storage. Set SQLDATA to point to it. SQLTYPE = 496
When the FETCH is executed, SMALLINT is converted to INTEGER. Similarly, you could have converted the retrieved PARTNO values to FLOAT merely by setting SQLTYPE to 480 and by allocating a floating-point word of storage.
This conversion can be done when the SQLDA is used for input also. Consider the normal case:
Figure 68. The EXECUTE Using Descriptor
As before, PARTNO is SMALLINT. The main variable is also allocated as SMALLINT (binary integer halfword), and the SQLTYPE that describes the main variable represents a SMALLINT. To perform data conversion on input, you need to change only the SQLTYPE and the type of storage allocated to hold the input values. This is done exactly as in the previous example. To insert a floating-point variable into the SMALLINT PARTNO column, for example, these steps are needed:
EXEC SQL PREPARE S1 FROM :STRING EXEC SQL PREPARE S2 FROM 'SELECT PARTNO FROM INVENTORY' EXEC SQL DESCRIBE S2 INTO SQLDA Allocate an 8-byte floating-point area. Set SQLDATA to point to it. Assign a floating-point number to the area. SQLTYPE = 480 EXEC SQL EXECUTE S1 USING DESCRIPTOR SQLDA
All dynamic data conversion is done according to the rules summarized under Converting Data.
If you change the SQLTYPE code and then allocate a storage area of an incorrect type, the system treats the storage area as though it were of the type indicated by SQLTYPE. For example, suppose SQLTYPE indicates that the storage area pointed to by SQLDATA is an INTEGER, but that the actual area allocated is a binary integer halfword (SMALLINT). The field is treated as though it is an INTEGER, not a SMALLINT. This type of error may yield confusing results.
When a datetime data code is used in an SQLDA on a FETCH, the system assumes that the variable declared to hold the result is fixed-length character.