Sample demonstrating the usage of LONG type

Table Of Contents 

• Overview of the Sample Application
• Required Software
• 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 JDBC. It uses the JDBC-Thin driver. This sample application simulates an Airlines Details display Form in which the Airline code, Name, Partner and Airlines insurance data details are displayed by the application.

Working of the Sample

The sample application uses a database table OTN_AIRLINES_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, all the details of the selected Airlines along with AIRLINE_INSURANCE_DATA which is stored in the LONG column are retrieved and displayed.

Here is the code usage for inserting an Airlines record in the database with its Insurance data stored in Long column. You can find more details of the code in LongSample.java file under src/oracle/otnsamples/jdbc/longsample folder. Look into Description of Sample Files section for folder and file details.

Code for inserting Long Data

public void insertData(String code, String name, String partner, String textfile) throws Exception { // Create a File to read data from the specified text file (textfile) File file = new File(getClass().getClassLoader().getResource(textfile).getFile()); // Prepare the statement for inserting a row into the OTN_AIRLINES_LONG PreparedStatement pstmt = connection.prepareStatement(" INSERT INTO "+ "otn_airlines_long( code, name, partner,"+ "airline_insurance_data) VALUES(?, ?, ?, ?)"); // Bind the parameter values for the above statement pstmt.setString(1, code); pstmt.setString(2, name); pstmt.setString(3, partner); // Bind the AIRLINE_INSURANCE_DATA column to an input Ascii stream that // returns the Ascii data to be inserted into the LONGRAW column pstmt.setCharacterStream(4, new FileReader(file), (int)file.length()); // Execute the statement pstmt.execute(); } } ................ ................

Code for retrieving Long Data

public void displayLongData(String code) { ........ ........ // Create a PreparedStatement object to execute the QUERY PreparedStatement pst = connection.prepareStatement("SELECT airline_insurance_data"+ " FROM otn_airlines_long WHERE code = ?"); // Bind the airline code to the query pst.setString(1, code); gui.putStatus("Reading Long Column from the DB, Please wait..."); // Excute the Query ResultSet resultSet = pst.executeQuery(); // Populate the ResultSet while(resultSet.next()) { InputStream insurancedata; // Holds the LONG data StringBuffer dataBuffer = new StringBuffer(); /* Obtain the LONG data into a byte array. LONG data can be accessed in two ways: 1) By retrieving all the data in one shot (getBytes method) 2) By using streams. The LONG data is made available to the program as an Ascii or Unicode stream, and the data can be retrieved chunk by chunk, which is more eficient in terms of memory usage In this sample we illustrate retrieval using streams */ insurancedata = resultSet.getAsciiStream(1); while((chunk = insurancedata.read()) != -1) { dataBuffer.append((char)chunk); } ................ ................

• 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 JDBC Driver. This can be downloaded from here.

• Unjar the provided LongSample.jar using the following command 

> jar xvf LongSample.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 'LongSample' with all the source files

• Edit LongSample/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 LongSample.jws from the LongSample directory.
• Next, select Project/Make LongSample.jpr from main menu.
• Now, select Run/Run LongSample.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 LongSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set before running run.bat file.
Example:

D:\LongSample> set JDBC_HOME=d:\oracle9i\jdbc\lib
D:\LongSample> set JAVA_HOME=d:\jdk1.3.1
D:\LongSample> run

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file: classes12.zip
• LongSample directory where Connection.properties exists and current directory are also added to the CLASSPATH
  Example: 
  D:\LongSample>set CLASSPATH=D:\oracle9i\jdbc\lib\classes12.zip;D:\LongSample;.
• Make sure that Java is in the PATH
  Example: D:\LongSample>set PATH=.;d:\jdk1.3.1\bin;%PATH%
  
• From the directory LongSample, compile all the java files using javac:
  Example: 
  D:\LongSample>javac -d . src\oracle\otnsamples\jdbc\longsample\*.java
• Run the class file using java from the same LongSample\src\oracle\otnsamples\jdbc\longsample directory
  Example:
  D:\LongSample>java oracle.otnsamples.jdbc.longsample.LongSample

 

From JDK for Red Hat Linux Advanced Server release 2.1

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

Run application using batch File: run.sh provided:

By setting few environment variables, the sample application could be directly run by just executing the batch file: run.sh from the command prompt, from LongSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set, else the user will be prompted to enter values.

• Go to LongSample directory and from the $ prompt use the command below to give execute permission to the file.
  $chmod 777 run.sh
• Now run the file:
  $sh run.sh
  

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file: classes12.zip
• LongSample directory where Connection.properties exists and current directory are also added to the CLASSPATH.
  Example: 
  $export CLASSPATH=/home1/idcotn/download/jdbc/lib/classes12.zip
  :/home1/idcotn/download/basicjdbc/LongSample:.
• Make sure that Java is in the PATH
  Example: $export PATH=/usr/java/jdk1.3.1_02/bin:$PATH
  
• From the directory LongSample, compile all the java files using javac:
  Example: 
  $javac -d . src/oracle/otnsamples/jdbc/longsample/*.java
• Run the class file using java from the same LongSample directory.
  Example:
  $java oracle.otnsamples.jdbc.longsample.LongSample