Whole document tree

8 New SQL Types

8.1     Taxonomy of SQL Types

The SQL99 draft specifies these data types:

• SQL2 built-in types-the familiar SQL `column types'
  
  
  • CHAR
    
    
  • FLOAT
    
    
  • DATE
    
    
  • etc.
    
    
• New built-in types-new types added by SQL99
  
  
  • BLOB-a Binary Large OBject
    
    
  • CLOB-a Character Large OBject
    
    
• Structured types, for example:
  
  
  • CREATE TYPE PLANE_POINT (X FLOAT, Y FLOAT)
    
    
• Distinct types-based on the representation of a built-in type, for example:
  
  
  • CREATE TYPE MONEY AS NUMERIC(10,2)
    
    
• Constructed types-based on a given base type:
  
  
  • REF(structured-type)-designates row containing a structured type instance
    
    
  • base-type ARRAY[n]-an array of n base-type elements
    
    
• Locator types-designate a datum that resides on the server
  
  
  • LOCATOR(structured-type)-locator to structured instance in server
    
    
  • LOCATOR(array)-locator to array in server
    
    
  • LOCATOR(blob)-locator to Binary Large Object in server
    
    
  • LOCATOR(clob)-locator to Character Large Object in server
    
    

A REF value persistently denotes an instance of a structured type that resides in the database. A LOCATOR exists only in the client environment and is a transient, logical pointer to data that resides on the database server. A locator typically refers to data that is too large to materialize on the client, for example, images or audio. There are operators defined at the SQL level to retrieve random-access pieces of the data denoted by the locator.

The remainder of this chapter discusses the default mechanism provided by the JDBC API for accessing each of the new SQL types mentioned above. The JDBC API also provides a means for customizing the mapping of SQL distinct and structured types into Java classes. This mechanism is discussed in the Chapter 9.

8.2     Blobs and clobs

8.2.1 Retrieving blobs and clobs

Blob blob = rs.getBlob(1);
Clob clob = rs.getClob(2);

An application does not deal directly with the LOCATOR(blob) and LOCATOR(clob) types that are defined in SQL. By default, a JDBC driver should implement the Blob and Clob interfaces using the appropriate locator type. Also, by default Blob and Clob objects only remain valid during the transaction in which they are created. A JDBC driver may allow these defaults to be changed. For example, the lifetime of Blob and Clob objects could be changed to session-scoped. However, the JDBC API does not specify how this is done.

8.2.2 Storing blobs and clobs

8.2.3 Metadata additions

8.3     Arrays

8.3.1 Retrieving arrays

Array a = rs.getArray(1);

The Array interface provides several methods which return the contents of the array to the client as a materialized Java programming language array (Java array) or ResultSet object. These methods are getArray()and getResultSet(), respectively. See the separate API documentation for details.

8.3.2 Storing arrays

8.3.3 Metadata additions

8.4     Refs

8.4.1 Retrieving refs

Ref ref = rs.getRef(1);

The Ref interface does not provide methods for dereferencing. Instead, a Ref can be passed as an input parameter to an appropriate SQL statement that fetches the object that it references. See the separate JDBC API documentation for details.

8.4.2 Storing refs

8.4.3 Metadata additions

8.5     Distinct types

8.5.1 Retrieving distinct types

      CREATE TYPE MONEY AS NUMERIC(10,2)

a value of type MONEY could be retrieved as follows:

java.math.BigDecimal bd = rs.getBigDecimal(1);

8.5.2 Storing distinct types

8.5.3 Metadata additions

An SQL DISTINCT type must be defined as part of a particular database schema before it is used in a schema table definition. Information on schema-specific user-defined types-of which DISTINCT types are one particular kind-can be retrieved by calling the DatabaseMetaData.getUDTs() method. For example,

int[] types = {Types.DISTINCT};
ResultSet rs = dmd.getUDTs("catalog-name", "schema-name", 
	"%", types);

returns descriptions of all the SQL DISTINCT types defined in the catalog- name.schema-name schema. If the driver does not support UDTs or no matching UDTs are found then an empty result set is returned.

Each type description has the following columns:

TYPE_CAT	String => the type's catalog (may be null)
TYPE_SCHEM	String => the type's schema (may be null)
TYPE_NAME	String => the database type name
JAVA_CLASS	String => a Java class or interface name
DATA_TYPE	short => value defined in java.sql.Types, e.g. DISTINCT
REMARKS	String => explanatory comment on the type

Most of the columns above should be self-explanatory. The TYPE_NAME is the SQL type name given to the DISTINCT type-MONEY in the example above. This is the name used in a CREATE TABLE statement to specify a column of this type.

When DATA_TYPE is Types.DISTINCT, the JAVA_CLASS column contains a fully qualified Java class name. Instances of this class will be created if getObject() is called on a column of this DISTINCT type. For example, JAVA_CLASS would default to java.math.BigDecimal in the case of MONEY above. The JDBC API does not prohibit a driver from returning a subtype of the class named by JAVA_CLASS. The JAVA_CLASS value reflects a custom type mapping when one is used. See Chapter 9 for details.

8.6     Structured types

8.6.1 Retrieving structured types

Struct struct = (Struct)rs.getObject(1);

retrieves a Struct value from the first column of the current row of result set rs. The Struct interface contains methods for retrieving the attributes of a structured type as an array of java.lang.Object values. By default, a JDBC driver should materialize the contents of a Struct prior to returning a reference to it to the application. Also, by default a Struct object is considered valid as long as the Java application maintains a reference to it. A JDBC driver may allow these defaults to be changed-to allow an SQL LOCATOR to be used, for example-but the JDBC API does not specify how this is done.

8.6.2 Storing structured types

8.6.3 Metadata additions

A structured SQL type must be defined as part of a particular database schema before it can be used in a schema table definition. Information on schema-specific user-defined types-of which STRUCT types are one particular kind-can be retrieved by calling the DatabaseMetaData.getUDTs() method. For example,

int[] types = {Types.STRUCT};
ResultSet rs = dmd.getUDTs("catalog-name", "schema-name", 
	"%", types);

returns descriptions of all the structured SQL types defined in the catalog- name.schema-name schema. If the driver does not support UDTs or no matching UDTs are found then an empty result set is returned. See section 8.5.3 for a description of the result set returned by getUDTs().

When the DATA_TYPE returned by getUDTs() is Types.STRUCT, the JAVA_CLASS column contains the fully qualified Java class name of a Java class. Instances of this class are manufactured by the JDBC driver when getObject() is called on a column of this STRUCT type. Thus, JAVA_CLASS defaults to java.sql.Struct for structured types. Chapter 9 discusses how this default can be modified by a Java application. We note here only that the JDBC API does not prohibit a driver from returning a subtype of the class named by JAVA_CLASS.