Informix JDBC Driver Programmer's Guide Manipulating Informix Data Types

Sun's JDBC 2.0 specification refers to an SQL type called a structured type or struct, which is equivalent to an Informix named row. The specification defines two approaches to exchange structured type data between a Java client and a relational database:

• Using the SQLData interface. A single Java class per named row type implements the SQLData interface. The class has a member for each element in the named row.
• Using the Struct interface. This interface instantiates the necessary Java object for each element in the named row and constructs an array of java.util.Object Java objects.

Whether Informix JDBC Driver instantiates a Java object or a Struct object for a fetched named row depends on whether there is a customized type-mapping entry or not, as follows:

• If there is an entry for a named row in the Connection.getTypeMap() map, or if you provided a type mapping using the getObject() method, a single Java object is instantiated.
• If there is no entry for a named row in the Connection.getTypeMap() map, and if you have not provided a type mapping using the getObject() method, a Struct object is instantiated.

Unnamed rows are always fetched into Struct objects.

Important: Regardless of whether you use the SQLData or Struct interface, if a named or unnamed row contains an opaque data type column, there must be a type-mapping entry for it. If you are using the Struct interface to access a row that contains an opaque data type column, you need a customized type map for the opaque data type column, but not for the row as a whole.

For more information about custom type mapping, see Mapping Data Types.

The Java class for the named row must implement the SQLData interface. The class must have a member for each element in the named row. The class can have other members in addition to these. The members can be in any order and need not be public. The class must implement the writeSQL(), readSQL(), and getSQLTypeName() methods as defined in the SQLData interface, and can implement additional methods. You can use the ClassGenerator utility to create the class; for more information, see The ClassGenerator Utility.

To link this Java class with the named row, create a customized type mapping using the Connection.setTypeMap() method or the getObject() method. For more information about type mapping, see Mapping Data Types.

You cannot use the SQLData interface to access unnamed rows.

The JDBC 2.0 documentation does not specify that Struct objects can be parameters to the PreparedStatement.setObject() method. However, Informix JDBC Driver can handle any object passed by the PreparedStatement.setObject() or ResultSet.getObject() method that implements the java.sql.Struct interface.

You must use the Struct interface to access unnamed rows.

You do not need to create your own class to implement the Struct interface. However, if you do not create your own class, you must perform a fetch to retrieve the ROW data and type information before you can insert or update the ROW data.

If you create your own class to implement the java.sql.Struct interface, the class you create has to implement all the java.sql.Struct methods, including the getSQLTypeName() method.

If you do not create your own Struct class, Informix JDBC Driver automatically calls the getSQLTypeName() method. In this case, this method returns the type name for a named row or the row definition for an unnamed row. If you create your own class, however, you can choose what the getSQLTypeName() method returns.

You have to return the row definition for unnamed rows, but you can return the row name or the row definition for named rows. Each has advantages:

• Row definition. The driver does not need to query the database server for the type information. In addition, the row definition returned does not have to match the named row definition exactly, because the database server provides casting, if needed. This is useful if you want to use strings to insert into an opaque type in a row, for example.
• Row name. If a user-defined routine takes a named row as a parameter, the signature has to match, so you must pass in a named row. For more information about user-defined routines, see the Informix Guide to SQL: Reference or the Informix Guide to SQL: Syntax.

Important: If you use the Struct interface for a named row and provide type-mapping information for the named row, a ClassCastException message is generated when the ResultSet.getObject() method is called, because Java cannot cast between an SQLData object and a Struct object.

Informix has extended the java.sql.SQLOutput and java.sql.SQLInput methods to support Collection and Interval objects in named and unnamed rows. These extensions include the following methods:

• The com.informix.jdbc.IfmxComplexSQLInput.readObject() method returns the appropriate java.util.Collection object if the data is a set, list, or multiset data type. This method returns the appropriate IntervalYM or IntervalDF object for an interval data type, depending on the qualifier.
• The com.informix.jdbc.IfmxComplexSQLOutput.writeObject() method accepts objects derived from the java.util.Collection interface or from IntervalYM and IntervalDF objects.

When a Struct or SQLData object inserts data into a row column and getSQLTypeName() returns the name of a named row, 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 it requests the data from the database server.

The following example includes a Java class that implements the java.sql.SQLData interface.

Here is the database schema:

Create row type fullname_t (first char(20), last char(20));

Create row type person_t (id int, name fullname_t, age int);

Create table teachers (person person_t, dept char (20));

Insert into teachers values ("row(100, row('Bill', 'Smith'), 27)", "physics");

Here is the fullname Java class:

import java.sql.*;

public class fullname implements SQLData

{

public String first;

public String last;

private String sql_type = "fullname_t";

public String getSQLTypeName()

{

return sql_type;

}

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

{

sql_type = type;

first = stream.readString();

last = stream.readString();

}

public void writeSQL (SQLOutput stream) throws SQLException

{

stream.writeString(first);

stream.writeString(last);

}

/*

* Function not required by SQLData interface, but makes

* it easier for displaying results.

*/

public String toString()

{

String s = "fullname: ";

s += "first: " + first + " last: " + last;

return s;

}

}

Here is the person Java class:

import java.sql.*;

public class person implements SQLData

{

public int id;

public fullname name;

public int age;

private String sql_type = "person_t";

public String getSQLTypeName()

{

return sql_type;

}

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

{

sql_type = type;

id = stream.readInt();

name = (fullname)stream.readObject();

age = stream.readInt();

}

public void writeSQL (SQLOutput stream) throws SQLException

{

stream.writeInt(id);

stream.writeObject(name);

stream.writeInt(age);

}

public String toString()

{

String s = "person:";

s += "id: " + id + "\n";

s += " name: " + name.toString() + "\n";

s += " age: " + age + "\n";

return s;

}

}

Here is an example of fetching a named row. The complete demonstration is in the demo1.java file in the complex-types directory. For more information, see Appendix A, Sample Code Files.

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

conn.setTypeMap(map);

map.put("fullname_t", Class.forName("fullname"));

map.put("person_t", Class.forName("person"));

...

PreparedStatement pstmt;

ResultSet rs;

pstmt = conn.prepareStatement("select person from teachers");

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

rs = pstmt.executeQuery();

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

while (rs.next())

{

person who = (person) rs.getObject(1);

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

System.out.println("Data fetched:");

System.out.println("row: " + who.toString());

}

pstmt.close();

The conn.getTypeMap() method returns the named row mapping information from the java.util.Map object through the Connection object.

The map.put() method registers the mappings between the nested named row on the database server, fullname_t, and the Java class fullname, and between the named row on the database server, person_t, and the Java class person.

The person who = (person) rs.getObject(1) statement retrieves the named row into the Java object who. Informix JDBC Driver recognizes that this object who is a named row, a distinct type, or an opaque type, because the information sent by the database server has an extended name of person_t. The driver looks up person_t and finds out it is a named row. The driver calls the map.get() method with the key person_t, which returns the person class object. An object of class person is instantiated.

The readSQL() method in the person class calls methods defined in the SQLInput interface to convert each field in the ROW column into a Java object and assign each to a member in the person class.

Here is an example method for inserting a Java object into a named row column using the setObject() method. The complete demonstration is in the demo2.java file in the complex-types directory. For more information, see Appendix A, Sample Code Files.

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

map.put("fullname_t", Class.forName("fullname"));

map.put("person_t", Class.forName("person"));

...

PreparedStatement pstmt;

System.out.println("Populate person and fullname objects");

person who = new person();

fullname name = new fullname();

name.last = "Jones";

name.first = "Sarah";

who.id = 567;

who.name = name;

who.age = 17;

String s = "insert into teachers values (?, `physics')";

pstmt = conn.prepareStatement (s);

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

pstmt.setObject(1, who);

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

int rowcount = pstmt.executeUpdate();

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

pstmt.close();

The conn.getTypeMap() method returns the named row mapping information from the java.util.Map object through the Connection object.

The map.put() method registers the mappings between the nested named row on the database server, fullname_t, and the Java class fullname, and between the named row on the database server, person_t, and the Java class person.

Informix JDBC Driver recognizes that the object who implements the SQLData interface, so it is either a named row, a distinct type, or an opaque type. Informix JDBC Driver calls the getSQLTypeName() method for this object (required for classes implementing the SQLData interface), which returns person_t. The driver looks up person_t and finds out it is a named row.

The writeSQL() method in the person class calls the corresponding SQLOutput.writeXXX() method for each member in the class, each of which maps to one field in the named row person_t. The writeSQL() method in the class contains calls to the SQLOutput.writeObject(name) and SQLOutput.writeInt(id) methods. Each member of the class person is serialized and written into a stream.

This example fetches an unnamed ROW column. Here is the database schema:

Create table teachers

(

person row(

id int,

name row(first char(20), last char(20)),

age int

),

dept char(20)

);

Insert into teachers values ("row(100, row('Bill', 'Smith'), 27)", "physics");

Here is the rest of the example. The complete demonstration is in the demo3.java file in the complex-types directory. For more information, see Appendix A, Sample Code Files.

PreparedStatement pstmt;

ResultSet rs;

pstmt = conn.prepareStatement("select person from teachers");

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

rs = pstmt.executeQuery();

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

rs.next();

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

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

System.out.println("\nData fetched:");

Integer id;

Struct name;

Integer age;

Object[] elements;

/* Get the row description */

String personRowType = person.getSQLTypeName();

System.out.println("person row description: " + personRowType);

System.out.println("");

/* Convert each element into a Java object */

elements = person.getAttributes();

/*

* Run through the array of objects in `person' getting out each structure

* field. Use the class Integer instead of int, because int is not an object.

*/

id = (Integer) elements[0];

name = (Struct) elements[1];

age = (Integer) elements[2];

System.out.println("person.id: " + id);

System.out.println("person.age: " + age);

System.out.println("");

/* Convert `name' as well. */

/* get the row definition for `name' */

String nameRowType = name.getSQLTypeName();

System.out.println("name row description: " + nameRowType);

/* Convert each element into a Java object */

elements = name.getAttributes();

/*

* run through the array of objects in `name' getting out each structure

* field.

*/

String first = (String) elements[0];

String last = (String) elements[1];

System.out.println("name.first: " + first);

System.out.println("name.last: " + last);

pstmt.close();

The Struct person = (Struct) rs.getObject(1) statement instantiates a Struct object if column 1 is a ROW type and there is no extended data type name (if it is a named row).

The elements = person.getAttributes(); statement performs the following actions:

• Allocates an array of java.lang.Object objects with the correct number of elements.
• Converts each element in the row into a Java object. If the element is an opaque type, you must provide type mapping in the Connection object or pass in a java.util.Map object in the call to the getAttributes() method.

The String personrowType = person.getSQLTypeName(); statement returns the row type information. If this type is a named row, the statement returns the name. Because the type is not a named row, the statement returns the row definition: row(int id, row(first char(20), last char(20)) name, int age).

The example then goes through the same steps for the unnamed row name as it did for the unnamed row person.

The following example uses a user-created class, GenericStruct, that implements the java.sql.Struct interface. As an alternative, you can use a Struct object returned from the ResultSet.getObject() method instead of the GenericStruct class.

import java.sql.*;

import java.util.*;

public class GenericStruct implements java.sql.Struct

{

private Object [] attributes = null;

private String typeName = null;

/*

* Constructor

*/

GenericStruct() { }

GenericStruct(String name, Object [] obj)

{

typeName = name;

attributes = obj;

}

public String getSQLTypeName()

{

return typeName;

}

public Object [] getAttributes()

{

return attributes;

}

public Object [] getAttributes(Map map) throws SQLException

{

// this class shouldn't be used if there are elements

// that need customized type mapping.

return attributes;

}

public void setAttributes(Object [] objArray)

{

attributes = objArray;

}

public void setSQLTypeName(String name)

{

typeName = name;

}

} /* GenericStruct class */

The following Java program inserts a ROW column. The complete demonstration is in the demo4.java file in the complex-types directory. For more information, see Appendix A, Sample Code Files.

PreparedStatement pstmt;

ResultSet rs;

GenericStruct gs;

String rowType;

pstmt = conn.prepareStatement("insert into teachers values (?, `Math')");

System.out.println("prepare insert...ok\n");

System.out.println("Populate name struct...");

Object[] name = new Object[2];

// populate inner row first

name[0] = new String("Jane");

name[1] = new String("Smith");

rowType = "row(first char(20), last char(20))";

gs = new GenericStruct(rowType, name);

System.out.println("Instantiate GenericStructObject...okay\n");

System.out.println("Populate person struct...");

// populate outer row next

Object[] person = new Object[3];

person[0] = new Integer(99);

person[1] = gs;

person[2] = new Integer(56);

rowType = "row(id int, " +

"name row(first char(20), last char(20)), " +

"age int)";

gs = new GenericStruct(rowType, person);

System.out.println("Instantiate GenericStructObject...okay\n");

pstmt.setObject(1, gs);

System.out.println("setObject()...okay");

pstmt.executeUpdate();

System.out.println("executeUpdate()...okay");

pstmt.close();

At the pstmt.setObject(1, struct) method in this example, Informix JDBC Driver determines that the information is to be transported from the client to the database server as a ROW column, because the GenericStruct object is an instance of the java.sql.Struct interface.

Each element in the array is serialized, verifying that each element matches the type as defined by the getSQLTypeName() method.

The ClassGenerator utility generates a Java class for a named row type defined in the system catalog. It is an Informix extension to Sun's JDBC 2.0 specification.

The created Java class implements the java.sql.SQLData interface. The class has members for each field in the named row, and the readSQL(), writeSQL(), and SQLData.readSQL() methods read the attributes in the order in which they appear in the definition of the named row type in the database. Similarly, writeSQL() writes the data to the stream in that order.

ClassGenerator is packaged in the ifxtools.jar file, so the CLASSPATH environment variable must point to ifxtools.jar.

Here is the ClassGenerator usage:

java ClassGenerator rowtypename [-u URL] [-c classname]

The default value for classname is the value for rowtypename.

If the URL parameter is not specified, the required information is retrieved from the setup.std file in the home directory.

Here is the structure of setup.std:

URL jdbc:host-name:port-number
informixserver informixservername
database database
user user
passwd password

First you create the named row on the database server:

create row type employee (name char (20), age int);

Next, you run the class generation:

java ClassGenerator employee 

The class generator generates employee.java, as shown next, and retrieves the database URL information from setup.std, which has the following contents:

URL jdbc:davinci:1528
database test
user scott
passwd tiger
informixserver picasso_ius

Here is the generated .java file:

import java.sql.*;

import java.math.*;

public class employee implements SQLData

{

public String name;

public int age;

private String sql_type;

public String getSQLTypeName() { return "employee"; }

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

{

sql_type = type;

name = stream.readString();

age = stream.readInt();

}

public void writeSQL (SQLOutput stream) throws SQLException

{

stream.writeString(name);

stream.writeInt(age);

}

}

First, you create the named row on the database server:

create row type manager (emp employee, salary int);

Next, you run the class generation. In this case, the setup.std file is not consulted, because you provide all the needed information at the command line:

java ClassGenerator manager -c Manager -u "jdbc:davinci:1528/test:user=scott;

password=tiger;informixserver=picasso_ius"

The -c option defines the Java class you are creating, which is Manager (with uppercase M).

The preceding command generates the following Java class:

import java.sql.*;

import java.math.*;

public class Manager implements SQLData

{

public employee emp;

public int salary;

private String sql_type;

public String getSQLTypeName() { return "manager"; }

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

{

sql_type = type;

emp = (employee)stream.readObject();

salary = stream.readInt();

}

public void writeSQL (SQLOutput stream) throws SQLException

{

stream.writeObject(emp);

stream.writeInt(salary);

}

}

The following SQLInput methods are not supported for selecting a ROW column into a Java object that implements SQLData:

• readByte()
• readCharacterStream()
• readRef()

The following SQLOutput methods are not supported for inserting a Java object that implements SQLData into a ROW column:

• writeByte(byte)
• writeCharacterStream(java.io.Reader x)
• writeRef(Ref x)