Getting Array base type information from DatabaseMetaData

[Lance Andersen]

HI all,

As you are probably aware,  DatabaseMetaData::getColumns is  originally based off of ODBC SQLColumns.

As I have not used ODBC since the late 90s, I am not sure what SQLColumns returns in the case of a SQL Array and the current docs are not clear.  Does anyone have experience with this?

In addition to DatabaseMetaData::getColumns, we probably have to also update DatabaseMetaData::getProcedureColumns and DatabaseMetaData::getFunctionColumns once we finalize the path forward.

EG members,  do any of your JDBC drivers have extensions for returning SQL Array information either via DatabaseMetaData::getColumns or an additional method?

Best
Lance

On Oct 6, 2021, at 3:21 AM, Lukas Eder <lukas.eder at gmail.com<mailto:lukas.eder at gmail.com>> wrote:

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<mailto: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><mailto: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><mailto:Lance.Andersen at oracle.com<mailto:Lance.Andersen at oracle.com>>




[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>