Informix JDBC Driver Programmer's Guide Manipulating Informix Data Types

This section discusses mapping issues between data types defined in a Java program and the data types supported by the Informix database server. In particular, it covers the following topics:

• Mapping between JDBC API data types and Informix data types, next
• PreparedStatement.setXXX() methods supported by Informix JDBC Driver on page 3-68.
• ResultSet.getXXX() methods supported by Informix JDBC Driver on page 3-77.

Since there are variations between the SQL data types supported by each database vendor, the JDBC API defines a set of generic SQL data types in the class java.sql.Types. Use these JDBC API data types to reference generic SQL types in your Java programs that use the JDBC API to connect to Informix databases.

The following table shows the Informix data type to which each JDBC API data type maps.

The LONGVARBINARY and LONGVARCHAR JDBC types can also map to Informix BLOB and CLOB types, respectively.

Important: Informix JDBC Driver maps java.sql.Timestamp to the Informix type datetime year to fraction(5) and java.sql.Time to the Informix type datetime hour to second. Informix datetime types are very restrictive and are not interchangeable. If you attempt to map java.sql.Time to datetime year to fraction(5) or java.sql.Timestamp to datetime hour to second, you might get an error from the Informix database server. Any other datetime qualifiers are not supported.

Important: The Ref type is not supported by Informix servers.

The following table lists mappings between the extended data types supported in Informix Dynamic Server with Universal Data Option and the corresponding Java and JDBC types.

A Java boolean object can map to a smallint object or an Informix boolean data type. Informix JDBC Driver attempts to map it according to the column type. However, in cases such as PreparedStatement host variables, Informix JDBC Driver cannot access the column types, so the mapping is somewhat limited. Refer to "PreparedStatement.setXXX() Extensions," next, for more details on data type mapping.

Informix Dynamic Server with Universal Data Option introduces many extended data types, such as BLOB, CLOB, boolean, lvarchar, and opaque types. As a result, there can be multiple mappings between a JDBC or Java data type and the corresponding Informix data type.

For example, you can use PreparedStatement.setAsciiStream() to insert into either a text column or a CLOB column. Similarly, you can also use PreparedStatement.setBinaryStream() to insert into a byte column or a BLOB column. Because the actual column information is not available to Informix JDBC Driver at all times, there can be ambiguity for the driver when it maps data types.

Normally, with INSERT, SELECT, or DELETE statements, the column information is available to the driver, so the driver can determine how the data can be sent to the database server.

However, when the data is referenced in an UPDATE statement or inside a WHERE clause, Informix JDBC Driver does not have access to the column information. In those cases, unless you use the extended methods listed next, the driver maps those data columns using the corresponding pre-Universal Data Option data types listed in the table on page 3-65. For the PreparedStatement.setAsciiStream() method, the driver tries to map to a text data type, and for the PreparedStatement.setBinaryStream() method, it tries to map to a byte data type.

To direct the driver to map to a certain data type (so there is no ambiguity in UPDATE statements and WHERE clauses), you can use extensions to the PreparedStatement.setXXX() methods. The Informix type must be the last parameter to the standard JDBC PreparedStatement.setXXX() interface.

The only data types that might have ambiguity are boolean, lvarchar, text, byte, BLOB, and CLOB. Refer to the data type mapping table on page 3-67 for more details on how these extended data types are mapped to Java and JDBC types.

The java.sql.PreparedStatement interface enables you to map to a specific Informix data type in cases that might result in ambiguity when you are inserting or updating data. The extensions are listed here. Each extension is followed by its full signature.

IfmxPreparedStatement.setArray()

public void setArray(int parameterIndex, java.sql.Array x, int ifxType) throws

SQLException

IfmxPreparedStatement.setAsciiStream()

public void setAsciiStream(int i, InputStream x, int length, int ifxType) throws

SQLException

IfmxPreparedStatement.setBigDecimal()

public void setBigDecimal(int i, java.math.BigDecimal x, int ifxType)

throws SQLException

IfmxPreparedStatement.setBinaryStream()

public void setBinaryStream(int i, InputStream x, int length, int ifxType) throws

SQLException

IfmxPreparedStatement.setBlob()

public void setBlob(int parameterIndex, Blob x, int ifxType) throws SQLException

IfmxPreparedStatement.setBoolean()

public void setBoolean(int i, boolean x, int ifxType) throws SQLException

IfmxPreparedStatement.setByte()

public void setByte(int i, byte x, int ifxType) throws SQLException

IfmxPreparedStatement.setBytes()

public void setBytes(int i, byte x[], int ifxType) throws SQLException

IfmxPreparedStatement.setCharacterStream()

public void setCharacterStream(int parameterIndex, java.io.Reader reader,

int length, int ifxType) throws SQLException

IfmxPreparedStatement.setClob()

public void setClob(int parameterIndex, Clob x, int ifxType) throws SQLException

IfmxPreparedStatement.setDate()

public void setDate(int i, Date x, int ifxType) throws SQLException

IfmxPreparedStatement.setDate()

public void setDate(int parameterIndex, java.sql.Date x, Calendar Cal,

int ifxType) throws SQLException

IfmxPreparedStatement.setDouble()

public void setDouble(int i, double x, int ifxType) throws SQLException

IfmxPreparedStatement.setFloat()

public void setFloat(int i, float x, int ifxType) throws SQLException

IfmxPreparedStatement.setInt()

public void setInt(int i, int x, int ifxType) throws SQLException

IfmxPreparedStatement.setLong()

public void setLong(int i, long x, int ifxType) throws SQLException

IfmxPreparedStatement.setNull()

public void setNull(int i, int sqlType, int ifxType) throws SQLException

IfmxPreparedStatement.setShort()

public void setShort(int i, short x, int ifxType) throws SQLException

IfmxPreparedStatement.setString()

public void setString(int i, String x, int ifxType) throws SQLException

IfmxPreparedStatement.setTime()

public void setTime(int i, Time x, int ifxType) throws SQLException

IfmxPreparedStatement.setTime()

public void setTime(int parameterIndex, java.sql.Time x, Calendar Cal,

int ifxType) throws SQLException

IfmxPreparedStatement.setTimestamp()

public void setTimestamp(int i, Timestamp x, int ifxType) throws SQLException

IfmxPreparedStatement.setTimestamp()

public void setTimestamp(int parameterIndex, java.sql.Timestamp x, Calendar Cal)

throws SQLException

IfmxPreparedStatement.IfxSetObject()

public void IfxSetObject(int i, Object x, int scale, int ifxType) throws

SQLException

IfmxPreparedStatement.IfxSetObject()

public void IfxSetObject(int i, Object x, int ifxType) throws SQLexception

For the IfmxPreparedStatement.IfxSetObject extension, you cannot simply overload the method signature with an added ifxType parameter, because such overloading creates method ambiguity. You must name the method to IfxSetObject instead.

To use the preceding methods, you must cast your PreparedStatement references to IfmxPreparedStatement:

File file = new File("sblob_06.dat");

int fileLength = (int)file.length();

byte[] buffer = new byte[fileLength];

FileInputStream fin = new FileInputStream(file);

fin.read(buffer,0,fileLength);

String str = new String(buffer);

writeOutputFile("Prepare");

PreparedStatement p_stmt = myConn.prepareStatement(

"insert into sblob_t20(c1) values(?)");

writeOutputFile("IfxSetObject");

((IfmxPreparedStatement)p_stmt).IfxSetObject(

1,str,30,IfxTypes.IFX_TYPE_CLOB);

The extended IfmxPreparedStatement methods require you to pass in the Informix data type to which you want to map. These types are part of the com.informix.lang.IfxTypes class. The following table shows the IfxTypes constants and the corresponding Informix data types.

The following table lists the PreparedStatement.setXXX() methods that Informix JDBC Driver supports for nonextended data types. The top heading lists the standard JDBC API data types defined in the java.sql.Types class. These translate to specific Informix data types, as shown in the table on page 3-65. The table lists the setXXX() methods you can use to write data of a particular JDBC API data type.

An uppercase and bold X indicates the setXXX() method Informix recommends you use; a lowercase x indicates other setXXX() methods supported by Informix JDBC Driver.

The setNull() method writes an SQL null value.

The following table lists the PreparedStatement.setXXX() methods that Informix JDBC Driver supports for the Informix extended data types, the mappings for which are shown in the table on page 3-67. The table lists the setXXX() methods you can use to write data of a particular extended data type.

An uppercase and bold X indicates the setXXX() method Informix recommends you use; a lowercase x indicates other setXXX() methods supported by Informix JDBC Driver. The table does not include setXXX() methods that you cannot use with any of the Informix extended data types.

The setNull() method writes an SQL null value.

Use the ResultSet.getXXX() methods to transfer data from an Informix database to a Java program that uses the JDBC API to connect to an Informix database. For example, use the ResultSet.getString() method to get the data stored in a column of data type LVARCHAR.

Important: If you use an expression within an SQL statement-for example, SELECT mytype::LVARCHAR FROM mytab-you might not be able to use ResultSet.getXXX(columnName) to retrieve the value. Use ResultSet.getXXX(columnIndex) to retrieve the value instead.

The following table lists the ResultSet.getXXX() methods that Informix JDBC Driver supports for nonextended data types. The top heading lists the standard JDBC API data types defined in the java.sql.Types class. These translate to specific Informix data types, as shown in the table on page 3-65. The table lists the getXXX() methods you can use to retrieve data of a particular JDBC API data type.

An uppercase and bold X indicates the getXXX() method Informix recommends you use; a lowercase x indicates other getXXX() methods supported by Informix JDBC Driver.

The getXXX() methods return a null value if the retrieved column value is an SQL null value.

The following table lists the ResultSet.getXXX() methods that Informix JDBC Driver supports for the Informix extended data types, the mappings for which are shown in the table on page 3-67. The table lists the getXXX() methods you can use to retrieve data of a particular extended data type.

An uppercase and bold X indicates the getXXX() method Informix recommends you use; a lowercase x indicates other getXXX() methods supported by Informix JDBC Driver. The table does not include getXXX() methods that you cannot use with any of the Informix extended data types.

The getXXX() methods return a null value if the retrieved column value is an SQL null value.