Informix JDBC Driver Programmer's Guide Programming with Informix JDBC Driver

Internationalization allows you to develop software independently of the countries or languages of its users and then to localize your software for multiple countries or regions. Informix JDBC Driver extends the Java JDK 1.2 internationalization features by providing access to Informix databases that are based on different locales and code sets.

Versions 1.1 and 1.2 of the JDK provide a rich set of APIs for developing global applications. These internationalization APIs are based on the Unicode 2.0 code set and can adapt text, numbers, dates, currency, and user-defined objects to any country's conventions.

The internationalization APIs are concentrated in three packages:

• The java.text package contains classes and interfaces for handling text in a locale-sensitive way.
• The java.io package contains new classes for importing and exporting non-Unicode character data.
• The java.util package contains the Locale class, the localization support classes, and new classes for date and time handling.

For more information about JDK internationalization support, consult this Web site:

http://java.sun.com/products/jdk/1.2/docs/guide/internat/index.html

Warning: There is no connection between JDK locales and JDK code sets: you must keep these in agreement. For example, if you select the Japanese locale ja_JP, there is no Java method that tells you that the SJIS code set is the most appropriate.

Support for Informix GLS Variables

For general information about setting up Informix global language support (GLS), refer to the Informix Guide to GLS Functionality.

Internationalization adds several new environment variables to Informix JDBC Driver:

• DB_LOCALE
• CLIENT_LOCALE
• GL_DATE
• DBDATE
• DBCENTURY

The locale environment variables are available on and optional for Informix servers that support GLS:

• The DB_LOCALE variable specifies the locale of the database. Informix JDBC Driver uses this variable to perform code set conversion between Unicode and the database locale. The database server uses DB_LOCALE with CLIENT_LOCALE to establish the server processing locale.
• The CLIENT_LOCALE variable specifies the locale of the client that is accessing the database. Informix JDBC Driver uses this variable only to provide defaults for user-defined formats and to display error messages.

If set, the CLIENT_LOCALE value establishes the database server processing locale to provide defaults for user-defined formats like the GL_DATE format, and user-defined types can use it for code-set conversion.

The DB_LOCALE and CLIENT_LOCALE values must be the same, or their code sets must be convertible. For more information, see the Informix Guide to GLS Functionality.

The GL_DATE, DBDATE, and DBCENTURY variables are described in the following section.

Important: The DB_LOCALE, CLIENT_LOCALE, and GL_DATE variables are supported only if the database server supports the Informix GLS feature. If these environment variables are set and your application connects to a non-GLS server (server versions earlier than 7.2), a connection exception occurs. If you connect to a non-GLS server, the behavior is the same as for Informix JDBC Driver Version 1.22.

Support for Date End-User Formats

The date end-user format is the format in which a date appears in a string variable. This section describes the GL_DATE, DBDATE, and DBCENTURY variables, which specify date end-user formats. These variables are optional.

The GL_DATE environment variable specifies the end-user formats of values in DATE columns. This variable is supported in Informix database servers 7.2x and beyond. A GL_DATE format string can contain the following characters:

• One or more white-space characters
• An ordinary character (other than the % symbol or a white-space character)
• A formatting directive, which is composed of the % symbol followed by one or two conversion characters that specify the required replacement

Date formatting directives are defined in the following table.

Important: GL_DATE optional date format qualifiers for field specifications (for example %4m to display a month as a decimal number with a maximum field width of 4) are not supported.

Informix JDBC Driver does not support ALS 6.0, 5.0, or 4.0 era-based formats in the DBDATE and GL_DATE environment variables.

The GL_DATE conversion modifier O, which indicates use of alternative digits for alternative date formats, is not supported.

White space or other nonalphanumeric characters must appear between any two formatting directives. If a GL_DATE variable format does not correspond to any of the valid formatting directives, errors can result when the database server attempts to format the date.

For example, for a U.S. English locale, you can format an internal DATE value for 09/29/1998 using the following format:

* Sep 29, 1998 this day is:(Tuesday), a fine day *

To create this format, set the GL_DATE environment variable to this value:

* %b %d, %Y this day is:(%A), a fine day *

To insert this date value into a database table that has a date column, you can perform the following types of inserts:

• Nonnative SQL, in which SQL statements are sent to the database server unchanged. Enter the date value exactly as expected by the GL_DATE setting.
• Native SQL, in which escape syntax is converted to an Informix-specific format. Enter the date value in the JDBC escape format yyyy-mm-dd; the value is converted to the GL_DATE format automatically.

The following example shows both types of inserts:

stmt = conn.createStatement();

cmd = "create table tablename (col1 date, col2 char(100));";

rc = stmt.executeUpdate(cmd);

...

String[] dateVals = {

"`* Oct 08, 1998 this day is: (Thursday), a fine day *'",

"{d `1998-09-29'}"

};

String[] charVals = {

"`* Oct 08, 1998 this day is: (Thursday), a fine day *'",

"`* Sep 29, 1998 this day is: (Tuesday), a fine day *'"

};

int numRows = dateVals.length;

for (int i = 0; i < numRows; i++)

{

cmd = "insert into tablename values(" + dateVals[i] + ", " +

charVals[i] + ")";

rc = stmt.executeUpdate(cmd);

System.out.println("Insert: column col1 (date) = " + dateVals[i]);

System.out.println("Insert: column col2 (char) = " + charVals[i]);

}

To retrieve the formatted GL_DATE DATE value from the database, call the getString() method of the ResultSet class. To enter strings that represent dates into database table columns of char, varchar, or lvarchar, you can also build date objects that represent the date string value. The date string value must be in GL_DATE format. The following example shows both ways of selecting DATE values:

PreparedStatement pstmt = conn.prepareStatement("Select * from tablename "

+ "where col2 like ?;");

pstmt.setString(1, "%Tue%");

ResultSet r = pstmt.executeQuery();

while(r.next())

{

String s = r.getString(1);

java.sql.Date d = r.getDate(2);

System.out.println("Select: column col1 (GL_DATE format) = <"

+ s + ">");

System.out.println("Select: column col2 (JDBC Escape format) = <"

+ d + ">");

}

r.close();

pstmt.close();

Support for the DBDATE environment variable provides backward compatibility for client applications that are based on Informix database server versions prior to 7.2x. Informix recommends that you use the GL_DATE environment variable for new applications.

The DBDATE environment variable specifies the end-user formats of values in DATE columns. End-user formats are used in the following ways:

• When you input DATE values, Informix products use the DBDATE environment variable to interpret the input. For example, if you specify a literal DATE value in an INSERT statement, Informix database servers require this literal value to be compatible with the format specified by the DBDATE variable.
• When you display DATE values, Informix products use the DBDATE environment variable to format the output.

With standard formats, you can specify the following attributes:

• The order of the month, day, and year in a date
• Whether the year is printed with two digits (Y2) or four digits (Y4)
• The separator between the month, day, and year

The format string can include the following characters:

• Hyphen ( - ), dot, ( . ), and slash ( / ) are separator characters in a date format. A separator appears at the end of a format string (for example Y4MD-).
• A 0 indicates that no separator is displayed.
• D and M are characters that represent the day and the month.
• Y2 and Y4 are characters that represent the year and the number of digits in the year.

The following format strings are valid standard DBDATE formats:

• DMY2
• DMY4
• MDY4
• MDY2
• Y4MD
• Y4DM
• Y2MD
• Y2DM

The separator always goes at the end of the format string (for example, DMY2/). If no separator or an invalid character is specified, the slash ( / ) character is the default.

For the U.S. ASCII English locale, the default setting for DBDATE is Y4MD-, where Y4 represents a four-digit year, M represents the month, D represents the day, and hyphen ( - ) is the separator (for example, 1998-10-08).

To insert a date value into a database table with a date column, you can perform the following types of inserts:

• Nonnative SQL, in which SQL statements are sent to the database server unchanged. Enter the date value exactly as expected by the DBDATE setting.
• Native SQL, in which escape syntax is converted to an Informix-specific format. Enter the date value in the JDBC escape format yyyy-mm-dd; the value is converted to the DBDATE format automatically.

The following example shows both types of inserts (the DBDATE value is MDY2-):

stmt = conn.createStatement();

cmd = "create table tablename (col1 date, col2 varchar(20));";

rc = stmt.executeUpdate(cmd);

...

String[] dateVals = {"`08-10-98'", "{d `1998-08-11'}" };

String[] charVals = {"`08-10-98'", "`08-11-98'" };

int numRows = dateVals.length;

for (int i = 0; i < numRows; i++)

{

cmd = "insert into tablename values(" + dateVals[i] + ", " +

charVals[i] + ")";

rc = stmt.executeUpdate(cmd);

System.out.println("Insert: column col1 (date) = " + dateVals[i]);

System.out.println("Insert: column col2 (varchar) = " + charVals[i]);

}

To retrieve the formatted DBDATE DATE value from the database, call the getString method of the ResultSet class. To enter strings that represent dates into database table columns of char, varchar, or lvarchar, you can build date objects that represent the date string value. The date string value needs to be in DBDATE format. The following example shows both ways to select DATE values:

PreparedStatement pstmt = conn.prepareStatement("Select * from tablename "

+ "where col1 = ?;");

GregorianCalendar gc = new GregorianCalendar(1998, 7, 10);

java.sql.Date dateObj = new java.sql.Date(gc.getTime().getTime());

pstmt.setDate(1, dateObj);

ResultSet r = pstmt.executeQuery();

while(r.next())

{

String s = r.getString(1);

java.sql.Date d = r.getDate(2);

System.out.println("Select: column col1 (DBDATE format) = <"

+ s + ">");

System.out.println("Select: column col2 (JDBC Escape format) = <"

+ d + ">");

}

r.close();

pstmt.close();

Important: Informix JDBC Driver does not support ALS 6.0, 5.0, or 4.0 era-based formats in the DBDATE and GL_DATE environment variables.

DBCENTURY Variable

The DBCENTURY environment variable enables you to choose the appropriate four-digit year expansion for one- or two-digit year DATE and DATETIME values. See the Informix Guide to SQL: Reference for detailed information about this environment variable.

Informix JDBC Driver accepts the DBCENTURY value but does not use it to expand a two-digit year to a four-digit year. When you specify a two-digit year, Informix JDBC Driver supplies the first two digits of the current year. For example, in 1999, the driver supplies 19; in 2000, the driver supplies 20.

Informix JDBC Driver always includes four-digit years when it sends java.sql.Date and java.sql.Timestamp values to the server. Similarly, the server always includes four-digit years when it sends Informix date and datetime values to Informix JDBC Driver.

Informix JDBC Driver also does not use the DBCENTURY value for string-to-date value conversion. Informix JDBC Driver performs string-to-date, string-to-datetime, and string-to-timestamp conversions under these circumstances:

• If you pass a Java String object to the setObject() method to insert data into a Date or Datetime column.
• If you use the getDate() or getTimeStamp() method to retrieve data from a string-type database column, such as CHAR.

The precedence rules that define how to determine an end-user format for an internal DATE value are listed here:

• If a DBDATE format is specified, this format is used.
• If a GL_DATE format is specified, a locale must be determined:
• If a CLIENT_LOCALE value is specified, it is used in conjunction with the GL_DATE format string to display DATE values.
• If a DB_LOCALE value is specified but a CLIENT_LOCALE value is not, the DB_LOCALE value is compared with the database locale (read from the systables table of the user database) to verify that the DB_LOCALE value is valid. If the DB_LOCALE value is valid, it is used in conjunction with the GL_DATE format string to display DATE values. If the DB_LOCALE value is not valid, the database locale is used in conjunction with the GL_DATE format string.
• If neither CLIENT_LOCALE nor DB_LOCALE values are specified, the database locale is used in conjunction with the GL_DATE format string to display DATE values.
• If a CLIENT_LOCALE value is specified, the DATE formats conform to the default formats associated with this locale.
• If a DB_LOCALE value is specified but no CLIENT_LOCALE value is specified, the DB_LOCALE value is compared with the database locale to verify that the DB_LOCALE value is valid. If the DB_LOCALE value is valid, the DB_LOCALE default formats are used. If the DB_LOCALE value is not valid, the default formats for dates associated with the database locale are used.
• If neither CLIENT_LOCALE nor DB_LOCALE values are specified, all DATE values are formatted in U.S. English format, Y4MD-.

Code-set conversion converts character data from one code set (the source code set) to another (the target code set). In a client/server environment, character data might need to be converted from one code set to another if the client and database server computers use different code sets to represent the same characters. For detailed information about code set conversion, see the Informix Guide to GLS Functionality.

You must specify code set conversion for the following types of character data:

• SQL data types (char, varchar, nchar, nvarchar)
• SQL statements
• Database objects such as database names, column names, table names, statement identifier names, and cursor names
• Stored procedure text
• Command text
• Environment variables

Informix JDBC Driver converts character data as it is sent between client and database server. The code set (encoding) used for the conversion is specified in the systables catalog for the opened database. You set the DB_LOCALE and CLIENT_LOCALE values in the connection properties or database URL.

Java is Unicode based, so Informix JDBC Driver converts data between Unicode and the Informix database code set. The code set conversion value is extracted from the DB_LOCALE value specified at the time the connection is made. If this DB_LOCALE value is incorrect, the database locale (stored in the database systables catalog) is used in the connection and in the code set conversion.

The DB_LOCALE value must be a valid Informix locale, with a valid Informix code set name or number as shown in the compatibility table that follows. The following table maps the supported JDK 1.2 encodings to Informix code sets.

You cannot use an Informix locale with a code set where there is no JDK supported encoding. This incorrect usage results in an Encoding not supported error message.

If the connection is made but the database server returns a warning of a mismatch between the DB_LOCALE value sent and the real value in the database systables catalog, the correct database locale is automatically extracted from the systables catalog and the client uses the correct JDK encoding for the connection.

The following table shows the supported locales.

Because the Unicode code set includes all existing code sets, the Java virtual machine (JVM) must render the character using the platform's local code set. Inside the Java program, you must always use Unicode characters. The JVM on that platform converts input and output between Unicode and the local code set. For example, you specify button labels in Unicode, and the JVM converts the text to display the label correctly. Similarly, when the getText() method gets user input from a text box, the client program gets the string in Unicode, no matter how the user entered it.

Never read a text file one byte at a time. Always use the InputStreamReader() or OutputStreamWriter() methods to manipulate text files. By default, these methods use the local encoding, but you can specify an encoding in the constructor of the class, as follows:

InputStreamReader = new InputStreamReader (in, "SJIS");

You and the JVM are responsible for getting external input into the correct Java Unicode string. Thereafter, the database locale encoding is used to send the data to and from the database server.

If you do not specify the database name at connection time, the connection must be opened with the correct DB_LOCALE value for the specified database.

If close database and database dbname statements are issued, the connection continues to use the original DB_LOCALE value to interpret the database name, so if the DB_LOCALE value of the new database does not match, an error is returned. In this case, the client program must close and reopen the connection with the correct DB_LOCALE value for the new database.

If you supply the database name at connection time, the DB_LOCALE value must be set to the correct database locale.

Informix JDBC Driver does not automatically convert between code sets for TEXT, BYTE, CLOB, and BLOB data types. You can convert between code sets for TEXT data by using the getBytes(), getString(), InputStreamReader(), and OutputStreamWriter() methods. These methods take a code set parameter that converts to and from Unicode and the specified code set. These methods are covered in detail in Sun's JDK documentation.

Here is sample code that shows how to convert a file from the client code set to Unicode and then from Unicode to the database code set. When you retrieve data from the database, you can use the same approach to convert the data from the database code set to the client code set.

File infile = new File("data_jpn.dat");

File outfile = new File ("data_conv.dat");

...

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

...

// Convert data from client encoding to database encoding

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

try

{

String from = "SJIS";

String to = "8859_1";

convert(infile, outfile, from, to);

}

catch (Exception e)

{

System.out.println("Failed to convert file");

}

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

try

{

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

fin = new FileInputStream(outfile);

pstmt.setAsciiStream(1 , fin, fileLength);

pstmt.executeUpdate();

}

catch (Exception e)

{

System.out.println("Failed to setAsciiStream");

}

...

public static void convert(File infile, File outfile, String from, String to)

throws IOException

{

InputStream in = new FileInputStream(infile);

OutputStream out = new FileOutputStream(outfile);

Reader r = new BufferedReader( new InputStreamReader( in, from));

Writer w = new BufferedWriter( new OutputStreamWriter( out, to));

//Copy characters from input to output. The InputStreamReader converts

// from the input encoding to Unicode, and the OutputStreamWriter

// converts from Unicode to the output encoding. Characters that can

// not be represented in the output encoding are output as `?'

char[] buffer = new char[4096];

int len;

while ((len = r.read(buffer)) != -1)

w.write(buffer, 0, len);

r.close();

w.flush();

w.close();

}