Sample demonstrating usage of ColumnType specification

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 illustrates the Column-Type Specification performance extension of Oracle JDBC drivers. It gives a comparison between DEFAULT FETCH and FETCH WITH COLUMN TYPE SPECIFIED. The corresponding execution time will be displayed in the text fields. Thus the user will be able to see the advantage of specifying the Column Type by comparing the execution time.

Working of the Sample

• After invoking the application, the connection status is shown in status bar. Errors if any are also shown in the status bar.
• Press "DEFAULT FETCH" button to see the execution time taken for querying in normal mode.
• Press "FETCH WITH COLUMN TYPE SPECIFIED" button to see the execution time taken for querying after specifying the column type

Here is the code usage for using ColumnType. You can find more details of the code in ColumnTypeSample.java file under src/oracle/otnsamples/jdbc/columntype folder. Look into Description of Sample Files section for folder and file details.

public void selectRecords( boolean specifyColumnType ) { ........... .......... String query = "SELECT language_id FROM products WHERE ROWNUM < 11"; // Create a PreparedStatement based on the query in query PreparedStatement prepare = connection.prepareStatement(query); // Casting the PreparedStatement to OraclePreparedStatement and // Specifying the Standard_rate Column type as FLOAT using // defineColumnType() method. if ( specifyColumnType ) ((OraclePreparedStatement)prepare).defineColumnType(1,Types.VARCHAR); // Get time at start of query java.util.Date bfdte = new java.util.Date(); // Execute the Query in query ResultSet rst = prepare.executeQuery(); String temp = null; // Populate the ResultSet object while ( rst.next() ) { numFetchedRows++; if( specifyColumnType ) // Column Type specified temp = rst.getString(1); else // Column type not specified temp = new Float( rst.getFloat(1) ).toString(); } // Get time at end of query java.util.Date afdte = new java.util.Date(); // Compute time taken timetaken = new Integer((int)( afdte.getTime()- bfdte.getTime())); ..................... ......................

• 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. The JDBC driver is available at ORACLE_HOME/jdbc/lib. Or it could be downloaded from here.

• Unjar the provided ColumnTypeSample.jar using the following command 

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


• Edit ColumnTypeSample/Connection.properties file in your favorite editor. Change the HostName, Port, SID, UserName and 
Password to connect to OE user in your 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 ColumnTypeSample.jws from the ColumnTypeSample directory.
• Next, select Project/Make ColumnTypeSample.jpr from main menu.
• Now, select Run/Run ColumnTypeSample.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 ColumnTypeSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set before running run.bat file.
Example:

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

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file: classes12.zip or classes12.jar. 
• Also add ColumnTypeSample directory where Connection.properties exists and the current directory to the CLASSPATH
  Example: 
  D:\ColumnTypeSample>set CLASSPATH=D:\oracle9i\jdbc\lib\classes12.zip;D:\ColumnTypeSample;.
  
• From the directory ColumnTypeSample\src\oracle\otnsamples\jdbc\columntype, compile all the java files using javac:
  Example: 
  D:\ColumnTypeSample\src\oracle\otnsamples\jdbc\columntype>javac -d . *.java
  
• Run the class file using java from  ColumnTypeSample\src\oracle\otnsamples\jdbc\columntype directory
  Example:
  D:\ColumnTypeSample\src\oracle\otnsamples\jdbc\columntype>
  java oracle.otnsamples.jdbc.columntype.ColumnTypeSample

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 ColumnTypeSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set, else the user will be prompted to enter values.

• Go to ColumnTypeSample 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 or classes12.jar.
• ColumnTypeSample  directory where Connection.properties exists and current directory are also added to the CLASSPATH.
  Example:
  $export CLASSPATH=/home1/jdbc/lib/classes12.zip:/home1/ColumnTypeSample:.
• From the directory ColumnTypeSample/src/oracle/otnsamples/jdbc/columntype, compile all the java files using javac:
  Example:
  $javac -d . *.java
• Run the class file using java from the same ColumnTypeSample/src/oracle/otnsamples/jdbc/columntype directory.
  Example:
  $java oracle.otnsamples.jdbc.columntype.ColumnTypeSample