For all but the last several years of Informix's 20-year history, their major emphasis has been on making Informix databases work with other Informix databases. As long as Informix maintained a one-source type of environment, this made perfect sense. Administrators of each system only needed to be familiar with one system, the Informix system.
In today's mix-and-match environment, it is essential to be able to communicate across different databases so that you can integrate multiple systems into one somewhat cohesive "system." You need to make data flow between ASCII files on one end to an Informix database in the middle to an Oracle database for Application A to a Microsoft SQL database for Application B on another machine. And the whole system has to tie into the Web. Oh and—by the way—everything is transferred as XML data.
Informix as a company seemed to recognize this trend from the periphery. First, MetaCube opened up to ODBC data sources. Then came DataStage, which was based upon open exchange of data between systems. Its parent, Ardent, was bought by Informix and now the Ardent people seem to be fully in control of Informix after its recent stock market tumble. You'll be seeing a lot more interest from Informix in open systems now that the Ardent folks are in charge. Up to now, Informix somewhat begrudgingly accepted the existence of other databases. Hopefully, the new leadership will be somewhat more open to the rest of the world.
Currently, you have three main options for connecting with an Informix database:
- ESQL/C and other ESQL language variations allow you to write applications that access Informix databases through an embedded set of calls (the native interface). The big disadvantage here is that you have to write your own programs or wait until Informix Corporation develops something for you.
- ODBC, the Open Database Connectivity Standard, has opened up access to just about any data source conceivable.
- JDBC, which can use ODBC data sources as well as the Informix native connectivity, opens up a wide amount of machine-independent possibilities when used to connect Java applications to Informix and other databases.
In this 10-Minute Solution, I will download, install, set up, and test Informix JDBC drivers. Because the vast majority of Java clients that talk to Informix databases are Windows-based applications, that's what I'll concentrate on in this paper. Installations for other OSs are similar, because Java is a machine- and OS-agnostic application.
Choosing Your JDBC Version
Informix's Web site lists two JDBC drivers available at no cost through Informix's electronic software distribution partner, Intraware. Other third-party solutions also exist, but for this paper I'll concentrate only on those that Informix supports officially. There are JDBC solutions for both Java 1.x and Java 2.x. To find out if you have a development environment installed, type javac ? at a command prompt. Javac is the Java compiler. To see which version of the Java runtime you have installed, type java –version at a command prompt.
To write and test Java applets and classes, you should first have a Java development environment installed on your machine. If you do not have a Java environment installed, go to Sun's Products & APIs page. From there, you can install the proper Java environment. If you just want to use JDBC to work with existing applets, you do not need the Java development environment.
Downloading the JDBC Drivers or SDK
Once you have decided which version you need, go to the Intraware Web site for Informix products. If this is your first visit to Intraware, you first have to register and get a password.
You can get the JDBC drivers either as part of the Informix Client SDK (Software Development Kit) or as a standalone version as the JDBC download. If you already have Informix client software installed, all you really need is the JDBC download. If you do not have the connectivity products already installed, go ahead and download and install the entire SDK.
VERY IMPORTANT: I have had installations of the JDBC/SDK go wrong for various reasons and have gotten somewhat defensive every time I do work with Informix connectivity pieces. If you are going to do any of this type of work, first test it on a machine that is not critical to your operations. Do this with the knowledge that you run a risk of having to install your client software completely if the world falls in on you. In all fairness, my problems occurred a while ago and the installs seem to have gotten cleaner, but take this warning as a word to the wise.
From the home page above, scroll down to "Free Informix Downloads" and select JDBC Driver or SDK to download the correct software. If you choose JDBC Driver, you will go through several licensing screens and will finally be given the choice to load JDBC Version 2.11 (the Java 2.x version) or Version 1.50 (the Java 1.x version). Save the downloaded file in a temporary location (JDBC alone takes about 10.5MB; SDK is about 25MB).
Installing the JDBC Drivers
When you get the file downloaded, use an unzip program to extract the two files into a temporary directory. The biggest file is the setup.class file, which contains the compiled Java necessary for the install. The install.txt file gives you the installation instructions. Check it for any last-minute changes. Once you have the components extracted, go into the directory and type java setup. You will get the splash screen shown in Figure 1.
Figure 1: Beginning the JDBC driver installation
Next you will get a screen requesting serial number and key information. This should be any valid Informix serial number/key combination. If you don't know it, check with your DBA. It does not seem to matter what product serial number/keys you use; they all seem to work.
Now comes the licensing screen. You need to check the "Accept all the terms" box before you continue. The install will next ask you for the directory in which to install the product. Remember this directory, as it will be needed when you are setting your "CLASSPATH" variable (see below). Note that this directory does not have to be in the standard directory tree that is created with Informix installs. This directory can be located anywhere in the file system.
Once the install is complete, you can go to the $TARGETDIR\docs\javadoc directory. Click on the index.html document and your browser will open the detailed Java release notes and programming guide. This is a very handy reference and probably deserves an entry in your browser's "favorites" folder.
Setting the CLASSPATH
Java source code is created in files with the *.java extension. These files are first compiled by using the javac compiler. The compiled files have the same base name as the *.java file, except they have the .class suffix. For example, the program test1.java will compile into test1.class. Java and JDBC need to know where to find the Java classes. If your Java programs have trouble finding the components, they will give an error saying that they can't find "main" or other parts of the application. Additionally, Java classes can be stored in an archive form in files suffixed with the ending .jar (for Java archive). The first place Java looks for .jar or .class files is in the directories included in the Java CLASSPATH. Also, it is usually a good to place the current directory (.) at the beginning of the CLASSPATH. Note that the CLASSPATH needs to include the full address of each necessary .jar file, including the filename. Thus, my CLASSPATH looks like this:
CLASSPATH= .;d:\infjava\lib\JLogin.jar;d:\infjava\lib\ifxjdbc.jar;
d:\infjava\lib\ifxsqlj.jar
Notice the ".;" at the beginning of the path. This tells the system to look first in the current directory for any needed classes.
Passing the URL String
One of the quirks of the Java language is the way in which it allows the user to connect to databases. Rather than storing database connection information either in the Registry or in operating system files, Java wants you to pass it a string called a URL string every time you execute a Java application. The URL string looks like this:
jdbc:informix-sqli://hostname:port_num:informixserver=myserver;
user=username;password=yourpw
Here, "hostname" can be either the host name or the IP address of the server. The "port_num" is the port number that the Informix server is communicating over (default on NT is "turbo" on port 1526). The informixserver variable is the same as $INFORMIXSERVER for your Informix installation. It is the name of the Informix instance, not the host. Finally username and password need to be recognized by the server.
The URL string is used as follows:
java program_name URLString
You'll see more of this later as you begin to run some of the test programs in the demo subdirectory of the JDBC installation.
Testing the Connection with jlogin
Informix has made available a very good utility called "jlogin." It's used in much the same manner as ilogin, only with Java connections. This utility is written Java and is distributed as a Java class. To download this utility, click this URL: examples.informix.com/utilities/DatasourcePing/jlogin.zip. Save this file to a temporary directory, unzip the contents into the $TARGETDIR\lib directory, and make sure that the CLASSPATH has the full path to the jlogin.jar program.
To run jlogin, type the following at a command prompt from anywhere in the file system:
java JLogin
Note that the J and L are both capitalized. Java is sensitive to capitalization. Even though the jlogin.jar file may be on a system that does not differentiate between upper and lower case (such as MS-DOS), the Java internals require the correct capitalization. Because the routines inside the jlogin.jar file refer to JLogin, that is the only way to invoke the applet.
The jlogin program is not necessary for the operation of Java and JDBC, but it makes it much easier to test out the components of the URL string. Figure 2 shows the JLogin screen.
Figure 2: The JLogin screen
To use JLogin, simply enter either the server's name or IP address in the boxes, followed by the port number, username, password, server name, and database. Click either the Ping Server or Ping Database buttons. The results will appear in the status box at the bottom of the screen. Once you have the entries down correctly and are able to succeed in accessing the database or the server, you can put them together into a URL string that you can use for creating, testing, and running the demo applets that come with the JDBC installation.
Running the Demo Programs
The demo programs are included in the "demo" subdirectory under the $TARGETDIR where you installed JDBC. There are a total of nine subdirectories under the demo directory, covering such areas as basics, blobs/clobs, rmi/Jini access, and complex data types. All of these programs are supplied in both *.java and *.class forms (that is, they have all been compiled for you already, so you do not need the development environment to run them). If you want to modify any of the code in the *.java files and recompile the .class files, you will need the Java development environment.
For now, let's just run the first program in the $TARGETDIR\infjava\demo\basic subdirectory. This program simply creates a database on the target machine. To run it, go into the "basic" subdirectory and verify that the program "CreateDB.java" is there. If it is, type the following all on one line:
D:\infjava\demo\basic>java CreateDB jdbc:informix-
sqli://scsibox:1526:informixserver=ol_scsibox;
user=informix;password=Informix
The string above is written on one line; don't wrap your real commands.
URL strings are long and subject to typographical errors. In a development environment, it makes sense to create batch files or scripts that insert the URL strings for you. The URL string must be on one line and can be quoted by single quotes, double quotes, or no quotes at all. (Note that there is a peculiarity on NT systems that will cause the program to fail if you quote the URL string in single quotes. Use double quotes or no quotes at all in this case.)
Running the CreateDB program gives you the following output at the command line:
>>>Create Database test.
URL = "jdbc:informix-sqli://scsibox:1526:informixserver=ol_scsibox;
user=informix;password=informix"
>>>End of Create Database test.
This output indicates a successful completion of the create database. You can verify it by going to the host machine. When you do you'll see a database called "testDB" on the server.
As you begin to experiment with the various programs in the demo directory tree, you will find that the expected results of each program are usually delineated in the comments at the beginning of each *.java file.
This 10-Minute Solution provides the basis for any further work you do with Java and Informix. As stated earlier, there is also a way to have the JDBC drivers accept ODBC data sources. Next time, I'll explore how to use non-Informix ODBC packages and the JDBC/ODBC bridge to give Informix systems an easy way to talk to flat ASCII files, comma-separated-variable files, and XML files.
