Getting Array base type information from DatabaseMetaData

[Lukas Eder]

The SQL standard INFORMATION_SCHEMA.ELEMENT_TYPES table could help for
inspiration here. Some key features, which I recommend considering, include:

- Fully qualified array type name (important in e.g. Oracle, which has
nominally typed arrays, not so important in PostgreSQL, which has
structurally typed arrays). This is already provided by
DatabaseMetaData::getColumns in TYPE_NAME
- Fully qualified element type name (important in all dialects that support
UDTs, including Oracle and PostgreSQL)
- The usual type attributes, such as character length, character set,
collation, precision, scale for the element type

I hope this helps,
Lukas

On Tue, Oct 5, 2021 at 11:56 PM Lance Andersen <lance.andersen at oracle.com>
wrote:

> Hi Dave,
>
> There  indeed does not appear to be a way to return the actual type of the
> SQL Array via DatabaseMetaData.getColumns (most likely due to the lack of
> use of SQL Array at the time and it was an oversight).
>
>  As to what could be done, we could repurpose the SOURCE_DATA_TYPE column
> or add a 25th column called ARRAY_TYPE.
>
> It would probably be best to add the ARRAY_TYPE to the ResultSet returned
> from getColumns()
>
>
> Best
> Lance
>
> On Oct 1, 2021, at 9:12 AM, Dave Cramer <davecramer at gmail.com<mailto:
> davecramer at gmail.com>> wrote:
>
> Greetings,
>
> I'm looking at how to provide base type information for arrays from
> DatabaseMetaData
>
> getColumns has no way to return the base type. The best we can do is return
> Types.Array.
>
> Seems to me this is a gap. Am I missing something?
>
> Regards,
> Dave Cramer
>
> [cid:E1C4E2F0-ECD0-4C9D-ADB4-B16CA7BCB7FC at home]
>
>
>
> Lance Andersen| Principal Member of Technical Staff | +1.781.442.2037
> Oracle Java Engineering
> 1 Network Drive
> Burlington, MA 01803
> Lance.Andersen at oracle.com<mailto:Lance.Andersen at oracle.com>
>
>
>
>