Introduction

Application programmers find that, in many situations, embedded SQL offers significant advantages in programs that access information in database tables. DataBlade developers have asked: "Can embedded SQL be used in a Java UDR?". The answer is, "Yes!", and this paper explains how.

What is Embedded SQL?

Embedded SQL, in one form or another, is familiar to most application programmers who use relational databases. It was commonly used by COBOL programmers when RDBMSs began to replace hierarchical and network databases during the mid-1980s, and it has been used for many years with Fortran, C, and other "Third Generation" languages.

Embedded SQL is just what it sounds like: SQL statements embedded directly within code written in another programming language. It provides very straight forward database access for application programs.

In the Java language, Embedded SQL was defined as part of a collection of standards developed by the SQLj organization, and now published as an ANSI standard. Embedded SQL offers a streamlined alternative to JDBC for programs that use static SQL statements in an application, and it also provides compile-time type checking, which can reduce runtime errors. Note that programs requiring dynamic SQL will need to use JDBC.

Beginning with release 9.21 of the Informix database engine, Embedded SQL can be used in both UDRs, and client applications.

Back to top

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.

You'll need the latest version of the Java compiler, JDK 1.2.2 or later. This can be downloaded free from Sun's Java web site.

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

Back to top

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.)

Back to top

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.

Deploying a Java UDR That Uses Embedded SQL

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.

Resources

• "Creating UDRs in Java": A PDF copy of this manual can be downloaded from the Documentation section on the Informix home page.
  
  
• "Informix JDBC Driver Programmer's Guide": A PDF copy of this manual is included in the JDBC distribution.
  
  
• Java UDRs: A First Look at Java-in-the-Server covers the basics of configuring your server installation to support Java, as well as implementing a simple Java User Defined Routine.
  
  

About the author

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• What is Embedded SQL?
• Developing a UDR with Embedded SQL
• Filenames
• Establishing a Database Connection
• Resources
• About the author
• Comments

Next steps from IBM

Spring, JRuby, and Ajax development is easier with WebSphere Application Server - a smart Java 5 and J2EE Web services-based application server.

---

• Try: The no-charge WebSphere Application Server Community Edition is a pre-integrated, lightweight Java 5 application server built on Apache Tomcat and other best-of-breed open source software such as OpenEJB, Apache Axis, and Apache Derby.
• Article: The article leverage the Spring Framework and the WebSphere Application Server to improve your J2EE project productivity.
• Tutorial: See how the free WebSphere Application Server and XML can improve the efficiency of your JRuby on Rails and Ajax development.
• Buy: WebSphere Application Server - Express

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers