Informix JDBC Driver Programmer's Guide Manipulating Informix Data Types

Informix has extended the JDBC 2.0 definition of the java.sql.SQLInput and java.sql.SQLOutput methods to fully support Informix fixed binary and variable binary opaque types. This extension includes the following interfaces:

• IfmxUdtSQLInput
• IfmxUdtSQLOutput

The com.informix.jdbc.IfmxUdtSQLInput interface extends java.sql.SQLInput with several added methods. To use these methods, you must cast the SQLInput references to IfmxUdtSQLInput. Here are the signatures and descriptions of each added method:

public int length();


This method returns the total length of the entire data stream.

public String readString(int maxlen) throws SQLException;


This method reads the next attribute in the stream as a Java string.  This method is similar to the SQLInput.readString() method except that a fixed length of data is read in. Since the opaque type is unknown to the driver, you must supply a maximum length for the driver to read in the next attribute properly.

public byte[] readBytes(int maxlen) throws SQLException;


This method reads the next attribute in the stream as Java byte array.  This method is similar to the SQLInput.readBytes() method except that a fixed length of data is read in. Since the opaque type is unknown to the driver, you must supply a maximum length for the driver to read in the next attribute properly.

The com.informix.jdbc.IfmxUdtSQLOutput interface extends java.sql.SQLOutput with several added methods. To use these methods, you must cast the SQLOutput references to IfmxUdtSQLOutput. Here are the signatures and descriptions of each added method:

public void writeString(String x, int length) throws SQLException;

This method writes the next attribute to the stream as a Java String. This method is similar to the SQLOutput.writeString() method except that a fixed length of data is written to the stream. If the string passed in is shorter than the specified length, the driver pads the string with zeros. Since the opaque type is unknown to the driver, you must supply a length for the driver to write the next attribute properly.

public void writeBytes(byte[] b, int length) throws SQLException;

This method writes the next attribute to the stream as a Java byte array. This method is similar to the SQLOutput.writeBytes() method except that a fixed length of data is written to the stream. If the array passed in is shorter than the specified length, the driver pads the array with zeros. Since the opaque type is unknown to the driver, you must supply a length for the driver to write the next attribute properly.

Informix opaque types map to Java objects, which must implement the java.sql.SQLData interface. These Java objects describe all the data members that make up the opaque type. These Java objects are strongly typed; that is, each read or write method in the readSQL or WriteSQL method of the Java object must match the corresponding data member in the opaque type definition. Informix JDBC Driver cannot perform any type conversion because the type structure is unknown to it.

Furthermore, the driver also requires that all opaque data be transported as Informix DataBlade API data types, as defined in incl/public/mitypes.h (this file is included in all Informix Dynamic Server with Universal Data Option installations). All opaque data is stored in the database server table in a C struct, which is made up of various DataBlade API types, as defined in the opaque type. For more information, see the DataBlade API Programmer's Manual.

The following table lists the mapping of DataBlade API types to their corresponding Java types.

The C struct may contain padding bytes. The driver automatically skips these padding bytes to make sure the next data member is properly aligned. Therefore, your Java objects do not have to take care of alignment themselves.

In addition, 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 an opaque type column and getSQLTypeName() returns the name of the opaque 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.

You can insert an opaque type as either its original type or its cast type. Here is an example of how to insert opaque data using the original type:

String s = "insert into charattr_tab (int_col, charattr_col) values (?, ?)";

System.out.println(s);

pstmt = conn.prepareStatement(s);

...

charattrUDT charattr = new charattrUDT();

charattr.chr1 = "a";

charattr.bold = true;

charattr.fontsize = (short)1;

pstmt.setInt(1, 1);

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

pstmt.setObject(2, charattr);

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

pstmt.executeUpdate();

If a casting function is defined and you would like to insert data as the casting type instead of the original type, you must call the setXXX() method that corresponds to the casting type. For example, if you have defined a function casting CHAR or LVARCHAR to a charattrUDT column, you can use the setString() method to insert data, as follows:

// Insert into UDT column using setString(int,String) and Java String object.

String s =

"insert into charattr_tab " +

"(decimal_col, date_col, charattr_col, float_col) " +

"values (?,?,?,?)";

writeOutputFile(s);

PreparedStatement pstmt = myConn.prepareStatement(s);

...

String strObj = "(A, f, 18)";

pstmt.setString(3, strObj);

...

The charattrUDT class must implement the SQLData interface, as in the following example:

import java.sql.*;

import com.informix.jdbc.*;

/*

* C struct of charattr_udt:

*

* typedef struct charattr_type

* {

* char chr1[4+1];

* mi_boolean bold; // mi_boolean (1 byte)

* mi_smallint fontsize; // mi_smallint (2 bytes)

* }

* charattr;

*

* typedef charattr charattr_udt;

*

*/

public class charattrUDT implements SQLData

{

private String sql_type = "charattr_udt";

// an ASCII character/a multibyte character, and is null-terminated.

public String chr1;

// Is the character in boldface?

public boolean bold;

// font size of the character

public short fontsize;

public charattrUDT() { }

public charattrUDT(String chr1, boolean bold, short fontsize)

{

this.chr1 = chr1;

this.bold = bold;

this.fontsize = fontsize;

}

public String getSQLTypeName()

{

return sql_type;

}

// reads a stream of data values and builds a Java object

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

{

sql_type = type;

chr1 = ((IfmxUdtSQLInput)stream).readString(5);

bold = stream.readBoolean();

fontsize = stream.readShort();

}

// writes a sequence of values from a Java object to a stream

public void writeSQL(SQLOutput stream) throws SQLException

{

((IfmxUdtSQLOutput)stream).writeString(chr1, 5);

stream.writeBoolean(bold);

stream.writeShort(fontsize);

}

// overides Object.equals()

public boolean equals(Object b)

{

return (chr1.equals(((charattrUDT)b).chr1) &&

bold == ((charattrUDT)b).bold &&

fontsize == ((charattrUDT)b).fontsize);

}

public String toString()

{

return "chr1=" + chr1 + " bold=" + bold + " fontsize=" + fontsize;

}

}

In your JDBC application, a custom type map must map the type name charattr_udt to the charattrUDT class, 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("charattr_udt", Class.forName("charattrUDT"));

To retrieve Informix opaque types, you must use ResultSet.getObject(). Informix JDBC Driver converts the data to a Java object according to the custom type map you provide. Using the previous example of the charattrUDT type, you can fetch the opaque data, as in the following example:

String s = "select int_col, charattr_col from charattr_tab order by 1";

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++;

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

int intret = rs.getInt("int_col");

System.out.println("\tint_col " + intret);

charattrUDT charattrret = (charattrUDT)rs.getObject("charattr_col");

System.out.print("\tcharattr_col ");

if (curRow == 2 || curRow == 6)

{

if (rs.wasNull())

System.out.println("<null>");

else

System.out.println("***ERROR: " + charattrret);

}

else

System.out.println(charattrret+"");

} //while

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

stmt.close();

A smart large object can be a data member within an opaque type, although you are most likely to create a large object on the database server, outside of the opaque type context, using the Informix BLOB and CLOB extension classes.

A large object is stored as a MI_LO_HANDLE object within the opaque type. The MI_LO_HANDLE object is created using the methods provided in com.informix.jdbc.IfxSmartBlob, and the large object handle obtained from these methods becomes the data member within the opaque type. Both BLOB and CLOB objects use the same MI_LO_HANDLE, as follows:

import java.sql.*;

import com.informix.jdbc.*;

/*

* C struct of large_bin_udt:

*

* typedef struct LARGE_BIN_TYPE

* {

* MI_LO_HANDLE lb_handle; // handle to large object (72 bytes)

* }

* large_bin_udt;

*

*/

public class largebinUDT implements SQLData

{

private String sql_type = "large_bin_udt";

public Clob lb_handle;

public largebinUDT() { }

public largebinUDT(Clob clob)

{

lb_handle = clob;

}

public String getSQLTypeName()

{

return sql_type;

}

// reads a stream of data values and builds a Java object

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

{

sql_type = type;

lb_handle = stream.readClob();

}

// writes a sequence of values from a Java object to a stream

public void writeSQL(SQLOutput stream) throws SQLException

{

stream.writeClob(lb_handle);

}

}

In a JDBC application, you create the MI_LO_HANDLE object using the methods provided by the IfxSmartBlob class:

String s = "insert into largebin_tab (int_col, largebin_col, lvc_col) " +

"values (?,?,?)";

System.out.println(s);

pstmt = conn.prepareStatement(s);

...

// create a large object using IfxSmartBlob's methods

String filename = "lbin_in1.dat";

File file = new File(filename);

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

FileInputStream fin = new FileInputStream(file);

IfxLobDescriptor loDesc = new IfxLobDescriptor(conn);

System.out.println("create large object descriptor...ok");

IfxLocator loPtr = new IfxLocator();

IfxSmartBlob smb = new IfxSmartBlob((IfxConnection)conn);

int loFd = smb.IfxLoCreate(loDesc, 8, loPtr);

System.out.println("create large object...ok");

int n = smb.IfxLoWrite(loFd, fin, fileLength);

System.out.println("write file content into large object...ok");

pstmt.setInt(1, 1);

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

// initialize largebin object using the large object created

// above, before doing setObject for the large_bin_udt column.

largebinUDT largebinObj = new largebinUDT();

largebinObj.lb_handle = new IfxCblob(loPtr);

pstmt.setObject(2, largebinObj);

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

pstmt.setString(3, "Hong Kong");

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

pstmt.executeUpdate();

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

// close/release large object

smb.IfxLoClose(loFd);

System.out.println("close large object...ok");

smb.IfxLoRelease(loPtr);

System.out.println("release large object...ok");

See Manipulating Informix BLOB and CLOB Data Types for details.

The following methods are not supported for opaque types:

• java.sql.SQLInput
• readAsciiStream()
• readBinaryStream()
• readBytes()
• readCharacterStream()
• readObject()
• readRef()
• readString()
• java.sql.SQLOutput
• writeAsciiStream(InputStream x)
• writeBinaryStream(InputStream x)
• writeBytes(byte[] x)
• writeCharacterStream(Reader x)
• writeObject(Object x)
• writeRef(Ref x)
• writeString(String x)