SQLJ BFILE Sample Application

Table Of Contents 

• Overview of the Sample Application
• Notations used
• Required Software
• Application Set-up and Configuration
• Deploying and Running the Application 
• Using Oracle9i JDeveloper 
• From command line
• Description of Sample Files

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.

This sample application demonstrates Accessing of BFILEs using SQLJ. BFILEs are large binary data objects stored in operating system files (external) outside of the database tablespaces.

This sample uses the JDBC-Thin driver; the code is the same for JDBC-OCI, except for the database URL syntax.

Sample Application Scenario

• When the application is invoked, it connects to the database. The status and errors, if any, will be displayed in the status bar. If the connection is successful, it checks if the table OTN_BFILE_TABLE if already exists. If not it creates the table OTN_BFILE_TABLE(BFILE_COLUMN BFILE). If the table is present, it deletes all the rows in the table.
• After creating the table, the application prompts the user to enter full direcory path of the image file (architect.gif). Then it inserts a single row comprising of the locator to the image file 'architect.gif' into the Table.
  Note: 'architect.gif' file is provided along with the Sample. It has to be copied to some directory on the computer on which database is running
• After insertion of BFILE Data, the SELECT button can be pressed to view the image in the panel.
• When the SELECT button is pressed, following takes place:
  a) BFILE Locator is selected from the table OTN_BFILE_TABLE
  b) Binary Stream is opened on the selected BFILE locator
  c) Image Data (Binary Data) is fetched
  d) Image is displayed.
  

Here is the code usage for reading from a BFILE table. You can find more details of the code in SqljBFILESample.sqlj file under src/oracle/otnsamples/sqlj/bfile folder. Look into Description of Sample Files section for folder and file details.

public void selectBFILE() {
..........
..........
oracle.sql.BFILE  bfile;
//Embedded SQL : This Call gets BFILE Locator into iterator variable.
// Note that there is only one row in the Database Table.
#sql { Select BFILE_COLUMN AS bfilecolumn INTO :bfile From OTN_BFILE_TABLE };

gui.putStatus("Selected BFILE Locator..");

// Open the file with openFile Method of oracle.sql.BFILE class
bfile.openFile();

// Open the Input Binary Stream with getBinaryStream() method of
// oracle.sql.BFILE class
InputStream instream = bfile.getBinaryStream();
gui.appendStatus("Opened Binary Stream ...");

gui.displayFile(instream); // Invoke displayFile Method to display image
..........
..........
}

The following notations are used through out this document

Notation	Description
<SAMPLE_HOME>	Folder where the SqljBFILESample.jar is unzipped.
<JAVA_HOME>	Folder where JAVA is installed.
<JDBC_LIB>	Folder where the Oracle JDBC driver exists.
<SQLJ_LIB>	Folder where the Oracle SQLJ translator and runtime files exist.
<SQLJ_EXE_HOME>	Folder where the SQLJ executable exists.

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.3.x or above This can be downloaded from here .
• Oracle9i Database or higher running SQL*Net TCP/IP listener. This can be downloaded from OTN site.
• Oracle9i v9.0.2 or higher SQLJ translators and runtime, downloadable from OTN site.
• Oracle9i v9.0.2 or higher JDBC Drivers, downloadable from OTN site.
  Note : If Oracle9i client is already installed on your system then this driver need not be downloaded separately.
  
  

• For setting up the environment variables in different platforms, please refer environment set up document.

• Unjar the provided SqljBFILESample.jar using the following command 
  

  > jar xvf SqljBFILESample.jar

  Note: You will find jar.exe in <JAVA_HOME>\bin. Ensure <JAVA_HOME>\bin is present in your system path. 
  This creates a folder SqljBFILESample with all the source files.

• Copy the image file SqljBFILESample\doc\architect.gif to some directory on the machine on which database is running.
• The user to which you are connecting to in the database requires 'CREATE ANY DIRECTORY' privilege to run the sample. If the user does not have the privilege, you can connect to system or sys user and grant the privilege by executing the following

  SQL>grant CREATE ANY DIRECTORY to scott;

• Edit SqljBFILESample\Connection.properties file in your favorite editor. Change the sqlj.url value to connect to your own database.

sqlj.url	=	jdbc:oracle:thin:@localhost:1521:ORCL
sqlj.user	=	scott
sqlj.password	=	tiger

 

This section describes the steps required in running this application usin