|
|
|
Using jdbcKona/Informix4
I. IntroductionWhat's in this document
This document includes documentation on how to set up and use WebLogic
jdbcKona/Informix4 (formerly known as FastForward from Connect
Software, acquired in April 1998). This database driver is a Type 4,
pure-Java, two-tier A Type 4 driver is similar to a Type 2 driver in other ways, however. Both Type 2 and Type 4 drivers are two-tier drivers; that means that each client requires an in-memory copy of the driver to support its connection to the database.
Within the WebLogic environment, you can use a Type 2 or a Type 4
two-tier driver to connect the WebLogic Server jdbcKona/Informix4 supports concurrent ResultSet execution; that is, you do not have to finish with and close one ResultSet on a Connection before you can open and work with another. However, the driver cannot support both concurrent ResultSet execution and client-side caching, because of issues in dealing with BLOBs. It is possible that a later version of the driver will allow row caching if the row contains no BLOB data. If you are using jdbcKona/Informix4 with WebLogic JDBC, you should also refer to the Developers Guide Using WebLogic JDBC for more information.
Note that this JDBC driver is only supported with versions of the
JDK Before you begin working with the jdbcKona/Informix4 driver, make sure you have installed the driver according to the installation instructions and, in particular, that you know the port on which the database is listening for connection requests. Mapping of types from Informix to jdbcKona/Informix4 to Java
III. Implementing with jdbcKona/Informix4
How to connect to an Informix DBMS with jdbcKona/Informix4Here is how you set up your application to connect to Informix using jdbcKona/Informix4. In general, connecting happens in two steps:
In both steps, you are describing the JDBC driver: in the first step, you use the full package name of the driver. Note that it is dot-delimited. In the second step, you identify the driver with its URL, which is colon-delimited. The URL must include at least weblogic:jdbc:informix4, and may include other information, including the server host name and the database name. There are several variations on this basic pattern. Method 1The simplest way to connect is to pass the URL of the driver that includes the name of the server, along with a username and password, as arguments to the DriverManager.getConnection() method. The pattern for the URL is: jdbc:weblogic:informix4:[dbname]@[serverhost]:[port]?[prop1]&[prop2] as in this example: Class.forName("weblogic.jdbc.informix4.Driver").newInstance();
Connection conn = DriverManager.getConnection(
"jdbc:weblogic:informix4:mydb@host:8659",
"scott",
"tiger");
where mydb is the alias of an
Informix database and host:8659 is the hostname and port of the server
supporting it.
Method 2You can also pass a java.util.Properties object with parameters for connection as an argument to the DriverManager.getConnection() method. This example shows how to use a Properties object to connect to a database named "mydb" on a server named "host" (as in the example above). Properties props = new Properties();
props.put("user", "scott");
props.put("password", "tiger");
props.put("db", "mydb");
props.put("server", "host");
props.put("port", "8659");
Class.forName("weblogic.jdbc.informix4.Driver").newInstance();
Connection conn = DriverManager.getConnection(
"jdbc:weblogic:informix4",
props);
You can combine the "db", "server", and "port" properties into one "server" property, as in this example: Properties props = new Properties();
props.put("user", "scott");
props.put("password", "tiger");
props.put("server", "mydb@host:8659");
Class.forName("weblogic.jdbc.informix4.Driver").newInstance();
Connection conn = DriverManager.getConnection(
"jdbc:weblogic:informix4",
props);
There are any number of variations on supplying information in the URL or in the Properties object. Whatever information you pass in the URL of the driver, you do not need to include in the Properties object. Method 3If you prefer, you can load the JDBC driver from the command line with the command:
$ java -Djdbc.drivers=weblogic.jdbc.informix4.Driver classname where classname is the name of the application you want to run; and then use a Properties object to set parameters necessary for connecting to the DBMS. In this case, you will not need to call the Class.forName().newInstance() method, as shown here: Properties props = new Properties();
props.put("user", "scott");
props.put("password", "tiger");
Connection conn = DriverManager.getConnection(
"jdbc:weblogic:informix4:mydb@host:8659",
props);
Other properties you can set for jdbcKona/Informix4There are other Informix-specific properties that you can set in the connection URL or Properties object. These properties give you more control over an Informix-specific environment. For more information on these variables, please refer to your Informix documentation.
Here is an example of these properties used in a URL. Note that the URL is always used as a single line but has been broken here for simplicity. jdbc:weblogic:informix4:mydb@host:1493 ?weblogic.informix4.delimited_identifiers=y &weblogic.informix4.db_money=DM &weblogic.informix4.db_date=Y4MD Note the use of "?" and "&", which are special characters for URLs. For more on using URLs with WebLogic, read the Developers Guide, Using URLs to set properties for a JDBC Connection. Here is how these properties might be used with a Properties object: Properties props = new Properties();
props.put("user", "scott");
props.put("password", "tiger");
props.put("weblogic.informix4.delimited_identifiers", "y");
props.put("weblogic.informix4.db_money", "DM");
Connection conn =
DriverManager.getConnection(
"jdbc:weblogic:informix4:mydb@host:8659",
props);
Retrieving the SERIAL column after an insertYou can obtain serial values after an insert by using the Statement.getSerialNumber() method, a WebLogic extension to JDBC in jdbcKona/Informix4. This allows you to track the index order of rows as you add them to the table. Note that you must create the table with a SERIAL column.To use this extension, you will need to explicitly cast your Statement object to weblogic.jdbc.informix4.Statement. Here is a simple code example that illustrates how to use the getSerialNumber() method. weblogic.jdbc.informix4.Statement stmt =
(weblogic.jdbc.informix4.Statement)conn.createStatement();
String sql = "CREATE TABLE test ( s SERIAL, count INT )";
stmt.executeUpdate(sql);
for (int i = 100; i < 110 ; i++ ) {
sql = "INSERT INTO test VALUES (0, " + i + ")";
stmt.executeUpdate(sql);
int ser = stmt.getSerialNumber();
System.out.println("serial number is: " + ser);
}
sql = "SELECT * from test";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println("row: " + rs.getString(2) +
" serial: " + rs.getString(1));
}
Support for the Informix INTERVAL data typeUse a literal string in the Informix INTERVAL format to enter an INTERVAL value in a SQL statement. Use preparedStatement.setString() to set an INTERVAL value parameter in a prepared statement. For retrieving INTERVAL data from an Informix server, the dbKona/Informix4 driver supports three standard API methods on a ResultSet:
The InformixInterval class provides the following public methods:
Using ResultSetMetaData methodsThe metadata the Informix server returns with query results is available via ResultSetMetaData methods. However, the Informix server does not return information for the following:
Using autocommit modeUnlike other database system attributes, the autocommit mode of an Informix database is not dynamically settable. It is defined when the database is created. You can't change it with a call to the Connection.setAutoCommit method. Only non-ANSI, non-logged databases support changing autocommit. The JDBC spec says that the autocommit mode should be true by default, but it is not possible to do this with Informix. All that you can do is determine what the autocommit mode is. To change the autocommit state, you must rebuild your database (for more information, see the information on "CREATE DATABASE" in the Informix docs). This affects how transactions and locking work. A JDBC program could behave very differently depending on how the Informix database is created. Before you depend on autocommit, you should know the setting of autocommit for the database you will be using. You can check its state with the Connection.getAutoCommit() method, which returns true if autocommit is enabled. For Informix, this method returns false by default for ANSI databases; it may return true or false, depending on how the database was created, for a non-ANSI database. Here is what is supported in jdbcKona/Informix4 when you call the Connection.setAutoCommit() method:
Your program must then operate in accordance with the state of your Informix database. If you are using a non-ANSI database and you set autocommit to false, all transactional SQL must be carried out using the Connection.commit() or Connection.rollback() methods. You should never execute the explicit transaction controls BEGIN WORK, COMMIT WORK, or ROLLBACK WORK calls on a Statement, since jdbcKona/Informix4 uses transaction commands internally to simulate an autocommit=false status. You should always control a transaction using commit() and rollback() methods in the Connection class. For non-ANSI databases without logging, autocommit=false cannot be supported, since transactions are not supported. Consequently, only autocommit=true is supported for use with such databases.
Support for Informix-specific featuresjdbcKona/Informix4 includes support for other Informix-specific features that may not be part of the JDBC specification, but that add power when writing a client application for an Informix database. Creating TEMP tablesCREATE TEMP TABLE is an INFORMIX SQL command that isn't covered by the JDBC specification. jdbcKona/Informix4 supports this Informix feature by making the proper acknowledgment to the Informix server that allows the SQL command to be executed. Informix TEMP tables are useful as scratch pads. Users can create them on the fly to hold data to be used for intermediate processing; for example, data might be retrieved initially in an intermediate TEMP TABLE, and then, with further selects or other operations, might be reduced further to a more manageable size, before retrieval by a client application. The TEMP table disappears when the session is over or the client closes the connection. DBMSes often use TEMP tables internally to correlate or reduce data retrieved from many or very large tables. Retrieving VARCHAR/CHAR data as bytesjdbcKona/Informix4 provides an extension to JDBC for Informix that allows users to retrieve VARCHAR and CHAR columns using the ResultSet.getBytes(String columnName) and ResultSet.getBytes(int columnIndex) methods. Although this is outside the scope of the JDBC specification, this was implemented in response to customer requests. No cast of the ResultSet is required to take advantage of this feature. SET LOCK MODEYou can use the SET LOCK MODE command with an Informix database to influence how the database deals with a locked record when it receives another request to update or delete it. The command "SET LOCK MODE TO WAIT" just means that instead of throwing an error to the client application, the Driver should be made to wait until the record in question becomes free (like the Oracle waitOnResource() command that is supported in jdbcKona/Oracle).Don't confuse the use of this command with the need to adjust a transaction isolation level. If you have an ANSI database, then by default the transaction level is set to serializable. Please read Informix documentation on "SET TRANSACTION" and "SET ISOLATION" to find out more about it. Use of this feature depends on your application, which may not allow a long wait for a record.
Codeset supportAs a Java application, jdbcKona/Informix4 handles character strings as Unicode
strings. To exchange character strings with a database that may
operate with a different codeset, you must set the the weblogic.codeset connection property
to the proper JDK codeset. If there is no direct mapping between the
codeset of your database and the character sets provided with the JDK,
you can set the weblogic.codeset connection property to the most appropriate
Java character set. You can find the List
of supported encodings at the JavaSoft website.
For example, to use the cp932 codeset, create a Properties object and set the weblogic.codeset property before calling DriverManager.getConnection(), as in this example: java.util.Properties props = new java.util.Properties();
props.put("weblogic.codeset", "cp932");
props.put("user", "scott");
props.put("password", "tiger");
String connectUrl = "jdbc:weblogic:informix4:mydb@host:1493";
Class.forName("weblogic.jdbc.informix4.Driver").newInstance();
Connection conn =
DriverManager.getConnection(connectUrl, props);
Using Unicode streams in a prepared statement
Connection c =
DriverManager.getConnection(database,user,password);
PreparedStatement ps =
c.prepareStatement("insert into dbTEST values (99,?)");
String s = new String("\u93e1\u68b0\u897f");
weblogic.jdbc.informix4.UnicodeInputStream uis =
new weblogic.jdbc.informix4.UnicodeInputStream(s);
try {
ps.setUnicodeStream(1,uis,uis.available());
}
catch (java.io.IOException ioe) {
System.out.println("-- IO Exception in setUnicodeStream");
}
ps.executeUpdate();
To retrieve data from a UnicodeInputStream you can use java.io.InputStream. For example, InputStream uisout = rs.getUnicodeStream(2);
int i=0;
while (true) {
try {
i = uisout.read(); // read 1 byte at a time from UnicodeStream
}
catch (IOException e) {
System.out.println("-- IOException reading UnicodeStream");
}
For more information, check the full example included in the distribution, in the examples/jdbc/informix4 directory.
jdbcKona/Informix4 is a complete and compliant implementation of the JDBC specification, except for those features described in JDBC that are either unsupported or unavailable in Informix. Since there is often confusion about the implementation the DatabaseMetaData interface, we list all of its methods here. Most are supported; some are planned for a future release, and some (because of Informix limitations or implementations) will not be supported for this JDBC driver.
|
|
|
|
Copyright © 2000 BEA Systems, Inc. All rights reserved.
|
