SQLJ: Sample demonstrating the usage of LONG datatype

Table Of Contents 

• Overview of the Sample Application
• Required Software
• Notations Used
• Application Set-up and Configuration
• Running the Application 
• From Oracle9i JDeveloper 
• From JDK for Windows
• From JDK for Linux
• Description of Sample Files

This application shows how to perform inserts and retrieval from a LONG column using SQLJ.

SQLJ enables applications programmers to embed SQL operations in Java code. A SQLJ program is a Java program containing embedded SQL statements that comply with the ISO standard SQLJ Language Reference syntax.

SQLJ consists of a translator and a runtime component (translator.jar/zip and runtime12ee.jar/zip) and is smoothly integrated into the development environment. The translation, compilation, and customization take place in a single step when the front-end utility sqlj is run. The translation process replaces embedded SQL with calls to the SQLJ runtime, which implements the SQL operations. When the end user runs the SQLJ application, the runtime is invoked to handle the SQL operations.

SQLJ runs on top of JDBC. To access an Oracle database, you would typically use an Oracle JDBC driver. In order to run SQLJ programs, apart from SQLJ classes, JDBC classes should be present in the system CLASSPATH. SQLJ code is written and saved in *.sqlj files and should be translated to *.java files before compiling using the front-end utility sqlj.

LONG columns store variable-length character strings containing up to 2 gigabytes. LONG columns have many of the characteristics of VARCHAR2 columns. You can use LONG columns to store long text strings. The length of LONG values may be limited by the memory available on your computer.

This sample application simulates an Airlines Details display Form in which the Airline code, Name, Partner and airline_insurance_data details are displayed by the application.

Working of the Sample

The sample application uses a database table OTN_AIRLINE_LONG. The airline_insurance_data is stored in a Long column. When the application is invoked, the table and few records are created if they do not exist. The status of the connection is shown in the status bar. Errors if any, are shown in the status bar. When the user clicks on any row in the JTable, the airline_insurance_data details which is the LONG column of the selected Airlines is retrieved and displayed.

Here is the SQLJ code usage for inserting an OTN_AIRLINE_LONG record in the database. Retrieval of the Long data is also illustrated. You can find more details of the code in SqljLongSample.sqlj and PopulateTable.sqlj files under src/oracle/otnsamples/sqlj/longsample folder. Look into Description of Sample Files section for folder and file details.

   .........
   .........				
  /** Database Connection Object */
  private Connection connection = null;
     
  /** Database Connection Context object. **/
  private DefaultContext connContext = null;


  private Connection dbConnection() {
    try {

      // Load the properties file to get the connection information.
      Properties prop = loadParams("Connection");

      // Create a OracleDataSource instance.
      OracleDataSource ods = new OracleDataSource();
      .........
      .........

      // Create a connection  object.
      connection = ods.getConnection();
      

      // get a default context using above connection to execute SQLJ statement.
      connContext = new DefaultContext(connection);         

      //Set the above connection context as the default context for this
      //application.
      DefaultContext.setDefaultContext(connContext);
  } catch(Exception ex){ //Catch exceptions.
      .........
  }
  return connection;
 }			  
			  

     ............

	// SQLJ iterator declaration that represents the Long column
    // fetched from AIRLINE_LONG_TABLE.
    #sql iterator InsuranceInfo( AsciiStream airline_insurance_data );
	.............
	.............
	
    /**
     *  Retrieve the LONG data in the AIRLINE_INSURANCE_DATA column, and
     *  displays in the TextArea.
     **/
    private void displayLongData(String code) {
      AsciiStream  insurancedata; // Hold the LONG data.
      StringBuffer dataBuffer = new StringBuffer();
      int chunk;

      // Create an instance of the InsuranceInfo iterator to hold the rows
      // retrieved by the query below.
      InsuranceInfo info = null;

      try {
         // Embedded SQL: this call selects the LONG column and binds the 
         // retrieved data into the data class variable. The code for the 
         // selection is bound to code class variable.
         #sql info= { SELECT AIRLINE_INSURANCE_DATA 
                      FROM otn_airlines_long WHERE Code =:code};

         ..........

         // Retrieve the stream for the LONG column from the iterator.
         while (info.next()){
           insurancedata = info.airline_insurance_data();// Get the AsciiStream.

           // Read data from the Ascii Stream and save to the String Buffer.
           while((chunk = insurancedata.read()) != -1)
              dataBuffer.append((char)chunk);

              .............      
         }

         // Close the iterator.
         info.close();     
      }  catch(Exception ex){ // Catch exceptions.
           .............
      }
    }				  
				  

Insert Long data:

/** * This method inserts contents of the specified file content into the * AIRLINE_INSURANCE_DATA column in the OTN_AIRLINES_LONG. */ private void insertData(String code, String name, String partner, String textfile) throws Exception { // Create an AsciiStream object. CharacterStream aStr = null; try { // Create a File to read data from the specified text file // from 'textfiles' directory. String fileName = "."+File.separator+"textfiles"+File.separator+textfile; File file = new File(fileName); aStr = new CharacterStream(new FileReader(file), (int)file.length()); // Create statement for inserting a row into: OTN_AIRLINES_LONG table. #sql { INSERT INTO otn_airlines_long( code, name, partner, airline_insurance_data) VALUES(:code,:name,:partner,:aStr) }; } catch (Exception ex) { ............. } }

Required Software

Back To Top

• Oracle9i JDeveloper ( Note: Oracle9i JDeveloper is Oracle's Visual Java Development Tool and can be downloaded from here)
  or JDK1.2.x or above This can be downloaded from here.
• Oracle9i Database or higher running SQL*Net TCP/IP listener. This can be downloaded from here.
• Oracle9i SQLJ Translators, Release 9.2.0.1.0. This can be downloaded from here.
• Oracle9i JDBC Driver. This can be downloaded from here.

Note: Both JDBC and SQLJ libraries are available with Oracle Database or client installation and need not be downloaded separately.

• Unjar the provided SqljLongSample.jar using the following command 

> jar xvf SqljLongSample.jar

Note: You will find jar.exe in JDK_HOME\bin. Ensure JDK_HOME\bin is present in your system path. 
(JDK_HOME is the root directory of the JDKx.x installation). This creates a folder 'SqljLongSample' with all the source files

• Edit SqljLongSample/Connection.properties file in your favorite editor. Change the HostName, Port, SID, UserName and 
Password to connect to your own database.

This sample application can be run in 3 different ways listed below.

• From Oracle9i JDeveloper
• From JDK for Windows
• From JDK for Linux

From Oracle9i JDeveloper

• Open Oracle9i JDeveloper and use File/Open option to select the SqljLongSample.jws from the SqljLongSample directory.
• Next, select Project/Make SqljLongSample.jpr from main menu.
• Now, select Run/Run SqljLongSample.jpr from main menu to run the application.

From JDK for Windows

This section will describe steps to run the application from console using JDK on Windows. The sample can be run either manually or using a script file .

Run application using batch File: run.bat provided:

By setting few environment variables, the sample application could be directly run by just executing the batch file: run.bat from the command prompt, from SqljLongSample directory. Environmental variables JAVA_HOME and JDBC_LIB, SQLJ_LIB, SQLJ_EXE_HOME have to be set before running run.bat file. Please look into Notations sections for more details on these environmental variables.

Note: If you have already run the sample application using JDeveloper, you will need to delete *.generated.java files that would have been created by JDeveloper.
Example:

D:\SqljLongSample> set SQLJ_EXE_HOME=d:\sqlj\bin
D:\SqljLongSample> set SQLJ_LIB=d:\sqlj\lib
D:\SqljLongSample> set JDBC_LIB=d:\oracle9i\jdbc\lib
D:\SqljLongSample> set JAVA_HOME=d:\jdk1.3.1
D:\SqljLongSample> run

Running the application manually:

• Set CLASSPATH to include:
  • Oracle9i JDBC Driver file: classes12.jar/zip
  • Oracle9i SQLJ files: translator.jar/zip and runtime12ee.jar/zip
  • SqljLongSample directory where Connection.properties exists(the current directory).
    Example: 
    D:\SqljLongSample>set CLASSPATH=d:\sqlj\lib\translator.jar;d:\sqlj\lib\runtime12ee.jar;
    D:\oracle9i\jdbc\lib\classes12.zip;.
    
• Make sure that Java and SQLJ(where sqlj is found) are in the PATH.
  Example: D:\SqljLongSample>set PATH=.;d:\jdk1.3.1\bin;d:\sqlj\bin;%PATH%
  
• From the directory SqljLongSample, translate all *.sqlj files to *.java files using sqlj:
  Example: 
  D:\SqljLongSample>sqlj -compile=false -status src\oracle\otnsamples\sqlj\longsample\*.sqlj
• From the same SqljLongSample directory, now compile all *.java files using javac:
  Example: 
  D:\SqljLongSample>javac -d . src\oracle\otnsamples\sqlj\longsample\*.java
  
• Run the class file using java from the same SqljLongSample directory.
  Example:
  D:\SqljLongSample>java oracle.otnsamples.sqlj.longsample.SqljLongSample
  

From JDK for Linux

This section will describe steps to run the application from console using JDK on Red Hat Linux Advanced Server Release 2.1. The sample can be run either manually or using a script file .

Run application using script file: run.sh provided. (For Bourne Shell):

By setting few environment variables, the sample application could be directly run by just executing the script file: run.sh from the command prompt, from SqljLongSample directory. The user will be prompted to enter the environmental variables JAVA_HOME, SQL_EXE, SQLJ_LIB and JDBC_LIB when the script is run. Please look into Notations sections for more details on these environmental variables.

• Go to SqljLongSample directory and from the $ prompt, use the command below to run the script file:
  $sh run.sh
  

Running the application manually:

• Set CLASSPATH to include:
  • Oracle9i JDBC Driver file: classes12.jar/zip
  • Oracle9i SQLJ classes: translator.jar/zip and runtime12ee.jar/zip
  • SqljLongSample directory where Connection.properties exists(the current directory).
    Example:
    $export CLASSPATH=/home1/idcotn/download/jdbc/lib/classes12.zip:
    /home1/idcotn/download/sqlj/lib/translator.jar:
    /home1/idcotn/download/sqlj/lib/runtime12ee.jar:.
• Make sure that Java and SQLJ (where sqlj is found) are in the PATH.
  Example: $export PATH=/usr/java/jdk1.3.1_02/bin:/home1/idcotn/download/sqlj/bin:$PATH
  
• From the directory SqljLongSample, translate all *.sqlj files to *.java files using sqlj:
  Example: 
  $sqlj -compile=false -status /src/oracle/otnsamples/sqlj/longsample/*.sqlj
• From the directory SqljLongSample, now compile all the java files using javac:
  Example:
  $javac -d . /src/oracle/otnsamples/sqlj/longsample/SqljLongSample/*.java
• Run the class file using java from the same SqljLongSample directory.
  Example:
  $java oracle.otnsamples.sqlj.longsample.SqljLongSample