Schema Discovery
You can invoke the List and List Columns operations to retrieve schema information, as described in the following sections.
You can also query the available System Tables to access all of the available connector metadata, which additionally includes information on data source capabilities and batch operation statistics.
Retrieving the Table Listing
Use the List operation to list the tables.
Visual Editor
To configure the operation in the Visual Editor, select the connector inside your flow and specify the following connector properties.
- Connector Configuration: Select a connection you created. See Establishing a Connection for more information.
- Operation: Choose List.
XML Editor
The XML below pulls the table listing into your flow:
<cdata-sapfieldglass:list-tables config-ref="CData_SAPFieldglass_Configuration" doc:name="CData SAPFieldglass"/>
Metadata Returned
The List operation returns a dataset with the following columns:
| Column Name | Data Type | Description |
| TABLE_CAT | String | The table catalog. |
| TABLE_SCHEM | String | The table schema. |
| TABLE_NAME | String | The table name. |
| TABLE_TYPE | String | The table type. |
| REMARKS | String | The table description. |
Retrieving Column Metadata
Use the ListColumns operation to retrieve column information. You can restrict the results by table name.
Visual Editor
To configure the operation in the Visual Editor, select the connector inside your flow and specify the following connector properties.
- Connector Configuration: Select a connection you created. See Establishing a Connection for more information.
- Operation: Choose List Columns.
- SQL query: Specify the table name or use a MEL expression to do so.
XML Editor
The XML below lists the columns for the AuditTrails table, based on the table specified by the #[flowVars.table] variable.
<cdata-sapfieldglass:list-columns config-ref="CData_SAPFieldglass_Configuration" doc:name="CData SAPFieldglass" table="#[flowVars.table]"/>
Metadata Returned
The List Columns operation returns a dataset with the following columns:
| Column Name | Data Type | Description |
| TABLE_CAT | String | The database name. |
| TABLE_SCHEM | String | The table schema. |
| TABLE_NAME | String | The table name. |
| COLUMN_NAME | String | The column name. |
| DATA_TYPE | int | The data type identified by the value of a constant defined in java.sql.Types. |
| TYPE_NAME | String | The data type name used by the connector. |
| COLUMN_SIZE | int | The length in characters of the column or the numeric precision. |
| BUFFER_LENGTH | int | The buffer length. |
| DECIMAL_DIGITS | int | The column scale or number of digits to the right of the decimal point. |
| NUM_PREC_RADIX | int | The radix, or base. |
| NULLABLE | int | Whether the column can contain null as defined by the following JDBC DatabaseMetaData constants: columnNoNulls (0) or columnNullable (1). |
| REMARKS | String | The column description. |
| COLUMN_DEF | String | The default value for the column. |
| SQL_DATA_TYPE | int | Reserved by the specification. |
| SQL_DATETIME_SUB | int | Reserved by the specification. |
| CHAR_OCTET_LENGTH | int | The maximum length of binary and character-based columns. |
| ORDINAL_POSITION | int | The column index, starting at 1. |
| IS_NULLABLE | String | Whether a null value is allowed: YES or NO. |
| SCOPE_CATALOG | String | The table catalog that is the scope of a reference attribute. |
| SCOPE_SCHEMA | String | The table schema that is the scope of a reference attribute. |
| SCOPE_TABLE | String | The table name that is the scope of a reference attribute. |
| SOURCE_DATA_TYPE | int | The source type of a distinct type. Or, a user-generated Ref type. If DATA_TYPE is not DISTINCT, this value is null. If a user-generated Ref, this value is null. |
| IS_AUTOINCREMENT | String | Whether the column value is assigned by SAP Fieldglass in fixed increments. |
| IS_GENERATEDCOLUMN | String | Whether the column is generated: YES or NO. |
| ISREADONLY | boolean | Whether the column is read-only. |
| ISKEY | boolean | Whether the column is a key. |