print Printer View e-mail E-mail this page Bookmark Bookmark 
/**
* @author  Umesh Kulkarni
* @version 1.0

*
* Development Environment         : JDeveloper 2.0
*
* Name of the Application         : sqljBFILESample.sqlj
*
* Creation/ Modification History  :

*
*       ukulkarn.in       11-Jan-1999    Creation
*
* Overview of the Application     :
*  This sample application illustrates accessing of BFILEs using SQLJ.
*
*  BFILE datatype allows us to store large binary objects (like image files,

*  executable files etc) in operating system files outside the database.
*
*  This Sample uses JDBC-Thin Driver and JDBC-Streams to access an image file
*  stored as BFILE column and displays the image in the panel.
*
*  The GUI for this sample is handled in sqljBFILEFrame class. (sqljBFILEFrame.java)
*
**/


// SQLJ Runtime Classes
import sqlj.runtime.*;
import sqlj.runtime.ref.*;

import java.io.*;
import java.sql.*;// Package for JDBC Classes

// Package for using Oracle Extensions to JDBC

import oracle.jdbc.driver.*;
import oracle.sql.*;

public class sqljBFILESample {

  DefaultContext m_dCtx;  // Default Connection Context
  sqljBFILEFrame m_GUI;   // GUI handler for this sample

  /**
  *  Constructor. Instantiates GUI.

  **/
  public sqljBFILESample() {
    m_GUI = new sqljBFILEFrame(this);
    m_GUI.setVisible(true);
  }

  /**
  * The main entry point to the application class. Instantiates the class,
  * sets up database connection. Also the table BFILE_TABLE is created
  * and image file is inserted into BFILE_TABLE.
  **/

  public static void main(String[] args) {
    sqljBFILESample  rootFrame = new sqljBFILESample(); // Root Frame
    rootFrame.dbConnection(); // Setup Database Connection
    rootFrame.createTable(); // Creates Table BFILE_TABLE
    rootFrame.insertData(); // Insert Image into BFILE_TABLE
  }

  /**
  * Dispatches the GUI events to the appropriate method, which performs
  * the required operations using SQLJ. This method is invoked when an event
  * (like Button Clicks etc) occurs in GUI. This method is invoked from the
  * setupListeners section of sqljBFILEFrame.java

  **/
  public void dispatchEvent(String p_eventName){
    // Dispatch Event
    if(p_eventName.equals("SELECT"))
       selectBFILE();
    else if(p_eventName.equals("EXIT"))
       exitApplication();
  }

  /**
  * Creates a database connection object using SQLJ. Please substitute the
  * database connection parameters with appropriate values in
  * ConnectionParams.java

  **/
  public void dbConnection(){

    m_GUI.m_selectButton.setEnabled(false);
    try{
      // Load the Oracle JDBC Driver and register it.
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

      m_GUI.putStatus("Trying to connect to the Database");

      // Form the database connect string(TNSNAMES entry) as a name-value pair
      // using the connection parameters as specified in ConnectionParams.java
      String l_dbConnectString =
          "(DESCRIPTION=(ADDRESS=(HOST="+ConnectionParams.s_hostName+")"+

          "(PROTOCOL=tcp)(PORT="+ConnectionParams.s_portNumber+"))"+
          "(CONNECT_DATA=(SID="+ConnectionParams.s_databaseSID+")))";

      // To Connect to the  Oracle database, use an instance of  the
      // DefaultContext class. The syntax of the DefaultContext constructor is
      // DefaultContext(databaseURL,username,password, boolean auto-commit);
      // The databaseURL syntax is as follows:
      //      "jdbc:oracle:<driver>:@<db connection string>"
      // <driver>, can be 'thin' or 'oci8'
      // <db connect string>, is a Net8 name-value, denoting the TNSNAMES entry
      m_dCtx = new DefaultContext(
                  "jdbc:oracle:thin:@"+l_dbConnectString,
                  ConnectionParams.s_userName,
                  ConnectionParams.s_password,
                  false);


      // Set the above connection context as the default context for this
      // application
      DefaultContext.setDefaultContext(m_dCtx);

      m_GUI.putStatus("Connected to "+ConnectionParams.s_databaseSID+ " as "+
              ConnectionParams.s_userName);
    } catch(Exception ex){ //Trap SQL errors
      m_GUI.putStatus(
             "Error in Connecting to the Database "+'\n'+ex.toString());
    }
  }


  /**
  * Method to drop table BFILE_TABLE if it already exists. Then it creates the

  * table BFILE_TABLE (BFILE_COLUMN BFILE). This Table is used to store a row
  * with image file as a BFILE Column in that row.
  **/
  public void createTable() {
    // Drop Table BFILE_TABLE
    try {
      // Drop the TABLE BFILE_TABLE if it exists
      // Embedded SQL : This Call Drops the Table BFILE_TABLE
      #sql { DROP TABLE BFILE_TABLE };
    } catch (SQLException ex) { // Trap SQL Errors
      m_GUI.putStatus(" Error while Dropping Table " + ex.toString());
    }
    // Create Table BFILE_TABLE
    try {
      // Create the TABLE BFILE_TABLE
      // Embedded SQL : This Call Creates the Table BFILE_TABLE
      #sql { CREATE TABLE BFILE_TABLE(BFILE_COLUMN  BFILE) };

      m_GUI.putStatus("Created table BFILE_TABLE.  Inserting Image Data.. ");
    } catch (SQLException ex) { // Trap SQL Errors
      m_GUI.putStatus(" Error while Creating Table " + ex.toString());
    }
  }

  /**
  * Method to insert a single record consisting of an image file ('architect.gif')
  * as BFILE column into the table BFILE_TABLE.
  * Please copy the file 'architect.gif' provided along with the sample to the
  * machine where database is running.
  **/
  public void insertData() {
    // Get the full directory Path of the image file (architect.gif) from user
    String l_dirPath = m_GUI.getDirectoryPath();
    if (!l_dirPath.equals("Cancel"))
    try {
       // Prepare the Create Directory Statement

       String l_comstmt = "Create Or Replace Directory BFILEDIR AS " + "'" +
           l_dirPath + "'";

       #sql { BEGIN   EXECUTE IMMEDIATE :(l_comstmt); END; };

       // Embedded SQL : This Call inserts the Image as a BFILE Column
       #sql { INSERT INTO BFILE_TABLE VALUES (BFILENAME('BFILEDIR','architect.gif')) };

       // Embedded SQL : This Call commits the inserted record.
       #sql { COMMIT };
       m_GUI.putStatus("Created and populated BFILE_TABLE\n" +
                     "Press SELECT to select image." );
       m_GUI.m_selectButton.setEnabled(true);
    }catch (SQLException ex)  { // Trap SQL Errors
     m_GUI.putStatus(" Error while inserting BFILE Data " + ex.toString());
    }else
     exitApplication();
  }


  /**
  * Method to Select the BFILE Locator from the BFILE_TABLE. After selecting
  * BFILE Locator, it invokes the displayFile() method to display the image.
  * Note that BFILE Columns in Oracle8i store the locator to the external files
  * and not the actual File Data.
  **/
  public void selectBFILE() {
    try {
      // Note :  Using Oracle Extension oracle.sql.BFILE to get BFILE Locator
      oracle.sql.BFILE  l_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 :l_bfile
                            From BFILE_TABLE };

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

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


      // Open the Input Binary Stream with getBinaryStream() method of
      // oracle.sql.BFILE class
      InputStream l_instream = l_bfile.getBinaryStream();

      m_GUI.appendStatus("Opened Binary Stream ...");
      m_GUI.displayFile(l_instream); // Invoke displayFile Method to display image
    } catch (SQLException ex) { // Trap SQL Errors
      m_GUI.putStatus(" Error while selecting BFILE Locator ..." + ex.toString());
    }
  }

  /**
  * Close the connection and exit Application
  **/
  public void exitApplication(){
    try{
      if (m_dCtx != null)
       m_dCtx.close(); // Close the connection
    }catch (SQLException ex){ // Trap SQL Errors
     m_GUI.putStatus("Error while closing connection.\n" + ex.toString());

    }
    System.exit(0);
  }
}
E-mail this page
Printer View Printer View
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Privacy