SQL_ATTR_TYPE_EXCEPTION_BEHAVIOR & SQLGetData

[matthew-wozniczka]

If a fetch is interrupted by SQL_METADATA_CHANGED due to the setting of SQL_ATTR_TYPE_EXCEPTION_BEHAVIOR, and the application chooses to retrieve the value using SQLGetData, what happens to the existing binding?

Does the length/indicator get set to SQL_DATA_UNAVAILABLE?

[matthew-wozniczka]

The same question can be asked for SQL_DATA_AT_FETCH columns, or if the application uses SQLGetNestedHandle instead of SQLGetData

[mikepizzo]

Calling GetNestedHandle on anything other than a collection or dynamic-valued column, or GetData on a collection or dynamic valued column, should return an error with SQLState 07009 Invalid descriptor index.

Yes, I think SQL_DATA_UNAVAILABLE is appropriate for data retrieved using SQLGetData.

[matthew-wozniczka]

My comment about SQLGetNestedHandle was about the scenario in which the data-at-fetch/type-exception column was of collection or structured type. Though it could also apply depending on #45

Yes, I think SQL_DATA_UNAVAILABLE is appropriate for data retrieved using SQLGetData.

My only concern is that it becomes ambiguous (after the fact) whether the data wasn't there because the dynamic column was not present on that row, or if the application decided to fetch the data early with SQLGetData. Perhaps we could have a separate status flag? Not a huge deal though, as the application can keep track for itself.

[mikepizzo]

Yes; the application has to keep track for itself.

[mikepizzo]

Actually, we have added a SQL_TYPE_EXCEPTION value for this case.

If the fetch is interrupted due to a type exception, the driver first sets the str_len_or_indicator (if bound) to SQL_TYPE_EXCEPTION, then returns SQL_METADATA_CHANGED. The app can retrieve the data using SQLGetData (or SQLGetNestedHandle, as appropriate) and then call SQLNextColumn to continue processing. The driver doesn't touch the str_len_or_indicator value again until after the next call to SQLFetch/SQLFetchScroll.

Updated 3.7.3.1 SQL_ATTR_TYPE_EXCEPTION_BEHAVIOR:

SQL_ATTR_TYPE_EXCEPTION_BEHAVIOR is a SQL_USMALLINT value that allows the client to control how type exceptions occur when retrieving bound values. It does not affect SQLGetData.

• SQL_TE_ERROR – (Default) Return an error if the data value is incompatible with the bound type.
• SQL_TE_CONTINUE – Set the str_type_or_ind_ptr value (if not null) to SQL_TYPE_EXCEPTION if the data value is incompatible with the bound type and continue, or error if str_type_or_ind_ptr is a null pointer.
• SQL_TE_REPORT_EXCEPTION – Set the str_type_or_ind_ptr value to SQL_TYPE_EXCEPTION if the data value is incompatible with the bound type and return SQL_METADATA_CHANGED. The application can retrieve the value for the column using SQLGetData or SQLGetNestedHandle, as appropriate. If the rowset size is 1, the application can update the binding for this column for the next call to SQLFetch/SQLFetchScroll.
• SQL_TE_REPORT_ALL – Set the str_type_or_ind_ptr value (if not null) to SQL_TYPE_EXCEPTION and return SQL_METADATA_CHANGED if the data value does not match the IRD (for rows) or IPD (for output parameters). The IRD or IPD is updated to reflect the type of the current value and the application can retrieve the value for the column using SQLGetData or SQLGetNestedHandle, as appropriate. If the rowset size is 1, the application can update the binding for this column to be used in the next call to SQLFetch/SQLFetchScroll.