Refer to "Restrictions and Limitations" for a list of methods that are not supported by Informix JDBC Driver and should not be used in your Java program. This reference also includes a list of methods that behave differently than described in the JDBC API specification.

Manipulating Informix BYTE and TEXT Data Types

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 multi-byte characters.

Both data types have no maximum size, and 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 the following Web site:

http://www.informix.com/answers

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());
//	     e.printStackTrace();
     }

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());
//        e.printStackTrace();
  } 
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");
    }

try
	{
	pstmt.executeUpdate();
	} 
catch (SQLException e) 
	{
	System.out.println("Failed to Insert into tab: " + e.toString());
	e.printStackTrace();
	} 

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().

Selecting from BYTE and TEXT Columns

All the data in the returned stream must be read before you get the value of any other column in the table. The next call to a getXXX() method implicitly closes the stream.

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:

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");
	    }
        }
catch (Exception e) { }
dispValue(value);

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 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. 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().

Manipulating Informix INTERVAL Data Types

The following example from the Interval.java program shows how to insert into and select from the two types of INTERVAL data types:

/*
 * Create a table with two interval columns
 */
System.out.println("");
System.out.println("Create a table");
try 
    {
    cmd = "create table intrvl_tab " +
	    "(ym interval year to month, ds interval day to second)";

    int rowcount = stmt.executeUpdate(cmd);
    System.out.println("Create table rowcount = " + rowcount);
    }
catch (SQLException e)
    {
    System.out.println("ERROR: execution failed - statement: " + cmd);
    System.out.println("ERROR: " + e.getMessage());
    return;
    }

/*
 * Insert an interval using a String host variable.
 */
System.out.println("");
System.out.println("Insert an interval using a String host variable");
try 
    {
    String ym = new String("102-11");      // 102 years and 11 months
    String ds = new String("5 10:03:55");  // 5 days 10 hours 3 minutes
					   // and 55 seconds
    cmd = "insert into intrvl_tab values (?, ?)";
    PreparedStatement pstmt = conn.prepareStatement(cmd);

    pstmt.setString(1, ym);
    pstmt.setString(2, ds);

    int rowcount = pstmt.executeUpdate();
    System.out.println("Insert rowcount = " + rowcount);
    }
catch (SQLException e)
    {
    System.out.println("ERROR: execution failed - statement: " + cmd);
    System.out.println("ERROR: " + e.getMessage());
    return;
    }

/*
 * Now fetch the row back
 */
System.out.println("");
System.out.println("Select from intrvl_tab");
try 
    {
    cmd = "select * from intrvl_tab";
    ResultSet rs = stmt.executeQuery(cmd);

    String ym;
    String ds;
           while(rs.next())
               {
               ym = rs.getString(1);
               System.out.println("   Select: ym = " + ym);

               ds = rs.getString(2);
               System.out.println("   Select: ds = " + ds);
               }

    }
catch (SQLException e)
    {
    System.out.println("ERROR: execution failed - statement: " + cmd);
    System.out.println("ERROR: " + e.getMessage());
    return;
    }

The Informix JDBC Driver implementation of the Statement.execute() method returns a single ResultSet object. This differs from the JDBC API specification, which states that the method can return multiple ResultSet objects.

Be sure to always explicitly close a Statement, PreparedStatement, and CallableStatement object by calling the appropriate close() method in your Java program when you have finished processing the results of an SQL statement. This immediately deallocates the resources that have been allocated to execute your SQL statement. Although the ResultSet.close() method closes the ResultSet object, it does not deallocate the resources allocated to the Statement, PreparedStatement, or CallableStatement objects.

Example of Sending a Query to an Informix Database

try
    {
    PreparedStatement pstmt = conn.prepareStatement("Select * from x where a = ?;");
    pstmt.setInt(1, 11);
    ResultSet r = pstmt.executeQuery();

    while(r.next())
        {
        short i = r.getShort(1);
        System.out.println("Select: column a = " + i);
        }
    r.close();
    pstmt.close();
    }
catch (SQLException e)
    {
    System.out.println("ERROR: Fetch statement failed: " + e.getMessage());
    }

The program returns resulting rows in a ResultSet object, through which the program iterates with the ResultSet.next() method. The program retrieves individual column values with the ResultSet.getShort() method, since the data type of the selected column is SMALLINT.

Finally, both the ResultSet and PreparedStatement objects are explicitly closed with the appropriate close() method.

For more information on which getXXX() methods should be used to retrieve individual column values, refer to "Mapping Data Types".