Skip to content
This repository has been archived by the owner on Aug 2, 2022. It is now read-only.

DESCRIBE query enhancements #325

Open
lyndonbauto opened this issue Dec 20, 2019 · 1 comment
Open

DESCRIBE query enhancements #325

lyndonbauto opened this issue Dec 20, 2019 · 1 comment
Labels
JDBC/ODBC formatting This issue is related to JDBC or ODBC driver client SQL

Comments

@lyndonbauto
Copy link
Contributor

Having additional metadeta provided about columns when executing DESCRIBE queries would be beneficial for ODBC drivers.

The following fields are NULL and cannot be NULL based on SQLColumns spec from msdn:

  • DATA_TYPE
  • SQL_DATA_TYPE
  • SQL_DATETIME_SUB (when returning a date)

So having values for this metadata would be ideal. Though, an ODBC driver could extrapolate the DATA_TYPE and SQL_DATA_TYPE from the TYPE_NAME.

Fields that I think would provide the biggest enhancements are:

  • COLUMN_SIZE
  • BUFFER_LENGTH
  • COLUMN_DEF

Aside from adding additional metadata, adding support to specify a catalog in the query would also provide improved usability.

For reference, currently using the query 'DESCRIBE TABLES LIKE kibana_sample_data_flights COLUMNS LIKE FlightDelay' returns the following column metadata:

{
  "schema": [
    {
      "name": "TABLE_CAT",
      "type": "keyword"
    },
    {
      "name": "TABLE_SCHEM",
      "type": "keyword"
    },
    {
      "name": "TABLE_NAME",
      "type": "keyword"
    },
    {
      "name": "COLUMN_NAME",
      "type": "keyword"
    },
    {
      "name": "DATA_TYPE",
      "type": "integer"
    },
    {
      "name": "TYPE_NAME",
      "type": "keyword"
    },
    {
      "name": "COLUMN_SIZE",
      "type": "integer"
    },
    {
      "name": "BUFFER_LENGTH",
      "type": "integer"
    },
    {
      "name": "DECIMAL_DIGITS",
      "type": "integer"
    },
    {
      "name": "NUM_PREC_RADIX",
      "type": "integer"
    },
    {
      "name": "NULLABLE",
      "type": "integer"
    },
    {
      "name": "REMARKS",
      "type": "keyword"
    },
    {
      "name": "COLUMN_DEF",
      "type": "keyword"
    },
    {
      "name": "SQL_DATA_TYPE",
      "type": "integer"
    },
    {
      "name": "SQL_DATETIME_SUB",
      "type": "integer"
    },
    {
      "name": "CHAR_OCTET_LENGTH",
      "type": "integer"
    },
    {
      "name": "ORDINAL_POSITION",
      "type": "integer"
    },
    {
      "name": "IS_NULLABLE",
      "type": "keyword"
    },
    {
      "name": "SCOPE_CATALOG",
      "type": "keyword"
    },
    {
      "name": "SCOPE_SCHEMA",
      "type": "keyword"
    },
    {
      "name": "SCOPE_TABLE",
      "type": "keyword"
    },
    {
      "name": "SOURCE_DATA_TYPE",
      "type": "short"
    },
    {
      "name": "IS_AUTOINCREMENT",
      "type": "keyword"
    },
    {
      "name": "IS_GENERATEDCOLUMN",
      "type": "keyword"
    }
  ],
  "total": 1,
  "datarows": [[
    "odfe-cluster",
    null,
    "kibana_sample_data_flights",
    "FlightDelayMin",
    null,
    "integer",
    null,
    null,
    null,
    10,
    2,
    null,
    null,
    null,
    null,
    null,
    1,
    "",
    null,
    null,
    null,
    null,
    "NO",
    ""
  ]],
  "size": 1,
  "status": 200
}
@dai-chen dai-chen added the JDBC/ODBC formatting This issue is related to JDBC or ODBC driver client label Jan 9, 2020
@dai-chen
Copy link
Member

dai-chen commented Mar 6, 2020

The meaning of the columns in ODBC specs:

  1. DATA_TYPE: SQL data type. This can be an ODBC SQL data type or a driver-specific SQL data type.
  2. SQL_DATA_TYPE: This column is the same as the DATA_TYPE column, except for datetime and interval data types. This column returns the nonconcise data type (such as SQL_DATETIME or SQL_INTERVAL), instead of the concise data type (such as SQL_TYPE_DATE or SQL_INTERVAL_YEAR_TO_MONTH) for datetime and interval data types. If this column returns SQL_DATETIME or SQL_INTERVAL, the specific data type can be determined from the SQL_DATETIME_SUB column.
  3. SQL_DATETIME_SUB (when returning a date): The subtype code for datetime and interval data types. For other data types, this column returns a NULL.
  4. COLUMN_SIZE: If DATA_TYPE is SQL_CHAR or SQL_VARCHAR, this column contains the maximum length in characters of the column. For datetime data types, this is the total number of characters required to display the value when it is converted to characters. For numeric data types, this is either the total number of digits or the total number of bits allowed in the column, according to the NUM_PREC_RADIX column.
  5. BUFFER_LENGTH: The length in bytes of data transferred on an SQLGetData, SQLFetch, or SQLFetchScroll operation if SQL_C_DEFAULT is specified. For numeric data, this size may differ from the size of the data stored on the data source. This value might differ from COLUMN_SIZE column for character data.
  6. COLUMN_DEF: The default value of the column. The value in this column should be interpreted as a string if it is enclosed in quotation marks.

@dai-chen dai-chen mentioned this issue Jul 10, 2020
Closed
@dai-chen dai-chen added the SQL label Sep 11, 2020
@dai-chen dai-chen mentioned this issue Apr 18, 2022
Closed
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
JDBC/ODBC formatting This issue is related to JDBC or ODBC driver client SQL
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@dai-chen @lyndonbauto