Can Array, Multiset, UDT and Row columns be bound as string?

[mikepizzo]

In ODBC 3.x, all types can be bound as string. This makes it easy for example, to paste results in a grid.

Is this true with Array, Multiset, UDT, and Row? If so, do we have a canonical representation (i.e., JSON, CSV, or XML)? Or, does the driver just return the strings "Array", "Multiset", "UDT", and "Row"? If the later, are the localized?

What about Variant? Can a variant contain an array, multiset, udt, or row and, if so, can it be bound as string? Presumably variant's ability to be bound as a string relies on the actual type being able to be bound as string.

[matthew-wozniczka]

We could use SQL_DESC_MIME_TYPE to determine the format returned.

I suggest defining a standard format (JSON?), with a NULL SQL_DESC_MIME_TYPE defaulting to that format. Maybe something like

ODBC-json-escape ::= ODBC-esc-initiator json 'json-value' ODBC-esc-terminator

// type is optional (default to SQL_WVARCHAR)
json-value ::= {json-type : type-object, value : json-actual-value}

// For scalars, the value is in its string representation
json-actual-value ::= json-string | json-collection-value | json-structure-value

json-string ::= double-quoted string

// For the following two, the json-value objects have their type-key optional (type is inherited from parent collection or element type if not present)
json-collection-value ::= array of json-value objects
json-structure-value ::= object (keys are field names, values are json-value objects)

// class is optional (defaults to scalar), subtypes is optional, for json-desc-filed-key, at least SQL_DESC_TYPE is needed, all unmentioned descriptor fields defaulted according to sqlbindcolumn rules
type-object ::= {class : json-type-class, subtypes : json-subtypes(, json-desc-field-key : json-desc-field-value)+}
json-type-class ::= scalar | structure | array | multiset
subtypes ::= array of type-objects (1 element for array/multiset, 1 per field for structures). If missing, element type is SQL_VARIANT for collections or the structured type has 0 fixed columns.
json-desc-field-key ::= SQL_DESC_TYPE | ...
json-desc-field-value :: integer or string

// Assume SQL_ARRAY == 89 (SQL_INTEGER == 4)
// Example: [1, 2, 3] == {json '{type: {class: "array", subtypes: [{class: "scalar", SQL_DESC_TYPE: 4}], SQL_DESC_TYPE: 89}, value: [{value: "1"}, {value: "2"}, {value: "3"}]}'}

[matthew-wozniczka]

For variant, we could disallow it (forcing a SQL_METDATA_CHANGED followed by SQLGetNestedHandle)

[matthew-wozniczka]

What to do about display size? Probably allow it to be inaccurate....

On a related note, I think we should be able to bind complex types as SQL_C_BINARY (even if not as string)

[matthew-wozniczka]

Another question: If struct/collection columns could be bound as string/binary, would they count as 'variable length types' for the purposes of allowing fetching in chunks?

I could see it being useful to stream large nested objects...

[matthew-wozniczka]

In fact, it could be useful to allow non-scalar types (at least structured types, although I can see it working for collection types as well) to be bound to 'scalar' custom-c-types (which are actually a c-struct, like SQL_C_SS_TIMESTAMPOFFSET)

[mikepizzo]

Would we require drivers to support at least binding to JSON (or CSV? or ??), or do we need a way the application can query for supported formats? What if the user doesn't specify; default to JSON (or CSV?) (seems arbitrary) or some default string like "Record", "Collection", etc.?