Developing a UDR with Embedded SQL

This section will cover the nuts and bolts of developing a Java UDR with embedded SQL. Our topics will include:

• Software Requirements
• Server Configuration
• Development Environment
• Runtime Environment
• Embedded SQL Syntax
• Compiling Embedded SQL Java Programs

Software Requirements

1. To develop Java UDRs that employ embedded SQL, the first thing you'll need is version 9.21 or later of Informix Foundation. Foundation consists of the Informix database engine, plus a suite of supporting software that includes "Krakatoa", which permits Java routines to run inside the server. Note that 9.20 and earlier versions will not support embedded SQL.
2. Next, you'll need the Informix JDBC Driver, version 2.1+ . The JDBC Driver can be downloaded free from the Informix download web site. Versions of the JDBC Driver prior to 2.1 will not support embedded SQL.

Server Configuration

Your Informix server configuration file (typically, $INFORMIXDIR/etc/onconfig on Unix, or %INFORMIXDIR%\etc\ONCONFIG.<servername> on NT) includes a number of parameters for setting the server's environment to support Krakatoa (a. k. a., "Java-in-the-server"). Most are pre-set in the default configuration file, and most of the information needed to set the rest can be found in the release notes for your platform.

One setting, which is specific to using embedded SQL in Java UDRs, may not be documented in your version of the server: JVPCLASSPATH must include the ifxsqlj.jar (or ifxsqlj-g.jar) file in your JDBC installation, and it must also include the full path to the directory where the .class file for your Java UDR will reside.

On NT, your Krakatoa-related ONCONFIG settings should look something like this:

VPCLASS      jvp,num=1                       # Number of JVPs to start with
            JVPJAVAHOME  D:\informix\extend\krakatoa\jre # JDK installation root directory
            JVPHOME      D:\informix\extend\krakatoa     # Krakatoa installation directory
            JVPLOGFILE   D:\informix\extend\krakatoa\jvp.log    # VP log file
            JVPPROPFILE  D:\informix\extend\krakatoa\.jvpprops  # JVP property file
            JDKVERSION   1.2                  # JDK version supported by this server
            JVMTHREAD    native               # Java VM thread type (green or native)
            # The path to the JRE libraries relative to JVPJAVAHOME
            JVPJAVALIB   \bin\
            # The JRE libraries to use for the Java VM
            JVPJAVAVM    hpi;jvm;java;net;math;zip;jpeg
            # Classpath to use upon Java VM start-up (use _g version for debugging)
            # IMPORTANT: In this sample, the line is broken to fit on a page. In a live
            # ONCONFIG file, your JVPCLASSPATH entry MUST be a single, unbroken line.
            JVPCLASSPATH d:/informix/extend/krakatoa/jdbc.jar;d:/informix/extend/krakatoa/
            krakatoa.jar;d:/java/jdbc211jc1/lib/ifxsqlj.jar;d:/informix/extend/SQLjDemo.1.0

Note that in this example, the Informix installation directory is D:\informix.

Most of the values here were inserted automatically when Foundation was installed. However, it's worth reiterating a couple of comments about JVPCLASSPATH:

• In any installation where Krakatoa is enabled, JVPCLASSPATH will include the path to the jdbc.jar and krakatoa.jar (or their debugging equivalents) in $INFORMIXDIR/extend/krakatoa. If you are going to use embedded SQL, however, you must add the full path to the ifxsqlj.jar file in your JDBC installation.
• SQLj standards call for your Java routines to be bundled in .jar files, and for those .jar files to be stored in the database by means of the install_jar() SQL routine. The corresponding SQL routine is then mapped to the stored .jar file.
  
  For any Java UDR, an alternative to this approach is to map your SQL routine directly to the .class file for your Java implementation, and enable the server to find your .class file by naming its directory in your server's JVPCLASSPATH variable.
  
  At the time of this writing, Java UDRs that include embedded SQL must use the latter method.
• JVPCLASSPATH is (at the time of this writing) limited to 256 characters. If you include a large number of directories where .class files will be stored, then you may overrun this limit, and your UDR will fail at runtime.
  
  An alternative syntax allows you to specify JVPCLASSPATH as the name of a file, for instance:
  
  JVPCLASSPATH file:d:/informix/extend/krakatoa/classpath.txt
  
  The content of classpath.txt takes exactly the same format as the text in your server configuration file would: full pathnames separated by semicolons on NT, or colons on Unix, all on a single line. In this case, however, the length of the line is not limited to 256 characters.

Development Environment

Developing a Java UDR that uses embedded SQL is not much different from developing any other Java UDR:

• You'll need access to JDK 1.2.x (1.2.x is compatible with the Java Runtime Environment (JRE) that is included with Krakatoa), so you'll need to set your PATH environment variable to include the "bin" directory of your JDK installation.
• You'll want to have access to your Informix installation, so that you can test your UDR-in-progress. This means having INFORMIXDIR set to point to the root of your Informix installation, and including $INFORMIXDIR/bin in your PATH.

However, there's one additional requirement:

• Set CLASSPATH to include each of the .jar files in the lib directory of your JDBC installation. You can use either the debug versions, whose names end with "-g.jar", or the optimized versions.

Runtime Environment

Other than configuring your server as described above, there are no special runtime requirements for using Java UDRs with embedded SQL.

Embedded SQL Syntax

This paper can present only the barest minimum of information about embedded SQL syntax, but it does seem appropriate to take a quick look at it here. Much more information about SQLj syntax can be found in the demo programs included with the Informix JDBC Driver distribution. Remember, however, when reviewing those demos, that they are client applications, and are not server-side Java routines.

In this section, we'll look at:

• Filenames
• SQLj import files
• Establishing a database connection
• Embedded SQL syntax

Filenames

As with other Java programs, the names of those using embedded SQL are case sensitive. The precompiler will generate a .java file with the same name prefix, and the compiler will generate a .class file with the same name prefix.

The filename extension used for Java embedded SQL programs is ".sqlj".

SQLj import files

Your "import" list must include:

1. java.sql.* (Since you will be using SQL to access a database.)
2. com.informix.jdbc.* (The precompiler translates your SQL statements into JDBC calls.)
3. sqlj.runtime.* (To provide runtime support for sqlj.)
4. sqlj.runtime.ref.DefaultContext (For access to the underlying routines that establish your database connection.)

Establishing a Database Connection

In a client application using Java embedded SQL, there are a lot of options for establishing database connections, including multiple concurrent connections to several databases or server installations. To support this flexibility, SQLj uses the concept of a "context" to extend the simpler idea of a "connection", and it provides a whole range of classes and methods for managing "contexts".

Since a UDR runs inside the server, it only needs a "DefaultContext". You get a DefaultContext from a database connection that is obtained by using the JDBC DriverManager.getConnection() call, and supplying a "direct connection" database URL:

public static Connection conn = null;
            public static String DRIVER = "com.informix.jdbc.IfxDriver";
            public static String DBURL = "jdbc:informix-direct";
            ...
            public static Connection newConnection() throws SQLException {
            // Load the JDBC driver
            try {
            Class.forName( DRIVER );
            }
            ...
            // Get a database connection
            try {
            conn = DriverManager.getConnection (DBURL);
            }
            ...
            // Use the connection to get a connection context
            DefaultContext ctx = DefaultContext.getDefaultContext();
            try {
            ctx = new DefaultContext(newConnection());
            }
            ...
            }   

Please refer to the downloadable demo for a complete example.

Embedded SQL syntax

Once you have established a database connection, and obtained a DefaultContext, accessing information stored in a database is simple. You'll need a program variable of the appropriate type to receive the data, and an SQL statement to retrieve it.

If you have a table like this:

          CREATE TABLE excuses (id integer, words lvarchar);
            INSERT INTO excuses VALUES (1, "Dog ate my homework");
            INSERT INTO excuses VALUES (2, "Missed the bus");
            INSERT INTO excuses VALUES (3, "I Forgot!"); 

Then you could retrieve the text of an excuse into a program variable like this:

          ...
            String excuse = new String();
            #sql {
            SELECT words
            INTO :excuse
            FROM excuses
            WHERE id = 3
            }; 

Note that the program variable "excuse" is preceeded by a colon when used in the SQL statement. It's important to note, too, the placement of the semicolon that terminates the SQL statement: It follows the closing bracket.

Complete information about mapping Informix data types to JDBC data types can be found in the Informix JDBC Driver Programmer's Guide, which is included with your JDBC distribution.

Compiling Embedded SQL Java Programs

Compiling a Java UDR containing embedded SQL is simple, and is identical to compiling a client application that contains embedded SQL. If your source file is "Foo.sqlj", then compile it like this:

java ifxsqlj Foo.sqlj

ifxsqlj will precompile Foo.sqlj into Foo.java, then will call javac to compile Foo.java into Foo.class.

Along the way, two additional files are generated:

• Foo_SJProfile0.ser
• Foo_SJProfileKeys.class

These files contain hooks for vendor-specific customizations, as provided by the SQLj standards. In this way, vendors can optimize the implementation of embedded SQL statements for their own database products, and still retain a vendor-neutral, portable compiled object.

Tip: In the current release of the SQLj package, ifxsqlj may hang and produce no feedback at all if it encounters certain syntax errors in your code. If this happens, break out with ^C, then try to compile the generated .java file manually: javac Foo.java . The javac compiler will flag the errors in the .java file, and you can trace them back to your .sqlj code.

All three of the files generated by a successful compile will need to be made available to the server at runtime, by placing them in a directory named in your server's JVPCLASSPATH entry.

The hard part is done now: You've written your UDR and successfully compiled it. Now all you need to do is "register it to a database", which amounts to mapping it to an SQL routine.

Let's assume we have a "bladelet" with a single routine, "Foo()". Let's also assume we're going to follow the convention that most DataBlades do, and store our "Foo" files in "$INFORMIXDIR/extend/Foo". We'll call the script that maps our "Foo" Java routine to an SQL "Foo" routine -- that "registers the UDR to the database" -- "register.sql".

Following this convention, $INFORMIXDIR/extend/Foo will contain:

• register.sql
• Foo.class
• Foo_SJProfileKeys.class
• Foo_SJProfile0.ser

Your register.sql script looks like this:

create procedure testmain()
            external name 'Foo.Foo()'
            language java; 

The first "Foo" in the "external name" identifies the Java class, and the second "Foo" refers to the method name within that class.

How does the server find the class? It looks in each of the directories named in its JVPCLASSPATH variable until it finds the one with the name "Foo.class". If we've remembered to include d:\informix\extend\Foo in our JVPCLASSPATH entry, the server will find it.

IBM, DB2, Informix, and WebSphere are trademarks or registered trademarks of IBM Corporation in the United States, other countries, or both.

Windows and Windows NT are registered trademarks of Microsoft Corporation in the United States, other countries, or both.

Java and all Java-based trademarks and logos are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.

Other company, product, and service names may be trademarks or service marks of others.