IBM Informix Embedded SQLJ Quick Reference

> > > ## IBM Informix Embedded SQLJ Quick Reference

> > > ### (Embedded SQL for Java)

> > > ### Release Version: 1.01.JC2

> > > ### Release Date: December 2001

> > 1. Installation
> > 2. User Guide
> > 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
> > 3. Informix Extensions
> > 4. Limitations and Restrictions
> > 5. Demo Programs
> > 6. 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,

> > - A Java environment compatible with JDK 1.2 or higher.
> > - The SQLJ product (ifxsqlj.jar) and tools (ifxtools.jar)
> > - A JDBC driver implementing package java.sql.
> > - A database system accessible via the JDBC driver.

> > #### 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
> > > > 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);

> > > > 2. Use the iterator class name to declare an iterator object
> > > > e.g.
> > > > NameIter niter1;
> > > > 3. Populate the iterator object in a query
> > > > e.g.
> > > > #sql niter1 = { SELECT NAME, SALARY FROM TAB2 };
> > > > 4. Loop through the iterator object, use accessor methods to obtain data from columns
> > > > 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
> > > > 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) ;

> > > > 2. Use the iterator class name to declare an iterator object
> > > > e.g.
> > > > PosIter piter1;
> > > > 3. Populate the iterator object in a query
> > > > e.g.
> > > > #sql piter1 = { SELECT NAME, SALARY FROM TAB2 };
> > > > 4. Loop through the iterator object, use FETCH to obtain data from columns

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

> > Translate and compile the application before execution.
> > - Translate the SQLJ source

> > The SQLJ translator converts an SQLJ source file (xx.sqlj), which contains Java source code and embedded SQL code, into a java file (xx.java), which contains the original Java code and converted JDBC API (wrapper) calls, along with some serialized Profile files (xx_SJProflie?.ser), which contain SQLJ statement information.
> > e.g.
> > > ls
> > xx.sqlj
> > > java ifxsqlj xx.sqlj
> > > ls
> > xx.java xx.sqlj xx_SJProfile0.ser

> > - Compile the application

> > Use your Java compiler to compile the xx.java file into xx.class file. A Profile Key class, which contains Profile index information, and Iterator classes, if any, will also be generated.
> > e.g.
> > > javac xx.java

> > The following files are generated
> > xx.class xx_SJProfileKeys.class IteratorName.class

> > Translation and compilation can be done in one step using the utility "ifxsqljc".
> > e.g.
> > > java ifxsqljc xx.sqlj

> > - Run the application just like any other Java application.
> > e.g.
> > > java xx

> > #### 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

> > Property files can be used to supply options to the SQLJ translator. Refer to the demo programs for an example of using property file.

> > Property file can be specified in the translator command line using the -props option.
> > e.g.
> > java ifxsqlj -props=mysqlj.properites xx.sqlj

> > If no property file is specified in the command line, SQLJ looks for default property files with the name sqlj.properties.
> > The order in which the translator looks for default property files:
> > (the latter setting overrides the former)
> > - the Java home directory, if exists
> > - the user's home directory, if exists
> > - the current directory

> > #### 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

> > - The SQLJ website <www.sqlj.org>
> > {back to top}