Informix JDBC Driver Programmer's Guide Manipulating Informix Data Types

The section describes the Informix BYTE and TEXT data types and how to manipulate columns of these data types with the JDBC API.

The BYTE data type is a data type for a simple large object that stores any kind of data in an undifferentiated byte stream. Examples of binary data include spreadsheets, digitized voice patterns, and video clips. The TEXT data type is a data type for a simple large object that stores any kind of text data. It can contain both single and multibyte characters.

Columns of either data type have a theoretical limit of 2³¹ bytes and a practical limit determined by your disk capacity.

For more detailed information about the Informix BYTE and TEXT data types, refer to Informix Guide to SQL: Reference and Informix Guide to SQL: Syntax. You can find the on-line version of both of these guides at http://www.informix.com/answers.

Whenever a BLOB, CLOB, text, or byte object is fetched from the database server, the data is cached into memory. If the size of the large object is bigger than the value in the LOBCACHE environment variable, the large object data is stored in a temporary file. For more information about the LOBCACHE variable, see Memory Management of Large Objects.

To insert into or update BYTE and TEXT columns, read a stream of data from a source, such as an operating system file, and transmit it to the database as a java.io.InputStream object. The PreparedStatement interface provides methods for setting an input parameter to this Java input stream. When the statement is executed, Informix JDBC Driver makes repeated calls to the input stream, reading its contents and transmitting those contents as the actual parameter data to the database.

For BYTE data types, use the PreparedStatement.setBinaryStream() method to set the input parameter to the InputStream object. For TEXT data types, use the PreparedStatement.setAsciiStream() method.

The following example from the ByteType.java program shows how to insert the contents of the operating system file data.dat into a column of data type BYTE:

try

{

stmt = conn.createStatement();

stmt.executeUpdate("create table tab1(col1 byte)");

}

catch (SQLException e)

{

System.out.println("Failed to create table ..." + e.getMessage());

}

System.out.println("Trying to insert data using Prepare Statement ...");

try

{

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

}

catch (SQLException e)

{

System.out.println("Failed to Insert into tab: " + e.toString());

}

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

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

InputStream value = null;

FileInputStream fileinp = null;

int row = 0;

String str = null;

int rc = 0;

ResultSet rs = null;

System.out.println("Inserting data ...\n");

try

{

fileinp = new FileInputStream(file);

value = (InputStream)fileinp;

}

catch (Exception e) {}

try

{

pstmt.setBinaryStream(1,value,10); //set 1st column

}

catch (SQLException e)

{

System.out.println("Unable to set parameter");

}

set_execute();

...

public static void set_execute()

{

try

{

pstmt.executeUpdate();

}

catch (SQLException e)

{

System.out.println("Failed to Insert into tab: " + e.toString());

e.printStackTrace();

}

}

The example first creates a java.io.File object that represents the operating system file data.dat. The example then creates a FileInputStream object to read from the File object. The FileInputStream object is cast to its superclass InputStream, which is the expected data type of the second parameter to the PreparedStatement.setBinaryStream() method. The setBinaryStream() method is executed on the already prepared INSERT statement, which sets the input stream parameter. Finally, the PreparedStatement.executeUpdate() method is executed, which actually inserts the contents of the data.dat operating system file into the BYTE column of the table.

The TextType.java program shows how to insert data into a TEXT column. It is very similar to inserting into a BYTE column, except the method setAsciiStream() is used to set the input parameter instead of setBinaryStream().

After you select from a table into a ResultSet object, you can use the ResultSet.getBinaryStream() and ResultSet.getAsciiStream() methods to retrieve a stream of binary or ASCII data from BYTE and TEXT columns, respectively. Both methods return an InputStream object, which can be used to read the data in chunks.

All the data in the returned stream in the current row must be read before you call the next() method to retrieve the next row.

The following example from the ByteType.java program shows to how select data from a BYTE column and print out the data to the standard output device:

try

{

stmt = conn.createStatement();

rs = stmt.executeQuery("Select * from tab1");

while( rs.next() )

{

row++;

value = rs.getBinaryStream(1);

System.out.println("\nResult of row #" + row + ", size = "

+ value.available() + " from getAsciiStream(1) ..\n");

dispValue(value);

}

}

catch (Exception e) { }

...

public static void dispValue(InputStream in)

{

int size;

byte buf;

int count = 0;

try

{

size = in.available();

byte ary[] = new byte[size];

buf = (byte) in.read();

while(buf!=-1)

{

ary[count] = buf;

count++;

buf = (byte) in.read();

}

System.out.println(new String(ary).trim());

}

catch (Exception e)

{

System.out.println("Error occur during reading stream ... \n");

}

}

The example first puts the result of a SELECT statement into a ResultSet object. It then executes the method ResultSet.getBinaryStream() to retrieve the BYTE data into a Java InputStream object.

The method dispValue(), whose Java code is also included in the example, is used to actually print out the contents of the column to standard output device. The dispValue() method uses byte arrays and the InputStream.read() method to systematically read the contents of the BYTE column.

The TextType.java program shows how to select data from a TEXT column. It is very similar to selecting from a BYTE column, except the getAsciiStream() method is used instead of getBinaryStream().