REST API: SQL Columns

Obtain a list of columns associated with the data source. The SQL Columns service implements the CA IDMS Server JDBC API method getColumns within the class IdmsDatabaseMetaData.
HTTP Method and URI Path
The URI path has the following format:
GET
<base url>
/sql/columns/
<datasource>
?
<query parameters>
Where:
  • <base URL>
    Specifies the base URL address for the CA IDMS REST API service
  • <datasource>
    (required) Identifies the CA IDMS system where the API request is to be sent and is defined in the datasources.yml file
  • <query parameters>
    (optional) The following parameters can be used to filter results. See "About Parameter Values" for usage information.
    • catalog -- Filter for the catalog name (Catalogs are not used within CA IDMS.)
    • columnPattern -- Filter pattern for the column name
    • schemaPattern -- Filter pattern for the schema name
    • tablePattern -- Filter pattern for the table name
By default, the Columns endpoint returns only columns that are accessible to the current user.
If you do not provide any patterns (for example, table, schema, or column) or if you provide only a column pattern, you may receive an error message indicating that there are too many results to be returned.
About Parameter Values:
  • The filtering parameters allow the wildcards
    %
    and
    _
    . Within a pattern string,
    %
    means match any substring of 0 or more characters, and
    _
    means match any one character. Only metadata entries matching the search pattern are returned. In the URL, the
    %
    must be encoded as
    %25
    . To see examples of ways to use wildcards in your request, see Examples for Specifying Patterns.
  • Parameter values are case sensitive.
Example: Returns the columns with the schema pattern "DEMOEMPL%"
https://zoshost.domain.com:10010/api/v1/sql/columns/SYSDEMO?schemaPattern=DEMOEMPL
Response:
{ "columns": [ { "tableCatalog": null, "tableSchema": "DEMOEMPL", "tableName": "BENEFITS", "columnName": "FISCAL_YEAR", "dataType": 2, "typeName": "UNSIGNED NUMERIC", "columnSize": 4, "bufferLength": 0, "decimalDigits": 0, "numPrecRadix": 10, "nullable": 0, "remarks": null, "columnDef": null, "sqlDataType": 0, "sqlDateTimeSub": 0, "charOctetLength": 4, "ordinalPosition": 0, "isNullable": "NO", "scopeCatalog": null, "scopeSchema": null, "scopeTable": null, "sourceDataType": 0, "isAutoIncrement": "NO" }, { "tableCatalog": null, "tableSchema": "DEMOEMPL", "tableName": "BENEFITS", "columnName": "EMP_ID", "dataType": 2, "typeName": "UNSIGNED NUMERIC", "columnSize": 4, "bufferLength": 0, "decimalDigits": 0, "numPrecRadix": 10, "nullable": 0, "remarks": null, "columnDef": null, "sqlDataType": 0, "sqlDateTimeSub": 0, "charOctetLength": 4, "ordinalPosition": 1, "isNullable": "NO", "scopeCatalog": null, "scopeSchema": null, "scopeTable": null, "sourceDataType": 0, "isAutoIncrement": "NO" }, { "tableCatalog": null, "tableSchema": "DEMOEMPL", "tableName": "BENEFITS", "columnName": "VAC_ACCRUED", "dataType": 3, "typeName": "UNSIGNED DECIMAL", "columnSize": 6, "bufferLength": 0, "decimalDigits": 2, "numPrecRadix": 10, "nullable": 0, "remarks": null, "columnDef": null, "sqlDataType": 0, "sqlDateTimeSub": 0, "charOctetLength": 4, "ordinalPosition": 2, "isNullable": "NO", "scopeCatalog": null, "scopeSchema": null, "scopeTable": null, "sourceDataType": 0, "isAutoIncrement": "NO" }, { "tableCatalog": null, "tableSchema": "DEMOEMPL", "tableName": "BENEFITS", "columnName": "VAC_TAKEN", "dataType": 3, "typeName": "UNSIGNED DECIMAL", "columnSize": 6, "bufferLength": 0, "decimalDigits": 2, "numPrecRadix": 10, "nullable": 0, "remarks": null, "columnDef": null, "sqlDataType": 0, "sqlDateTimeSub": 0, "charOctetLength": 4, "ordinalPosition": 3, "isNullable": "NO", "scopeCatalog": null, "scopeSchema": null, "scopeTable": null, "sourceDataType": 0, "isAutoIncrement": "NO" }, { "tableCatalog": null, "tableSchema": "DEMOEMPL", "tableName": "BENEFITS", "columnName": "SICK_ACCRUED", "dataType": 3, "typeName": "UNSIGNED DECIMAL", "columnSize": 6, "bufferLength": 0, "decimalDigits": 2, "numPrecRadix": 10, "nullable": 0, "remarks": null, "columnDef": null, "sqlDataType": 0, "sqlDateTimeSub": 0, "charOctetLength": 4, "ordinalPosition": 4, "isNullable": "NO", "scopeCatalog": null, "scopeSchema": null, "scopeTable": null, "sourceDataType": 0, "isAutoIncrement": "NO" }, { "tableCatalog": null, "tableSchema": "DEMOEMPL", "tableName": "BENEFITS", "columnName": "SICK_TAKEN", "dataType": 3, "typeName": "UNSIGNED DECIMAL", "columnSize": 6, "bufferLength": 0, "decimalDigits": 2, "numPrecRadix": 10, "nullable": 0, "remarks": null, "columnDef": null, "sqlDataType": 0, "sqlDateTimeSub": 0, "charOctetLength": 4, "ordinalPosition": 5, "isNullable": "NO", "scopeCatalog": null, "scopeSchema": null, "scopeTable": null, "sourceDataType": 0, "isAutoIncrement": "NO" } ... ], "errors": [] }
Example: Returns columns with the table pattern "BENEFITS"
https://zoshost.domain.com:10010/api/v1/sql/columns/SYSDEMO?tablePattern=BENEFITS
Response:
{ "columns": [ { "tableCatalog": null, "tableSchema": "DEMOEMPL", "tableName": "BENEFITS", "columnName": "FISCAL_YEAR", "dataType": 2, "typeName": "UNSIGNED NUMERIC", "columnSize": 4, "bufferLength": 0, "decimalDigits": 0, "numPrecRadix": 10, "nullable": 0, "remarks": null, "columnDef": null, "sqlDataType": 0, "sqlDateTimeSub": 0, "charOctetLength": 4, "ordinalPosition": 0, "isNullable": "NO", "scopeCatalog": null, "scopeSchema": null, "scopeTable": null, "sourceDataType": 0, "isAutoIncrement": "NO" }, { "tableCatalog": null, "tableSchema": "DEMOEMPL", "tableName": "BENEFITS", "columnName": "EMP_ID", "dataType": 2, "typeName": "UNSIGNED NUMERIC", "columnSize": 4, "bufferLength": 0, "decimalDigits": 0, "numPrecRadix": 10, "nullable": 0, "remarks": null, "columnDef": null, "sqlDataType": 0, "sqlDateTimeSub": 0, "charOctetLength": 4, "ordinalPosition": 1, "isNullable": "NO", "scopeCatalog": null, "scopeSchema": null, "scopeTable": null, "sourceDataType": 0, "isAutoIncrement": "NO" }, { "tableCatalog": null, "tableSchema": "DEMOEMPL", "tableName": "BENEFITS", "columnName": "VAC_ACCRUED", "dataType": 3, "typeName": "UNSIGNED DECIMAL", "columnSize": 6, "bufferLength": 0, "decimalDigits": 2, "numPrecRadix": 10, "nullable": 0, "remarks": null, "columnDef": null, "sqlDataType": 0, "sqlDateTimeSub": 0, "charOctetLength": 4, "ordinalPosition": 2, "isNullable": "NO", "scopeCatalog": null, "scopeSchema": null, "scopeTable": null, "sourceDataType": 0, "isAutoIncrement": "NO" }, { "tableCatalog": null, "tableSchema": "DEMOEMPL", "tableName": "BENEFITS", "columnName": "VAC_TAKEN", "dataType": 3, "typeName": "UNSIGNED DECIMAL", "columnSize": 6, "bufferLength": 0, "decimalDigits": 2, "numPrecRadix": 10, "nullable": 0, "remarks": null, "columnDef": null, "sqlDataType": 0, "sqlDateTimeSub": 0, "charOctetLength": 4, "ordinalPosition": 3, "isNullable": "NO", "scopeCatalog": null, "scopeSchema": null, "scopeTable": null, "sourceDataType": 0, "isAutoIncrement": "NO" }, { "tableCatalog": null, "tableSchema": "DEMOEMPL", "tableName": "BENEFITS", "columnName": "SICK_ACCRUED", "dataType": 3, "typeName": "UNSIGNED DECIMAL", "columnSize": 6, "bufferLength": 0, "decimalDigits": 2, "numPrecRadix": 10, "nullable": 0, "remarks": null, "columnDef": null, "sqlDataType": 0, "sqlDateTimeSub": 0, "charOctetLength": 4, "ordinalPosition": 4, "isNullable": "NO", "scopeCatalog": null, "scopeSchema": null, "scopeTable": null, "sourceDataType": 0, "isAutoIncrement": "NO" }, { "tableCatalog": null, "tableSchema": "DEMOEMPL", "tableName": "BENEFITS", "columnName": "SICK_TAKEN", "dataType": 3, "typeName": "UNSIGNED DECIMAL", "columnSize": 6, "bufferLength": 0, "decimalDigits": 2, "numPrecRadix": 10, "nullable": 0, "remarks": null, "columnDef": null, "sqlDataType": 0, "sqlDateTimeSub": 0, "charOctetLength": 4, "ordinalPosition": 5, "isNullable": "NO", "scopeCatalog": null, "scopeSchema": null, "scopeTable": null, "sourceDataType": 0, "isAutoIncrement": "NO" } ... ], "errors": [] }