INFORMIX
Informix JDBC Driver Programmer's Guide
Chapter 2: Programming with Informix JDBC Driver
Contents Index

Establishing a Connection

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.

Loading Informix JDBC Driver

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:

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.

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

Windows
If you are writing an applet that will 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 in the following code example:

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

Creating a Connection

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:

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.

Important: Not all methods of the Connection interface are supported by Informix JDBC Driver. For a list of unsupported methods, see "Restrictions and Limitations".

Format of Database URLs

Informix JDBC Driver supports database URLs of the following format:

In the preceding syntax:

Important: Blank spaces are not allowed in the database URL.
The following table describes the variable parts of the database URL.

Database URL Variable Required? Description

ip-address

host-name

Yes

The IP address or the host name of the computer running the Informix database server.

An example of an IP address is 123.45.67.89.

An example of a host name is myhost.com or myhost.informix.com.

port-number

Yes

The port number of the Informix database server.

dbname

No

The name of the Informix database to which you want to connect. If you do not specify the name of a database, a connection is made to the Informix database server.

server-name

Yes

The name of the Informix server to which you want to connect. This is the value of the INFORMIXSERVER environment variable.

The INFORMIXSERVER environment variable is required in the database URL, unless it is included in the property list.

user

Yes

The name of the user that wants to connect to the Informix database or database server.

password

Yes

The password of the user specified by user.

name=value

No

A name-value pair that specifies a value for the Informix environment variable contained in the name variable, recognized by either Informix JDBC Driver or Informix database servers. The name variable is case insensitive.

Informix JDBC Driver reads Informix environment variables from either the database URL or from a connection property list, described in "Specifying Environment Variables with the Properties Class". The user's environment is not consulted.

Refer to "Supported Informix Environment Variables" for a list of Informix environment variables supported by Informix JDBC Driver.

Database Versus Database Server Connections

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 do not specify the name of a database, a connection is made to the database server specified by the INFORMIXSERVER environment variable instead.

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 an INFORMIX-OnLine 5.x database server, you must specify the USEV5SERVER environment variable in the database URL or property list. Its value should be 1, such as "USERV5SERVER=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 database URL that is passed in as a parameter to the program when the program is run at the command line is as follows; note that the URL does not include the name of a database:

The code is as follows:

Specifying Environment Variables with the Properties Class

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; 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".

Supported Informix Environment Variables

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

Important: The variable DBDATE is not supported. To be compatible with the JDBC API specification, the DBDATE format has been hard-coded as Y4MD-, such as 1998-05-01.

Supported Informix Environment Variables Description

DBANSIWARN

Checks for Informix extensions to ANSI standard syntax.

DBSPACETEMP

Specifies the dbspaces in which temporary tables will be built.

DBUPSPACE

Specifies the amount of system disk space that the UPDATE STATISTICS statement can use when it simultaneously constructs multiple-column distributions.

DELIMIDENT

Specifies that strings set off by double quotes are delimited identifiers.

FET_BUF_SIZE

Overrides the default setting for the size of the fetch buffer for all data except large objects.

IFX_AUTOFREE

When set to 1, specifies that the Statement.close() method does not require a network round-trip to free the server cursor resources if the cursor has already been closed in the database server.

The database server automatically frees the cursor resources after the cursor is closed, either explicitly by the ResultSet.close() method or implicitly by the OPTOFC environment variable.

Once the cursor resources have been freed, the cursor can no longer be referenced.

INFORMIXCONRETRY

Specifies the maximum number of additional connection attempts that should be made to each database server by the client during the time limit specified by the default value of the INFORMIXCONTIME environment variable (15 seconds).

INFORMIXOPCACHE

Specifies the size of the memory cache for the staging-area blobspace of the client application.

INFORMIXSERVER

Specifies the default database server to which an explicit or implicit connection is made by a client application.

INFORMIXSTACKSIZE

Specifies the stack size, in kilobytes, that the database server uses for a particular client session.

NODEFDAC

When set to YES, prevents default table and routine privileges from being granted to the PUBLIC user when a new table or routine is created in a database that is not ANSI compliant. Default is NO.

OPTCOMPIND

Specifies the join method that the query optimizer should use.

OPTOFC

When set to 1, the ResultSet.close() method does not require a network round-trip if all the qualifying rows have already been retrieved in the client's tuple buffer. The database server automatically closes the cursor after all the rows have been retrieved.

Informix JDBC Driver might or might not have additional rows in the client's tuple buffer before the next ResultSet.next() method is called. Therefore, unless Informix JDBC Driver has received all the rows from the database server, the ResultSet.close() method might still require a network round-trip when OPTOFC is set to 1.

PATH

Specifies the directories that should be searched for executable programs.

PDQPRIORITY

Determines the degree of parallelism used by the database server.

PLCONFIG

Specifies the name of the configuration file used by the high-performance loader.

PSORT_DBTEMP

Specifies one or more directories to which the database server writes the temporary files it uses when performing a sort.

PSORT_NPROCS

Enables the database server to improve the performance of the parallel-process sorting package by allocating more threads for sorting.

USEV5SERVER

When set to YES, specifies that the Java program is connecting to an INFORMIX-OnLine 5.x database server.

This environment variable is mandatory if you are connecting to an INFORMIX-OnLine 5.x database server.

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 the following Web site:




Informix JDBC Driver Programmer's Guide, Version 1.22
Copyright © 1998, Informix Software, Inc. All rights reserved.