JDBC Reference
Page 7 of 31

java.sql.DatabaseMetaData

Stored Prepared Statements for DatabaseMetaData Queries

The JDBC driver has built-in queries that it uses to supply the results of the JDBC DatabaseMetaData methods that supply information about the Cloudscape system. These methods are useful for applications working with generic DBMSs, such as database tools, not Cloudscape-specific applications, which can query system catalogs directly.

The cloudscape.jdbc.metadataStoredPreparedStatements property configures Cloudscape's built-in JDBC driver to take advantage of stored prepared statements to avoid preparing these queries each time an application starts up.

JDBC DatabaseMetaData queries are duplicated in every Cloudscape database even if a single Cloudscape system manages several databases.

With this property, you have the option of having Cloudscape create and store the statements as needed (dynamically) or all at once when the database is created.

For more information, see cloudscape.jdbc.metadataStoredPreparedStatements in Tuning Cloudscape.

DatabaseMetaData Result Sets

DatabaseMetaData result sets do not close the result sets of other statements, even when auto-commit is set to true.

DatabaseMetaData result sets are closed if a user performs any other action on a JDBC object that causes an automatic commit to occur. If you need the DatabaseMetaData result sets to be accessible while executing other actions that would cause automatic commits, turn off auto-commit with setAutoCommit(false).

getProcedureColumns

Cloudscape does not have stored procedures, which are procedures written in SQL dialect. Instead, Cloudscape allows you to call Java methods within SQL-J statements. It also allows you to define aliases for static Java methods; these are called method aliases (see CREATE METHOD ALIAS statement). Cloudscape returns information about the parameters and return values of these method aliases in the getProcedureColumns call. If the corresponding Java method is overloaded, it returns information about each signature separately.

getProcedureColumns returns a ResultSet. Each row describes a single parameter or return value.

Parameters to getProcedureColumns

The JDBC API defines the following parameters for this method call:

  • catalog

    always use null for this parameter in Cloudscape.

  • schemaPattern

    Cloudscape methods do not exist within schemas. Always use "" for this parameter in Cloudscape.

  • procedureNamePattern

    a String object representing a procedure name pattern. Use "%" to find all method aliases in the database. (Cloudscape stores the names of method aliases in SYS. SYSALIASES).

  • columnNamePattern

    a String object representing the name pattern of the parameter names or return value names. In, Cloudscape, the parameter names and return value names always begin with the letters PARAM and are followed by a sequential integer beginning with1. For example, if a method has two parameters, their names will be PARAM1 and PARAM2. Return value names are always PARAM0. Use "%" to find all parameter names.

Columns in the ResultSet Returned by getProcedureColumns

Columns in the ResultSet returned by getProcedureColumns are as described by the API. Further details for some specific columns:

  • PROCEDURE_CAT

    always "null" in Cloudscape

  • PROCEDURE_SCHEM

    always "null" in Cloudscape

  • PROCEDURE_NAME

    the name of method alias

  • COLUMN_NAME

    the name of the parameter (see columnNamePattern)

  • COLUMN_TYPE

    short indicating what the row describes. Always is DatabaseMetaData.procedureColumnIn for method parameters, unless the parameter is an array. If so, it is DatabaseMetaData.procedureColumnInOut. It always returns DatabaseMetaData.procedureColumnReturn for return values. (For more information about INOUT parameters and how they relate to arrays, see CallableStatements and INOUT Parameters.)

  • TYPE_NAME

    Cloudscape-specific name for the type.

  • NULLABLE

    always returns DatabaseMetaData.procedureNoNulls for primitive parameters and DatabaseMetaData.procedureNullable for object parameters

  • REMARKS

    a String describing the java type of the method parameter

  • METHOD_ID

    a Cloudscape-specific column numbers overloaded methods with a short. For example, if a method alias is defined for a static Java method that has three different signatures, Cloudscape returns either 1, 2, or 3 (short) in this column.

NEW: Implementation of the DatabaseMetaData.getProcedureColumns method is new in Version 3.6.

DatabaseMetaData Functionality Not Supported

In the current release, Cloudscape does not provide all of the DatabaseMetaData functionality. The following JDBC requests result in empty result sets, in the format required by the JDBC API:

  • getColumnPrivileges
  • getTablePrivileges

Cloudscape does not implement privileges, and thus has no information to provide for these calls.

getBestRowIdentifier looks for identifiers in this order:

  • a primary key on the table
  • a unique constraint or unique index on the table
  • all the columns in the table

Because of this last choice, it will always find a set of columns that identify a row. However, if there are duplicate rows in the table, use of all columns may not necessarily identify a unique row in the table.