/*
   * @author  : Reghu

   * @Version : 1.0
   *
   * Development Environment        :  Oracle9i JDeveloper
   * Name of the Application        :  DBBroker.java
   * Creation/Modification History  :
   *
   * Reghu    17-Jan-2002   Created

   * Savitha  15-Sep-2002   modified to add few xmlnews sample specific methods
   *                        like: clobUpdate, editNewsitem, executeCallable,
   *                        executeClob, executeForClob, insertNewsitem. These
   *                        methods are mostly for NewsItemSession EJB.
   *
   */
  package oracle.otnsamples.eis;


  import java.io.Reader;

  // IO Imports
  import java.io.Writer;

  import java.sql.CallableStatement;

  // Necessary classes to execute the SQL Query
  import java.sql.Connection;

  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.ResultSetMetaData;
  import java.sql.SQLException;

  // Java Utility classes
  import java.util.ArrayList;
  import java.util.Hashtable;
  import java.util.Properties;


  // RowSet related classes
  import javax.sql.RowSet;

  import oracle.jdbc.OracleResultSet;

  import oracle.jdbc.rowset.OracleCachedRowSet;

  // Dynamic News specific Utility Class
  import oracle.otnsamples.util.Utilities;

  import oracle.sql.CLOB;



  /**
   * This class has methods to which SQL queries can be passed as parameters and
   * return the results as RowSet. A RowSet is an object which encapsulates a
   * set of rows. These rows are accessible though the javax.sql.RowSet
   * interface.
   */
  public class DBBroker {
    // Caches DBBroker object
    private static Hashtable cache = new Hashtable(  );


    // Database Connection Implementation object
    private Connector connector = null;

    /**
     * Default Constructor. Takes no arguments
     */
    protected DBBroker(  ) {
    }

    /**
     * Constructor, extracts the name of the class which implements Database
     * Connection from the DBDetails.properties file and loads it into memory.
     *

     * @param <b>dbKey </b> The key for Connection Implementation Class
     *
     * @throws <b>Exception </b> if any exception thrown in the flow of
     *         execution.
     */
    protected DBBroker( String dbKey ) throws Exception {
      // Load the database specific class details from the properties file.
      Properties prop = Utilities.loadParams( "DBDetails" );

      // Get the connection class name from the Properties object
      String className = (String) prop.get( dbKey );

      // Load the class and get an instance of it.
      connector = (Connector) Class.forName( className ).newInstance(  );


      // Clear the properties object
      prop.clear(  );
    }

    /**
     * This method returns DBBroker object from the cache, if it is already
     * available. It will instantiate the DBBroker class if the cache is empty.
     *
     * @param <b>dbKey </b> The key for Connection implementation Class
     *
     * @return <b>DBBroker</b> An instance of this class
     *
     * @throws <b>Exception </b> if any exception thrown in the flow of
     *         execution

     */
    public static DBBroker getDBBroker( String dbKey )
                                  throws Exception {
      if ( !cache.contains( dbKey ) ) {
        DBBroker broker = new DBBroker( dbKey );
        cache.put( dbKey, broker ); // Putting into the cache
      }

      return (DBBroker) cache.get( dbKey );
    }

    /**
     * This method returns Connector object based on the key value passed.
     *
     * @param <b>dbKey </b> The key for Connection Implementation Class
     *

     * @return <b>Connector</b> instance of the database connection class
     *
     * @throws <b>Exception </b> if any exception thrown in the flow of
     *         execution
     */
    public static Connector getDBConnector( String dbKey )
                                      throws Exception {
      // Load the DB Class Details from the properties file.
      Properties prop = Utilities.loadParams( "DBDetails" );

      // Get the connection details from the Properties object
      String className = (String) prop.get( dbKey );

      // Clear the properties object for garbage collection.
      prop.clear(  );

      // Load the class, get an instance of it and returns.

      return (Connector) Class.forName( className ).newInstance(  );
    }

    /**
     * This method executes the sql query passed as the parameter and returns the
     * results as a Cached RowSet.
     *
     * @param <b>query </b> The SQL Query
     * @param <b>params </b> List of parameters
     *
     * @return <b>RowSet</b> Query result as a rowset object
     *
     * @throws <b>DBException </b> if any error in executing the sql
     *         query
     */
    public RowSet execute( String query, ArrayList params )
                     throws DBException {
      Connection con = null; // Database Connection object


      // Create an Oracle Cached RowSet Object
      OracleCachedRowSet crset = new OracleCachedRowSet(  );

      try {
        // Get the Database Connection
        con = connector.getConnection(  );

        // Use PreparedStatement object to execute the SQL Query
        PreparedStatement pst = con.prepareStatement( query );

        // Bind the columns values from the ArrayList object
        if ( params != null ) {
          for ( int i = 0; i < params.size(  ); i++ ) {
            pst.setObject( i + 1, (String) params.get( i ) );
          }
        }

        // Execute the Query to get the ResultSet object

        ResultSet rst = pst.executeQuery(  );

        // Populate the Cached Rowset from ResultSet
        crset.populate( rst );

        // Close the ResultSet and Statement
        rst.close(  );
        pst.close(  );
      }
       catch ( SQLException ex ) {
        throw new DBException( "Unable to execute the SQL Query in execute "
                                 + "method of DBBroker class of given status "
                                 + " : " + ex.getMessage(  ) );
      }
       catch ( Exception ex ) {
        throw new DBException( "Exception thrown in execute() method of "
                                 + "DBBroker class of given status : "
                                 + ex.getMessage(  ) );
      }
       finally {

        try {
          connector.releaseConnection( con ); // Releases the connection
        }
         catch ( Exception ex ) {
          throw new DBException( "Unable to release the connection of given "
                                   + "status : " + ex.getMessage(  ) );
        }
      }

      return crset;
    }

    /**
     * This method executes the sql query passed as the parameter and returns the
     * results as a Cached RowSet.
     *
     * @param <b>query </b> The SQL Query
     * @param <b>params </b> List of parameters
     *
     * @return <b>ArrayList</b> Query result as a rowset object
     *

     * @throws <b>DBException </b> if any error in executing the sql
     *         query
     */
    public ArrayList editNewsItem( String query, ArrayList params )
                             throws DBException {
      Connection con = null; // Database Connection object
      CLOB clob = null;
      int bytesread = 0;
      Reader reader; // Reader object to read the clob value

      // Buffer to hold the CLOB data
      StringBuffer clobData = new StringBuffer(  );

      // Initialize ResultSet
      ResultSet rs = null;

      // Initialize ResultSetMetaData
      ResultSetMetaData rsmd;
      int columnCount;

      // Initialize the ArrayList to be returned.
      ArrayList data = new ArrayList(  );


      try {
        // Get the Database Connection
        con = connector.getConnection(  );

        // Use PreparedStatement object to execute the SQL Query
        PreparedStatement pst = con.prepareStatement( query );

        // Bind the columns values from the ArrayList object
        if ( params != null ) {
          for ( int i = 0; i < params.size(  ); i++ ) {
            pst.setObject( i + 1, params.get( i ) );
          }
        }

        // Execute the Query to get the ResultSet object
        rs = pst.executeQuery(  );
        rsmd = rs.getMetaData(  );
        columnCount = rsmd.getColumnCount(  );

        while ( rs.next(  ) ) {
          // Get rows and add it to a String Array
          String[] row = new String[ columnCount ];


          for ( int i = 0; i < columnCount; i++ ) {
            if ( ( i == 0 ) || ( i == 10 ) || ( i == 11 ) || ( i == 12 ) ) {
              row[ i ] = String.valueOf( rs.getInt( i + 1 ) );
            }
            else {
              if ( i == 5 ) {
                OracleResultSet ors = (OracleResultSet) rs;
                clob = ors.getCLOB( i + 1 );

                // read the clob value
                if ( clob != null ) {
                  // Open the stream to read data
                  reader = clob.getCharacterStream(  );

                  //  Buffer size is fixed using the getBufferSize() method which
                  //  returns the optimal buffer size to read data from the LOB
                  char[] charbuffer = new char[ clob.getBufferSize(  ) ];

                  // Keep reading from the CLOB and append it to the StringBuffer till
                  // there is no more to read
                  while ( ( bytesread = reader.read( charbuffer ) ) != -1 ) {
                    clobData.append( charbuffer, 0, bytesread );
                  }


                  // Close the input stream
                  reader.close(  );

                  // Free memory for Clob
                  clob.freeTemporary(  );
                  row[ i ] = clobData.toString(  );
                }
              }
              else {
                row[ i ] = rs.getString( i + 1 );
              }
            }
          }

          // Adds the String Array to the ArrayList
          data.add( row );
        }

        pst.close(  );

        return data;
      }
       catch ( SQLException ex ) {
        throw new DBException( "Unable to execute the SQL Query in getClobExecute "

                                 + "method of DBBroker class of given status "
                                 + " : " + ex.getMessage(  ) );
      }
       catch ( Exception ex ) {
        throw new DBException( "Exception thrown in getClobExecute() method of "
                                 + "DBBroker class of given status : "
                                 + ex.getMessage(  ) );
      }
       finally {
        try {
          connector.releaseConnection( con ); // Releases the connection
        }
         catch ( Exception ex ) {
          throw new DBException( "Unable to release the connection of given "
                                   + "status : " + ex.getMessage(  ) );
        }
      }
    }

    /**
     * This method executes the sql query passed as the parameter and returns the
     * results as a String. In this method parameters are converted to clob and
     * used in the query. This method is used in ejb method:
     * ejbHomeGenerateBatchNews to generate batch/static news page.
     *
     * @param <b>query </b> The SQL Query

     * @param <b>params </b> List of parameters
     *
     * @return <b>String</b> Query result as a String
     *
     * @throws <b>DBException </b> if any error in executing the sql
     *         query
     */
    public String executeForClob( String query, ArrayList params )
                            throws DBException {
      Connection con = null; // Database Connection object

      // Create Resultsets to store values.
      ResultSet rs;
      OracleResultSet ors;
      CLOB clob = null;
      int bytesread = 0;
      Reader reader; // Reader object to read the clob value

      // Buffer to hold the CLOB data
      StringBuffer clobData = new StringBuffer(  );

      try {
        // Get the Database Connection
        con = connector.getConnection(  );

        // Use PreparedStatement object to execute the SQL Query
        PreparedStatement pst = con.prepareStatement( query );

        String parameter;

        // Bind the columns values from the ArrayList object
        if ( params != null ) {
          for ( int i = 0; i < params.size(  ); i++ ) {
            parameter = (String) params.get( i );

            // Use getCLOB method to get CLOB value for the passed string value.
            pst.setObject( i + 1, getCLOB( parameter, con ) );
          }
        }

        // Execute the Query to get the ResultSet object
        rs = pst.executeQuery(  );
        ors = (OracleResultSet) rs;

        while ( ors.next(  ) ) {
          clob = ors.getCLOB( 1 );
        }

        if ( clob != null ) {
          // Open the stream to read data
          reader = clob.getCharacterStream(  );

          //  Buffer size is fixed using the getBufferSize() method which returns
          //  the optimal buffer size to read data from the LOB
          char[] charbuffer = new char[ clob.getBufferSize(  ) ];


          // Keep reading from the CLOB and append it to the StringBuffer till
          // there is no more to read
          while ( ( bytesread = reader.read( charbuffer ) ) != -1 ) {
            clobData.append( charbuffer, 0, bytesread );
          }

          // Close the input stream
          reader.close(  );

          // Free memory for Clob
          clob.freeTemporary(  );
        }
        else {
          clobData.append( "no data" );
        }

        // Close the ResultSet and Statement
        rs.close(  );
        pst.close(  );
      }
       catch ( SQLException ex ) {
        throw new DBException( "Unable to execute the SQL Query in executeForClob"
                                 + "method of DBBroker class of given status "
                                 + " : " + ex.getMessage(  ) );
      }
       catch ( Exception ex ) {
        throw new DBException( "Exception thrown in executeForClob method of "
                                 + "DBBroker class of given status : "
                                 + ex.getMessage(  ) );

      }
       finally {
        try {
          connector.releaseConnection( con ); // Releases the connection
        }
         catch ( Exception ex ) {
          throw new DBException( "Unable to release the connection of given "
                                   + "status : " + ex.getMessage(  ) );
        }
      }

      return clobData.toString(  );
    }

    /**
     * This method is implemented to execute the SQL Query and returns the number
     * of rows updated/inserted/deleted.
     *
     * @param <b>query </b> The SQL Query
     * @param <b>params </b> List of parameters
     *
     * @return <b>int</b>Number of rows updated/deleted
     *
     * @throws <b>DBException </b> if any error in executing the sql
     *         query
     */
    public int executeUpdate( String query, ArrayList params )
                        throws DBException {
      Connection conn = null; // Database Connection object
      int updateCount = 0;


      try {
        // Get the Database Connection
        conn = connector.getConnection(  );

        // Use PreparedStatement to execute the Query
        PreparedStatement pst = conn.prepareStatement( query );

        // Bind the columns values from the ArrayList passed
        if ( params != null ) {
          for ( int i = 0; i < params.size(  ); i++ ) {
            pst.setObject( i + 1, params.get( i ) );
          }
        }

        // Execute the Query to get the number of rows updated
        updateCount = pst.executeUpdate(  );

        // Close the Statement object
        pst.close(  );
      }
       catch ( SQLException ex ) {
        throw new DBException( "Unable to execute the SQL Query in "
                                 + "executeUpdate method of given "
                                 + "status : " + ex.getMessage(  ) );
      }
       catch ( Exception ex ) {
        throw new DBException( "Exception thrown in executeUpdate() method "
                                 + "of DBBroker class of given status : "
                                 + ex.getMessage(  ) );
      }

       finally {
        try {
          connector.releaseConnection( conn );
        }
         catch ( DBConException ex ) {
          throw new DBException( "Unable to release the connection of given "
                                   + "status : " + ex.getMessage(  ) );
        }
      }

      return updateCount;
    }

    /**
     * This method is implemented to execute a database stored procedure. This
     * method calls a db stored procedure called: updatexmlnews  which is used
     * to do partial update on the XMLTYPE column in NewsItems table. The db
     * procedure takes in three parameters: column name (which is xml element
     * name), its value to which it has to be updated and id of the news item on
     * which the update is done.
     *
     * @param <b>params </b> List of parameters
     *
     * @throws <b>DBException </b> if any error in executing the sql
     *         query
     */
    public void executeCallable( ArrayList params )
                           throws DBException {
      Connection conn = null; // Database Connection object
      CallableStatement cs = null;
      CLOB clob = null;
      String flag = "";


      try {
        // Get the Database Connection
        conn = connector.getConnection(  );

        if ( params != null ) {
          if ( ( params.get( 0 ).equals( "description" ) ) ) {
            // call Stored DB procedure for updating description column
            cs =
              (CallableStatement) conn.prepareCall( "begin updatexmlnewsdesc (?,?,?); end;" );
            flag = "D";
          }
          else {
            // call Stored DB procedure for updating columns
            // other than description
            cs =
              (CallableStatement) conn.prepareCall( "begin updatexmlnews (?,?,?); end;" );
            flag = "O";
          }
        }

        if ( flag.equals( "D" ) ) {
          for ( int i = 0; i < params.size(  ); i++ ) {
            // Bind the columns values from the ArrayList passed 
            // bind values explicitly.  string value for the column name, 
            // clob value for description and int value for news item id
            if ( i == 1 ) {
              clob = this.getCLOB( (String) params.get( i ), conn );
              cs.setObject( ( i + 1 ), clob );
            }
            else {
              if ( i == 2 ) {
                int j = Integer.parseInt( (String) params.get( i ) );

                cs.setInt( i + 1, j );
              }
              else {
                cs.setObject( i + 1, (String) params.get( i ) );
              }
            }
          }
        }
        else if ( flag.equals( "O" ) ) {
          for ( int i = 0; i < params.size(  ); i++ ) {
            // Bind the columns values from the ArrayList passed                      
            cs.setObject( i + 1, (String) params.get( i ) );
          }
        }

        // Execute callable statement.  
        cs.execute(  );

        // Close the Statement object
        cs.close(  );
      }
       catch ( SQLException ex ) {
        throw new DBException( "Unable to execute the SQL Query in "
                                 + "executeCallable method of given "
                                 + "status : " + ex.getMessage(  ) );
      }
       catch ( Exception ex ) {
        throw new DBException( "Exception thrown in executeCallable method "
                                 + "of DBBroker class of given status : "
                                 + ex.getMessage(  ) );
      }
       finally {
        try {
          connector.releaseConnection( conn );

        }
         catch ( DBConException ex ) {
          throw new DBException( "Unable to release the connection of given "
                                   + "status : " + ex.getMessage(  ) );
        }
      }
    }

    /**
     * This method returns a CLOB Object with the specified Data. This CLOB can
     * now be used to insert in the database table which has a CLOB column. In
     * this sample, this Object is used to insert XML String in the DataBase
     * XMLType column.
     *
     * @param <b>clobData </b> Data to be inserted in the CLOB Object
     * @param <b>conn </b> Database Connection Object
     *
     * @return CLOB Object
     *
     * @throws <b>DBException </b> In Case an exception is thrown.
     * @throws <b>SQLException</b> if there is any SQL error.
     */
    private CLOB getCLOB( String clobData, Connection conn )
                    throws DBException, SQLException {
      CLOB tempClob = null;

      try {
        //  create a new temporary CLOB
        tempClob = CLOB.createTemporary( conn, true, CLOB.DURATION_SESSION );

        // Open the temporary CLOB in readwrite mode to enable writing
        tempClob.open( CLOB.MODE_READWRITE );

        // Get the output stream to write
        Writer tempClobWriter = tempClob.getCharacterOutputStream(  );


        // Write the data into the temporary CLOB
        tempClobWriter.write( clobData );

        // Flush and close the stream
        tempClobWriter.flush(  );
        tempClobWriter.close(  );

        // Close the temporary CLOB
        tempClob.close(  );

        return tempClob;
      }
       catch ( SQLException sqlexp ) {
        tempClob.freeTemporary(  );
        throw new DBException( "Exception thrown in getCLOB method "
                                 + "of DBBroker class of given status : "
                                 + sqlexp.getMessage(  ) );
      }
       catch ( Exception exp ) {
        tempClob.freeTemporary(  );
        throw new DBException( "Exception thrown in getCLOB method "
                                 + "of DBBroker class of given status : "
                                 + exp.getMessage(  ) );
      }
    }

    /**
     * This method executes the sql query passed as the parameter and returns the
     * results as a String. In this method, the resulting clob data of the sql
     * query is  converted and returned as a String.
     *
     * @param <b>query </b> The SQL Query
     * @param <b>params </b> List of parameters
     *
     * @return <b>String</b> Query result as a String

     *
     * @throws <b>DBException </b> if any error in executing the sql
     *         query
     */
    public String executeClob( String query, ArrayList params )
                         throws DBException {
      // Create Resultsets to store values.
      ResultSet rs;
      OracleResultSet ors;
      CLOB clob = null;
      int bytesread = 0;
      Reader reader; // Reader object to read the clob value

      // Buffer to hold the CLOB data
      StringBuffer clobData = new StringBuffer(  );
      Connection con = null; // Database Connection object

      // Create an Oracle Cached RowSet Object
      OracleCachedRowSet crset = new OracleCachedRowSet(  );

      try {
        // Get the Database Connection
        con = connector.getConnection(  );

        // Use PreparedStatement object to execute the SQL Query
        PreparedStatement pst = con.prepareStatement( query );

        // Bind the columns values from the ArrayList object
        if ( params != null ) {
          for ( int i = 0; i < params.size(  ); i++ ) {
            pst.setObject( i + 1, (String) params.get( i ) );
          }
        }

        // Execute the Query to get the ResultSet object
        rs = pst.executeQuery(  );
        ors = (OracleResultSet) rs;


        while ( ors.next(  ) ) {
          clob = ors.getCLOB( 1 );
        }

        if ( clob != null ) {
          // Open the stream to read data
          reader = clob.getCharacterStream(  );

          //  Buffer size is fixed using the getBufferSize() method which returns
          //  the optimal buffer size to read data from the LOB
          char[] charbuffer = new char[ clob.getBufferSize(  ) ];

          // Keep reading from the CLOB and append it to the StringBuffer till
          // there is no more to read
          while ( ( bytesread = reader.read( charbuffer ) ) != -1 ) {
            clobData.append( charbuffer, 0, bytesread );
          }

          // Close the input stream
          reader.close(  );

          // Free memory for Clob
          clob.freeTemporary(  );
        }
        else {
          clobData.append( "no Data" );
        }

        // Close the ResultSet and Statement
        rs.close(  );
        pst.close(  );
      }
       catch ( SQLException ex ) {
        throw new DBException( "Unable to execute the SQL Query in executeForClob"
                                 + "method of DBBroker class of given status "
                                 + " : " + ex.getMessage(  ) );
      }

       catch ( Exception ex ) {
        throw new DBException( "Exception thrown in executeForClob method of "
                                 + "DBBroker class of given status : "
                                 + ex.getMessage(  ) );
      }
       finally {
        try {
          connector.releaseConnection( con ); // Releases the connection
        }
         catch ( Exception ex ) {
          throw new DBException( "Unable to release the connection of given "
                                   + "status : " + ex.getMessage(  ) );
        }
      }

      return clobData.toString(  );
    }

    /**
     * This method executes the sql query passed as the parameter and returns the
     * results as a RowSet. This method is used while inserting a record into
     * NewsItems table in the NewsItemBean EJB. In this method, only the fifth
     * parameter is converted to clob and inserted into xmltype column since
     * clob can hold large values which is required in of the news item.
     *
     * @param <b>query </b> The SQL Query
     * @param <b>params </b> List of parameters
     *
     * @throws <b>DBException </b> if any error in executing the sql
     *         query
     */
    public void insertNewsItem( String query, ArrayList params )
                          throws DBException {
      int[] updatedRows;
      CLOB clob = null;

      // Initialize statement Object
      PreparedStatement pstmt = null;
      Connection con = null; // Database Connection object


      try {
        // Get the Database Connection
        con = connector.getConnection(  );

        // Get the statement Object
        pstmt = con.prepareStatement( query );

        if ( params != null ) {
          for ( int i = 0; i < params.size(  ); i++ ) {
            if ( i == 4 ) {
              clob = this.getCLOB( (String) params.get( i ), con );
              pstmt.setObject( ( i + 1 ), clob );
            }
            else {
              pstmt.setObject( i + 1, params.get( i ) );
            }
          }
        }

        // Execute the Query to get the ResultSet object
        boolean flg = pstmt.execute(  );

        // Close the Statement
        pstmt.close(  );
      }
       catch ( SQLException ex ) {
        throw new DBException( "Unable to execute the SQL Query in clobExecute()"
                                 + "method of DBBroker class of given status "
                                 + " : " + ex.getMessage(  ) );
      }
       catch ( Exception ex ) {
        throw new DBException( "Exception thrown in clobExecute() method of "
                                 + "DBBroker class of given status : "
                                 + ex.getMessage(  ) );
      }
       finally {
        try {
          // Close Statement
          if ( pstmt != null ) {

            pstmt.close(  );
          }

          // Free CLOB
          if ( clob != null ) {
            freeCLOB( clob );
          }

          connector.releaseConnection( con ); // Releases the connection
        }
         catch ( Exception ex ) {
          throw new DBException( "Unable to release the connection of given "
                                   + "status : " + ex.getMessage(  ) );
        }
      }
    }

    /**
     * This method executes the sql query passed as the parameter and returns the
     * results as a RowSet. This method is used while inserting a record into
     * news items table in the NewsItemBean EJB. In this method, only the fifth
     * parameter is converted to clob and inserted into xmltype column since
     * clob can hold large values which is required in of the news item.
     *
     * @param <b>query </b> The SQL Query
     * @param <b>params </b> List of parameters
     *
     * @throws <b>DBException </b> if any error in executing the sql
     *         query
     */
    public void clobUpdate( String query, ArrayList params )
                      throws DBException {
      int[] updatedRows;
      CLOB clob = null;

      // Initialize statement Object
      PreparedStatement pstmt = null;
      Connection con = null; // Database Connection object

      try {
        // Get the Database Connection

        con = connector.getConnection(  );

        // Get the statement Object
        pstmt = con.prepareStatement( query );

        if ( params != null ) {
          for ( int i = 0; i < params.size(  ); i++ ) {
            if ( i == 0 ) {
              clob = this.getCLOB( (String) params.get( i ), con );
              pstmt.setClob( ( i + 1 ), clob );
            }
            else {
              int j = Integer.parseInt( (String) params.get( i ) );
              pstmt.setInt( i + 1, j );
            }
          }
        }

        // Execute the Query to get the ResultSet object
        boolean flg = pstmt.execute(  );

        // Close the Statement
        pstmt.close(  );
      }
       catch ( SQLException ex ) {
        throw new DBException( "Unable to execute the SQL Query in clobExecute()"
                                 + "method of DBBroker class of given status "
                                 + " : " + ex.getMessage(  ) );
      }
       catch ( Exception ex ) {
        throw new DBException( "Exception thrown in clobExecute() method of "
                                 + "DBBroker class of given status : "
                                 + ex.getMessage(  ) );
      }
       finally {
        try {
          // Close Statement
          if ( pstmt != null ) {
            pstmt.close(  );
          }

          // Free CLOB

          if ( clob != null ) {
            freeCLOB( clob );
          }

          connector.releaseConnection( con ); // Releases the connection
        }
         catch ( Exception ex ) {
          throw new DBException( "Unable to release the connection of given "
                                   + "status : " + ex.getMessage(  ) );
        }
      }
    }

    /**
     * This method is used to free the memory used to manage clob objects. Since
     * the clob holds a very large set of data, it is important that memory is
     * released once the clob is used. The method cleans up temporary and unused
     * memory.
     *
     * @param <b>clob </b> The clob which requires cleaning
     *
     * @throws <b>DBException </b> if any error in freeing clob memory
     */
    private void freeCLOB( CLOB clob ) throws DBException {
      try {
        clob.freeTemporary(  );
      }
       catch ( SQLException sqlexp ) {
        throw new DBException( sqlexp.getMessage(  ) );
      }
    }
  }