SQL_VARIANT & persistence of nested statement state

[matthew-wozniczka]

If you have a SQL_VARIANT column which is of some collection/structure type on row N, some scalar type on row N + 1, and goes back to row Ns type on row N + 2, should the state of the nested statement (attributes/descriptor fields) be preserved from row N? Or does it get 'reset' on row N + 1, leaving us with a 'fresh' statement on row N + 2?

Similarly, what if it goes from collection -> structure -> collection, or flips between multiple different 'types' of collections/structures (For example, Address -> Point -> Address, or Address[] -> Point[] -> Address[]). Should each type (now the question comes up, what defines a 'type' w.r.t. dynamic columns, etc) have it's own state?

[mikepizzo]

For simplicity I would say the ARD is preserved. I'd rather not define data-dependent rules around when it does or does not get reset.

[matthew-wozniczka]

Does that imply the driver needs to keep a copy of the ARD for every distinct type a variant column has taken on over the course of a cursor?

This would be a large burden on the implementation.

[mikepizzo]

No; I wasn't suggesting that the driver keep different ARDs for different types; rather, if the driver returns a nested handle for a variant column that it returns the same nested handle, and the application must fix it as appropriate.

For structured- or collection-valued variant columns, though, it might be cleaner to simply always return a new handle. The application could simply keep ARDs for "common" bindings.

[mikepizzo]

Any time the driver returns METADATA_CHANGED for a structured- or collection-valued column, any statement handle associated with that column is no longer returned for subsequent rows; the next call to SQLGetNestedHandle for the column returns a new statement handle. Applications can copy ARDs for common structures between statement handles.

[mikepizzo]

Applied to docs. Reopening for DM handling.