http://download-west.racle.com/docs/cd/b10501_01/java.920/A96655/GetStart.htm
Getting start
This Chapter Guides You Through The Basics of Testing Your Oracle Sqlj Installation and Configuration And Running A Simple Application.
Note That if you are using an oracle Database and Oracle JDBC Driver, You Should Also Verify Your JDBC Installation Accenting To The Oracle9i JDBC Developer's Guide and Reference.
This Chapter Discusses The Following Topics:
Assumptions and Requirements Checking The Installation and Configuration Testing The Setup
Assumptions and Requirements
THIS Section Discusses Basic Assumptions About your environment and requests of Your System So That You Can Run Oracle SQLJ, COVERING TOPICS:
Assumptions about Your Environment Requirements for Using Oracle SQLJ ORACLE SQLJ ENVIRONMENT: Key Scenarios And Liidelines Environment Issues and Limitations Oracle Sqlj Backward Compatibility
Assumptions About your environment
The Following Assumtions Are Made About The System On Which You Will Be Running Oracle Sqlj.
You have a standard Java environment that is operational on your system. This would typically be using a Sun Microsystems JDK, but other implementations of Java will work. Make sure you can run Java (typically java) and your Java compiler (typically javac). To translate and run Oracle SQLJ applications on a Sun JDK, you must use a JDK 1.2.x (or higher) or JDK 1.1.x version, with an appropriate JDBC driver. There are Oracle JDBC Thin and OCI driver versions that work with any Of these JDK Scenarios. for more information, see "Oracle Sqlj Environment: Key Scenarios and Guidelines".
Note: A Java runtime environment (JRE), such as the one installed with Oracle9i, is not by itself sufficient for translating SQLJ programs A JRE is sufficient, however, for running SQLJ programs that have already been translated and compiled.You can already. run JDBC applications in your environment. If you are using an Oracle database and Oracle JDBC driver, then you should complete the steps in Chapter 2, "Getting Started", of the Oracle9i JDBC Developer's Guide and Reference.You can also refer to Chapter 1 , "Overview", of this document for information about the oracle jdbc drivers and how to decide Which is appropriate for your situation.
Notes: if you are useful a non-oracle JDBC Driver, You Must Do The Following:
Modify connect.properties, as discussed in "Set Up the Runtime Connection". Modify the demo applications, as discussed in "Driver Selection and Registration for Runtime", so that your driver is registered before the call to the Oracle.connect () method .
Requirements for Using Oracle SQLJ
The Following Are Required To Use Oracle SQLJ:
. A JDBC driver implementing the standard java.sql JDBC interfaces from Sun Microsystems Oracle SQLJ works with any standard JDBC driver a database system that is accessible using your JDBC driver class files for the SQLJ translator Translator-related classes are available in the file: [ Oracle_Home] /sqlj/lib/translator.jar (or .zip) class files for the SQLJ runtime Several SQLJ runtime versions are available. You must select a runtime version that is compatible with your Java environment and JDBC driver (these are all in [ Oracle_Home] / SQLJ / LIB).
runtime12.jar (or .zip) - for use with Oracle9i JDBC drivers under JDK 1.2.x or higher, providing full ISO SQLJ functionality runtime12ee.jar (or .zip) - for use with Oracle9i JDBC drivers in a J2EE environment ( Using jdk 1.2.x or higher, providing full iso sqlj functionality runtime11.jar (or .zip) - for use with oracle9i jdbc drivers under jdk 1.1.x runtime.jar (or .zip) - for use with ilder oracle JDBC drivers and any JDK environment (intended for Oracle JDBC release 8.1.7 and prior) runtime-nonoracle.jar (or .zip) - for use with non-Oracle JDBC drivers and any JDK environmentNotes: Also be aware of the following:
In SQLJ release 8.1.6 and earlier, there was only one runtime library, and the runtime JAR / ZIP file was a subset of the translator JAR / ZIP file. In Oracle9i this is no longer the case. You must now specify both a runtime file and the translator file in your classpath. The runtime-nonoracle library provides portability across different Java and JDBC environments, but does not support Oracle-specific functionality. The runtime library provides flexibility across different Java and Oracle JDBC environments, but does not support all ISO SQLJ functionality. for ISO SQLJ-compliant support for JDBC 2.0 types such as java.sql.Ref, Clob, Blob, Struct, and SQLData, use the runtime12 or runtime12ee library with JDK 1.2 or J2EE and an Oracle9i JDBC driver. If you will be running only SQLJ applications that have already been translated, compiled, and customized, you will not need the translator JAR / ZIP file. The translator and runtime JAR files are compressed. The ZIP files are uncompressed, howeve r, for maximum portability.
Oracle Sqlj Environment: Key Scenarios and Guidelines
To ensure you have a fully working environment, you must consider several aspects of your environment -. SQLJ and its code generation mode, JDBC, and the JDK This section first discusses the two main Oracle scenarios of supported combinations, and then discusses some important General Guides.Also See "Environment Issues and Limitations" for related information.
NOTE: CODE generation is determined by the oracle sqlj -codegen option. See "Code Generation" for more information.
Scenario 1: Oracle-Specific Code
THIS INTEE CASE, The SQLJ Generic Runtime Library. NOT I.
SQLJ code generation: -codegen = Oracle (Default) SQLJ Library During Translation:
Oracle SQLJ Runtime11 Library with JDK 1.1 Al:
Oracle SQLJ runtime12 or runtime12ee library with JDK 1.2 or higher JDBC and JDK: Oracle JDBC version 9.0.1 or higher with JDK 1.1 or higher If you might be running against either the 9.0.1 or 9.2 JDBC driver, translate against 9.0.1. In General, Compile Against the Oldest Driver You Might Use. Sqlj Library During Runtime: Same Library As for Translation
Scenario 2: Iso Standard Code
...............
SQLJ code generation: -codegen = ISO SQLJ LIBRARY DURING TRANSLATION:
SQLJ Runtime11 Library with JDK 1.1 Al:
Sqlj runtime12 or runtime12ee library with jdk 1.2 or higher or, for oracle JDBC Version 8.1.7 and prior:
SQLJ Generic Runtime Library with Any JDK 1.1 or Higher JDBC and JDK: Any Oracle JDBC Version with Any JDK 1.1 or Higher Sqlj Library During Runtime: Same Library As for Translation
Environment Scenarios: Key GuidelinesRegarding your Environment for Running Oracle SQLJ, Be aware of the Following Important Guidelines and considances:
In General, Use the Same Versions of the Sqlj Library, JDBC Library, And JDK in Translasing and Compiling All Components of Your Application.ALWAYS BE AWARE OF THE FOLLOWING CROSILIBILITY CONSIDERATIONS:
If you want to be able to run the same compiled code in either a JDK 1.1 environment or a JDK 1.2 or higher environment, then translate against the runtime11 library under JDK 1.1. This allows the option of using the runtime12 or runtime12ee library under JDK 1.2 at runtime. If you translate against JDK 1.2, several JDBC 2.0 APIs that are not supported under JDK 1.1 are compiled into your class files. If you want to be able to run against either a version 9.0.1 or a version 9.2 Oracle JDBC driver , then translate against a 9.0.1 driver. This allows you to use either driver version at runtime. Generated code is optimized toward the JDBC driver in the classpath during translation. For maximal cross-compatibility, avoid using declared connection context classes. If you use JPublisher, use the default settings for the -compatible and -context options. See the Oracle9i JPublisher User's Guide for information about these options. You can also consider using the runtime library for cross-com PATIBILITY, BUT this Library Has Disadvantages (Such as Not Supporting The Oracle9i Oracle.jdbc.Oraclexxxxxxxxx Interfaces, Which Causes Problems In The Middle Tier).
Environment Issues and Limitations
This section discusses a key environmental issue - JDK migration regarding type maps - and lists resulting limitations and some additional environment issues and limitations These are among the considerations in planning the typical scenarios outlined in "Oracle SQLJ Environment: Key Scenarios and Guidelines". .Notes:
Where the generic runtime library is discussed, an Oracle JDBC environment is still assumed (version 8.1.7 or prior). For non-Oracle JDBC environments, substitute the runtime-nonoracle library. The runtime and runtime-nonoracle libraries are intended mainly for backward Compatibility. They do not support Oracle-Specific Features.
JDK MIGRATION ISSUES REGARDING TYPE MAPS
The Type for JDBC TYPE MAPS CHANGED BETWEEN JDK 1.1.x and jdk 1.2.x, from java.util.dictionary to java.util.map.
The getTypeMap () method of all SQLJ connection context classes returns a type map instance. The Java type system requires that an implemented method must return exactly the type specified in an underlying interface (in this case, the JDBC Connection interface). Consider the following Method Signature:
Java.util.hashtable gettypemap () {...}
This Would Seem to Implement Both The JDK 1.1 and JDK 1.2 Specifications, Because Hashtable Extends Dictionary and Implements Map; However, IT IS Not Acceptable to the Java Type System.
This Incompatibility Between The JDK 1.1 and JDK 1.2 Interfaces Has The Following Consequences IF Your Sqlj Code Declares Connection Context Types:
If you compile under JDK 1.1.x, including when you translate against the SQLJ runtime11 library, or when you compile under any JDK when translating against the SQLJ generic runtime library, the generated code will return Dictionary instances. The code should run under both JDK 1.1.x and JDK 1.2.x or higher. (Under JDK 1.2, the SQLJ runtime uses Java Reflection as necessary.) If you compile under JDK 1.2.x or higher, including when you translate against the SQLJ runtime12 or runtime12ee library, you Will Not Be Able To Run Under JDK 1.1.x (Because Java.util.map, Among Other Things, Was Not Defeed in JDK 1.1.x). if you translate .sqlj Files Under JDK 1.1.x, The Generated .java Files will not compile under JDK 1.2.x or higher. (This is relevant if you run SQLJ with the -compile = false setting, in order to translate and compile separately.) Likewise, if you translate under JDK 1.2.x or higher, the Generated .java Files Will Not Compile Under JDK 1.1.x.note: if You Use Jpublisher, The Defa ult setting is to use DefaultContext instances for connections, in which case the preceding issues do not arise. But with JPublisher nondefault -context option settings, or with a -compatible option setting of both8i or 8i, JPublisher-generated .sqlj source code declares connection Context Types. See The Oracle9i JPublisher User's Guide for More Information.
Other Environment Issues and Limitations
.
With the default -codegen = oracle setting, you can not use the generic runtime library. Use the runtime11 library (for JDK 1.1) or the runtime12 or runtime12ee library (for JDK 1.2 or higher). For Oracle JDBC version 8.1.7 or prior, you must use -codegen = iso and the generic runtime library (not runtime11, runtime12, or runtime12ee). For any SQLJ application using declared connection context classes, all modules must be translated against JDK 1.1 (using the runtime or runtime11 library) or all modules must be translated against JDK 1.2 or higher (using the runtime12 or runtime12ee library). There can not be a mixture. This includes situations where you use JPublisher with a nondefault -context option setting. you can run against a JDK version that is at least as high as the version you translated against. If you translate under JDK 1.1.x, you can run the application under either JDK 1.1.x or JDK 1.2 or later. This assumes that you do not have any JDBC code that uses the oracle. JDBC2 PACKAG E, Which Oracle Sqlj Does Not Support. Oracle JDBC Used This package to support JDBC 2.0 Types under jdk 1.1.x. if you translate Under JDK 1.2, You Can Run The Application Under JDK 1.2 or Higher.Notes:
Oracle JDBC Releases 8.1.5 And Prior Do Not Support JDK 1.2.x. in Oracle9i, Oracle Sqlj And Oracle JDBC Do Not Support JDK 1.0.2. (Release 8.1.6 Was The Last Oracle JDBC Release To Support JDK 1.0.2, while Oracle SQLJ has never supported JDK 1.0.2.) This includes applets running in browsers that use JDK 1.0.2 except where special preparations have been made. (This chapter does not discuss applets. Refer to "running SQLJ in applets".)
Oracle Sqlj Backward Compatibility
Be aware of the following regarding Oracle SQLJ backward compatibility: Code generated with an earlier release of the SQLJ translator will continue to do the following (subject to cross-compatibility limitations discussed in "Environment Issues and Limitations"):
run against current runtime (.jar or .zip) libraries be compilable against current runtime (.jar or .zip) libraries Oracle-specific translator output (code generated with the default -codegen = oracle setting) must be created and executed using the runtime11 , Runtime12, or runtime12ee library. furthermore:
Such code will be executable under future releases of Oracle JDBC and SQLJ. Such code, however, will not be executable under previous releases of Oracle JDBC and the Oracle SQLJ runtime. In these circumstances, you will have to retranslate the code. Also remember that Oracle-Specific Code Is Not Portable. Iso Standard Generated Code (-codegen = ISO) Can Be Created and Executed Against An Earlier Oracle JDBC Release Use The Current Runtime (.jar or .zip) Library.
Note: Regarding Oracle JDBC backward compatibility to prior database releases, any given Oracle JDBC driver release is compatible with any Oracle database release from 7.3.4 up to the release number of the JDBC driver For more information see the Oracle9i JDBC Developer's Guide and Reference. .
Checking the installation and configuration and configuration
Once You have verified That The Above Assumptions and Requirements Are Satisfied, You Must Check Your Oracle Sqlj Installation.
Check for Installed Directories and FILES
Verify That The Following Directories Have Been Installed and Are Populate.
Directories for Oracle JDBC
IF you are using one of the oracle jdbc drivers, refer to the oracle9i jdbc developer's guide and reference for information about jdbc files That Should Be installed on your system.director for oracle sqlj
Installing The Oracle9i Java Environment Will include, Among Other Things, Installing a Sqlj Directory under Your [Oracle_Home] Directory. The Sqlj Directory Contains The Following Subdirectories:
DEMO (DEMO Applications, Including Some Reference In this chapter) DOC LIB (.jar or .zip files containing class files for sqlj)
In Addition, Directly Under [Oracle_Home] is The Following Directory, Containing Utilities for All Java Product Areas:
bin
CHECK That All these Directories have been created and populate, especially lib and bin.
The structure is similar if you download SQLJ from a Web site, such as the Oracle Technology Network http://otn.oracle.com address. The bin directory, with both SQLJ and JPublisher executable files, is directly under the sqlj directory.
Set the path and classpath
Make Sure Your Path and ClassPath Environment Variables Have The next nexttings for oracle SQLJ (AND Oracle JDBC if Applicable).
Path and classpath for oracle JDBC
IF you are useless one of the oracle jdbc drivers, you will need the oracle jdbc class jar / zip file what is appropriate for your environment.
JDK 1.1-Compatible Classes Are In classes111.jar or .zip; JDK 1.2 (or higher) compatible class 40.jar or .zip; jdk 1.4-compatible classes are in ojdbc14.jar or .zip. PRESUMING You Use a sun Microsystems JDK, make sure the appropriate JAR / ZIP file name is in your classpath setting. There may also be alternative JDBC driver libraries available, such as classes12_g.jar, which permits driver debugging information to be printed.For more information about libraries and required Path and classpath settings for oracle JDBC, Refer to the Oracle9i JDBC Developer's Guide and Reference.
Path and classpath for oracle sqlj
Set your path and classpath variable sqlj.
Path setting
TO BE ABLE TO Run The Sqlj Script (Which Invokes The Sqlj Translator) WITHOUT HAVING TO FULLY Specify ITS PATH, VERIFY That Your Path Environment Variable Has Been Updated to include the Following:
[Oracle_home] / bin
Use backward slash for windows. Replace [Oracle_Home] with your actual oracle home directory.
Classpath Setting
Update Your Classpath Environment Variable To include the capital Directory as well as the folload (Either .jar or .zip):
[Oracle_Home] /sqlj/lib/translator.jar
Use backward slashs for windows. Replace [Oracle_Home] with your actual
Oracle Home Directory.
In Adduion, You Must Include One of the Following Runtime Libraries in Your ClassPath (Either .jar or .z):
[Oracle_Home] /SQLJ/LIB/Runtime12.jar
[Oracle_Home] /sqlj/lib/runtime12ee.jar
[Oracle_Home] /sqlj/lib/runtime11.jar
[Oracle_home] /sqlj/lib/runtime.jar
[Oracle_Home] /sqlj/lib/runtime-nonoracle.jar
See "Requirements for Using Oracle SQLJ" regarding which runtime library to use for your JDBC driver and Java environment.Important: You will not be able to run the SQLJ translator if you do not add a runtime library You must specify a runtime library as. Well As the Translator Library IN Your ClassPath. To See if Sqlj Is Installed Correctly, And To See Version Information For SQLJ, JDBC, AND JAVA, EXECUTE The FOLLOWING COMMAND: SQLJ -VERSION-LONG
Verify Installation of SQLJUTL Package
Note: This step is relevant only for online checking during translation, and is applicable only if you are using SQLJ stored procedures or functions with a pre-8.1.5 Oracle database (or an 8.1.5 or later database that was installed without a server -Side JVM).
The package sqljutl is required for online checking of stored procedures and functions in an Oracle database. For Oracle release 8.1.5 and later, it should have been installed automatically under the SYS schema during installation of your database server-side JVM. To verify the Installation of Sqljutl, Issue The Following SQL Command (from SQL * Plus, for Example):
Describe sys.sqljutl
This should result in a brief description of the package. If you get a message indicating that the package can not be found, then you must install it manually. To do so, use SQL * Plus to run the sqljutl.sql script, which is located As Follows:
[ORACLE_HOME] /SQLJ/LIB/SQLJUTL.SQL
(The Sqljutl Package Is Installed in The Sys Schema.)
Consult Your Installation Instructions if Necessary.
Testing the setup
You can test your database, JDBC, and SQLJ Setup Using Demo Applications Defined in The Following Source Files:
TestInstallCreateTable.java TestInstallJDBC.java TestInstallSQLJ.sqlj TestInstallSQLJChecker.sqljThere is also a Java properties file, connect.properties, that helps you set up your database connection. You must edit this file to set appropriate user, password, and URL values.
THESE DEMO Applications Are Provided With Your Sqlj Installation In The Demo Directory:
[Oracle_Home] / SQLJ / DEMO
You Must Edit Some of The Source Files As Necessary and Translate / Compile The AS Appropriate (as Explained In The Following Subsections).
The demo applications provided with the Oracle SQLJ installation refer to tables on an Oracle account with user name scott and password tiger. Most Oracle installations have this account. You can substitute other values for scott and tiger if desired.
NOTE: Running The demo Applications Requires That The Directory Be The Current Directory and That The Current Directory (".") Be in your classpath, AS Described Earlier.
SET UP THE RUNTIME Connection
THIS Section Describes How To Update The Connect.properties File to Configure Your Oracle Connection for Runtime. The File Is in The Directory and Looks Something Like the Following:
# UERS SHOULD UNCOMMENT One of the Following Urls or Add Their Own.
# (If using thin, edit as appropriate.)
# SQLJ.URL = JDBC: Oracle: Thin: @localhost: 1521: Orcl
# SQLJ.URL = JDBC: Oracle: OCI: @
#
# User name and password here
SQLJ.User = Scott
Sqlj.password = Tiger
(User Scott and Password Tiger Are Used for the demo Applications.).
Connecting with an an an oracle JDBC Driver
With Oracle9i, use "oci" in the connect string for the Oracle JDBC OCI driver in any new code. For backward compatibility, however, "oci8" is still accepted, so you do not have to change existing code. (Also, "oci7 "is accepted for Oracle JDBC release 7.3.4.) If you are using the JDBC Thin driver, then uncomment the thin URL line in connect.properties and edit it as appropriate for your Oracle connection. Use the same URL that was specified when your JDBC Driver WAS SET UP.
Connecting with a non-oracle JDBC Driver
IF you are using a non-oracle JDBC Driver, Then Add a line to connect.properties to set the appropriate URL, AS FOLLOWS:
SQLJ.URL = Your_URL_HERE
Use The Same Url That Was Specified When Your JDBC Driver WAS SET UP.
You Must Also Register The Driver Explicitly In Your Code. This is Performed Automatically In The demo and test programs if you Use an oracle jdbc driver. See "driver selection and registration for runtime".
In Addition, In The Sqlj Demo Programs, You Must Replace The Following Code:
Oracle.connect (URL, User, Password);
With the folload:
Drivermanager.RegisterDriver (New YourDriver ());
Connection conn = drivermanager.getConnection (URL, User, Password);
Conn.setautocommit (false);
DefaultContext.SetDefaultContext (New DefaultContext (CONN));
Create a Table to Verify The Database
. The following tests assume a table called SALES If you compile and run TestInstallCreateTable as follows, it will create the table for you if the database and your JDBC driver are working and your connection is set up properly in the connect.properties file:
Javac TestinstallCreatetable.java
Java TestinstallCreateTable
Note: If you already have a table called SALES in your schema and do not want it altered, edit TestInstallCreateTable.java to change the table name Otherwise, your original table will be dropped and replaced.If you do not want to use TestInstallCreateTable,. You can INSTEAD CREATE THE SALES TABLE Using The Following Command in A Command-Line Processor (SUCH AS SQL * PLUS):
Create Table Sales
Item_number number,
Item_name char (30),
Sales_Date Date,
Cost Number,
Sales_rep_number number,
SALES_REP_NAME CHAR (20));
Verify the JDBC Driver
If you want to further test the oracle JDBC Driver, Use the testinstalljdbc demo.
Verify That Your Connection IS Set Up Up Properly in Connect.Properties As Described Above, The Compile and Run Testinstalljdbc:
Javac Testinstalljdbc.java
Java Testinstalljdbc
The Program Should Print:
Hello, JDBC!
Verify the sqlj translator and runtime
NOW Translate and Run The Testinstallsqlj Demo, a Sqlj Application That Has Similar Functionality To Testinstalljdbc. Use The Following Command To Translate The Source:
SQLJ Testinstallsqlj.sqlj
After a brief Wait You Should Get Your System Prompt Back with no error output. Note That Command Also Compiles The Application And Customizes It to use an oracle data.
In a UNIX environment, the sqlj script is in [Oracle_Home] / bin, which should already be in your path as described above. (On Windows, use the sqlj.exe executable in the bin directory.) The SQLJ translator JAR / ZIP file ............................................. ...CRIPLILE,
Now Run the Application:
Java Testinstallsqlj
The Program Should Print:
Hello, SQLJ!
Verify the SQLJ Translator Connection to the DatabaseIf the SQLJ translator is able to connect to a database, then it can provide online semantics-checking of your SQL operations during translation. The SQLJ translator is written in Java and uses JDBC to get information it needs from a database connection that you specify. you provide the connection parameters for online semantics-checking using the sqlj script command line or using a SQLJ properties file (called sqlj.properties by default).
While still in the demo directory, edit the file sqlj.properties and update, comment, or uncomment the sqlj.password, sqlj.url, and sqlj.driver lines, as appropriate, to reflect your database connection information, as you did in the Connect.properties File. for some assistance, see the comments in the sqlj.properties file.
FOLLOWING IS An Example of What The Appropriate Driver, URL, AND Password Settings Might Beness......................
SQLJ.URL = JDBC: Oracle: OCI: @
Sqlj.driver = Oracle.jdbc.Oracledriver
Sqlj.password = Tiger
Online semantics-checking is enabled as soon as you specify a user name for the translation-time connection. You can specify the user name either by uncommenting the sqlj.user line in the sqlj.properties file or by using the -user command-line Option. The User, Password, URL, AND Driver Options All Can Be Set Either on the command line or in the profment in "Connection Options".
You can test online semantics-checking by translating the file testinstallsqljchecker.sqlj, located in the demo directory, As Follows (Or Using Another User Name IF Appriate):
SQLJ -USER = Scott testinstallsqljchecker.sqlj
This Should Produce The Following Error Message if you are use one of the oracle JDBC Drivers:
Testinstallsqljchecker.sqlj: 41: Warning: Unable to check SQL Query. Error
Returned by Database IS: ORA-00904: Invalid Column Name
Edit TestInstallSQLJChecker.sqlj to fix the error on line 41. The column name should be ITEM_NAME instead of ITEM_NAMAE Once you make this change, you can translate and run the application without error using the following commands.:
SQLJ -USER = Scott testinstallsqljchecker.sqlj
Java Testinstallsqljchecker
IF everything works, this Prints:
Hello, SQLJ Checker!
