Informix JDBC Driver Programmer's Guide Manipulating Informix Data Types

A distinct type can map to the underlying base type or to a user-defined Java object. For example, a distinct type of int can map to int or to a Java object that encapsulates the data representation. This Java object must implement the java.sql.SQLData interface. You must provide a custom type map as described in Mapping Data Types to map this Java object to the corresponding SQL type name.

When an SQLData object inserts data into a distinct type column and getSQLTypeName() returns the name of the distinct type, Informix JDBC Driver uses the type information to verify that the data provided matches the data the database server expects. The driver asks the database server for the type information each time.

However, you can set an environment variable in the database URL, ENABLE_CACHE_TYPE=1, so the driver caches the type information the first time it is retrieved. In this case, Informix JDBC Driver asks the cache for the type information before requesting the data from the database server.

A distinct type can map to either the underlying base type or to a user-defined Java object that implements the SQLData interface. Here is the SQL statement that defines the distinct type:

CREATE DISTINCT TYPE mymoney AS NUMERIC(10, 2);

CREATE TABLE distinct_tab (mymoney_col mymoney);

Here is an example of mapping to the base type:

String s = "insert into distinct_tab (mymoney_col) values (?)";

System.out.println(s);

pstmt = conn.prepareStatement(s);

...

BigDecimal bigDecObj = new BigDecimal(123.45);

pstmt.setBigDecimal(1, bigDecObj);

System.out.println("setBigDecimal...ok");

pstmt.executeUpdate();

When you map to the underlying type, Informix JDBC Driver maps to the underlying type on the client side, because the database server provides implicit casting between the underlying type and the distinct type.

You can also map distinct types to Java objects that implement the SQLData interface. Here is the SQL statement that defines the distinct type:

CREATE DISTINCT TYPE mymoney AS NUMERIC(10, 2);

Here is the rest of the example:

import java.sql.*;

import com.informix.jdbc.*;

public class myMoney implements SQLData

{

private String sql_type = "mymoney";

public java.math.BigDecimal value;

public myMoney() { }

public myMoney(java.math.BigDecimal value)

{

this.value = value;

}

public String getSQLTypeName()

{

return sql_type;

}

public void readSQL(SQLInput stream, String type) throws SQLException

{

sql_type = type;

value = stream.readBigDecimal();

}

public void writeSQL(SQLOutput stream) throws SQLException

{

stream.writeBigDecimal(value);

}

// overides Object.equals()

public boolean equals(Object b)

{

return value.equals(((myMoney)b).value);

}

public String toString()

{

return "value=" + value;

}

}

...

String s = "insert into distinct_tab (mymoney_col) values (?)";

pstmt = conn.prepareStatement(s);

myMoney mymoney = new myMoney();

mymoney.value = new java.math.BigDecimal(123.45);

pstmt.setObject(1, mymoney);

System.out.println("setObject(myMoney)...ok");

pstmt.executeUpdate();

In this case, you use the setObject() method instead of the setBigDecimal() method to insert data.

You can fetch a distinct type as its underlying base type or as a Java object, if the mapping is defined in a custom type map. Using the previous example, you can fetch the data as a Java object as in the following example:

java.util.Map customtypemap = conn.getTypeMap();

System.out.println("getTypeMap...ok");

if (customtypemap == null)

{

System.out.println("\n***ERROR: typemap is null!");

return;

}

customtypemap.put("mymoney", Class.forName("myMoney"));

...

String s = "select mymoney_col from distinct_tab order by 1";

try

{

System.out.println("Select (i)");

System.out.println(s);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(s);

System.out.println("execute...ok");

System.out.println("Fetching data ...");

int curRow = 0;

while (rs.next())

{

curRow++;

myMoney mymoneyret = (myMoney)rs.getObject("mymoney_col");

System.out.println("currentrow=" + curRow + " : " + mymoneyret);

}

System.out.println("total rows expected: " + curRow);

stmt.close();

}

catch (SQLException e)

{

System.out.println("***ERROR: " + e.getErrorCode() + " " + e.getMessage());

e.printStackTrace();

}

The following methods are not supported for distinct types:

• java.sql.SQLInput
• readCharacterStream()
• readRef()
• java.sql.SQLOutput
• writeCharacterStream(Reader x)
• writeRef(Ref x)