IBM Informix Embedded SQLJ Quick Reference

(Embedded SQL for Java)

Release Version: 1.01.JC2

Release Date: December 2001

  1. Installation
  2. User Guide

  3. 2.1 Requirements
    2.2 Overview
    2.3 Construct an SQLJ program
    2.4 Build and execute an SQLJ application
    2.5 Connection Context
    2.6 Tools and Utilities
    2.7 Property File
    2.8 On-line Checking
    2.9 Advanced Features
  4. Informix Extensions
  5. Limitations and Restrictions
  6. Demo Programs
  7. References

1. Installation

The IBM Informix Embedded SQLJ product is bundled with the IBM Informix JDBC product and distributed in a Java InstallShield class file  setup.class

You can download the IBM Informix JDBC/SQLJ product from Informix download site. Follow the installation instructions and execute the setup.class to install the product.

    > java setup

After installation, the IBM Informix Embedded SQLJ has the following components, among others of the JDBC/SQLJ bundle.
( IFXJLOCATION refers to the directory of the installed products)

  • IFXJLOCATION/lib/
  • ifxsqlj-g.jar: the IBM Informix SQLJ product, debug version
  • ifxsqlj.jar: the IBM Informix SQLJ product, optimized version
  • ifxtools-g.jar: the IBM Informix SQLJ tools and utilities, debug version
  • ifxtools.jar: the IBM Informix SQLJ tools and utilities, optimized version
  • IFXJLOCATION/doc/release/sqlj: documentation
  • IFXJLOCATION/demo/sqlj : demo programs
  •          After installation, add these 2 jar files to your CLASSPATH
  • IFXJLOCATION/lib/ifxsqlj-g.jar  (or ifxsqlj.jar)
  • IFXJLOCATION/lib/ifxtools-g.jar (or ifxtools.jar)
  •  {back to top}

    2. User Guide

            This is a simplified user guide for basic operations.
            For detailed user guide, please refer to IBM Informix Embedded SQLJ User's Guide.
     

     2.1 Requirements

                      To use SQLJ you need the followings,

     2.2 Overview

    SQLJ is a language that embeds SQL statements in Java programs. An SQLJ source is a Java program with portions written in SQL. The SQLJ translator replaces embedded SQL statements with JDBC API calls. The underlying JDBC driver provides connectivity to the database.
    IBM Informix Embedded SQLJ is based on the SQLJ Reference Implementation released by the SQLJ working group. Members of the working group include Compaq/Tandem, IBM, Oracle and Sybase.

      2.3 Construct an SQLJ program

                2.3.1 JDBC Driver and Database Connection
    Typically an application has a connection manager class that manages and provides default settings of JDBC driver, database connection, user id and password for run time connection. Use the ConnectionManager class in the  demo programs  as an example. Modify the followings to reflect your settings,
     
  • DRIVER: the JDBC driver
  • DBURL: the URL of the database
  • UID: the user ID for database account
  • PWD: the password for database account

  •     e.g.
        DRIVER = "com.informix.jdbc.IfxDriver";
        DBURL  = "jdbc:host:port:informixserver=falcon";
        UID  = "userid" ;
        PWD  = "xxxxx" ;

    Note: in DBURL, host is your machine host name or IP address, port is the port number. For more details please refer to the user guide of IBM Informix JDBC driver.
     

      2.3.2 Embedding SQL statements (SQLJ clauses)
    In your Java host programs, embedded SQL statements can appear anywhere Java statements can legally appear. An embedded SQL statement is in the format

    #sql { SQL statement text };

    An embedded statement will be converted into a Java statement block. Use embedded statements in your program logic to accomplish database operations like table creations, data insertions and queries.

                2.3.3 Using Host Variables
    Use host variables to convey data between the Java host program and embedded SQL statements. Any Java variables can be used as host variables, subject to Java accessibility rules. Host variables, prefixed by a colon (:),  can appear in embedded SQL statements.

    e.g.
    int num = 100;
    #sql { DELETE FROM TAB1 WHERE ID > :num };

                2.3.4 Using Result Set Iterators
    A Result Set Iterator, corresponding to cursor of Embedded SQL, is a Java object that receives query output rows. It holds a JDBC Result Set, provides iteration functionality and accessor methods to columns.
    2 mechanisms of matching iterator columns and query columns are supported: named binding and positional binding.

    NOTE: Iterator columns, being Java identifiers, are case sensitive.

    2.3.4.1 Named Binding

    1. Declare a SQLJ named iterator class

    2. At Java class definition level declare an iterator class with column names and their Java types specified.

      e.g.
      #sql public iterator NameIter(String name, int salary);
       

    3. Use the iterator class name to declare an iterator object

    4. e.g.
      NameIter niter1;
       
    5. Populate the iterator object in a query

    6. e.g.
      #sql niter1 = { SELECT NAME, SALARY FROM TAB2 };
       
    7. Loop through the iterator object, use accessor methods to obtain data from columns

    8. e.g.
      String name;
      int salary;
      while( niter1.next() )
          {
              name= niter1.name();
              salary= niter1.salary();
          }

      Method next() advances the iterator object to the successive row. It returns false if the successive row does not exist.
      Accessor methods are generated by the SQLJ translator when coverting the iterator declaration into a Java class.

    2.3.4.2 Positional Binding
    1. Declare a SQLJ positional iterator class

    2. At Java class definition level declare an iterator class with only column types but not column names.

      e.g.
      #sql public iterator PosIter(String , int) ;
       

    3. Use the iterator class name to declare an iterator object

    4. e.g.
      PosIter piter1;
       
    5. Populate the iterator object in a query

    6. e.g.
      #sql piter1 = { SELECT NAME, SALARY FROM TAB2 };
       
    7. Loop through the iterator object, use FETCH to obtain data from columns

    8.  

       
       
       
       
       

      e.g.
      String name;
      int salary;

      while (true)
          {
              # sql { FETCH :piter1 INTO :name, :salary };
              if (piter1.endFetch())
                  break;
          }

      The SQLJ translator positionally matches and assigns columns into the variables in the INTO list.
      Method endFetch()  is initially false. After a FETCH it returns true if row exists, false otherwise.

                2.3.5 Calling Stored Procedures and Functions
    Embedded SQLJ statements can call database stored procedures or functions in the following manners:
    • Calling stored procedures
      #sql { CALL proc(:arg1, :arg2) };

      Note: IBM Informix JDBC driver does not support OUT or INOUT parameters.
       

    • Calling stored functions

    •  

       
       
       
       
       

      Assume function func() returns an integer, the following assigns the value to the Java variable x.

      int x;
      #sql { EXECUTE FUNCTION ( func(arg1) ) INTO :x };

    2.4 Build and execute an SQLJ application

     2.5 Connection Context

  • Default Connection Context

  • A Connection Context manages a database connection. SQLJ provides default Connection Context, if not specified, for SQLJ clauses in an application program. Default Connection Context is set, typically in the connection manager class, by calling sqlj.runtime.ref.DefaultContext.setDefaultContext().
     
  • Explicit Connection Context

  • Users can declare Connection Contexts and designate a connection for an SQLJ clause.
    e.g.
    #sql context Employee;
    Employee conn1;
    /* ... connection setup for conn1 ... */
    #sql [conn1] { ...SQL text... };

    2.6 Tools and Utilities

    The IBM Informix SQLJ tools and utilities are available in ifxtools.jar
    Note: This tool package and usage is different from that provided by the Reference Implementation. See Informix Extensions:Tools and Utilities.
    • ifxsqlj

    • the SQLJ translator
      Translate an SQLJ source into Java code.
      Usage:      java ifxsqlj [options] xx.sqlj
          Please refer to the IBM Informix Embedded SQLJ User's Guide for the list of options.

      The order in which the translator looks for options:
      (the latter setting overrides the former)

      1. default values (provided by SQLJ), if any
      2. default property files
      3. command line options
      4. if property files specified in command line by -prop option, process them as if items in the property files were specified inline
         
    • ifxsqljc

    • the SQLJ translator plus the Java compiler
      Usage:     java ifxsqljc [options] xx.sqlj
      Please refer to the  IBM Informix Embedded SQLJ User's Guide for the list of options.
       
    • ifxprofp

    • the Profile printer
      Display the contents of a profile.
      Usage:     java ifxprofp xx_SJProfile0.ser

    2.7 Property File

    2.8 On-line Checking

    If database connection is provided at SQLJ translation time, SQLJ performs on-line semantics and schema checking against the underlying database.
    This checking is turned on by giving -user option in the translation command line or property files.
        e.g.
            java ifxsqlj -user=usrid xx.sqlj
            (where usrid is the user id for the connection)
    Other connection information (driver, database url, password) must also be given either in the command line or property files.

    2.9 Advanced Features

    The following is a brief introduction to useful advanced features. Please refer to the User Guide for more information.
  • Multiple Connection Contexts

  • An SQLJ program may contain multiple Connection Contexts and work with multiple data sources at the same time.
     
  • Execution Context

  • Users can declare Execution Contexts for execution control and execution status inquiry. The followings are the attributes managed by Execution Context,
    • Maxrows
    • MaxFieldSize
    • QueryTimeout
    • UpdateCount
    • SQLWarnings
  • Multi-threading

  • Connection Contexts and be safely shared between threads, subject to the synchronization limitations imposed by the underlying JDBC driver and DBMS.
     
  • Interoperability with JDBC

  •  SQLJ supports static SQL, meaning all SQL operation information are available at translation time. If any piece of SQL can't be obtained until runtime, use JDBC for dynamic perations. JDBC connection and SQLJ Connection Context are interoperable. JDBC ResultSet and SQLJ Iterator are interoperable. The demo programs Demo05.sqlj and Demo06.sqlj demostrate these two interoperations.
     {back to top}
     

    3. Informix Extensions

  • The SQLJ product

  •  

     
     
     
     
     

    This version contains no Informix extensions.
     

  • Tools and Utilities

  •  

     
     
     
     
     

    The Reference Implementation provides 2 formats for each tools: shell scripts for Unix and C executables for Windows. To eliminate platform dependency, IBM Informix SQLJ provides wrapper tools (ifxsqlj, ifxsqljc and ifxprofp in ifxtools.jar) so that the same commands can be used across platforms.

    {back to top}
     
     

    4. Limitations and Restrictions

        none

    {back to top}
     
     

    5. Demo Programs

    You can find the demo programs in the demo directory of your product installation $IFXJLOCATION/demo/sqlj/.

    Follow the instructions in $IFXJLOCATION/demo/sqlj/README to run demo programs.

    {back to top}

     

    6. References

    {back to top}