sqljLOBSample
/** * @author srangan.in * @version 1.0 * * Development Environment : JDeveloper 2.0 * Name of the Application : sqljLOBSample.java * Creation/Modification History : * * srangan.in 10-JAN-1998 Created * * Overview of Application : * * This sample illustrates access and manipulation of CLOB and BLOB columns * using SQLJ. * * The sample illustrates the above operations on CLOB and BLOB columns in the * AIRPORT_LOB_DETAILS table. It inserts sample .gif and .txt file contents, * into the LOB columns for the chosen airport. If LOB data already exists for * the chosen airport, it retrieves and displays them. It also * illustrates manipulation of LOB columns, by allowing users to enter text to * be appended to the CLOB column. * */ // SQLJ runtime classes import sqlj.runtime.*; import sqlj.runtime.ref.*; import java.io.*; import java.sql.*; //Package for JDBC classes //Package for Oracle Extensions to JDBC import oracle.sql.*; import oracle.jdbc.driver.*; // Iterator to retrieve airport records #sql iterator AirportIter (String code, String description, String city); // Iterator to retrieve LOB details for an airport. This is a Positional // iterator declaration #sql iterator AirportLobIter (BLOB, CLOB); public class sqljLOBSample { DefaultContext m_ctx; //Database Connection Context sqljLOBFrame m_GUI; // The GUI handler for the sample // Sample file names static final String s_MapFileName = "map.gif"; static final String s_SugBookFileName = "suggestionbook.txt"; /** * Constructor. Initializes GUI **/ public sqljLOBSample() { try { m_GUI = new sqljLOBFrame(this); } catch (Exception ex) { m_GUI.putStatus("Error in GUI initialization\n"+ex.toString()); } } /** * Main entry point for the class. Instantiates the root frame, * sets up the database connection and populates the JTable with AIRPORTS rows **/ public static void main(String[] args) { sqljLOBSample lobs = new sqljLOBSample(); // Instantiate root frame lobs.m_GUI.setVisible(true); lobs.dbConnection(); // Setup db connection if (lobs.m_ctx != null) lobs.populateAirports(); // Populate the JTable with all airports rows } /** * Dispatches the GUI events to the appropriate method, which performs * the required JDBC operations. This method is invoked when event occurs * in the GUI (like table Selection, Button clicks etc.). **/ public void dispatchEvent (String p_eventName) { //Dispatch Event if (p_eventName.equals("Load Sample Files")) loadSamples(m_GUI.getSelectedCode()); else if (p_eventName.equals("Add Suggestions")) { String l_suggestions = m_GUI.getSuggestionText(); if (!l_suggestions.equals("CANCEL")) { addSuggestions(m_GUI.getSelectedCode(),l_suggestions); m_GUI.m_sugArea.append(new String(l_suggestions)); } } else if (p_eventName.equals("Airport Selected in Table")) airportSelected(m_GUI.getSelectedCode()); 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(){ 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_ctx = 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_ctx); 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()); } } /** * Queries all rows from the AIRPORTS table and populates the JTable with * the returned rows **/ public void populateAirports() { try { m_GUI.appendStatus("\nPopulating Airports. Please wait..."); // Iterator to hold records returned by the query below AirportIter l_airportRecs = null; // Embedded SQL: This call selects the code, description and city values // for all rows in the AIRPORTS table. The set of rows returned by the query is // assigned to the iterator declared above. #sql l_airportRecs = {SELECT AIRPORT_CODE code, DESCRIPTION, NAME city FROM AIRPORTS, CITIES WHERE CTY_ID = ID}; // Loop through the result set and populate JTable with all airports while (l_airportRecs.next()) { //Retrieve column values for this row String l_code = l_airportRecs.code(); String l_desc = l_airportRecs.description(); String l_city = l_airportRecs.city(); m_GUI.addToJTable(l_code, l_desc, l_city); //Insert into Jtable } l_airportRecs.close(); // Close the statement, which also closes the ResultSet m_GUI.putStatus("Connected to database and retrieved all airport rows"); m_GUI.appendStatus("\nPlease Choose an airport "); } catch (SQLException ex) { // Trap SQL errors m_GUI.putStatus("Error Querying Airports table: \n"+ex.toString()); } } /** * Creates a new row for the selected airport in AIRPORT_LOB_DETAILS. * It then loads the sample files into the LOB columns, using JDBC. **/ void loadSamples(String p_airportCode) { try { m_GUI.putStatus("Creating row for airport in AIRPORT_LOB_DETAILS.. "); // Embedded SQL: This call creates a new row in the AIRPORT_LOB_DETAILS // table for the selected airport. The BLOC and CLOB column values // are initialized to empty locator values. The BLOB data will be // inserted in the calls below. #sql {insert into AIRPORT_LOB_DETAILS (AIRPORT_CODE,AIRPORT_MAP,AIRPORT_SUG_BOOK) values(:p_airportCode , empty_blob() , empty_clob())}; m_GUI.appendStatus("Created.\nLoading <map.gif> into BLOB column for airport..."); AirportLobIter l_airportLobs = null; BLOB l_mapBLOB = null; CLOB l_sugBookCLOB = null; // Embedded SQL: Call to retrieve the BLOB and CLOB locators // for the chosen airport. #sql l_airportLobs = {SELECT AIRPORT_MAP,AIRPORT_SUG_BOOK FROM AIRPORT_LOB_DETAILS WHERE AIRPORT_CODE= :p_airportCode FOR UPDATE}; // Embedded SQL: Retreive the column locators from the positional iterator #sql { fetch :l_airportLobs INTO :l_mapBLOB, :l_sugBookCLOB }; // Write sample file contents to LOB if the above fetch was successful if (!l_airportLobs.endFetch()) { // Open the sample file as a stream for insertion into the BLOB column File l_mapFile = new File(s_MapFileName); InputStream l_sampleFileStream=new FileInputStream(l_mapFile); // Buffer to hold chunks of data to being written to the BLOB. byte[] l_buffer = new byte[10 * 1024]; long l_nread = 0; // Number of bytes read long l_offset = 1; // Offset at which to write // Read a chunk of data from the sample file input stream, and write the // chunk to the BLOB column using dbms_write. Repeat till file has been // fully read. while ((l_nread= l_sampleFileStream.read(l_buffer)) != -1) { // Embedded SQL call to write the data into the BLOB using the dbms_lob // server-side package. The parameters are: // 1st parameter - BLOB to write // 2nd parameter - Number of bytes to write // 3rd parameter - Offset from which to write // 4th parameter - The buffer in which the data is held #sql { call dbms_lob.write(:INOUT l_mapBLOB, :l_nread, :l_offset, :l_buffer) }; l_offset += l_nread; } l_sampleFileStream.close(); // Close file streams // Load the suggestion book sample file into the CLOB column m_GUI.appendStatus("Done\nLoading <suggestionbook.txt> into CLOB column .."); // Open the sample file as a stream for insertion into the CLOB column File l_sugbookFile = new File(s_SugBookFileName); FileReader l_sugFileReader = new FileReader(l_sugbookFile); // Buffer to hold chunks of data to being written to the CLOB. char[] l_cbuffer = new char[10* 1024]; l_nread = 0; // Number of bytes read from file l_offset = 1; // Offset at which to write // Read a chunk of data from the sample file input stream, and write the // chunk to the CLOB column using dbms_write. Repeat till file has been // fully read. while ((l_nread= l_sugFileReader.read(l_cbuffer)) != -1) { String l_str = new String(l_cbuffer); // Embedded SQL call to write the data into the CLOB using the dbms_lob // server-side package. The parameters are: // 1st parameter - CLOB to write // 2nd parameter - Number of bytes to write // 3rd parameter - Offset from which to write // 4th parameter - The buffer in which the data is held #sql { call dbms_lob.write(:INOUT l_sugBookCLOB, :l_nread, :l_offset, :l_str) }; l_offset += l_nread; } l_sugFileReader.close(); // Close File stream m_GUI.putStatus("Done Loading sample files"); m_GUI.appendStatus("\nRetrieving and displaying sample files.."); // Retrieve and display the LOB data just inserted drawBLOB(l_mapBLOB,p_airportCode); writeCLOB(l_sugBookCLOB,p_airportCode); m_GUI.putStatus("Done loading and displaying LOB data"); } // Close the iterator l_airportLobs.close(); } catch (Exception ex) { // Trap SQL errors m_GUI.putStatus("Error loading sample files for the selected airport"); m_GUI.appendStatus("\n"+ex.toString()); } } /** * Accepts suggestions from an JOptionPane and appends the entered * suggestions to the CLOB column **/ void addSuggestions(String p_airportCode, String p_suggestions) { try { m_GUI.putStatus("Appending entered suggestions to CLOB column. Please wait..."); AirportLobIter l_airportLobs = null; BLOB l_blob = null; CLOB l_clob = null; // Embedded SQL: Call to retrieve the BLOB and CLOB locators // for the chosen airport. #sql l_airportLobs = {SELECT AIRPORT_MAP,AIRPORT_SUG_BOOK FROM AIRPORT_LOB_DETAILS WHERE AIRPORT_CODE= :p_airportCode FOR UPDATE}; // Embedded SQL: fetch the LOB column locators #sql { fetch :l_airportLobs INTO :l_blob, :l_clob }; // If the above fetch was successful, write to CLOB if (!l_airportLobs.endFetch()) { long l_length = p_suggestions.length(); // Length of data to be appended // Get the length of the CLOB data in the column in database long l_clobLen; #sql l_clobLen = { VALUES(dbms_lob.getlength(:l_clob)) }; // Embedded SQL: Append the entered suggestions to the end of the CLOB // data, using the dbms_lob server side package #sql { call dbms_lob.write(:INOUT l_clob, :l_length, :l_clobLen, :p_suggestions) }; m_GUI.appendStatus("Done"); } l_airportLobs.close(); // Close statement which also closes open result sets } catch(SQLException ex) { m_GUI.putStatus("Error appending suggestions to the CLOB column"); m_GUI.appendStatus("\n"+ex.toString()); } } /** * This method is called when a row is selected from the airport JTable. * It checks if there exists data in AIRPORT_LOB_DETAILS for the selected * airport. If there exists data, it calls drawBLOB and writeCLOB to * display the data **/ void airportSelected(String p_airportCode) { try { m_GUI.putStatus("Retrieving LOB details for selected airport.."); AirportLobIter l_airportLobs = null; BLOB l_blob = null; CLOB l_clob = null; // Embedded SQL: Call to retrieve the BLOB and CLOB locators // for the chosen airport. #sql l_airportLobs = {SELECT AIRPORT_MAP,AIRPORT_SUG_BOOK FROM AIRPORT_LOB_DETAILS WHERE AIRPORT_CODE= :p_airportCode FOR UPDATE}; // Embedded SQL: Fetch the locators from the positional iterator #sql { fetch :l_airportLobs INTO :l_blob, :l_clob }; // If the above fetch was successful, retrieve and display the LOB data // else allow load of sample files if (!l_airportLobs.endFetch()) { // LOB details exist // Display airport map and suggestion book (LOB details) drawBLOB(l_blob,p_airportCode); writeCLOB(l_clob, p_airportCode); m_GUI.putStatus("Done retrieving and displaying LOB details"); } else { // No LOB details m_GUI.m_loadButton.setEnabled(true); m_GUI.putStatus("No airport map and suggestion book exist for selected airport"); m_GUI.appendStatus("\nPress <Load Sample Files> to load LOB details"); } l_airportLobs.close(); } catch (Exception ex) { // Trap SQL errors m_GUI.putStatus("Error retrieving LOB Details for the selected airport"); m_GUI.appendStatus(ex.toString()); } } /** * Retrieve the BLOB data from input BLOB column into a local file, * and draws the image **/ void drawBLOB(BLOB p_blob, String p_airPCode) { try { // Open a file stream to save the BLOB data FileOutputStream l_fileOutStream = new FileOutputStream(p_airPCode+".gif"); // Retrieve the length of the BLOB data long l_blobLen; #sql l_blobLen = { VALUES(dbms_lob.getlength(:p_blob)) }; long l_readLen; // Length of CLOB data read byte[] l_buffer = new byte[100]; // Temoprary buffer to transfer read data into StringBuffer for (long i=1;i<=l_blobLen;i+=l_readLen) { l_readLen = 1000; // Length of data to be read // Embedded SQL call to read the data from the CLOB using the dbms_lob // server-side package. The parameters are: // 1st parameter - BLOB to read // 2nd parameter - Number of bytes to read.(number of bytes actually read) // 3rd parameter - Offset // 4th parameter - The buffer in which the data is returned #sql { call dbms_lob.read(:p_blob, :inout l_readLen, :i, :out l_buffer) }; l_fileOutStream.write(l_buffer,0,(int)l_readLen); // Save in StringBuffer } // Flush and close the streams l_fileOutStream.flush(); l_fileOutStream.close(); m_GUI.drawMap(p_airPCode); // Draw retrieved image to GUI } catch (Exception ex) { // Trap SQL and IO errors m_GUI.putStatus("Error in retrieving and drawing map for selected airport"); m_GUI.appendStatus("\n"+ex.toString()); } } /** * Retrieve the character data from the input CLOB, save in a * StringBuffer and display the StringBuffer contents in GUI **/ void writeCLOB(CLOB p_clob, String p_airPCode) { try { // Holds the CLOB data being read StringBuffer l_suggestions = new StringBuffer(); // Retrieve the length of the CLOB data long l_clobLen; #sql l_clobLen = { VALUES(dbms_lob.getlength(:p_clob)) }; long l_readLen; // Length of CLOB data read String l_buffer; // Temoprary buffer to transfer read data into StringBuffer for (long i=1;i<=l_clobLen;i+=l_readLen) { l_readLen = 1000; // Length of data to be read // Embedded SQL call to read the data from the CLOB using the dbms_lob // server-side package. The parameters are: // 1st parameter - CLOB to read // 2nd parameter - Number of bytes to read.(number of bytes actually read) // 3rd parameter - Offset // 4th parameter - The buffer in which the data is returned #sql { call dbms_lob.read(:p_clob, :inout l_readLen, :i, :out l_buffer) }; l_suggestions.append(l_buffer); // Save in StringBuffer } m_GUI.m_sugArea.append(new String(l_suggestions)); // Display in GUI } catch (Exception ex) { // Trap SQL and IO errors m_GUI.putStatus("Error in getting and drawing CLOB for the airport, "+p_airPCode+":"); m_GUI.appendStatus(ex.toString()); } } /** * Close connection and exit application **/ public void exitApplication() { try { if (m_ctx != null) m_ctx.close(); } catch (SQLException ex) { // Trap SQL errors m_GUI.putStatus("Error closing connection"); m_GUI.appendStatus(ex.toString()); } System.exit(0); } }
Bookmark
