mikepizzo
commented
7 years ago Updated 6.2.1 Usage section of GetNestedHandle to read:
Multiple calls to SQLGetNestedHandle for the same ParentStatementHandle, Col_or_Param_Num, and SQL_DESC_CONCISE_TYPE return the same ChildStatementHandle (including any added/modified descriptor fields) unless that handle has been freed, in which case the next call to SQLGetNestedHandle for that ParentStatementHandle, Col_or_Param_Num, and SQL_DESC_CONCISE_TYPE combination returns a fresh statement handle with default values appropriate for the current row.
We have an (important) optimization of retaining the nested statement handle (including binding) for a child across parents (SQLGetNestedHandle returns the same statement handle for the same ParentStatementHandle/Col_or_Param_Num combination), unless that child handle is explicitly freed by the application (in which case it is "reset").
This works well when the children across parents have the same type (for example, an Address structured type on a customer). And it works for dynamic columns (that get added to the descriptor). However, it breaks down if the nested column type is variant (i.e., a "Transaction" property on a ledger that could be "buy", "sell", "trade", etc. -- each with different properties).
We could say that there is a unique handle for each combination of ParentStatementHandle/Col_or_Param_Num/column type. That way the application can specify a set of bindings for each type. If there is commonality between the types they can always bind to the same memory addresses. If a nested column is untyped (SQL_DESC_TYPE is SQL_ROW or SQL_VARIANT) then the descriptor grows with added fields unless/until the application calls SQLFreeStatement (on either the child statement or one of it's ancestors).