Determining what caused an error when retrieving data into a host variable

Errors that occur when DB2® passes data to host variables in an application are usually caused by a problem in converting from one data type to another. These errors do not affect the position of the cursor.

About this task

For example, suppose that you fetch an integer value of 32768 into a host variable of type SMALLINT. The conversion might cause an error if you do not provide sufficient conversion information to DB2.

The variable to which DB2 assigns the data is called the output host variable. If you provide an indicator variable for the output host variable or if data type conversion is not required, DB2 returns a positive SQLCODE for the row in most cases. In other cases where data conversion problems occur, DB2 returns a negative SQLCODE for that row. Regardless of the SQLCODE for the row, no new values are assigned to the host variable or to subsequent variables for that row. Any values that are already assigned to variables remain assigned. Even when a negative SQLCODE is returned for a row, statement processing continues and DB2 returns a positive SQLCODE for the statement (SQLSTATE 01668, SQLCODE +354).

Procedure

To determine what caused an error when retrieving data into a host variable:

When DB2 returns SQLCODE = +354, use the GET DIAGNOSTICS statement with the NUMBER option to determine the number of errors and warnings.
Example: Suppose that no indicator variables are provided for the values that are returned by the following statement:
```
FETCH FIRST ROWSET FROM C1 FOR 10 ROWS INTO :hva_col1, :hva_col2;
```
For each row with an error, DB2 records a negative SQLCODE and continues processing until the 10 rows are fetched. When SQLCODE = +354 is returned for the statement, you can use the GET DIAGNOSTICS statement to determine which errors occurred for which rows. The following statement returns num_rows = 10 and num_cond = 3:
```
GET DIAGNOSTICS :num_rows = ROW_COUNT, :num_cond = NUMBER;
```

To investigate the errors and warnings, use additional GET DIAGNOSTIC statements with the CONDITION option.

Example: To investigate the three conditions that were reported in the example in the previous step, use the following statements:

Statement Output

Table 1. GET DIAGNOSTIC statements to investigate conditions
Statement	Output
`GET DIAGNOSTICS CONDITION 3 :sqlstate = RETURNED_SQLSTATE, :sqlcode = DB2_RETURNED_SQLCODE, :row_num = DB2_ROW_NUMBER;`	`sqlstate = 22003 sqlcode = -304 row_num = 5`
`GET DIAGNOSTICS CONDITION 2 :sqlstate = RETURNED_SQLSTATE, :sqlcode = DB2_RETURNED_SQLCODE, :row_num = DB2_ROW_NUMBER;`	`sqlstate = 22003 sqlcode = -802 row_num = 7`
`GET DIAGNOSTICS CONDITION 1 :sqlstate = RETURNED_SQLSTATE, :sqlcode = DB2_RETURNED_SQLCODE, :row_num = DB2_ROW_NUMBER;`	`sqlstate = 01668 sqlcode = +354 row_num = 0`

GET DIAGNOSTICS CONDITION 3 :sqlstate
 = RETURNED_SQLSTATE, :sqlcode =
DB2_RETURNED_SQLCODE, :row_num
 = DB2_ROW_NUMBER;

sqlstate = 22003
sqlcode  = -304
row_num  = 5

GET DIAGNOSTICS CONDITION 2 :sqlstate
 = RETURNED_SQLSTATE, :sqlcode =
DB2_RETURNED_SQLCODE, :row_num
 = DB2_ROW_NUMBER;

sqlstate = 22003
sqlcode  = -802
row_num  = 7

GET DIAGNOSTICS CONDITION 1 :sqlstate
 = RETURNED_SQLSTATE, :sqlcode =
DB2_RETURNED_SQLCODE, :row_num
 = DB2_ROW_NUMBER;

sqlstate = 01668
sqlcode  = +354
row_num  = 0

This output shows that the fifth row has a data mapping error (-304) for column 1 and that the seventh row has a data mapping error (-802) for column 2. These rows do not contain valid data, and they should not be used.