To use SQLJ you need the followings,IBM Informix Embedded SQLJ Quick Reference
(Embedded SQL for Java)
Release Version: 1.01.JC2
Release Date: December 2001
- Installation
- 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- Informix Extensions
- Limitations and Restrictions
- Demo Programs
- 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.classYou 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)After installation, add these 2 jar files to your CLASSPATHIFXJLOCATION/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 {back to top}IFXJLOCATION/lib/ifxsqlj-g.jar (or ifxsqlj.jar) IFXJLOCATION/lib/ifxtools-g.jar (or ifxtools.jar) 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
- 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 Connection2.3.3 Using Host VariablesTypically 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,2.3.2 Embedding SQL statements (SQLJ clauses)
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.
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.4 Using Result Set IteratorsUse 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.5 Calling Stored Procedures and FunctionsA 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
2.3.4.2 Positional Binding
- 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);
- Use the iterator class name to declare an iterator object
e.g.
NameIter niter1;
- Populate the iterator object in a query
e.g.
#sql niter1 = { SELECT NAME, SALARY FROM TAB2 };
- 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.
- 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) ;
- Use the iterator class name to declare an iterator object
e.g.
PosIter piter1;
- Populate the iterator object in a query
e.g.
#sql piter1 = { SELECT NAME, SALARY FROM TAB2 };
- 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.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.javaThe following files are generated
xx.class xx_SJProfileKeys.class IteratorName.classTranslation 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 xx2.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)
- default values (provided by SQLJ), if any
- default property files
- command line options
- 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.ser2.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.sqljIf 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.{back to top}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.
3. Informix Extensions
{back to top}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.
4. Limitations and Restrictions
none5. Demo Programs
You can find the demo programs in the demo directory of your product installation $IFXJLOCATION/demo/sqlj/.{back to top}Follow the instructions in $IFXJLOCATION/demo/sqlj/README to run demo programs.
6. References
{back to top}