DatabaseMetaData interface

The DatabaseMetaData interface is implemented by the IBM Developer Kit for Java™ JDBC driver to provide information about its underlying data sources. It is used primarily by application servers and tools to determine how to interact with a given data source. Applications may also use DatabaseMetaData methods to obtain information about a data source, but this is less typical.

The DatabaseMetaData interface includes over 150 methods that can be categorized according to the types of information they provide. These are described below. The DatabaseMetaData interface also contains over 40 fields that are constants used as return values for various DatabaseMetaData methods.

See "Changes in JDBC 3.0" and "Changes in JDBC 4.0" below for information about changes made to methods in the DatabaseMetaData interface.

Creating a DatabaseMetaData object

A DatabaseMetaData object is created with the Connection method getMetaData. Once the object is created, it can be used to dynamically find information about the underlying data source. The following example creates a DatabaseMetaData object and uses it to determine the maximum number of characters allowed for a table name:

Example: Create a DatabaseMetaData object

// con is a Connection object
DatabaseMetaData dbmd = con.getMetadata();
int maxLen = dbmd.getMaxTableNameLength();

Retrieving general information

Some DatabaseMetaData methods are used to dynamically find general information about a data source as well as to obtain details about its implementation. Some of these methods include the following:

getURL
getUserName
getDatabaseProductVersion, getDriverMajorVersion, and getDriverMinorVersion
getSchemaTerm, getCatalogTerm, and getProcedureTerm
nullsAreSortedHigh, and nullsAreSortedLow
usesLocalFiles, and usesLocalFilePerTable
getSQLKeywords

Determining feature support

A large group of DatabaseMetaData methods can be used to determine whether a given feature or set of features is supported by the driver or underlying data source. Beyond this, there are methods that describe what level of support is provided. Some of the methods that describe support for individual features include the following:

supportsAlterTableWithDropColumn
supportsBatchUpdates
supportsTableCorrelationNames
supportsPositionedDelete
supportsFullOuterJoins
supportsStoredProcedures
supportsMixedCaseQuotedIdentifiers

Methods to describe a level of feature support include the following:

supportsANSI92EntryLevelSQL
supportsCoreSQLGrammar

Data source limits

Another group of methods provide the limits imposed by a given data source. Some of the methods in this category include the following:

getMaxRowSize
getMaxStatementLength
getMaxTablesInSelect
getMaxConnections
getMaxCharLiteralLength
getMaxColumnsInTable

Methods in this group return the limit value as an integer. A return value of zero means that there is either no limit or the limit is unknown.

SQL objects and their attributes

A number of DatabaseMetaData methods provide information about the SQL objects that populate a given data source. These methods can determine the attributes of SQL objects. These methods also return ResultSet objects in which each row describes a particular object. For example, the getUDTs method returns a ResultSet object in which there is a row for each user-defined table (UDT) that has been defined in the data source. Examples of this category include the following:

getSchemas and getCatalogs
getTables
getPrimaryKeys
getProcedures and getProcedureColumns
getUDTs

Transaction support

A small group of methods provide information about the transaction semantics supported by the data source. Examples of this category include the following:

supportsMultipleTransactions
getDefaultTransactionIsolation

See Example: Returning a list of tables using the DatabaseMetaData interface for an example of how to use the DatabaseMetaData interface.

Changes in JDBC 3.0

There are changes to the return values for some of the methods in JDBC 3.0. The following methods have been updated in JDBC 3.0 to add fields to the ResultSets that they return.

getTables
getColumns
getUDTs
getSchemas

Note: If an application is being developed using Java Development Kit (JDK) 1.4, you may recognize that there are a certain number of columns being returned when testing. You write your application and expect to access all of these columns. However, if the application is being designed to also run on previous releases of the JDK, the application receives an SQLException when it tries to access these fields that do not exist in earlier JDK releases. Example: Using metadata ResultSets that have more than one column is an example of how an application can be written to work with several JDK releases.

Changes in JDBC 4.0

In V6R1, the command language interface (CLI) is changing the implementation of the MetaData APIs to also call the SYSIBM stored procedures. Because of this, the JDBC MetaData methods will use the SYSIBM procedures directly on V6R1, regardless of JDK level. You will notice the following differences due to this change:

The native JDBC driver previously permitted the user of localhost as the catalog name for most of the methods. In JDBC 4.0, the native JDBC driver will not return any information if localhost is specified.
The native JDBC driver always returned an empty result set when the nullable parameter of getBestRowIdentifier was set to false. This will be corrected to return the proper result.
The values returned by getColumns for the BUFFER_LENGTH, SQL_DATA_TYPE, and SQL_DATETIME_SUB columns may be different. These values should not be used in a JDBC application because the JDBC specification defines these columns as "unused."
The native JDBC driver previously recognized the table and schema parameters of getCrossReference, getExportedKeys, getImportedKeys, and getPrimaryKeys as a "pattern." Now, the table and schema parameters must match the name as stored in the database.
Views used for implementing system defined views were previously described by getTables() as SYSTEM TABLES. To be consistent with the DB2® family, these views are now described as VIEWS.

Column names returned by getProcedures are different. These column names are not defined by the JDBC 4.0 specification. Also, the remarks column for getProcedures used to return "" if no information was available. It now returns null.

Table 1. Column names returned by getProcedures in JDBC 4.0
Column #	Previous name	Name under JDBC 4.0
4	RESERVED1	NUM_INPUT_PARAMS
5	RESERVED2	NUM_OUTPUT_PARAMS
6	RESERVED3	NUM_RESULT_SETS

Some values returned by getProcedureColumns for various data types have changed, as shown below:

Table 2. Values returned by getProcedureColumns in JDBC 4.0
Data type	Column	Previous value	Value in JDBC 4.0
ALL	Remarks	""	null
INTEGER	Length	Null	4
SMALLINT	Length	Null	2
BIGINT	dataType	19 (incorrect)	-5
BIGINT	Length	Null	8
DECIMAL	Length	Null	precision + scale
NUMERIC	Length	Null	precision + scale
DOUBLE	TypeName	DOUBLE PRECISION	DOUBLE
DOUBLE	Length	Null	8
FLOAT	TypeName	DOUBLE PRECISION	DOUBLE
FLOAT	Length	Null	8
REAL	Length	Null	4
DATE	Precision	null	10
DATE	Length	10	6
TIME	Precision	null	8
TIME	Length	8	6
TIME	Scale	null	0
TIMESTAMP	Precision	null	26
TIMESTAMP	Length	26	16
TIMESTAMP	Scale	null	6
CHAR	typeName	CHARACTER	CHAR
CHAR	Precision	null	same as length
VARCHAR	typeName	CHARACTER VARYING	VARCHAR
VARCHAR	Precision	null	same as length
CLOB	dataType	null (incorrect)	2005
CLOB	typeName	CHARACTER LARGE OBJECT	CLOB
CLOB	Precision	null	same as length
CHAR FOR BIT DATA	dataType	1 (CHAR)	-2 (BINARY)
CHAR FOR BIT DATA	typeName	CHARACTER	CHAR () FOR BIT DATA
CHAR FOR BIT DATA	Precision	null	same as length
BLOB	dataType	null (incorrect)	2004
BLOB	typeName	BINARY LARGE OBJECT	BLOB
BLOB	Precision	null	same as length
DATALINK	dataType	null (incorrect)	70
DATALINK	Precision	null	same as length
VARCHAR FOR BIT DATA	dataType	12 (VARCHAR)	-3 (VARBINARY)
VARCHAR FOR BIT DATA	typeName	CHARACTER VARYING	VARCHAR () FOR BIT DATA
VARCHAR FOR BIT DATA	Precision	null	same as length

Restriction on READ ONLY stored procedures

The native JDBC supports the access = read only property. This property is enforced at the JDBC level. Because of this, the MetaData procedures should still work if this property is set. However, it is possible to use the native JDBC driver from a database stored procedure that is defined as READ ONLY. In this case, the MetaData procedures will not work.

New method: getClientInfoProperties()

The getClientInfoProperties method retrieves a list of the client info properties that the driver supports. Each client info property is stored in a SQL special register. The native JDBC driver will return a result set with the following information:

Table 3. Information returned by the getClientInfoProperties method
Name	Maximum length	Default value	Description
ApplicationName	255	blank	The name of the application currently utilizing the connection
ClientUser	255	blank	The name of the user that the application using the connection is performing work for. This may not be the same as the user name that was used in establishing the connection
ClientHostname	255	blank	The hostname of the computer that the application using the connection is running on
ClientAccounting	255	blank	Accounting information.

The SQL special registers corresponding to the client info properties are as follows:

Table 4. SQL special registers
Name	SQL special register
ApplicationName	CURRENT CLIENT_APPLNAME
ClientUser	CURRENT CLIENT_USERID
ClientHostname	CURRENT CLIENT_WRKSTNNAME
ClientAccounting	CURRENT CLIENT_ACCTNG

The clientInfoProperties may be set using the Connection object's setClientInfo method.