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

You must first establish a connection to an Informix database server or database before you can start sending queries and receiving results in your Java program.

You establish a connection by completing two actions:

1. Load Informix JDBC Driver.
2. Create a connection to either a database server or a specific database.

You have the following connection options:

• Dynamically reading the Informix sqlhosts file
• Encrypting the password

To load Informix JDBC Driver, use the Class.forName() method, passing it the value com.informix.jdbc.IfxDriver, as shown in the following code example from the CreateDB.java program:

try

{

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

}

catch (Exception e)

{

System.out.println("ERROR: failed to load Informix JDBC driver.");

e.printStackTrace();

return;

}

The Class.forName() method loads the Informix implementation of the Driver class, IfxDriver. The IfxDriver class then creates an instance of the driver and registers it with the DriverManager class.

Once you have loaded Informix JDBC Driver, you are ready to connect to an Informix database or database server.

If you are writing an applet to be viewed with Microsoft Internet Explorer, you might need to explicitly register Informix JDBC Driver to avoid platform incompatibilities.

To explicitly register the driver, use the DriverManager.registerDriver() method, as shown:

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

This method might register Informix JDBC Driver twice, which does not cause a problem.

To create a connection to an Informix database or database server, use the DriverManager.getConnection() method. This method creates a Connection object, which is later used to create SQL statements, send them to an Informix database, and process the results.

The DriverManager class keeps track of the available drivers and handles connection requests between appropriate drivers and databases or database servers. The url parameter of the getConnection() method is a database URL that specifies the subprotocol (the database connectivity mechanism), the database or database server identifier, and a list of properties. A second parameter to the getConnection() method, property, is the property list. See Specifying Environment Variables with the Properties Class for an example of how to specify a property list.

The following example shows a database URL that connects to a database called testDB:

jdbc:informix-sqli://123.45.67.89:1533/testDB:INFORMIXSERVER=myserver;

user=rdtest;password=test

The details of the database URL syntax are described in the next section.

The following code example from the CreateDB.java program shows how to connect to database testDB using Informix JDBC Driver. In the full example, the url variable, described in the preceding example, is passed in as a parameter when the program is run at the command line.

try

{

conn = DriverManager.getConnection(url);

}

catch (SQLException e)

{

System.out.println("ERROR: failed to connect!");

System.out.println("ERROR: " + e.getMessage());

e.printStackTrace();

return;

}

Important: The only connection type supported by Informix JDBC Driver is tcp. Shared memory and other connections types are not supported. The connection type is specified in the NETTYPE parameter of the sqlhosts or ONCONFIG file. For more information, see the Administrator's Guide for your database server.

Important: Not all methods of the Connection interface are supported by Informix JDBC Driver. For a list of unsupported methods, see Unsupported Methods.

Tip: You do not have to explicitly close a database connection. The database server closes the connection automatically.

Format of Database URLs

Informix JDBC Driver supports database URLs of the following format:

jdbc:informix-sqli://[{ip-address|host-name}:port-number][/dbname]:

INFORMIXSERVER=server-name;[user=user;password=password]

[;name=value[;name=value]...]

In the preceding syntax:

• curly brackets ( {} ) together with vertical lines ( | ) denote more than one choice of variable.
• italics denote a variable value.
• brackets ( [] ) denote an optional value.
• words or symbols not enclosed in brackets are required
  (INFORMIXSERVER=, for example).

Important: Blank spaces are not allowed in the database URL.

The following table describes the variable parts of the database URL.

Using the DriveManager.getConnection() method, you can create a connection to either an Informix database or an Informix database server.

To create a connection to an Informix database, specify the name of the database in the dbname variable of the database URL. If you omit the name of a database, a connection is made to the database server specified by the INFORMIXSERVER environment variable of the database URL or the connection property list.

If you connect directly to an Informix database server, you can execute an SQL statement that connects to a database later in your Java program.

All connections to both databases and database servers must include the name of an Informix database server via the INFORMIXSERVER environment variable.

Important: If you are connecting to a 5.x database server (either INFORMIX-OnLine Dynamic Server or INFORMIX-SE), you must specify the USEV5SERVER environment variable in the database URL or property list. Its value must be 1, such as USEV5SERVER=1.

The example given in Creating a Connection shows how to create a connection directly to the Informix database called testDB with the database URL.

The following example from the DBConnection.java program shows how to first create a connection to the Informix database server called myserver and then connect to the database testDB later in the Java program using the Statement.executeUpdate() method.

The following database URL is passed in as a parameter to the program when the program is run at the command line; note that the URL does not include the name of a database:

jdbc:informix-sqli://123.45.67.89:1533:INFORMIXSERVER=myserver;user=rdtest;

password=test

Here is the example code:

String cmd = null;

int rc;

Connection conn = null;

try

{

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

}

catch (Exception e)

{

System.out.println("ERROR: failed to load Informix JDBC driver.");

}

try

{

conn = DriverManager.getConnection(newUrl);

}

catch (SQLException e)

{

System.out.println("ERROR: failed to connect!");

}

try

{

Statement stmt = conn.createStatement();

cmd = "database testDB;";

rc = stmt.executeUpdate(cmd);

stmt.close();

}

catch (SQLException e)

{

System.out.println("ERROR: execution failed - statement: " + cmd);

System.out.println("ERROR: " + e.getMessage());

}

Informix JDBC Driver reads Informix environment variables only from the name-value pairs in the connection database URL or from a connection property list. The driver does not consult the user's environment for any environment variables. Refer to Supported Informix Environment Variables for a list of supported Informix environment variables.

To specify Informix environment variables in the name-value pairs of the connection database URL, refer to Format of Database URLs.

To specify Informix environment variables via a property list, use the java.util.Properties class to build the list of properties. The list of properties might include Informix environment variables, such as INFORMIXSERVER, as well as user and password. After you have built the property list, pass it to the DriverManager.getConnection() method as a second parameter. You still need to include a database URL as the first parameter, although in this case you do not need to include the list of properties in the URL.

The following code from the optofc.java example shows how to use the java.util.Properties class to set connection properties. It first uses the Properties.put() method to set the environment variable OPTOFC to 1 in the connection property list; then it connects to the database.

The DriverManager.getConnection() method in this example takes two parameters: the database URL and the property list. The example creates a connection similar to the example given in Creating a Connection.

The following database URL is passed in as a parameter to the example program when the program is run at the command line:

jdbc:informix-sqli://myhost:1533:informixserver=myserver;user=rdtest

;password=test

Here is the example program:

try

{

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

}

catch (Exception e)

{

System.out.println("ERROR: failed to load Informix JDBC driver.");

}

try

{

Properties pr = new Properties();

pr.put("OPTOFC","1");

conn = DriverManager.getConnection(newUrl, pr);

}

catch (SQLException e)

{

System.out.println("ERROR: failed to connect!");

}

The following table lists the Informix environment variables supported by Informix JDBC Driver.

For a detailed description of a particular environment variable, refer to Informix Guide to SQL: Reference. You can find the on-line version of this guide at http://www.informix.com/answers.

Informix JDBC Driver now supports the JNDI (Java naming and directory interface). This support enables JDBC programs to access the Informix sqlhosts file. The sqlhosts file lets a client application find and connect to an Informix database server anywhere on the network. For more information about this file, see the Administrator's Guide for your database server.

You can access sqlhosts data from a local file or from an LDAP (lightweight directory access protocol) server. The system administrator must load the sqlhosts data into the LDAP server using an Informix-supplied utility.

Your CLASSPATH variable must reference the JNDI JAR (Java archive) files and the LDAP SPI (service provider interface) JAR files. You must use LDAP Version 3.0 or later, which supports the object class extensibleobject.

An unsigned applet cannot access the sqlhosts file or an LDAP server. For more information, see Using the Driver in an Applet.

You can let Informix JDBC Driver look up the host name or port number in an LDAP server instead of specifying them in the database URL directly. You must specify the following properties in the database URL for the LDAP server:

• SQLH_TYPE=LDAP
• LDAP_URL=ldap://host-name:port-number
• LDAP_IFXBASE=Informix-base-DN
• LDAP_USER=user
• LDAP_PASSWD=password

If LDAP_USER and LDAP_PASSWD are not specified, Informix JDBC Driver uses an anonymous search to search the LDAP server. The LDAP administrator must make sure that an anonymous search is allowed on the sqlhosts entry. For more information, see your LDAP server documentation.

Here is an example database URL:

jdbc:informix-sqli[:/dbname]:informixserver=value;SQLH_TYPE=LDAP;

LDAP_URL=ldap://davinci:329;LDAP_IFXBASE=cn=informix,

cn=software,o=kmart,c=US;LDAP_USER=abcd;LDAP_PASSWD=secret

You can also specify the sqlhosts file in the database URL. The host name and port number are read from the sqlhosts file. You must specify the following properties for the file:

• SQLH_TYPE=FILE
• SQLH_FILE=sqlhosts-filename

The sqlhosts file can be local or remote, so you can refer to it in the local file system format or URL format. Here are some examples:

• SQLH_FILE=http://host-name:port-number/sqlhosts.ius
• SQLH_FILE=file://D:/local/myown/sqlhosts.ius
• SQLH_FILE=/u/local/sqlhosts.ius

Here is an example database URL:

jdbc:informix-sqli[:/dbname]:informixserver=value;SQLH_TYPE=FILE;

SQLH_FILE=/u/local/sqlhosts.ius

If the database URL references the LDAP server or sqlhosts file but also directly specifies the IP address, host name, and port number, the IP address, host name, and port number specified in the database URL take precedence.

If you want the LDAP server to store sqlhosts information that a JDBC program can look up, the following requirements must be met:

• The LDAP server must be installed on a computer that is accessible to the client. The LDAP administrator must create an IFXBASE entry in the LDAP server.

Refer to http://www.netscape.com for more information about LDAP directory servers.

• If you want to use the SqlhUpload and SqlhDelete utilities provided by Informix, which can load or delete the sqlhosts entries from a flat ASCII file, the servicename field in the sqlhosts file must specify the port number. For more information, see "Utilities to Update the LDAP Server with sqlhosts Data," next.
• The LDAP administrator must make sure that anonymous search is allowed on the sqlhosts entry. For more information, see the LDAP server documentation.

The SqlhUpload and SqlhDelete utilities are packaged in ifxtools.jar, so the CLASSPATH variable must point to ifxtools.jar (which, by default, is in the lib directory under the installation directory for Informix JDBC Driver). Make sure that the CLASSPATH variable also points to the JNDI JAR files and LDAP SPI JAR files.

This utility loads the sqlhosts entries from a flat ASCII file to the LDAP server in the prescribed format. Enter the following command:

java SqlhUpload sqlhfile.txt host-name:port-number [sqlhostsRdn]

The parameters of this command have the following meanings:

• sqlhfile.txt is the sqlhosts file to be uploaded.
• host-name:port-number is the host name and port number of the LDAP server.
• sqlhostsRdn is the RDN (relative distinguished name) of the sqlhosts node under the Informix base in LDAP. The default name is sqlhosts.

The utility prompts for other required information, such as the Informix base DN (distinguished name) in the LDAP server, the LDAP user, and the password.

You must convert the servicename field in the sqlhosts file to a string that represents an integer (the port number), because the Java.Socket class cannot accept an alphanumeric servicename for the port number. For more information about the servicename field, see the Administrator's Guide for your database server.

This utility deletes the sqlhosts entries from the LDAP server. Enter the following command:

java SqlhDelete host-name:port-number [sqlhostsRdn]

The parameters of this command have the same meanings as the parameters listed for SqlhUpload on page 2-19.

The utility prompts for other required information, such as the Informix base DN in the LDAP server, the LDAP user, and the password.

The SECURITY environment variable specifies the security operations that are performed when the Informix JDBC client and Informix database server exchange data. The only SECURITY option supported in Informix JDBC Driver is PASSWORD. By default, no SECURITY option is set. If the PASSWORD option is specified, the user-provided password is encrypted using 56-bit encryption when it is passed from the client to the database server.

Both the SECURITY and PASSWORD keywords are case insensitive.

Here is an example of the database URL syntax for the PASSWORD option:

String URL = "jdbc:informix-sqli://158.58.10.171:1664:user=myname;

password=mypassord;INFORMIXSERVER=myserver;SECURITY=PASSWORD";

The SECURITY=PASSWORD option is supported in the 7.31, 8.3, and 9.1x versions of the Informix database server. The connection is rejected if used with any other versions of the server.

If the SECURITY=PASSWORD option is enabled in the Informix JDBC client, the Informix database server must also be configured to support the corresponding security options requested by the client. Otherwise, an error is returned during connection.

To use the SPWDCSM csm option, which supports password encryption on the database server, you must configure the server's sqlhosts servername option. After this option is set on the server, only clients using the SECURITY=PASSWORD option can connect to that server name. To see if the SPWDCSM csm option is supported for your version of Informix database server, check the database server release notes. See the Administrator's Guide for your database server for general details on how to configure the csm options.

To use the SECURITY=PASSWORD option, you must install a JDK Java cryptography extension (JCE) compliant security package on the JDBC client and include the installation directory of the security package in the CLASSPATH variable.

Informix has certified the SunJCE 1.2 security package, which you can download free from the following Web site:

http://java.sun.com/products/jdk/1.2/jce

SunJCE is available only in the U.S. or Canada. If your site does not comply with this or other SunJCE licensing restrictions, you can try using Informix JDBC Driver with other JCE-certified security package providers. However, Informix has not tested and certified that these packages work correctly with Informix database servers configured to use the SPWDCSM csm option.

To install the SunJCE package, download the SunJCE distribution, extract the JAR file containing the SunJCE provider packages, and make sure the CLASSPATH environment variable includes the extracted JAR filename.

Edit the jdk1.2/lib/security/java.security file to add the following two lines:

security.provider.1=sun.security.provider.Sun
security.provider.2=com.sun.crypto.provider.SunJCE