JDBC Driver - DatabaseMetaData - Advanced

The object DatabaseMetadata of the Polypheny JDBC driver provides methods to query properties of the DBMS, properties of namespaces as well as the supported functions of the driver and Polypheny.

Some of the methods return more complex metadata in the form of ResultSets. For example, a list of all tables or all supported data types including their properties can be queried. These are explained in this section.

getProcedures

ResultSet getProcedures( String catalog,
                         String schemaPattern,
                         String procedureNamePattern )
                 throws SQLException

Retrieves a description of the stored procedures available in DBMS.

Only procedure descriptions matching the schema and procedure name criteria are returned. They are ordered by their properties PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME, and SPECIFIC_NAME.

Name Type Description
PROCEDURE_CAT String Procedure catalog (always null)
PROCEDURE_SCHEM String Procedure schema (always null)
PROCEDURE_NAME String Procedure name
(reserved)   Reserved for future use (always null)
(reserved)   Reserved for future use (always null)
(reserved)   Reserved for future use (always null)
REMARKS String Explanatory comment on the procedure
PROCEDURE_TYPE short Kind of procedure (See table below)
SPECIFIC_NAME String The name which uniquely identifies this procedure within its schema

Procedure Type Values:

Value Description
procedureResultUnknown Cannot determine if a return value will be returned
procedureNoResult Does not return a return value
procedureReturnsResult Returns a return value

Note: A user may not have permissions to execute any of the procedures that are returned by getProcedures.

Parameters

Name Type Description
catalog String A catalog name; this parameter is ignored. Although, in the database, "" retrieves those without a catalog; null means no narrowing by catalog.
schemaPattern String A schema name pattern; must match the schema name as it is stored in the database. "" retrieves those without a schema; null means no narrowing by schema.
procedureNamePattern String A procedure name pattern; must match the procedure name as it is stored in the database.

Returns: ResultSet - each row is a procedure description

Throws: SQLException - if a database access error occurs

getProcedureColumns

ResultSet getProcedureColumns( String catalog,
                               String schemaPattern,
                               String procedureNamePattern,
                               String columnNamePattern )
                       throws SQLException
This method currently throws an error if the driver is in strict mode. If not, it always returns an empty result set.

Retrieves a description of the stored procedure parameter and result columns. The catalog name is ignored.

Only descriptions matching the schema, procedure, and parameter name criteria are returned. They are ordered by PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME, and SPECIFIC_NAME. The return value, if any, is first, followed by parameter descriptions in call order and then by column descriptions in column number order.

Columns:

Name Type Description
PROCEDURE_CAT String Procedure catalog (may be null)
PROCEDURE_SCHEM String Procedure schema (may be null)
PROCEDURE_NAME String Procedure name
COLUMN_NAME String Column/parameter name
COLUMN_TYPE Short Kind of column/parameter (See the Column Type Values table below)
DATA_TYPE int SQL type from java.sql.Types
TYPE_NAME String SQL type name, for a UDT type the type name is fully qualified
PRECISION int Precision
LENGTH int Length in bytes of data
SCALE short Scale - null for data types where SCALE isn’t applicable
RADIX short Radix
NULLABLE short Can it contain NULL (See the Nullable Values table below)
REMARKS String Comment describing parameter/column
COLUMN_DEF String Default value for the column (See the Column Default Values explanation below)
SQL_DATA_TYPE int Reserved for future use
SQL_DATETIME_SUB int Reserved for future use
CHAR_OCTET_LENGTH int Max length of binary and character-based columns; NULL for other datatypes
ORDINAL_POSITION int Ordinal position of parameters and columns
IS_NULLABLE String Nullability of a column (See the ISO Nullability Values table below)
SPECIFIC_NAME String Name uniquely identifying this procedure within its schema

Column Type Values:

Value Description
procedureColumnUnknown Nobody knows
procedureColumnIn IN parameter
procedureColumnInOut INOUT parameter
procedureColumnOut OUT parameter
procedureColumnReturn Procedure return value
procedureColumnResult Result column in ResultSet

Nullable Values:

Value Description
procedureNoNulls Does not allow NULL values
procedureNullable Allows NULL values
procedureNullableUnknown Nullability unknown

Column Default Values:

This column contains information about the default value assigned to the procedure’s parameter or column. When a procedure is invoked or a row is inserted into a table without a specific value for this column/parameter, the system will use the default value specified in COLUMN_DEF. Specifics:

  • Enclosed in Single Quotes: If the default value for the column is enclosed in single quotes, it should be interpreted as a string. For example, if the default value is ‘Hello’, then the procedure or table will use the string “Hello” when no value is provided during invocation or insertion.

  • “NULL” (not enclosed in quotes): This represents that the specified default value for the column is NULL. That is, if no value is provided for this column during an operation, it will be set to NULL.

  • “TRUNCATE” (not enclosed in quotes): This indicates that the specified default value cannot be represented without truncation. This might be used in certain situations where the actual default value is too long to be stored fully, so a truncated version or a representation of the value is stored instead.

  • NULL Value: If the COLUMN_DEF itself has a value of NULL, it implies that a default value was not specified for this column/parameter. Hence, when the procedure is invoked or a row is inserted without a specific value for this column, there won’t be any default value to fall back on.

Parameters:

Name Type Description
catalog String A catalog name; ignored by the driver matches the catalog name in the database. "" retrieves those without a catalog; null means the catalog name should not be used to narrow the search.
schemaPattern String A schema name pattern; matches the schema name in the database. "" retrieves those without a schema; null means the schema name should not be used to narrow the search.
procedureNamePattern String A procedure name pattern; matches the procedure name in the database.
columnNamePattern String A column name pattern; matches the column name in the database.

Returns:

  • ResultSet - each row describes a stored procedure parameter or column.

Throws:

  • SQLException - if a database access error occurs.

getTables

ResultSet getTables( String catalog, 
                     String schemaPattern, 
                     String tableNamePattern, 
                     String[] types )
             throws SQLException

Retrieves a description of the tables available in the specified catalog. Descriptions are returned only if they match the schema, table name, and type criteria. The results are ordered by TABLE_TYPE, TABLE_CAT, TABLE_SCHEM, and TABLE_NAME.

Name Type Description
TABLE_CAT String Table catalog
TABLE_SCHEM String Table schema
TABLE_NAME String Table name
TABLE_TYPE String Table type
REMARKS String Explanatory comment on the table (always "")
TYPE_CAT String Types catalog (always null)
TYPE_SCHEM String Types schema (always null)
TYPE_NAME String Type name (always null)
SELF_REFERENCING_COL_NAME String Name of the designated “identifier” column of a typed table (always null)
REF_GENERATION String Specifies how values in SELF_REFERENCING_COL_NAME are created (always null)
OWNER String Owner of the table

Parameters:

Name Type Description
catalog String A catalog name; ignored by the driver. Must match the catalog name as it’s stored in the database. "" retrieves those without a catalog.
schemaPattern String A schema name pattern; must match the schema name as it’s stored in the database. "" retrieves those without a schema; null means no narrowing by schema.
tableNamePattern String A table name pattern; must match the table name as stored in the database.
types String[] A list of table types, which must be from the list of table types returned from getTableTypes(), to include; null returns all types.

Returns:

  • ResultSet - Each row describes a table.

Throws:

  • SQLException - In case of a database access error.

getSchemas

ResultSet getSchemas() throws SQLException

Retrieves the namespaces available in this database. The results are ordered by TABLE_CATALOG and TABLE_SCHEM.

Name Type Description
TABLE_SCHEM String Schema name
TABLE_CATALOG String Catalog name (may be null)
OWNER String Name of the owner of the namespace
SCHEMA_TYPE String Type of the schema, one of “RELATIONAL”, “DOCUMENT”, “GRAPH” (can be null)

Returns:

  • ResultSet - Each row describes a schema.

Throws:

  • SQLException - In case of a database access error.

getCatalogs

ResultSet getCatalogs() throws SQLException

Retrieves the databases available in this database. The results are ordered by database name.

Name Type Description
TABLE_CAT String Database name
OWNER String Name of the owner of the catalog
DEFAULT_SCHEMA String Default schema name for the given database

Returns:

  • ResultSet - Each row describes a catalog.

Throws:

  • SQLException - In case of a database access error.

getTableTypes

ResultSet getTableTypes() throws SQLException

Retrieves the table types available in this database. The results are ordered by table type.

Name Type Description
TABLE_TYPE String Table type. The available types in this implementation are “ENTITY”, “SOURCE”, “VIEW”, and “MATERIALIZED_VIEW”.

Returns:

  • ResultSet - Each row describes a table type.

Throws:

  • SQLException - In case of a database access error.

getColumns

ResultSet getColumns( String catalog,
                      String schemaPattern, 
                      String tableNamePattern, 
                      String columnNamePattern ) 
				throws SQLException

Retrieves a description of table columns available in the specified catalog, based on the provided patterns. The results are ordered by TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and ORDINAL_POSITION.

Name Type Description
TABLE_CAT String Table catalog (may be null)
TABLE_SCHEM String Table schema (may be null)
TABLE_NAME String Table name
COLUMN_NAME String Column name
DATA_TYPE int SQL type from java.sql.Types
TYPE_NAME String Data source dependent type name
COLUMN_SIZE int Column size (See note below)
BUFFER_LENGTH int Not used (always null)
DECIMAL_DIGITS int Number of fractional digits (Null for some data types)
NUM_PREC_RADIX int Radix (always null)
NULLABLE int Indicating if NULL is allowed
REMARKS String Comment describing column (always "")
COLUMN_DEF String Default value for the column
SQL_DATA_TYPE int Unused (always null)
SQL_DATETIME_SUB int Unused (always null)
CHAR_OCTET_LENGTH int For char types, max number of bytes in the column (always null)
ORDINAL_POSITION int Index of column in table (starting at 1)
IS_NULLABLE String ISO rules for column nullability
SCOPE_CATALOG String Relevant for reference attributes (Otherwise null)
SCOPE_SCHEMA String Relevant for reference attributes (Otherwise null)
SCOPE_TABLE String Relevant for reference attributes (Otherwise null)
SOURCE_DATA_TYPE short Source type for distinct/user-generated Ref types from java.sql.Types (always null)
IS_AUTOINCREMENT String Indicates if the column is auto incremented (always No)
IS_GENERATEDCOLUMN String Indicates if the column is generated (always No)
COLLATION String Specifies the name of the enum whos values are stored in this column (null if not applicable)

Note: For COLUMN_SIZE, for numeric data, it refers to the maximum precision. For character data, it’s the length in characters. For datetime datatypes, it’s the length in characters of the String representation. For binary data, it’s the length in bytes. For the ROWID datatype, it’s the length in bytes. Null is returned for data types where the column size is not applicable.

Returns:

  • ResultSet - Each row describes a column description.

Throws:

  • SQLException - In case of a database access error.

getColumnPrivileges

ResultSet getColumnPrivileges( String catalog,
                               String schema,
                               String table,
                               String columnNamePattern )
                       throws SQLException
This method throws an exception in strict mode. Otherwise, a dummy result set representing full rights is returned.

Obtains the description of the access rights for a table’s columns.

Name Type Description
TABLE_CAT String Table catalog (may be null)
TABLE_SCHEM String Table schema (may be null)
TABLE_NAME String Table name
COLUMN_NAME String Column name
GRANTOR String Granter of the access (may be null)
GRANTEE String Grantee of access
PRIVILEGE String Type of access (e.g., SELECT, INSERT, UPDATE, REFERENCES, …)
IS_GRANTABLE String “YES” if grantee can grant to others, “NO” if not, null if unknown

Parameters:

Parameter Type Description
catalog String The catalog name (ignored by the driver); matches the name in the database. Empty string for none. null to avoid narrowing by catalog.
schema String Matches the name in the database. Empty string for none. null to avoid narrowing by schema.
table String Matches the name in the database.
columnNamePattern String Matches the column name in the database.

Returns:

  • ResultSet - Each row provides a description of a column privilege.

Throws:

  • SQLException - In case of a database access error.

getTablePrivileges

ResultSet getTablePrivileges( String catalog,
                              String schemaPattern,
                              String tableNamePattern )
                      throws SQLException
This method throws an exception in strict mode. If not in strict mode, a full rights result set will be returned.

Obtains a description of the access rights for each table available in the database.

The privileges that match the schema and table name criteria are the only ones that get returned. The return order is by TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and PRIVILEGE.

Name Type Description
TABLE_CAT String Table catalog (may be null)
TABLE_SCHEM String Table schema (may be null)
TABLE_NAME String Table name
GRANTOR String The entity granting the access (may be null)
GRANTEE String The entity granted the access
PRIVILEGE String Type of access (e.g., SELECT, INSERT, UPDATE, REFERENCES, …)
IS_GRANTABLE String “YES” if the grantee can offer access to others, “NO” if not, null if undetermined

Parameters:

Parameter Type Description
catalog String The catalog name; however, this parameter is ignored. Matches the database name. An empty string fetches those without a catalog. Use null to avoid narrowing down by catalog.
schemaPattern String Matches the schema name in the database. An empty string retrieves those without a schema. Use null to avoid narrowing down by schema.
tableNamePattern String Matches the table name in the database.

Returns:

  • ResultSet - Each row provides a table privilege description.

Throws:

  • SQLException - In case of a database access error.

See Also:

  • getSearchStringEscape()

getBestRowIdentifier

ResultSet getBestRowIdentifier( String catalog,
                                String schema,
                                String table,
                                int scope,
                                boolean nullable )
                        throws SQLException

This function retrieves a description of the optimal set of columns of a table that uniquely identify a row. The results are ordered by SCOPE.

Each column description has the following fields:

Name Type Description
SCOPE short Actual scope of the result:
bestRowTemporary - very temporary, while using row
bestRowTransaction - valid for the remainder of the current transaction
bestRowSession - valid for the remainder of the current session
(always returns bestRowSession)
COLUMN_NAME String Column name
DATA_TYPE int SQL data type from java.sql.Types
TYPE_NAME String Data source dependent type name. For UDTs, the type name is fully qualified
COLUMN_SIZE int Specifies column size. See note below for detailed explanation.
BUFFER_LENGTH int Not used; always null
DECIMAL_DIGITS short Scale. Null is returned for types where DECIMAL_DIGITS isn’t applicable
PSEUDO_COLUMN short Specifies if it’s a pseudo column like Oracle’s ROWID:
bestRowUnknown - may or may not be a pseudo column
bestRowNotPseudo - is NOT a pseudo column
bestRowPseudo - is a pseudo column

Note: The COLUMN_SIZE column details the designated column size for the given column. For numerical data, it’s the maximum precision. For character data, it’s the length in characters. For datetime data types, it’s the length in characters of the String representation (assuming the max allowed precision of fractional seconds). For binary data, it’s the length in bytes. For the ROWID datatype, it’s the length in bytes. Null is returned where the column size isn’t applicable.

Parameters:

Parameter Type Description
catalog String Matches the database’s catalog name. This parameter is ignored. An empty string retrieves those without a catalog. null avoids narrowing down by catalog.
schema String Matches the database’s schema name. An empty string retrieves those without a schema. null avoids narrowing down by schema.
table String Matches the table name in the database.
scope int Defines the scope of interest. This parameter is ignored. Uses the same values as SCOPE.
nullable boolean If true, includes columns that are nullable.

Returns:

  • ResultSet - Each row provides a column description.

Throws:

  • SQLException - In case of a database access error.

getVersionColumns

ResultSet getVersionColumns( String catalog,
                            String schema,
                            String table )
                    throws SQLException

This function retrieves a description of a table’s columns that are automatically updated whenever any value in a row is updated. These columns are unordered.

Each column description has the following fields:

Name Type Description
SCOPE short Not used; always null
COLUMN_NAME String Column name
DATA_TYPE int SQL data type from java.sql.Types
TYPE_NAME String Data source dependent type name
COLUMN_SIZE int Specifies column size. See note below for detailed explanation.
BUFFER_LENGTH int Length of column value in bytes; always null
DECIMAL_DIGITS short Scale. Null is returned for types where DECIMAL_DIGITS isn’t applicable
PSEUDO_COLUMN short Specifies if it’s a pseudo column like Oracle’s ROWID:
versionColumnUnknown - may or may not be a pseudo column
versionColumnNotPseudo - is NOT a pseudo column
versionColumnPseudo - is a pseudo column

Note: The COLUMN_SIZE column indicates the designated column size for the given column. For numerical data, it represents the maximum precision. For character data, it indicates the length in characters. For datetime datatypes, it is the length in characters of the String representation (assuming the max allowed precision of fractional seconds). For binary data, it denotes the length in bytes. For the ROWID datatype, it’s the length in bytes. Null is returned where the column size isn’t applicable.

Parameters:

Parameter Type Description
catalog String Matches the database’s catalog name. This parameter is ignored. An empty string retrieves those without a catalog. null indicates not using the catalog name to narrow the search.
schema String Matches the database’s schema name. An empty string retrieves those without a schema. null indicates not using the schema name to narrow the search.
table String Matches the table name as stored in the database.

Returns:

  • ResultSet - Each row in this ResultSet object is a column description.

Throws:

  • SQLException - In case of a database access error.

getPrimaryKeys

ResultSet getPrimaryKeys( String catalog,
                          String schema,
                          String table )
                  throws SQLException

This function retrieves a description of the provided table’s primary key columns. These columns are ordered by COLUMN_NAME.

Each primary key column description has the following fields:

Name Type Description
TABLE_CAT String Table catalog (may be null)
TABLE_SCHEM String Table schema (may be null)
TABLE_NAME String Table name
COLUMN_NAME String Column name
KEY_SEQ short Sequence number within the primary key. E.g., a value of 1 represents the first column of the primary key. A value of 2 would indicate the second column within the primary key.
PK_NAME String Primary key name. This is always null.

Parameters:

Parameter Type Description
catalog String Matches the database’s catalog name. An empty string retrieves those without a catalog. null indicates not using the catalog name to narrow the search.
schema String Matches the database’s schema name. An empty string retrieves those without a schema. null indicates not using the schema name to narrow the search.
table String Matches the table name as stored in the database.

Returns:

  • ResultSet - Each row in this ResultSet object is a primary key column description.

Throws:

  • SQLException - In case of a database access error.

getImportedKeys

ResultSet getImportedKeys( String catalog,
                           String schema,
                           String table )
                   throws SQLException

This method retrieves descriptions of the primary key columns that are referenced by the specified table’s foreign key columns, i.e., the primary keys imported by the table. The results are ordered by PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, and KEY_SEQ.

Each primary key column description consists of:

Name Type Description
PKTABLE_CAT String Primary key table catalog being imported (may be null)
PKTABLE_SCHEM String Primary key table schema being imported (may be null)
PKTABLE_NAME String Primary key table name being imported
PKCOLUMN_NAME String Primary key column name being imported
FKTABLE_CAT String Foreign key table catalog (may be null)
FKTABLE_SCHEM String Foreign key table schema (may be null)
FKTABLE_NAME String Foreign key table name
FKCOLUMN_NAME String Foreign key column name
KEY_SEQ short Sequence number within a foreign key (e.g., a value of 1 represents the first column of the foreign key)
UPDATE_RULE short Behavior when the primary key is updated. Possible values: importedNoAction, importedKeyCascade, importedKeySetNull, importedKeySetDefault, importedKeyRestrict
DELETE_RULE short Behavior when the primary key is deleted. Possible values: importedKeyNoAction, importedKeyCascade, importedKeySetNull, importedKeySetDefault, importedKeyRestrict
FK_NAME String Foreign key name (may be null)
PK_NAME String Primary key name (may be null)
DEFERRABILITY short Defines if the evaluation of foreign key constraints can be deferred until commit. Possible values: importedKeyInitiallyDeferred, importedKeyInitiallyImmediate, importedKeyNotDeferrable

Parameters:

Parameter Type Description
catalog String Matches the catalog name as stored in the database (ignored); "" retrieves those without a catalog; null means the catalog name shouldn’t be used in the search.
schema String Matches the schema name as stored in the database; "" retrieves those without a schema; null indicates not using the schema name in the search.
table String Matches the table name as it’s stored in the database.

Returns:

  • ResultSet - Each row in this ResultSet object is a description of primary key columns that are referenced by the given table’s foreign key columns.

Throws:

  • SQLException - In case a database access error occurs.

See Also:

  • getExportedKeys( java.lang.String, java.lang.String, java.lang.String )

getExportedKeys

ResultSet getExportedKeys( String catalog,
                           String schema,
                           String table )
                   throws SQLException

This method retrieves descriptions of the foreign key columns that reference the specified table’s primary key columns (i.e., the foreign keys exported by the table). The results are ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and KEY_SEQ.

Each foreign key column description consists of:

Name Type Description
PKTABLE_CAT String Primary key table catalog (may be null)
PKTABLE_SCHEM String Primary key table schema (may be null)
PKTABLE_NAME String Primary key table name
PKCOLUMN_NAME String Primary key column name
FKTABLE_CAT String Foreign key table catalog being exported (may be null)
FKTABLE_SCHEM String Foreign key table schema being exported (may be null)
FKTABLE_NAME String Foreign key table name being exported
FKCOLUMN_NAME String Foreign key column name being exported
KEY_SEQ short Sequence number within a foreign key (e.g., a value of 1 represents the first column of the foreign key)
UPDATE_RULE short Defines behavior when the primary key is updated. Possible values: importedNoAction, importedKeyCascade, importedKeySetNull, importedKeySetDefault, importedKeyRestrict
DELETE_RULE short Behavior when the primary key is deleted. Possible values: importedKeyNoAction, importedKeyCascade, importedKeySetNull, importedKeySetDefault, importedKeyRestrict
FK_NAME String Foreign key name (may be null)
PK_NAME String Primary key name (may be null)
DEFERRABILITY short Specifies if the evaluation of foreign key constraints can be deferred until commit. Possible values: importedKeyInitiallyDeferred, importedKeyInitiallyImmediate, importedKeyNotDeferrable

Parameters:

Parameter Type Description
catalog String Matches the catalog name as stored in the database. Note: This parameter is ignored. "" retrieves those without a catalog; null means the catalog name shouldn’t be used in the search.
schema String Matches the schema name as stored in the database; "" retrieves those without a schema; null indicates not using the schema name in the search.
table String Matches the table name as it’s stored in the database.

Returns:

  • ResultSet - Each row in this ResultSet object provides a description of a foreign key column that references the specified table’s primary key columns.

Throws:

  • SQLException - In case a database access error occurs.

getCrossReference

ResultSet getCrossReference( String parentCatalog,
                             String parentSchema,
                             String parentTable,
                             String foreignCatalog,
                             String foreignSchema,
                             String foreignTable )
                     throws SQLException

This method retrieves a description of foreign key columns in a specified foreign key table. These columns reference the primary key or columns representing a unique constraint of the parent table. The number of columns from the parent table should correspond to the number of columns that constitute the foreign key. Results are ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and KEY_SEQ.

Each foreign key column description comprises:

Name Type Description
PKTABLE_CAT String Parent key table catalog (may be null)
PKTABLE_SCHEM String Parent key table schema (may be null)
PKTABLE_NAME String Parent key table name
PKCOLUMN_NAME String Parent key column name
FKTABLE_CAT String Foreign key table catalog (may be null)
FKTABLE_SCHEM String Foreign key table schema (may be null)
FKTABLE_NAME String Foreign key table name
FKCOLUMN_NAME String Foreign key column name
KEY_SEQ short Sequence number within foreign key
UPDATE_RULE short Behavior on parent key update: importedNoAction, importedKeyCascade, importedKeySetNull, importedKeySetDefault, importedKeyRestrict
DELETE_RULE short Behavior on parent key deletion: importedKeyNoAction, importedKeyCascade, importedKeySetNull, importedKeySetDefault, importedKeyRestrict
FK_NAME String Foreign key name (may be null)
PK_NAME String Parent key name (may be null)
DEFERRABILITY short Evaluation deferral for foreign key constraints: importedKeyInitiallyDeferred, importedKeyInitiallyImmediate, importedKeyNotDeferrable

Parameters:

Parameter Type Description
parentCatalog String Parent catalog name as stored in the database (ignored). "" retrieves those without a catalog; null means the catalog name will be dropped from the selection criteria.
parentSchema String Parent schema name as stored in the database; "" retrieves those without a schema; null indicates the schema name should be dropped from the selection criteria.
parentTable String Name of the table exporting the key, must match as stored in the database.
foreignCatalog String Foreign catalog name as stored in the database (ignored). "" retrieves those without a catalog; null means the catalog name will be dropped from the selection criteria.
foreignSchema String Foreign schema name as stored in the database; "" retrieves those without a schema; null indicates the schema name should be dropped from the selection criteria.
foreignTable String Name of the table importing the key, must match as stored in the database.

Returns:

  • ResultSet - Each row in this ResultSet object provides a description of a foreign key column in relation to the specified parent table.

Throws:

  • SQLException - In case a database access error occurs.

getTypeInfo

ResultSet getTypeInfo() throws SQLException

This method fetches a description of all the data types that the database supports. The results are organized by DATA_TYPE. Each type description consists of:

Name Type Description
TYPE_NAME String The name of the type.
DATA_TYPE int SQL data type derived from java.sql.Types.
PRECISION int Maximum precision. Pertains to numeric data, character data, datetime types, binary data, and ROWID datatype. Null is returned for data types where the column size is not relevant.
LITERAL_PREFIX String Prefix to quote a literal (may be null).
LITERAL_SUFFIX String Suffix to quote a literal (may be null).
CREATE_PARAMS String Always returns null.
NULLABLE short Describes if NULL can be utilized for this type: typeNoNulls, typeNullable, typeNullableUnknown.
CASE_SENSITIVE boolean Indicates if the type is case sensitive.
SEARCHABLE short Describes if “WHERE” can be based on this type: typePredNone, typePredChar, typePredBasic, typeSearchable.
UNSIGNED_ATTRIBUTE boolean Always returns false.
FIXED_PREC_SCALE boolean Always returns false.
AUTO_INCREMENT boolean Checks if it can be used for an auto-increment value.
LOCAL_TYPE_NAME String Localized variant of type name (may be null).
MINIMUM_SCALE short The minimal scale supported.
MAXIMUM_SCALE short The maximal scale supported.
SQL_DATA_TYPE int Always returns null.
SQL_DATETIME_SUB int Always returns null.
NUM_PREC_RADIX int Typically 2 or 10.

Returns:

  • ResultSet - Every row in this ResultSet object contains information about a single SQL type.

Throws:

  • SQLException - Thrown in the event of a database access error.

getIndexInfo

ResultSet getIndexInfo( String catalog,
                        String schema,
                        String table,
                        boolean unique,
                        boolean approximate )
                throws SQLException

This method retrieves information about the indices and statistics of a given table. The results are ordered by the criteria NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.

Each column in the index description has:

Name Type Description
TABLE_CAT String Table’s catalog (can be null).
TABLE_SCHEM String Schema of the table (can be null).
TABLE_NAME String Name of the table.
NON_UNIQUE boolean Indicates if index values can be non-unique. It is false when TYPE is tableIndexStatistic.
INDEX_QUALIFIER String Catalog of the index (always null)
INDEX_NAME String Name of the index. It is null when TYPE is tableIndexStatistic.
TYPE short Indicates the type of the index: tableIndexStatistic, tableIndexClustered, tableIndexHashed, tableIndexOther.
ORDINAL_POSITION short Represents the column’s sequence number within the index. It is zero when TYPE is tableIndexStatistic.
COLUMN_NAME String Name of the column. It is null when TYPE is tableIndexStatistic.
ASC_OR_DESC String Describes the column sort sequence. (always null)
CARDINALITY long For TYPE as tableIndexStatistic, it represents the number of rows in the table. (always returns -1)
PAGES long For TYPE as tableIndexStatistic, it denotes the number of pages used for the table. (always null)
FILTER_CONDITION String Any filter condition (always null).

Parameters:

Parameter Type Description
catalog String The catalog name, which should match as it’s stored in the database (ignored). An empty string retrieves without a catalog. If null, it indicates that the catalog name isn’t used for narrowing down the search.
schema String The schema name, which should match as it’s stored in the database. An empty string retrieves without a schema. If null, it indicates that the schema name isn’t used for narrowing down the search.
table String Name of the table, which should match as it’s stored in the database.
unique boolean If true, only unique value indices are returned. If false, it returns all indices, whether unique or not.
approximate boolean If true, the results may reflect approximate or outdated values. If false, the results are expected to be accurate.

Returns:

  • ResultSet: Each row in the returned ResultSet represents a description of an index column.

Throws:

  • SQLException: Thrown if there’s a database access error.

getUDTs

ResultSet getUDTs( String catalog,
                   String schemaPattern,
                   String typeNamePattern,
                   int[] types )
           throws SQLException
This method throws an exception in strict mode. If not in strict mode, an empty result set will be returned.

This method retrieves descriptions of the user-defined types (UDTs) specified within a given namespace. The UDTs associated with a namespace may be of type JAVA_OBJECT, STRUCT, or DISTINCT.

The method will only return types that match the provided schema, type name, and type criteria. The results are organized by DATA_TYPE, TYPE_CAT, TYPE_SCHEM, and TYPE_NAME.

Each type description contains:

Name Type Description
TYPE_CAT String The catalog associated with the type (can be null).
TYPE_SCHEM String The schema associated with the type (can be null).
TYPE_NAME String The name of the type.
CLASS_NAME String Name of the Java class.
DATA_TYPE int Type value as defined in java.sql.Types. The possible values are JAVA_OBJECT, STRUCT, or DISTINCT.
REMARKS String Comments explaining the type.
BASE_TYPE short The type code of the source for a DISTINCT type or the type implementing the user-generated reference for the SELF_REFERENCING_COLUMN of a STRUCT type. It is defined in java.sql.Types. It’s null if DATA_TYPE is not DISTINCT or not STRUCT with REFERENCE_GENERATION = USER_DEFINED.

Parameters:

Parameter Type Description
catalog String The catalog name (ignored). It should match as stored in the database. An empty string will fetch those without a catalog. If set to null, the catalog name isn’t used to narrow down the search.
schemaPattern String A schema pattern name that should match as it’s stored in the database. An empty string will fetch those without a schema. If null, the schema name isn’t used to narrow down the search.
typeNamePattern String A type name pattern that should match as it’s stored in the database. It can be a fully qualified name.
types int[] A list of user-defined types to include (JAVA_OBJECT, STRUCT, or DISTINCT). If set to null, all types are returned.

Returns:

  • ResultSet: Each row in the ResultSet provides a description of a UDT.

Throws:

  • SQLException: This exception is thrown if there’s a database access error.

getSuperTypes

ResultSet getSuperTypes(String catalog,
                        String schemaPattern,
                        String typeNamePattern)
                 throws SQLException
This method throws an exception in strict mode. If not in strict mode, an empty result set will be returned.

This method fetches details of the user-defined type (UDT) hierarchies specified within a given schema of the database. It models only the immediate super type/sub type relationship.

The method provides supertype information solely for UDTs that match the given catalog, schema, and type name criteria.

UDTs that do not have an immediate super type won’t appear in the result. Each row in the ResultSet returned by this method depicts the designated UDT and its direct super type. A row has the columns as follows:

Name Type Description
TYPE_CAT String The UDT’s catalog (can be null).
TYPE_SCHEM String The UDT’s schema (can be null).
TYPE_NAME String Type name of the UDT.
SUPERTYPE_CAT String Catalog of the direct super type (can be null).
SUPERTYPE_SCHEM String Schema of the direct super type (can be null).
SUPERTYPE_NAME String Name of the direct super type.

Parameters:

Parameter Type Description
catalog String A catalog name (ignored); an empty string retrieves those without a catalog. Null implies that the catalog name isn’t considered in the selection criteria.
schemaPattern String A schema name pattern; an empty string fetches those without a schema.
typeNamePattern String A UDT name pattern, which can be a fully-qualified name.

Returns:

  • ResultSet: Each row in the returned ResultSet offers information concerning the specified UDT.

Throws:

  • SQLException: This is thrown if a database access error arises.

getSuperTables

ResultSet getSuperTables(String catalog,
                         String schemaPattern,
                         String tableNamePattern)
                  throws SQLException
This method throws an exception in strict mode. If not in strict mode, an empty result set will be returned.

This method retrieves a description of the table hierarchies specified within a given schema in the database.

The method provides supertable information solely for tables that match the provided catalog, schema, and table name criteria.

Tables that don’t possess a super table won’t appear in the result. Supertables must be defined in the identical catalog and schema as their sub-tables. As a result, there’s no need for the type description to contain this information for the supertable.

The columns in each type description are as follows:

Name Type Description
TABLE_CAT String The table’s catalog (can be null).
TABLE_SCHEM String The table’s schema (can be null).
TABLE_NAME String The table name.
SUPERTABLE_NAME String Name of the direct super table.

Parameters:

Parameter Type Description
catalog String A catalog name (ignored); an empty string retrieves tables without a catalog. Null implies that the catalog name isn’t considered in the selection criteria.
schemaPattern String A schema name pattern; an empty string fetches tables without a schema.
tableNamePattern String A table name pattern, which can be a fully-qualified name.

Returns:

  • ResultSet: Each row in the returned ResultSet offers a type description of a table hierarchy.

Throws:

  • SQLException: This is thrown if a database access error arises.

getAttributes

ResultSet getAttributes( String catalog,
                         String schemaPattern,
                         String typeNamePattern,
                         String attributeNamePattern )
                 throws SQLException
This method throws an exception in strict mode. If not in strict mode, an empty result set will be returned.

This method fetches descriptions for the specified attribute of a given type that belongs to a user-defined type (UDT) located within a specified schema and catalog.

Returned descriptions exclusively match the criteria set by the catalog, schema, type, and attribute name. The results are systematically ordered by the fields: TYPE_CAT, TYPE_SCHEM, TYPE_NAME, and ORDINAL_POSITION. Attributes that are inherited will not be included in this description.

The columns present in the ResultSet object returned are:

Name Type Description
TYPE_CAT String The type’s catalog (potentially null).
TYPE_SCHEM String The type’s schema (potentially null).
TYPE_NAME String Name of the type.
ATTR_NAME String Name of the attribute.
DATA_TYPE int The SQL type of the attribute, sourced from java.sql.Types.
ATTR_TYPE_NAME String Dependent on data source type name. UDTs have fully qualified type names, while REFs denote the target type.
ATTR_SIZE int Size of the column. For character or date types, this indicates the maximum character count; for numeric or decimal types, it denotes precision.
DECIMAL_DIGITS int Number of fractional digits. Returns null for non-applicable data types.
NUM_PREC_RADIX int Typical radices are either 10 or 2.
NULLABLE int Indicates if NULL values are permissible: attributeNoNulls (not allowed), attributeNullable (allowed), attributeNullableUnknown (not specified).
REMARKS String Comments describing the column (can be null).
ATTR_DEF String The default value (potentially null).
SQL_DATA_TYPE int Unused.
SQL_DATETIME_SUB int Unused.
CHAR_OCTET_LENGTH int For character types, this specifies the maximum byte count for the column.
ORDINAL_POSITION int Position of the attribute in the UDT (commences from 1).
IS_NULLABLE String ISO rules dictate attribute nullability: YES means NULLs are permissible; NO indicates NULLs are not; an empty string signifies unknown nullability.
SCOPE_CATALOG String For reference attributes, specifies the catalog of the associated table (null if DATA_TYPE isn’t REF).
SCOPE_SCHEMA String For reference attributes, outlines the schema of the associated table (null if DATA_TYPE isn’t REF).
SCOPE_TABLE String For reference attributes, declares the table name of the associated scope (null if DATA_TYPE isn’t REF).
SOURCE_DATA_TYPE short The source type of a distinct type or user-generated Ref type, fetched from java.sql.Types. Returns null if DATA_TYPE isn’t either DISTINCT or user-generated REF.

Parameters:

Parameter Type Description
catalog String A catalog name that should match as it’s stored in the database (ignored); an empty string fetches those without a catalog. A null value indicates that the catalog shouldn’t be used to filter.
schemaPattern String A schema pattern name; an empty string fetches those without a schema. Null implies the schema name shouldn’t be part of the search filter.
typeNamePattern String Pattern for the type name that should match its database representation.
attributeNamePattern String An attribute name pattern that should match as it is declared in the database.

Returns:

  • ResultSet: Every row in the returned ResultSet provides an attribute description.

Throws:

  • SQLException: Thrown if there is a disruption or error while accessing the database.

getSchemas

ResultSet getSchemas(String catalog,
                     String schemaPattern)
              throws SQLException

This method fetches the namespaces that are available in the database. The outcome is ordered by the fields TABLE_CATALOG and TABLE_SCHEM.

The columns encapsulated in the ResultSet object are:

Name Type Description
TABLE_SCHEM String Represents the schema name.
TABLE_CATALOG String Represents the catalog’s name (can be null).
OWNER String Depicts the owner’s username associated with the namespace.
SCHEMA_TYPE String Denotes the type of the namespace. Can be one of: RELATIONAL, DOCUMENT, GRAPH.

Parameters:

Parameter Type Description
catalog String A catalog name that should be identical to its storage in the database (ignored); an empty string returns those without a catalog. A null value suggests that the catalog shouldn’t be a filtering criterion.
schemaPattern String A schema name that should match its database representation. A null value indicates that the schema name shouldn’t be part of the search filter.

Returns:

  • ResultSet: Each row in this returned ResultSet offers a schema description.

Throws:

  • SQLException: This is thrown if any interruptions or errors arise during database access.

getClientInfoProperties

ResultSet getClientInfoProperties()
                           throws SQLException

This method procures a list of client info properties supported by the driver. The ensuing ResultSet object is populated with columns that present details about each client info property.

The columns available in the ResultSet object are:

Name Type Description
NAME String Represents the name of the client info property.
MAX_LEN int Signifies the utmost length permissible for the value of the property.
DEFAULT_VALUE String Represents the default value assigned to the property.
DESCRIPTION String A detailed description of the property. Commonly, it includes insights regarding the storage location of this property in the database.

Note: The ResultSet is systematically sorted based on the NAME column.

Returns:

  • ResultSet: This object encapsulates each row where every row describes a supported client info property.

Throws:

  • SQLException: This is thrown in cases where there’s a disruption or issue while accessing the database.

getFunctions

ResultSet getFunctions( String catalog,
                        String schemaPattern,
                        String functionNamePattern )
                throws SQLException

This method is tasked with retrieving descriptions for both system and user functions present in the DBMS. The returned functions match the provided criteria for schema, and function name. The order in which they’re sorted is: FUNCTION_CAT, FUNCTION_SCHEM, FUNCTION_NAME, and SPECIFIC_NAME.

The columns present in the returned ResultSet are:

Column Name Type Description
FUNCTION_CAT String Denotes the function’s catalog. It may potentially be null.
FUNCTION_SCHEM String Represents the schema of the function, which can also be null.
FUNCTION_NAME String The official name of the function. This is the name that users would typically use to invoke the said function.
REMARKS String Provides an explanatory commentary or description about the function.
FUNCTION_TYPE short Describes the nature or kind of function. The potential values can be:
(1) functionResultUnknown: Unclear if there’s a return value or table.
(2) functionNoTable: The function doesn’t return a table.
(3) functionReturnsTable: The function does return a table.
SPECIFIC_NAME String This name serves to uniquely identify the function within its schema. It might be specified by the user or generated by the DBMS. It could differ from FUNCTION_NAME, especially in cases like overloaded functions.

It’s vital to note that a user might not always have the requisite permissions to execute any of the functions returned by the getFunctions method.

Parameters:

Parameter Description
catalog Name of a catalog (ignored). Must match as stored in the database. "" fetches those without a catalog. null means it shouldn’t be used in the search.
schemaPattern A pattern for the schema name. Must match as stored in the database. "" fetches those without a schema. null means it shouldn’t be used in the search.
functionNamePattern A pattern for the function name. Must match as stored in the database.

Returns:

  • ResultSet: An object in which each row represents a detailed description of a function.

Throws:

  • SQLException: Thrown in scenarios where there’s an issue accessing the database.

Note: This method exclusively returns SQL functions.

getFunctionColumns

ResultSet getFunctionColumns( String catalog,
                              String schemaPattern,
                              String functionNamePattern,
                              String columnNamePattern )
                      throws SQLException

This method retrieves a description of the system or user function parameters and return type for the specified catalog. Only descriptions that match the criteria for schema, function name, and parameter/column name are returned. The result set is ordered by FUNCTION_CAT, FUNCTION_SCHEM, FUNCTION_NAME, and SPECIFIC_NAME. Initially, the return value is presented (if any). Following this, the parameter descriptions are shown in their call order. Lastly, the column descriptions are listed in accordance with their column number.

The columns in the ResultSet are described as:

Column Name Type Description
FUNCTION_CAT String Catalog of the function. It might be null.
FUNCTION_SCHEM String Schema of the function. Can be null.
FUNCTION_NAME String The name users typically use to invoke the function.
COLUMN_NAME String Name of the column or parameter.
COLUMN_TYPE Short Kind of column or parameter. Possible values include:
- functionColumnUnknown
- functionColumnIn
- functionColumnInOut
- functionColumnOut
- functionColumnReturn
- functionColumnResult
DATA_TYPE int SQL type from java.sql.Types.
TYPE_NAME String SQL type name. For UDT types, the name is fully qualified.
PRECISION int Specifies the column size. Details mentioned below.
LENGTH int Byte length of the data.
SCALE short Scale value. Null if not applicable for the datatype.
RADIX short Radix of the data.
NULLABLE short Nullability status. Potential values:
- functionNoNulls
- functionNullable
- functionNullableUnknown
REMARKS String Comments or descriptions about the column or parameter.
CHAR_OCTET_LENGTH int Max length for binary and char-based parameters or columns. Null for other types.
ORDINAL_POSITION int Ordinal position starting from 1 for input and output parameters. Value of 0 for function’s return value. For result set columns, it’s the ordinal position of the column in the result set.
IS_NULLABLE String ISO rules for nullability. Values:
- YES
- NO
- empty string
SPECIFIC_NAME String Unique name identifying the function within its schema. Can differ from FUNCTION_NAME, e.g., in overloaded functions.

The PRECISION column reflects the specified column size for the parameter or column. For different data types, this represents different attributes as mentioned in the original documentation.

Parameters:

Parameter Description
catalog Catalog name. Matches as stored in the database. "" fetches without a catalog. null means don’t use in search.
schemaPattern Schema name pattern. Matches as stored in the database. "" fetches without a schema. null means don’t use in search.
functionNamePattern Function name pattern. Matches as stored in the database.
columnNamePattern Parameter or column name pattern. Matches as stored in the database.

Returns:

  • ResultSet: Each row provides details about a user function parameter, column, or return type.

Throws:

  • SQLException: Thrown if there’s an issue accessing the database.

getFunctionColumns

ResultSet getFunctionColumns( String catalog,
                              String schemaPattern,
                              String functionNamePattern,
                              String columnNamePattern )
                      throws SQLException

This method retrieves a description of the system or user function parameters and return type for the specified catalog. Only descriptions that match the criteria for schema, function name, and parameter/column name are returned. The result set is ordered by FUNCTION_CAT, FUNCTION_SCHEM, FUNCTION_NAME, and SPECIFIC_NAME. Initially, the return value is presented (if any). Following this, the parameter descriptions are shown in their call order. Lastly, the column descriptions are listed in accordance with their column number.

The columns in the ResultSet are described as:

Column Name Type Description
FUNCTION_CAT String Catalog of the function. It might be null.
FUNCTION_SCHEM String Schema of the function. Can be null.
FUNCTION_NAME String The name users typically use to invoke the function.
COLUMN_NAME String Name of the column or parameter.
COLUMN_TYPE Short Kind of column or parameter. See table below for possible values.
DATA_TYPE int SQL type from java.sql.Types.
TYPE_NAME String SQL type name. For UDT types, the name is fully qualified.
PRECISION int Specifies the column size. Details mentioned below.
LENGTH int Byte length of the data.
SCALE short Scale value. Null if not applicable for the datatype.
RADIX short Radix of the data.
NULLABLE short Nullability status. See table below for potential values.
REMARKS String Comments or descriptions about the column or parameter.
CHAR_OCTET_LENGTH int Max length for binary and char-based parameters or columns. Null for other types.
ORDINAL_POSITION int Ordinal position starting from 1 for input and output parameters. Value of 0 for function’s return value. For result set columns, it’s the ordinal position of the column in the result set.
IS_NULLABLE String ISO rules for nullability. See table below for possible values.
SPECIFIC_NAME String Unique name identifying the function within its schema. Can differ from FUNCTION_NAME, e.g., in overloaded functions.

COLUMN_TYPE Values:

Value Description
functionColumnUnknown Nobody knows.
functionColumnIn IN parameter.
functionColumnInOut INOUT parameter.
functionColumnOut OUT parameter.
functionColumnReturn Function return value.
functionColumnResult Parameter/column is a column in ResultSet.

NULLABLE Values:

Value Description
functionNoNulls Does not allow NULL values.
functionNullable Allows NULL values.
functionNullableUnknown Nullability is unknown.

IS_NULLABLE Values:

Value Description
YES The parameter or column can include NULLs.
NO The parameter or column cannot include NULLs.
empty string Nullability for the parameter or column is unknown.

Additional details about columns like PRECISION remain unchanged from the original documentation.

Parameters:

Parameter Description
catalog Catalog name. Matches as stored in the database. "" fetches without a catalog. null means don’t use in search.
schemaPattern Schema name pattern. Matches as stored in the database. "" fetches without a schema. null means don’t use in search.
functionNamePattern Function name pattern. Matches as stored in the database.
columnNamePattern Parameter or column name pattern. Matches as stored in the database.

Returns:

  • ResultSet: Each row provides details about a user function parameter, column, or return type.

Throws:

  • SQLException: Thrown if there’s an issue accessing the database.
© Polypheny GmbH. All Rights Reserved.