Dynamic column which changes type: What determines whether a new IRD entry is added, or an existing one changed?

[matthew-wozniczka]

Section 3.9.4.1 (Retrieving Dynamic Columns) states:

If the type and length information changes on subsequent rows, the driver may return SQL_METADATA_CHANGED based on the SQL_ATTR_TYPE_EXCEPTION_BEHAVIOR. Alternatively, the driver may create additional IRD records with the same name for dynamic columns of different types. Once added, the descriptor fields are a permanent part of the descriptor until it is freed.

How is this to be interpreted? Does the driver have a choice (i.e. driver-defined behaviour) of whether a new IRD record will be created or the existing one is modified? If so, there should be some way for the application to discover this behaviour.

[mikepizzo]

Introduce a statement attribute that the application can set to say always treat type changes as metadata changed, not as a new IRD record.

[matthew-wozniczka]

Just to be explicit, the options for this new attribute will be:

• Never create new IRD records for 'changed' columns
• Sometimes (perhaps always, perhaps never) create new IRD records

Right? (as opposed to always vs. sometimes)

[mikepizzo]

Yes; that is correct.

Proposal: Add new settable statement attribute for reusedynamicbinding. If set to true, then if a dynamic column is discovered whose name matches an already-added dynamic column, then that descriptor record is used. If set to false (the default), a driver may create a new descriptor records for dynamic columns with different types. In either case, if the type has changed a metadata_changed state is raised.

Note that you still may get multiple descriptor records with the same name, in which case if this is set to true the driver would ideally pick the descriptor record with the same name and that most closely matches the type, but there is no such guarantee.

[mikepizzo]

Changed SQL_ATTR_DYNAMIC_COLUMNS from a Boolean to a SQL_ATTR_DYNAMIC_COLUMN_BEHAVIOR, as follows:

3.9.1 SQL_ATTR_DYNAMIC_COLUMN_BEHAVIOR Statement Attribute

The new SQL_ATTR_DYNAMIC_COLUMN_BEHAVIOR statement attribute controls whether or not additional, dynamic columns may be returned for unbounded select results.

• SQL_DCB_IGNORE – (Default) Dynamic columns are ignored.
• SQL_DCB_REUSE_BINDINGS – Dynamic columns are added to the descriptor as they are discovered. Once discovered, the same descriptor record number is used for the same dynamic column, and the type information is updated as appropriate.
• SQL_DCB_OPEN_BINDINGS – Dynamic columns are added to the descriptor as they are discovered. New descriptor records MAY be created if the same dynamic column name is discovered with a different type.

Data sources with fixed schemas always return SQL_DCB_IGNORE for this value. Attempting to set this value for such a data source returns SQL_SUCCESS_WITH_INFO with a diagnostic code of 01S02, Option Value Changed, and reading this value will continue to return SQL_DCB_IGNORE.

If set to SQL_DCB_REUSE_BINDINGS or SQL_DCB_OPEN_BINDINGS, SQLFetch, SQLFetchScroll and SQL_NextColumn return SQL_DATA_AVAILABLE if new columns are discovered/added to the IRD while retrieving results.

If SQL_ATTR_DYNAMIC_COLUMN_BEHAVIOR is not SQL_DCB_IGNORE, then bound columns in the ARD that don’t apply to the current row have their len_or_ind_ptr set to SQL_DATA_UNAVAILABLE. If SQL_ATTR_DYNAMIC_COLUMN_BEHAVIOR is SQL_DCB_IGNORE, then only known columns are allowed in a select list and only known columns are returned when * is specified.

Applications may change SQL_ATTR_DYNAMIC_COLUMN_BEHAVIOR from to SQL_DCB_IGNORE at any time to ignore dynamic columns. Attempting to change SQL_ATTR_DYNAMIC_COLUMN_BEHAVIOR to SQL_DCB_REUSE_BINDINGS or SQL_DCB_OPEN_BINDINGS on a statement handle with an open cursor results in HY010, Function Sequence Error (raised by the Driver Manager).

[mikepizzo]

How important is it to be able to specify that a driver can create new bindings for the same dynamic parameter name but different types? Would it simplify things to say that the driver always used the same binding for a dynamic column of a particular name (and created a new binding with only if a new dynamic column with the same name was discovered in the same row?)