IBM INFORMIX OIJ

TO

IBM INFORMIX JDBC DRIVER

MIGRATION

GUIDE

Scott M. Stevens

February 1, 1999

Table of Contents

1. Introduction

2. Environment Setup

3. Package Changes

4. Setting up a server/database connection

5. Establishing a server/database connection

6. Executing a database query

7. Converting OIJ Value Objects to JDBC

8. Converting OIJ Cursor Logic to JDBC

9. Establishing an RMI connection with JDBC

10. Error Handling and Tracing Support

11. csql3.java (entire OIJ example)

12. csql3.java (entire JDBC example)

- Introduction

This document aides existing INFORMIX-Object Interface for Java (OIJ) customers to migrate their applications to JDBC using the IBM Informix JDBC Driver.

Each section of this document provides a discussion of a current OIJ implementation and the steps needed to migrate to a JDBC implementation.

At the end of this document, the complete OIJ csql3.java example application/applet has been converted to a JDBC implementation.

- Environment Setup

All of the classes used by the IBM INFORMIX JDBC Driver are included in a pre-packaged jar file called ifxjdbc.jar.

If you are converting an existing OIJ application to a JDBC application then the following setup must be performed:

- Add the directory path location of the ifxjdbc.jar file to the CLASSPATH environment variable.

For example:

setenv CLASSPATH $JDBCLOCATION/ifxjdbc.jar:$CLASSPATH

(where $JDBCLOCATION refers to the directory where the driver is installed)

OR

- Unjar the ifxjdbc.jar file and add the top level directory location of the unjarred file to the CLASSPATH environment variable.

For example:

jar xvf ifxjdbc.jar

setenv CLASSPATH $JDBCLOCATION:$CLASSPATH

(where ifxjdbc.jar contents is located underneath top level directory path)

If you are converting an existing OIJ applet to a JDBC applet that is to be downloaded from a web server then the following setup must be performed:

- Install the ifxjdbc.jar file in the same directory location as the applet.
- Add ifxjdbc.jar in the APPLET tag of the HTML file that references the applet. If the main applet (i.e. csql3.class) references other classes (i.e. ExampleApplet.class, InfoDialog.class, etc) then these classes can be bundled in another jar file (i.e. classbundle.jar) ) that can also be included as part of the APPLET tag.

For example:

<APPLET CODE="csql3.class"

ARCHIVE="classbundle.jar,ifxjdbc.jar"

CODEBASE="http://turquoise:8026/"

width=600 height=620>

Summary of Setup changes from OIJ to JDBC

:

- Change all references from "informix.jar" to "ifxjdbc.jar".

- Package Changes

Every "import" statement that references the OIJ "informix.api.*" packages must be deleted. JDBC usage requires an import statement for the "java.sql.*" packages.

- Setting up a server/database connection

The IBM INFORMIX OIJ product used the concept of credentials (direct and remote) to setup server/database connections. Credentials specify the information required to open these connections. When creating a connection, you could pass the connection parameters that comprise the specific credential (i.e. database, username, database server, password).

To migrate OIJ credential logic to JDBC, you need to form a database URL object that resembles the following:

jdbc:<sub-protocol>://<host>:<port-number>[/<dbname>]:INFORMIXSERVER=<server-name>;user=<user-name>;password=<password>

<sub-protocol>= informix-sqli (only sub-protocol currently supported)

<host> = host machine or ip-address where database server resides

<port-number> = database server port id

<dbname> = database name (optional for making a connection directly to a database)

<server-name> = name of default database server

<user-name> = username used to connect to database server

<password> = password used to connect to database server

OIJ Credential implementation examples

// Setup credential object

Credentials cred;

// Setup Direct credentials

cred = new DirectCredentials(db, user, sys, passwd);

// Setup RMI credentials

cred = new RMICredentials(appServer, db, user, sys, passwd);

JDBC implementation of Credential examples

// Setup URL object

String url = null;

// Setup URL for an INFORMIX-SQLI connection (ip-address used)

// (currently only sub-protocol that is supported)

url = "jdbc:informix-sqli://158.58.60.88:151/" + db + ":informixserver=" + sys + ";user=" + user + ";password=" + passwd;

// Setup URL for an INFORMIX-SQLI connection (host machine name used)

// (currently only sub-protocol that is supported)

url = "jdbc:informix-sqli://turquoise:151/" + db + ":informixserver=" + sys + ";user=" + user + ";password=" + passwd;

NOTE:

There is currently no way to directly convert a RMI Credential call from OIJ to JDBC. See section IX "Establishing an RMI connection with JDBC" for what you might do to use a JDBC/RMI implementation.

- Establishing a server/database connection

To establish a connection to the server/database, OIJ makes a call to the connection() method of the Credentials class. The connection is not useable until the open() method of the Connection class is called on the associated Connection object.

For JDBC, you must first explicitly load the IBM Informix JDBC driver and then open a connection to the server/database using the previously formed database URL object.

OIJ connection implementation example

//Create a connection

conn = cred.connection();

//Open a connection

conn.open();

JDBC implementation of connection example

// Load the IBM Informix JDBC Driver

DriverManager.registerDriver((Driver) Class.forName("com.informix.jdbc.IfxDriver").newInstance());

// Alternate way of loading the IBM Informix JDBC Driver

// May be used as a shortcut in Netscape Communicator browser

Class.forName("com.informix.jdbc.IfxDriver");

//Create and open a server/database connection

conn = DriverManager.getConnection(url);

- Executing a database query

To execute a database query with OIJ, several methods are available, depending on the type of SQL query. These methods are defined below along with the associated JDBC migration implementation:

- Query.ExecForStatus() – Queries that do not return any data.

OIJ Query.execForStatus() implementation example

Query query;

try

{

query.execForStatus("drop table blah;");

}

catch (Exception exce)

{

System.out.println("Caught: " + exce.toString());

}

JDBC implementation of Query.execForStatus() example

Connection conn = null;

Statement query = null;

try

{

query = conn.createStatement();

query.executeUpdate("drop table blah");

query.close();

}

catch (SQLException exce)

{

System.out.println("Caught: " + exce.getMessage());

}

- Query.ExecOneRow() - Queries that return (or expect to return) one row.

OIJ Query.ExecOneRow() implementation example

Query query;

Row row = null;

try

{

row = query.execOneRow("select * from employee where name = ‘Joe Employee’;");

String name = row.column("name").toString().trim();

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

String address = row.column("address").toString().trim();

System.out.println("address: " + address);

}

catch (DBException exce)

{

System.out.println("Caught: " + exce.getMessage());

}

JDBC implementation of Query.ExecOneRow() example

Connection conn = null;

Statement query = null;

ResultSet rs = null;

try

{

query = conn.createStatement();

rs = query.executeQuery("select * from employee where name = ‘Joe Employee’");

while (rs.next());

{

String name = rs.getString("name");

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

String address = rs.getString("address");

System.out.println("address: " + address);

}

rs.close();

query.close();

}

catch (SQLException exce)

{

System.out.println("Caught: " + exce.getErrorCode());

}

- Query.ExecToSet() - Queries that return more than one row. Query is run to completion and the results are stored in a single object in the client program’s memory. Connection is free for other queries.

OIJ Query.ExecToSet() implementation example

Query query;

Set set = null;

Value v;

String rowString = new String();

try

{

set = query.execToSet("select * from tmp;");

set.open();

while ((v = set.fetch()) != null)

{

rowString = v.toString();

System.out.println(rowString);

}

set.close();

}

catch (DBException exce)

{

System.out.println("Caught: " + exce.getMessage());

}

JDBC implementation of Query.ExecToSet() example

Connection conn = null;

Statement query = null;

ResultSet rs = null;

String st = new String();

try

{

query = conn.createStatement();

rs = query.executeQuery("select * from tmp");

while (rs.next())

{

System.out.println(rs.getString(1));

}

rs.close();

query.close();

}

catch (SQLException exce)

{

System.out.println("Caught: " + exce.getErrorCode());

}

- Query.ExecForIteration() – Queries that return a result set that must be processed one row at a time. The application must call nextRow() method to access the individual rows in the result set.

OIJ Query.ExecForIteration() implementation example

Query query;

Row row;

if (!query.execForIteration("select * from tmp;"));

{

System.out.println("Could not execute query");

}

while ((row = query.nextRow()) != null)

{

String rowString = new String();

for (Enumeration e = Enumerators.enumerator (row); e.hasMoreElements(); )

{

Value v = (Value) e.nextElement();

if (v != null)

rowString += v.toString();

}

System.out.println(rowString);

}

JDBC implementation of Query.ExecForIteration() example

Connection conn = null;

Statement query = null;

ResultSet rs = null;

query = conn.createStatement();

rs = query.executeQuery("select * from tmp");

ResultSetMetaData rsmd = rs.getMetaData();

int ncols = rsmd.getColumnCount();

String s = null;

//Get each row of data

while (rs.next())

{

String st = new String();

for (int numcol = 1; numcol <= ncols; numcol++)

{

switch(rsmd.getColumnType(numcol))

{

case Types.CHAR:

case Types.VARCHAR:

case Types.LONGVARCHAR:

s = rs.getString(numcol);

break;

case Types.SMALLINT:

short v = rs.getShort(numcol);

if (!rs.wasNull() )

s = "" + v;

break;

case Types.INTEGER:

int y = rs.getInt(numcol);

if (!rs.wasNull() )

s = "" + y;

break;

// Add more cases based on datatypes …

}

if (rs.wasNull() )

s = "<null> ";

st = st + s.trim() + " ";

} // for loop

st = st + "\n";

System.out.println(st);

} // while loop

rs.close();

query.close();

- Cursor.Open() - Open a cursor for a SELECT statement. Cursor.fetch() method is used to retrieve rows from the open cursor.

OIJ Cursor.Open() implementation example

Connection conn;

Cursor cursor;

Row row;

cursor = conn.cursor();

conn.open();

conn.setTransaction(Connection.BEGIN);

cursor.define("select * from tmp;", Cursor.SCROLL));

cursor.open();

while ((row = (Row) cursor.fetch()) != null

{

String rowString = new String();

for (Enumeration e = Enumerators.enumerator (row); e.hasMoreElements(); )

{

Value v = (Value) e.nextElement();

if (v != null)

rowString += v.toString();

}

System.out.println(rowString);

}

cursor.close();

conn.setTransaction(Connection.COMMIT);

JDBC implementation of Cursor.Open() example

Connection conn = null;

Statement query = null;

ResultSet rs = null;

conn.setAutoCommit(false);

query = conn.createStatement();

rs = query.executeQuery("select * from tmp");

ResultSetMetaData rsmd = rs.getMetaData();

int ncols = rsmd.getColumnCount();

String s = null;

//Get each row of data

while (rs.next())

{

String st = new String();

for (int numcol = 1; numcol <= ncols; numcol++)

{

switch(rsmd.getColumnType(numcol))

{

case Types.CHAR:

case Types.VARCHAR:

case Types.LONGVARCHAR:

s = rs.getString(numcol);

break;

case Types.SMALLINT:

short v = rs.getShort(numcol);

if (!rs.wasNull() )

s = "" + v;

break;

case Types.INTEGER:

int y = rs.getInt(numcol);

if (!rs.wasNull() )

s = "" + y;

break;

// Add more cases based on datatypes …

}

if (rs.wasNull() )

s = "<null> ";

st = st + s.trim() + " ";

} // for loop

st = st + "\n";

System.out.println(st);

} // while loop

rs.close();

query.close();

conn.commit();

- Statement.exec() - Execute a prepared SQL statement associated with a Statement object.

OIJ Statement.exec() implementation example

Connection conn;

Query query;

Statement pstmt;

Value values[] = new Value[1];

query.execForStatus("create table informixfans (name varchar(128));");

pstmt = conn.statement("insert into informixfans values(?);");

values[0].fromString("Joe Employee");

pstmt.exec(values);

values[0].fromString("Hello World");

pstmt.exec(values);

JDBC implementation of Statement.exec() example

Connection conn = null;

PreparedStatement pstmt = null;

Statement stmt = null;

stmt = conn.createStatement();

stmt.executeUpdate("create table informixfans (name varchar(128))");

pstmt = conn.prepareStatement("insert into informixfans values(?);");

pstmt.setString(1, "Joe Employee");

pstmt.executeUpdate();

pstmt.setString(1, "Hello World");

pstmt.executeUpdate();

Converting OIJ Value Objects to JDBC

OIJ creates Java objects that encapsulate data retrieved from an IBM Informix database. Value objects are used to create and deploy new client objects that represent new database server data types. An extensible class factory is used that maps database server data types to Java objects.

When converting an OIJ application that uses Value objects to JDBC, you need to have logic that queries the database metadata for the type of object referenced, and then perform a switch on the type returned. For each built-in type, there is an associated getXXX() method of the ResultSet class that performs the datatype/java class conversion. See the "IBM Informix JDBC Driver" documentation section titled "Data Type Mapping" for all of the currently supported built-in types and the associated getXXX() methods. See sections XI and XII of this document for the entire csql3.java example that shows the logic in detail for converting an OIJ value object to JDBC.

Currently, version 1.2 of the JDBC standard only supports SQL92 datatypes. Opaque datatypes, including user defined types, row types and collections, are supported in the JDBC 2.0 standard. Below are some examples of manipulating collections and user defined types in OIJ and performing the equivalent using JDBC 2.0.

NOTE: The OIJ product works with JDK 1.1.x but was never fully used or tested with JDK 1.2.x.

OIJ Collection implementation example

In OIJ, a user can create a table with a collection column. Updates, inserts, deletes and selects of data from a collection column may be performed as shown in the simple example below:

//Informix packages

import informix.api.*;

import informix.api.direct.*;

//Sun Java packages

import java.sql.*;

//Code

public class collexamp

{

public static void main(String args[])

{

informix.api.Connection conn = null;

Query query = null;

Set set = null;

Value value = null;

String dbname = "sampledb";

String username = "myuser";

String servername = "myserver";

String passwd = "mypasswd";

Credentials cred = new DirectCredentials(dbname, username, servername, passwd);

conn = cred.connection();

conn.open();

query = conn.query();

query.execForStatus("drop table colltbl;");

System.out.println("Create a collection table");

query.execForStatus("create table colltbl (dept varchar(12), direct_reports SET(varchar(30) not null));");

System.out.println("Insert 3 rows into collection table");

query.execForStatus("insert into colltbl values('Marketing', \"SET{'Emp1', 'Emp2', 'Emp3', 'Emp4'}\");");

query.execForStatus("insert into colltbl values('Sales', \"SET{'Emp5', 'Emp6', 'Emp7', 'Emp8', 'Emp9', 'Emp10'}\");");

query.execForStatus("insert into colltbl values('QA', \"SET{'Emp25', 'Emp26', 'Emp27'}\");");

System.out.println("Update 1 row of the collection table");

query.execForStatus("update colltbl set direct_reports = \"SET{'Emp6', 'Emp9', 'Emp12', 'Emp13', 'Emp15'}\" where 'Emp10' in direct_reports;");

System.out.println("Delete 1 row of the collection table data");

query.execForStatus("delete from colltbl where 'Emp2' in direct_reports;");

System.out.println("Select rows from the collection table");

set = query.execToSet("select * from colltbl;");

set.open();

String rowString = new String();

while ((value = set.fetch()) != null)

{

rowString = value.toString();

System.out.println(rowString);

}

conn.close();

set.close();

}

}

JDBC Collection implementation example

In JDBC, a user can basically perform the same functionality as in OIJ for inserts, updates and deletes but selection of collection data is quite different. In OIJ, it is not possible to select individual elements of a collection but in JDBC this can be done easily. Below is an example based on the OIJ collection example above:

import java.sql.*;

import java.util.*;

//Code

public class collexamp

{

public static void main(String args[])

{

Connection conn = null;

Statement stmt = null;

String url = null;

String dbname = "sampledb";

String username = "myuser";

String servername = "myserver";

String passwd = "mypasswd";

String url = "jdbc:informix-sqli://<host>:<port_id>/" + dbname + ":informixserver=" + servername + ";user=" + username + ";password=" + passwd;

System.out.println("Loading the JDBC driver");

Class.forName("com.informix.jdbc.IfxDriver");

System.out.println("Getting connection");

conn = DriverManager.getConnection(url);

stmt = conn.createStatement();

stmt.executeUpdate("drop table colltbl");

System.out.println("Create a collection table");

stmt.executeUpdate("create table colltbl (dept varchar(12), direct_reports SET(varchar(30) not null))");

System.out.println("Insert 3 rows into collection table");

stmt.executeUpdate("insert into colltbl values('Marketing', \"SET{'Emp1', 'Emp2', 'Emp3', 'Emp4'}\")");

stmt.executeUpdate("insert into colltbl values('Sales', \"SET{'Emp5', 'Emp6', 'Emp7', 'Emp8', 'Emp9', 'Emp10'}\")");

stmt.executeUpdate("insert into colltbl values('QA', \"SET{'Emp25', 'Emp26', 'Emp27'}\")");

System.out.println("Update 1 row of the collection table");

stmt.executeUpdate("update colltbl set direct_reports = \"SET{'Emp6', 'Emp9', 'Emp12', 'Emp13', 'Emp15'}\" where 'Emp10' in direct_reports");

stmt.executeUpdate("delete from colltbl where 'Emp2' in direct_reports");

System.out.println("Select rows from the collection table");

ResultSet rs = stmt.executeQuery("select * from colltbl");

ResultSet rs_collelem = null;

//Use an array object to retrieve a collection

java.sql.Array array;

while (rs.next())

{

String deptString = rs.getString(1);

System.out.print(deptString + " {");

//Retrieve the collection of the current row

array = rs.getArray(2);

rsa = array.getResultSet();

while (rsa.next())

{

//The second column of rsa array gets element value of current collection row

System.out.print(rsa.getString(2) + " ");

}

System.out.println("}");

}

stmt.close();

rs.close();

rsa.close();

conn.close();

}

}

For more information regarding how to manipulate collections using JDBC 2.0, see the "Manipulating Informix Collections and Arrays" section of the "IBM Informix JDBC Driver Programmer's Guide".

OIJ Named and Unnamed Row Types implementation example

In OIJ, a user can create a table with either named or unnamed row type definitions. The example below shows how to use named and unnamed row types with OIJ:

//Informix packages

import informix.api.*;

import informix.api.direct.*;

//Sun Java packages

import java.sql.*;

import java.util.Enumeration;

import java.lang.Integer;

//Code

public class rowtypexamp

{

public static void main(String args[])

{

informix.api.Connection conn = null;

Query query = null;

Row row = null;

Value value = null;

String dbname = "sampledb";

String username = "myuser";

String servername = "myserver";

String passwd = "mypasswd";

Credentials cred = new DirectCredentials(dbname, username, servername, passwd);

conn = cred.connection();

conn.open();

query = conn.query();

System.out.println("Create a named row type called namedtype_t");

query.execForStatus("create row type namedtype_t(name varchar(30), dept varchar(20), salary integer);");

System.out.println("Create a table with a named and unnamed row type column");

query.execForStatus("create table rowtypetbl (empid integer, empinfo namedtype_t, addempinfo ROW(project varchar(30), age integer));");

System.out.println("Insert 3 rows into table");

query.execForStatus("insert into rowtypetbl values(100, ROW('Emp100name', 'Marketing', 50000)::namedtype_t, ROW('falcon', 35));");

query.execForStatus("insert into rowtypetbl values(200, ROW('Emp200name', 'Development', 60000)::namedtype_t, ROW('jdbc', 31));");

query.execForStatus("insert into rowtypetbl values(300, ROW('Emp300name', 'QA', 70000)::namedtype_t, ROW('legacy', 38));");

System.out.println("Select rows from the table");

query.execForIteration("select empid, empinfo.name, empinfo.dept, empinfo.salary, addempinfo.project, addempinfo.age from rowtypetbl;");

while ((row = query.nextRow()) != null)

{

String rowString = new String();

for (Enumeration e = Enumerators.enumerator (row); e.hasMoreElements();)

{

value = (Value) e.nextElement();

if (value != null)

// Print out current row containing named and unnamed row type data

rowString += value.toString() + " ";

}

System.out.println(rowString);

}

conn.close();

}

}

JDBC Named and Unnamed Row Types implementation example

In JDBC, an SQL type called a structured type or struct is equivalent to an Informix named row type. One way to exchange structured type information between a Java client and a relational database is to create a single Java class per named row type which implements the SQLData interface. This class will have a member for each element in the named row. The other way to exchange structured type information between a Java client and database is to use the Struct interface which instantiates the necessary Java object for each element in the named row and constructs an array of java.util.Object Java objects. 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.

Below is an example of using named and unnamed row types with JDBC:

import java.sql.*;

import java.util.*;

//Code

public class rowtypexamp

{

public static void main(String args[])

{

Connection conn = null;

Statement stmt = null;

String url = null;

PreparedStatement pstmt = null;

String dbname = "sampledb";

String username = "myuser";

String servername = "myserver";

String passwd = "mypasswd";

String url = "jdbc:informix-sqli://<host>:<port_id>/" + dbname + ":informixserver=" + servername + ";user=" + username + ";password=" + passwd;

System.out.println("Loading the JDBC driver");

Class.forName("com.informix.jdbc.IfxDriver");

System.out.println("Getting connection");

conn = DriverManager.getConnection(url);

stmt = conn.createStatement();

System.out.println("Create a named row type in the database");

stmt.executeUpdate("create row type namedtype_t(name varchar(30), dept varchar(20), salary integer)");

System.out.println("Create a table using a named and unnamed row type");

/*

NOTE: namedtype_t is a row type that needs to be defined in a class created by user called namedtype_t.java. This class defines the named row type and creates the methods needed to implement the SQLData interface.

See the "Manipulating Informix Named and Unnamed Rows" section of the "IBM Informix JDBC Driver Programmer's Guide" for additional examples of creating classes used for named row types. Also see the r1_t.java example that is delivered with the JDBC 2.0 product under the demo/complex-types directory.

*/

System.out.println("Create a table with a named and unnamed row type column");

stmt.executeUpdate("create table rowtypetbl (empid integer, empinfo namedtype_t, addempinfo ROW(project varchar(30), age integer))");

System.out.println("Insert first row of data into table");

pstmt = conn.prepareStatement("insert into rowtypetbl (empid, empinfo, addempinfo) values(?, ?, ?)");

//Register the customized type mapping through the connection object

System.out.println("Setting up custom type mapping for named row type");

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

conn.setTypeMap(customtypemap);

customtypemap.put("namedtype_t", Class.forName("namedtype_t"));

//Populate user implemented GenericStruct class. This is based on java.sql.Struct interface and is used for unnamed row types.

GenericStruct struct = null;

Object [] objArray = null;

String rowDesc = "row(project varchar(30), age integer)";

objArray = new Object[2];

objArray[0] = new String("falcon");

objArray[1] = new Integer(35);

struct = new GenericStruct(rowDesc, objArray);

pstmt.setInt(1, 100);

namedtype_t col2 = new namedtype_t ("Emp100name", "Marketing", 50000);

pstmt.setObject(2, col2);

pstmt.setObject(3, struct);

pstmt.executeUpdate();

// Insert second row of data

objArray = new Object[2];

objArray[0] = new String("jdbc");

objArray[1] = new Integer(31);

struct = new GenericStruct(rowDesc, objArray);

pstmt.setInt(1, 200);

col2 = new namedtype_t ("Emp200name", "Development", 60000);

pstmt.setObject(2, col2);

pstmt.setObject(3, struct);

pstmt.executeUpdate();

// Insert third row of data

objArray = new Object[2];

objArray[0] = new String("legacy");

objArray[1] = new Integer(38);

struct = new GenericStruct(rowDesc, objArray);

pstmt.setInt(1, 300);

col2 = new namedtype_t ("Emp300name", "QA", 70000);

pstmt.setObject(2, col2);

pstmt.setObject(3, struct);

pstmt.executeUpdate();

pstmt.close();

System.out.println("Select rows from the table. Named row type data will be selected as SQLData and unnamed row type data will be selected as java.sql.Struct data.");

ResultSet rs = stmt.executeQuery("select empid, empinfo, addempinfo from rowtypetbl");

//Retrieving data

namedtype_t nt;

Struct unt;

while (rs.next())

{

//Retrieve first column of data: integer

int intret = rs.getInt(1);

System.out.println("empid = " + intret);

//Retrieve second column of data: named row type

nt = (namedtype_t) rs.getObject(2);

//This assumes that a toString() method was created in the named row type class namedtype_t to concatenate all of the fields of the row type definition

System.out.println("empinfo = " + ((Object) nt).toString());

//Retrieve third column of data: unnamed row type

unt = (Struct) rs.getObject(3);

//Need to call Struct.getAttributes() defined in GenericStruct class to materialize data. All elements in array are objects.

Object [] untobjArray = ((Struct) unt).getAttributes();

for (int i=0; i<untobjArray.length; i++)

{

System.out.print("addempinfo = " + untobjArray[i] + " ");

}

System.out.println();

}

stmt.close();

rs.close();

conn.close();

}

}

Below is an example of a user implementation of the java.sql.Struct interface, called GenericStruct:

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)

{

return attributes;

}

public void setAttributes(Object [] objArray)

{

attributes = objArray;

}

public void setSQLTypeName(String name)

{

typename = name;

}

} /* GenericStruct class */

Below is an example of the SQLData implementation of the namedtype_t class:

import java.sql.*;

import java.util.*;

import java.lang.*;

public class namedtype_t implements SQLData

{

String name;

String dept;

int salary;

String sqlType = "namedtype_t";

public namedtype_t();

{

}

/*

This constructor isn't required by the java.sql.SQLData interface

*/

public namedtype_t(String n, String d, int s)

{

name = n;

dept = d;

salary = s;

}

public String getSQLTypeName() throws SQLException

{

return(sqlType);

}

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

{

sqlType= typeName;

name = stream.readString();

dept = stream.readString();

salary = stream.readInt();

}

public void writeSQL(SQLOutput stream) throws SQLException

{

stream.writeString(name);

stream.writeString(dept);

stream.writeInt(salary);

}

/*

This constructor isn't required by the java.sql.SQLData interface

*/

public String toString()

{

String str = "name: " + name + " dept: " + dept + " salary: " + salary;

return (str);

}

} /* namedtype_t class */

For more information regarding how to manipulate named and unnamed row types using JDBC 2.0, see the "Manipulating Informix Named and Unnamed Rows" section of the "IBM Informix JDBC Driver Programmer's Guide".

OIJ vs. JDBC Opaque and User-Defined Types information

For OIJ, a good example to look at is the pnt.java example that is located in the "Creating a Simple Value Object" section of the "INFORMIX-Object Interface for Java Programmer's Guide". In this example, a user-defined type that represents a Point is defined and manipulated.

For JDBC, a very similar example to look at is located in the "Mapping Opaque Types - Data Binding Example" section of the "IBM Informix JDBC Driver Programmer's Guide". In this example, a user-defined type that represents a Circle is defined in a Java class file. A type mapping is set up and data that represents a Circle is retrieved from a database using the ResultSet class getObject() method.

Refer to the demo/complex-types and demo/udt-distinct directories of the JDBC 2.0 release for more examples of using opaque and user-defined types.

OIJ vs. JDBC Smart Large Object Support

For OIJ, a good example to look at with regards to providing Large Object support is the IUS_APIClass.java example located in the "Accessing Database Values - The LargeObject Interface" section of the "INFORMIX-Object Interface for Java Programmer's Guide". In this example, a Java class file is inserted into a table called javaapiclasses that is used by Object Interface for Java for dynamically loading value objects using database server-side large objects. The BLOB datatype is used to store the large object data. The OIJ Factory interface is used in this example to construct an uninitialized instance of the BLOB data type for insertion into a table. Then, an input stream needs to be created around the file representing the large object data by creating an instance of the FileInputStream class for this file. BLOB and CLOB types support streaming I/O access between the client and the database server. The value object for the BLOB representation can then be manipulated as shown in the example.

For JDBC, there are good examples of manipulating smart large objects in the "Manipulating Informix BLOB and CLOB Data Types" section of the "IBM Informix JDBC Driver Programmer's Guide". The examples in this section detail how to create smart large objects on the server, insert a smart large object into a BLOB or CLOB column, and select BLOB or CLOB data. The differences between OIJ and JDBC with regards to manipulating smart blobs is that instead of using value objects in OIJ you can call methods supplied by the JDBC ResultSet class to retrieve smart large object data. For example, you can instantiate a Blob class and retrieve the blob data by making a call to the getBlob method of the ResultSet class.

Refer to the demo/clob-blob directory of the JDBC 2.0 release for more examples of manipulating smart large objects.

- Converting OIJ Cursor Logic to JDBC

OIJ provides the following support for cursor scrolling logic:

- forward scrolling of result rows

row = ((Row) cursor.fetch())

- backward scrolling of result rows

(assuming that last row of result set has been read)

row = ((Row) cursor.position(Set.PRIOR))

- relative positioning within result rows

row = ((Row) cursor.relativePosition(2))

- absolute positioning within result rows

row = ((Row) cursor.absolutePosition(3))

- fetch first row of result set

row = ((Row) cursor.position(Set.FIRST))

- fetch last row of result set

row = ((Row) cursor.position(Set.LAST))

NOTE: A cursor defined in OIJ cannot be both scrollable and updateable.

An example of OIJ forward scrolling logic that is converted to JDBC can be found in the Cursor.open() example in section VI "Executing a database query" of this document.

The JDBC 1.2 standard currently only supports the forward scrolling cursor (i.e. resultset.next()). JDBC 2.0 supplies methods of the ResultSet class that supports the rest of the scrolling logic mentioned above:

- backward scrolling of result rows

resultset.afterLast()

resultset.previous()

- relative positioning within result rows

resultset.relative(2)

- absolute positioning within result rows

resultset.absolute(3)

- fetch first row of result set

resultset.first()

- fetch last row of result set

resultset.last()

For updateable cursors in OIJ, the following example logic can be used:

cursor = conn.cursor();

cursor.define("select * from foo;", informix.api.Cursor.UPDATEABLE);

Query query = conn.query();

query.execForIteration("select * from foo;");

Row row;

conn.setTransaction(Connection.BEGIN);

cursor.open();

while ((row = (Row) cursor.fetch()) != null)

{

Value v = row.column(0);

if (v.toString().equals("foobar"))

{

v.fromString("helloworld");

cursor.update("foo", row);

}

else if (v.toString().equals("helloworld"))

{

v.fromString("foobar");

cursor.update("foo", row);

}

}

cursor.close();

conn.setTransaction(Connection.COMMIT);

The OIJ updateable cursor logic can be converted to JDBC as defined below:

Connection conn = null;

Statement query = null;

ResultSet rs = null;

conn.setAutoCommit(false);

query = conn.createStatement();

rs = query.executeQuery("select * from foo for update");

String cursor = rs.getCursorName();

ResultSetMetaData rsmd = rs.getMetaData();

int ncols = rsmd.getColumnCount();

String s = null;

PreparedStatement ps = conn.prepareStatement("update foo set col1 = ?, " + "where current of " + cursor);

//Get each row of data

while (rs.next())

{

for (int numcol = 1; numcol <= ncols; numcol++)

{

switch(rsmd.getColumnType(numcol))

{

case Types.CHAR:

case Types.VARCHAR:

case Types.LONGVARCHAR:

s = rs.getString(numcol);

if (s.equals("foobar"))

ps.setString(numcol, "helloworld");

else

{

if (s.equals("helloworld"))

ps.setString(numcol, "foobar");

}

break;

default: // Dont care about other datatypes here

break;

}

ps.executeUpdate();

} // for loop

rs.updateRow();

} // while loop

rs.close();

query.close();

ps.close();

conn.commit();

For parameterized cursors in OIJ, the following example logic can be used:

String qtext;

Value params[] = new Value[1];

cursor = conn.cursor();

cursor.define("select tabname from systables where tabname like ?;", informix.api.Cursor.SCROLL);

Factory factory = conn.factory();

Value param = factory.makeinstance("varchar");

params[0] = param;

// qtext is a dynamically entered string by the user

params[0].fromString(qtext);

cursor.open(params);

Row row;

while ((row = (Row) cursor.fetch()) != null)

{

System.out.println(((Value) row).toString());

}

The OIJ parameterized cursor logic can be converted to JDBC as defined below:

Connection conn = null;

PreparedStatement query = null;

ResultSet rs = null;

String qtext; // User enters this value dynamically

query = conn.prepareStatement("select tabname from systables where tabname like ?;");

query.setString(1, qtext);

rs = query.executeQuery();

//Get each row of data

while (rs.next())

{

String s = rs.getString("tabname");

System.out.println(s);

}

rs.close();

query.close();

- Establishing an RMI connection with JDBC

OIJ/RMI to JDBC/RMI

The ability to call remote methods has many advantages, such as the ability to make thin clients, which allows the distribution across a networked environment and connecting across firewalls. Depending on your needs, different conversion solutions can be implemented.

The IBM Informix JDBC driver does not have its own remote implementation like OIJ did. Instead, you must choose between the options outlined below. You should also keep in mind that if your applications/applets are deployed inside an intranet, the JDBC driver allows a direct connection (using informix-sqli) to the IBM Informix database and RMI might not be needed. The new HTTP tunneling feature also provides easier access across firewalls and it can be used in conjunction with RMI. If you are currently using OIJ RMICredentials and want to continue to use RMI, you have the following options for migrating to JDBC:

Thin Client/Remote Method Development Option

This option lets you decide which methods in the application/applet will be remote methods. You must provide the remote implementation for these methods. Informix provides a high level example on how this might be implemented, and example code for this option is included in the JDBC Driver distribution. This option provides a thin client implementation and you would have greater control on how to implement your remote methods, but it also requires additional development resources. If you choose this option, you should follow the steps specified in this document and in the provided JDBC/RMI example.

Thin Client/Remote Method Implementation of java.sql.* interfaces

This option allows you to partition your applications/applets at the JDBC java.sql.* interfaces level. A free, public-domain software package is now available that provides remote implementations for all the java.sql.* interfaces. The software package is called RmiJdbc and it can be downloaded from

http://dyade.inrialpes.fr/mediation/download/RmiJdbc/RmiJdbc.html. The software package is free and can be redistributed or modified under the terms of the GNU General Public License as published by the Free Software Foundation. Examples are provided in the downloaded software distribution. This option provides a thin client implementation and you would be able to keep a consistent JDBC interface throughout your applications/applets. If you choose this option, you should follow the steps specified in this document and in the instructions provided with the RmiJdbc software.

- Error Handling and Tracing Support

OIJ provides a set of exception classes that can be passed to API error callbacks (if present) or throw an exception (if no error handler is present). The entire list of the Exception classes for OIJ can be found in the "INFORMIX-Object Interface for Java Programmer’s Guide".

Any database exception that is thrown in OIJ needs to be converted to a SQLException for JDBC:

OIJ Error Handling example

try

{

// code statements

}

catch (DBException exce)

{

System.out.println("Caught: " + exce.getMessage());

}

JDBC Error Handling example

try

{

// code statements

}

catch (SQLException exce)

{

System.out.println("Caught: " + exce.getMessage());

System.out.println("Caught: " + exce.getErrorCode());

System.out.println("Caught: " + exce.getSQLState());

}

The Error handling callback interfaces that exist in OIJ are not directly convertible to JDBC. You need to provide your own callback error handling.

OIJ allows you to view runtime trace information by implementing the Traceable interface. For JDBC, you might use the "ifxjdbc-g.jar" debug installation of the IBM INFORMIX JDBC Driver. There is general trace information in the driver code as well as SQLI messages that the client sends to the server or receives from the server. To get the trace information, you must set the following properties in the connection URL string or property list:

- TRACE=<trace-level> levels 0-3
- TRACEFILE=<trace-file-path>
- PROTOCOLTRACE=<protocol-trace-level> levels 0-2
- PROTOCOLTRACEFILE=<protocol-trace-file-path>

For details on JDBC driver tracing, see the "Debugging the Driver" section of the "IBM INFORMIX JDBC Driver" documentation.

- csql3.java (entire OIJ example)

Below is the entire csql3.java example using OIJ:

// IBM CORPORATION

//

// PROPRIETARY DATA

//

// THIS DOCUMENT CONTAINS TRADE SECRET DATA WHICH IS THE

// PROPERTY OF IBM CORPORATION. THIS DOCUMENT IS

// SUBMITTED TO THE RECIPIENT IN CONFIDENCE. INFORMATION

// CONTAINED HEREIN MAY NOT BE USED, COPIED OR DISCLOSED

// IN WHOLE OR IN PART EXCEPT AS PERMITTED BY WRITTEN

// AGREEMENT SIGNED BY AN OFFICER OF IBM CORPORATION

//

//

// THIS MATERIAL IS ALSO COPYRIGHTED AS AN UNPUBLISHED

// WORK UNDER SECTIONS 104 AND 408 OF TITLE 17 OF THE

// UNITED STATES CODE.

// UNAUTHORIZED USE, COPYING OR OTHER REPRODUCTION IS

// PROHIBITED BY LAW.

//

// Title: csql3.java

//

// Description:

// Submit a simple query example with error handling

// and transaction monitoring.

//Informix packages

import informix.api.*;

import informix.api.direct.*;

import informix.api.remote.rmi.*;

//Java packages

import java.applet.*;

import java.awt.*;

//New in JDK1.1

import java.awt.event.*;

import java.util.Enumeration;

import java.lang.Integer;

public class csql3 extends ExampleApplet implements Callback

{

Connection conn;

Query query;

public boolean createConnection(String appServer, String db, String user, String sys, String passwd, boolean remote)

{

//Every connection requires a credential

//Create a credential depending on the connection type

Credentials cred;

try {

if (remote == true)

{

//Create a remote credential

cred = new RMICredentials(appServer, db, user, sys, passwd);

status("Created a remote credential");

}

else

{

if (db.equals("") && user.equals("") && sys.equals("") && passwd.equals(""))

{

//Create a direct connection using environment variables

cred = new DirectCredentials();

status("Created a direct credential with environment variables");

}

else

{

cred = new DirectCredentials(db, user, sys, passwd);

status("Created a direct credential with user provided variables");

}

}

//Create a connection

conn = cred.connection();

//Create a query object

query = conn.query();

//Register the error callback function on a connection object

conn.registerCallback(this, (Object) "connection");

//Register the error callback function on a query object

query.registerCallback(this, (Object) "query");

//Open the connection here now since we are doing transactions in the Run method

conn.open();

status("Connection established");

return true;

} catch (Exception exce) {

error("Caught: " + exce.toString());

return false;

}

}

public boolean Run(String qtext)

{

status("Executing: " + qtext);

try {

if (!query.execForIteration(qtext))

return false;

// 1 begin

//Check the transactional state

if (conn.getTransactionState() == Connection.BEGIN)

println("<TRANSACTION...>");

else

println("<>");

// 1 end

Row row;

int rowcount = 0;

status("Reading Rows");

//Get each row

while ((row = query.nextRow()) != null)

{

rowcount++;

String rowString = new String();

for (Enumeration e = Enumerators.enumerator (row); e.hasMoreElements(); )

{

Value v = (Value) e.nextElement();

if (v != null)

rowString += v.toString();

}

println(rowString);

if (rowcount > 19)

{

info("Displaying only the first 20 rows of data");

query.flush();

break;

}

}

status("Row count: " + rowcount);

} catch (DBException dbexc) {

error("Caught: " + dbexc.toString());

return false;

}

return true;

}

public void keyPressed(KeyEvent e)

{

char c = e.getKeyChar();

//Use the escape key to abort the transaction

if (c == '\033')

{

//Clear the result box out

result.removeAll();

try {

// 2 begin

if (conn.getTransactionState() == Connection.BEGIN)

{

println("Exit within transaction, aborting transaction");

conn.setTransaction(Connection.ABORT);

println("<>");

}

// 2 end

} catch (DBException dbexc) {

error("Caught: " + dbexc.toString());

}

}

super.keyPressed(e);

}

//This action is required to abort the transaction when the Quit button from the applet pull down menu is selected.

public void actionPerformed(ActionEvent e)

{

Object target = e.getSource();

if (target instanceof MenuItem)

{

String label = (String) e.getActionCommand();

if (label.equals("Quit"))

{

try {

if ( conn !=null && conn.getTransactionState() == Connection.BEGIN )

conn.setTransaction(Connection.ABORT);

} catch (DBException dbexc) {

error("Caught: " + dbexc.toString());

}

}

}

super.actionPerformed(e);

}

// Callback interface methods

public boolean error(String message, ErrorManaged errObj, Object user, DBException err)

{

if (errObj instanceof QuerySubmitter)

{

error("Error Callback on " + (String) user + " :\n" + message + "\n" + "Could not execute query: " + query.queryText());

}

else

{

error("Error Callback on " + (String) user + " :\n" + message);

}

return true;

}

//This is required if this example is to run as a standalone application

public static void main(String args[])

{

csql3 applet = new csql3();

Frame frame = new AppletFrame("Example: csql3", applet, applet.file);

}

}

- csql3.java (entire JDBC example)

Below is the entire csql3.java example using JDBC:

// IBM CORPORATION, INC.

//

// PROPRIETARY DATA

//

// THIS DOCUMENT CONTAINS TRADE SECRET DATA WHICH IS THE

// PROPERTY OF IBM CORPORATION. THIS DOCUMENT IS

// SUBMITTED TO THE RECIPIENT IN CONFIDENCE. INFORMATION

// CONTAINED HEREIN MAY NOT BE USED, COPIED OR DISCLOSED

// IN WHOLE OR IN PART EXCEPT AS PERMITTED BY WRITTEN

// AGREEMENT SIGNED BY AN OFFICER OF IBM CORPORATION

//

//

// THIS MATERIAL IS ALSO COPYRIGHTED AS AN UNPUBLISHED

// WORK UNDER SECTIONS 104 AND 408 OF TITLE 17 OF THE

// UNITED STATES CODE.

// UNAUTHORIZED USE, COPYING OR OTHER REPRODUCTION IS

// PROHIBITED BY LAW.

//

// Title: csql3.java

//

// Description:

// Submit a simple query example with error handling

// and transaction monitoring.

//Java packages

import java.applet.*;

import java.awt.*;

import java.sql.*;

//New in JDK1.1

import java.awt.event.*;

public class csql3 extends ExampleApplet

{

Connection conn = null;

public boolean createConnection(String appServer, String db, String user, String sys, String passwd, boolean remote)

{

String url = null;

try {

if (remote == true)

{

if (appServer.equals ("") || db.equals("") || user.equals("") || sys.equals("") || passwd.equals(""))

{

status("RMI Server ID, Database, Username, System, and Password fields are required");

}

else

{

// Form a remote connection URL.

// NOTE: See discussion of how to implement a JDBC/RMI connection in section IX "Establishing an RMI connection with JDBC"

}

}

else

{

if (db.equals("") || user.equals("") || sys.equals("") || passwd.equals(""))

{

status("Database, Username, System, and Password fields are required");

}

else

{

// Form a direct connection URL. informix-sqli is the only

// sub-protocol currently supported

url = "jdbc:informix-sqli://turquoise:151/" + db + ":informixserver=" + sys + ";user=" + user + ";password=" + passwd;

status("Created a server/database direct connection URL");

}

}

// Load the IBM Informix JDBC Driver

loadDriver(url);

//Create a connection

if ((conn = getConnection(url)) == null)

status("Database connection failed.");

else

status("Database connection established");

return true;

} catch (Exception exce) {

error("Caught: " + exce.toString());

return false;

}

}

public boolean Run(String qtext)

{

ResultSet rs;

Statement sqlstmt;

int rowcount = 0;

try {

sqlstmt = conn.createStatement();

rs = sqlstmt.executeQuery(qtext);

status("Executing: " + qtext);

//Check the transactional state

if (conn.getAutoCommit())

println("<TRANSACTION...>");

else

println("<>");

}

catch (SQLException e)

{

status("Execution failed - statement: " + qtext);

status("Error: " + e.getMessage());

return false;

}

try {

ResultSetMetaData rsmd = rs.getMetaData();

int ncols = rsmd.getColumnCount();

String s = null;

//Get each row of data

status("Reading Rows");

while (rs.next())

{

String st = new String();

for (int numcol = 1; numcol <= ncols; numcol++)

{

switch(rsmd.getColumnType(numcol))

{

// Built-in types supported thus far for JDBC 1.2

case Types.CHAR:

case Types.VARCHAR:

case Types.LONGVARCHAR:

s = rs.getString(numcol);

break;

case Types.SMALLINT:

if (!rs.wasNull() )

s = "" + rs.getShort(numcol);

break;

case Types.INTEGER:

if (!rs.wasNull() )

s = "" + rs.getInt(numcol);

break;

case Types.BIGINT:

if (!rs.wasNull() )

s = "" + rs.getLong(numcol);

break;

case Types.FLOAT:

if (!rs.wasNull() )

s = "" + rs.getFloat(numcol);

break;

case Types.DOUBLE:

if (!rs.wasNull() )

s = "" + rs.getDouble(numcol);

break;

case Types.DECIMAL:

case Types.NUMERIC:

if (!rs.wasNull() )

s = "" + rs.getBigDecimal(numcol, 4);

break;

case Types.BIT:

if (!rs.wasNull() )

s = "" + rs.getBoolean(numcol);

break;

case Types.DATE:

if (!rs.wasNull() )

s = "" + rs.getDate(numcol);

break;

case Types.TIME:

if (!rs.wasNull() )

s = "" + rs.getTime(numcol);

break;

case Types.TIMESTAMP:

if (!rs.wasNull() )

s = "" + rs.getTimestamp(numcol);

break;

case Types.BINARY:

if (!rs.wasNull() )

s = "" + rs.getByte(numcol);

break;

case Types.VARBINARY:

case Types.LONGVARBINARY:

if (!rs.wasNull() )

s = "" + rs.getBytes(numcol);

break;

default:

status("COLUMN " + numcol + " DATATYPE NOT RECOGNIZED: ");

break;

}

if (rs.wasNull() )

s = "<null> ";

st = st + s.trim() + " ";

}

st = st + "\n";

println(st);

rowcount++;

if (rowcount > 19)

{

info("Displaying only the first 20 rows of data");

break;

}

}

status("Row count: " + rowcount);

rs.close();

sqlstmt.close();

}

catch (SQLException dbexc)

{

status("Caught: " + dbexc.toString());

return false;

}

return true;

}

public void keyPressed(KeyEvent e)

{

char c = e.getKeyChar();

//Use the escape key to abort the transaction

if (c == '\033')

{

//Clear the result box out

result.removeAll();

try {

if (conn.getAutoCommit())

{

println("Exit within transaction, closing transaction");

conn.close();

println("<>");

}

else

println("<>");

}

catch (SQLException dbexc)

{

error("Caught: " + dbexc.toString());

}

}

super.keyPressed(e);

}

//This action is required to abort the transaction when the

//Quit button from the applet pull down menu is selected.

public void actionPerformed(ActionEvent e)

{

Object target = e.getSource();

if (target instanceof MenuItem)

{

String label = (String) e.getActionCommand();

if (label.equals("Quit"))

{

try {

if ( conn !=null && conn.getAutoCommit() )

conn.close();

} catch (SQLException dbexc) {

error("Caught: " + dbexc.toString());

}

}

}

super.actionPerformed(e);

}

public boolean loadDriver(String url)

{

status("URL = \"" + url + "\"");

try

{

// For Netscape browsers

Class.forName("com.informix.jdbc.IfxDriver");

// For Microsoft IE browsers

// DriverManager.registerDriver((Driver) //Class.forName("com.informix.jdbc.IfxDriver").newInstance());

}

catch (Exception e)

{

status("Failed to load Informix JDBC driver.");

return false;

}

return true;

}

public Connection getConnection(String url)

{

Connection conn = null;

try

{

conn = DriverManager.getConnection(url);

}

catch (SQLException e)

{

status("Failed to connect!");

}

return conn;

}

//This is required if this example is to run as a standalone application

public static void main(String args[])

{

csql3 applet = new csql3();

Frame frame = new AppletFrame("Example: csql3", applet, applet.file);

}

}