search

microsoft / ODBC-Specification

Microsoft ODBC Specification
Other
121 stars 40 forks source link

SQLGetNestedHandle and missing/null collection/structure values #79

Closed matthew-wozniczka closed 6 years ago

matthew-wozniczka commented 7 years ago

There should be a way for SQLGetNestedHandle to indicate to the application that the value is NULL or missing (for a dynamic column).

See #37

mikepizzo commented 7 years ago

Not sure I understand the issues. We currently say that calling SQLGetNestedHandle for a non-structured or collection valued column returns SQL_ERROR with SQLState 07009, Invalid descriptor index. The descriptor field will indicate whether a column for this instance is structured or collection-valued.

matthew-wozniczka commented 7 years ago

What I mean is, if a column at the top level, for example, is SQL_MULTISET, on some rows it may be empty, on some non-empty, on some it may be NULL, and on some it may not exist at all (if it was a dynamic column).

The description for SQLGetNestedColumn doesn't say what happens in the latter two cases (and there doesn't seem to be any other mechanism to check other than binding the column as SQL_DATA_AT_FETCH and looking at the length indicator)

mikepizzo commented 7 years ago

Should only hit DATA_AVAILABLE case if there is nested data available. For GD_ANY, which allows client to retrieve in any order, calling SQLGetNestedHandle on a column with no nested content should return an error. The str_len_or_indicator_ptr will contain SQL_NULL_VALUE.

Note that the descriptor will tell you the type, so calling SQLGetNestedHandle on a column whose IRD is not structured or collection should return an error. Driver Manager should enforce this.

matthew-wozniczka commented 7 years ago

Are there other examples in ODBC where you're supposed to look at an out-parameter of a failing API function? Seems a bit inconsistent.

Also, which str_len_or_indicator_ptr are you referring to? SQLGetNestedHandle doesn't take that as a parameter, and the column may not be bound.

How about making SQLGetNestedHandle return SQL_NO_DATA if the values was NULL/unavailable? Doesn't allow the application to distinguish between the two cases though...

mikepizzo commented 7 years ago

For empty results, the same answer applies to dynamic or declared columns. If the collection is empty, just as SQLExecute/ExecDirect returns SUCCESS for an empty result (allowing you to describe the results), so should you be able to get a handle to describe an empty nested result.

The first question, then, is whether a (dynamic or declared) collection- or structured- valued column can be null. We should require the application bind the str_len_or_output_ptr for (nullable) collection- or structured -valued columns, and the driver set this value if the collection- or structured- valued column is null (alternatively we could have SQLGetNestedHandle return NO_DATA? -- see note below; if driver sets SQL_NULL_VALUE, then the app would have to reset to SQL_DATA_AT_FETCH before fetching the next row...)

The third question is whether a dynamic structured or collection valued column has a different state for missing. For a missing collection-valued dynamic column, the original fetch (or subsequent SqlNextColumn) would not return SQL_DATA_AVAILABLE for a collection-valued dynamic column if it was not present. Note that, in this case, the driver does NOT set the str_len_or_indicator_ptr, which distinguishes the case of null from missing.

Note: we currently require the app to specify SQL_DATA_AT_FETCH for collection- and structured- valued columns because we want the application to have a way to "unbind" such columns (by setting str_len_or_indicator_ptr to some value other than SQL_DATA_AT_FETCH). That would mean that the app would have to reset their str_len_or_indicator_ptr to SQL_DATA_AT_FETCH after retrieving a null value (or after the driver set SQL_DATA_UNAVAILABLE, if we had the driver set that for collection-valued variant columns that didn't apply to a particular row.

mikepizzo commented 7 years ago

See also issues #37 and #11.

Need to think through use of str_len_or_indicator_ptr for SQL_DATA_AT_FETCH, collection/structured columns, dynamic columns, etc.

matthew-wozniczka commented 7 years ago

We should require the application bind the str_len_or_output_ptr for (nullable) collection- or structured -valued columns

So, you must bind str_len_or_indicator_ptr for a column before calling SQLGetNestedHandle on it?

mikepizzo commented 6 years ago

PR #133 tackles null-valued collection and structural columns by requiring SQL_DESC_INDICATOR_PTR to be set for nullable data-at-fetch columns. This no longer requires re-binding, as we made SQL_DESC_DATA_AT_FETCH a separate descriptor field.