How does client determine the type of elements of an array?

[mikepizzo]

How does the client discover the type of the elements of the array? Do we overload either DATA_TYPE or SQL_DATA_TYPE so that one indicates an array and the other indicates the type of the array? ANSI uses DTD_IDENTIFIER?

Should we rename SQL_DATETIMESUB in SQLColumns to SQL SUBTYPE (or some such?) and use that for type of ARRAY/MULTISET? What if the type in the array is a DateTime; how would we represent the subtype?

Maybe use a macro, like type+10000 to be an array of the specified type?

What about arrays of arrays? ANSI syntax defines < array type > as < data type > ARRAY, and < collection type >, which includes < array type > and < multiset type >, is a valid < data type >. So they do appear to support arrays of arrays. Native JSON certainly represents arrays of arrays. May have to leverage a string value and provide a format like Array(Array(Array(SQL_VARCHAR))), or a column to specify the depth of an array?

ANSI uses nested descriptors to define the type of an array column in the result. This doesn’t help schema case. ANSI also has a CARDINALITY descriptor field (and col in SQLColumns) to specify the maximum cardinality of an array (not used for multiset).

[mikepizzo]

Ideas: 1) Use DATA_TYPE as SQL_ARRAY or SQL_MULTISET, and the rest of the properties (including SQL_DATA_TYPE) to describe the type of the array. ==>Simple, and works for everything except nested arrays. 2) Use a macro, like type+(10000*rank)? ==>Fragments type space and not intuitive 3) Encode in TYPE_NAME, i.e.; Array(Array(Array(SQL_VARCHAR))) ==>Parsing type name is inelegant, at best 4) Add a "Rank" or "Dimensions" column to the SQLColumns result set ==>would have to be after the last ISO SQL/CLI defined column to be compatible with ISO 5) Provide a path syntax to pass into the ColumnName of SQLColumns to drill in on membership, i.e. PhoneNumbers/$element ==>The name of the returned column would be the name of the column suffixed with /$element, and would support multiple levels; i.e., TableEntry/$element/$element

[mikepizzo]

Best option may be combination of 1) and 5).

For the simple case (arrays of primitive or structured types) this is the simplest. For nested arrays you can use the columnname/$element syntax to get the type of the element.

[mikepizzo]

If we pursue this, what syntax should we use to differentiate an element from the column? Is there a syntax we could use that wouldn't conflict with a possible column name?

[mikepizzo]

Suffix the column name with open/close brackets [].