Using Cloudscape's Java Extensions
Page 11 of 12

Using the Cloudscape Types to Query the System Tables

The preferred mechanism for getting metadata information about a ResultSet or about the database is to use the JDBC MetaData classes and methods. (For a good discussion of using JDBC's MetaData classes and methods, see JDBC Database Access with Java.)

However, you can also query the system tables directly to get information about tables, columns, indexes, and other dictionary objects. Chapter 4, "Cloudscape System Tables", in the Cloudscape Reference Manual details the contents and layout of the system tables.

Querying the system tables successfully requires using the Cloudscape-provided Java types stored in the system tables. These types, which are part of the COM.cloudscape.types package, are:

For an instructive example, look at the information provided by the column COLUMNDATATYPE in SYS.SYSCOLUMNS. Whereas another DBMS might have separate columns to store information about a data type's length, precision, nullability, and the like, Cloudscape stores all of that information in the Java type TypeDescriptor. Use methods to find out specific information. The API for each of these types is included in the javadoc directory:

Examining the API, you see that COM.cloudscape.types.TypeDescriptor includes the following methods:

  • getMaximumWidth
  • getPrecision
  • getScale
  • getStorageType
  • getTypeName
  • isNullable
  • getSQLstring

So, for example, to find out which columns in the table called Hotels are nullable, you could use the following query:

SELECT Columnname FROM SYS.Systables AS T,
SYS.Syscolumns AS C
WHERE T.Tableid = C.Referenceid AND T.Tablename = 'HOTELS'
AND C.ColumnDataType.isNullable()

COM.cloudscape.types.DependableFinder is less intuitive. The description of SYSDEPENDS provides example queries to help you get started using this type (see Chapter 4, "Cloudscape System Tables", in the Cloudscape Reference Manual).