//********************************************************************************************
// Class: OleTypeBRead
// Given an OLE_OBJECT_ID, extract OLE of type OLE_OBJECT_TYPE=B (upper case b) from PassPort
// table TIDBLOB.
// Runs in MS Windows command prompt.
// Code is for database MySQL using data type blob. Oracle code using data type long raw 
// has been commented out.
// Extract placed in: current directory/output/
//
//********************************************************************************************

import java.sql.*;
import java.io.*;

class OleTypeBRead {

  public static void main(String[] args) {

    String currentMethod = "OleTypeBRead";

    String Prompt        = "PROMPT";
    String UserID        = "UserID";
    String Password      = "Password";
    String Database      = "Database";
    String OLEID         = "OLEID";

    /* Oracle begin */
    /*String DBUrl         = "jdbc:oracle:oci8:@";*/
    /* Oracle end */

    /* MySQL begin */
    String DBUrl         = "jdbc:mysql://localhost/";
    /* MySQL end */

    String Owner         = "test.";

    /* Get the arguments. */

    if (Prompt.equals(args[0])) {

      //Prompt user 
      System.out.println (currentMethod + " executed with 1st argument = " + args[0] +
          ". Please enter the information to extract OLE.");

      UserID = readEntry ("User ID for JDBC: ");
      int slash_index = UserID.indexOf ('/');
      if (slash_index != -1) {
        Password = UserID.substring(slash_index + 1);
        UserID   = UserID.substring(0, slash_index);
      } else {
        Password = readEntry ("Password: ");
      }
      Database = readEntry ("Database (a TNSNAME entry, name-value pair): ");
      OLEID    = readEntry ("OLE ID: ");
    }

    /* end of getting arguments */

    /* Test the database connection */

    Connection connection = null;

    try {
      //Load the JDBC driver

      /* Oracle begin */
      /*DriverManager.registerDriver(new oracle.jdbc.OracleDriver());*/
      /* Oracle end */

      /* MySQL begin */
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      /* MySQL end */

      connection = DriverManager.getConnection(DBUrl + Database, UserID, Password);

      //Test the database connection
      Statement stmt = connection.createStatement();
      
      //Let the user know if database connection is OK
      String DB_OK = "'Database connection OK. Database: " + Database + "'";

      ResultSet rset = stmt.executeQuery ("Select " + DB_OK + " from dual");

      while (rset.next()) {
        System.out.println(currentMethod + ". " + rset.getString(1) );
      }

      rset.close();
      stmt.close();

    } catch (Throwable throwable) {
      String s2 = "Unexpected error when retrieving database connection: " +
                  throwable.getMessage();
      System.out.println(currentMethod + ". " + s2);
    }

    if (connection == null) {
      System.out.println(currentMethod + ". DB connection is null.");
    }

    /* end of database connection test */

    /* Select blob data from PassPort table tidblob. */
   
    System.out.println(currentMethod + ". Select Tidblob data.");
    
    StringBuffer stringbuffer = new StringBuffer();
    stringbuffer.append("Select OLE_OBJECT_BLOB from " + Owner + "tidblob");
    stringbuffer.append(" where OLE_OBJECT_ID = ? and OLE_OBJECT_TYPE = 'B' ");
    stringbuffer.append(" order by GEN_ARG asc");

    String s     = stringbuffer.toString();

    /* Oracle begin */
    /* String[] OOB = new String[4000]; */
    /* Oracle end */

    /* MySQL begin */
    byte[] OOB      = new byte[4000]; 
    int bytecnt     = 0;
    String blobdata = ""; 
    /* MySQL end */

    int row_cnt  = 0;
    
    try {
      
      PreparedStatement preparedstatement = connection.prepareStatement(s);
      preparedstatement.clearParameters();
      preparedstatement.setString(1, OLEID);
      ResultSet resultset = preparedstatement.executeQuery();

      for (row_cnt = 1; resultset.next(); row_cnt++) {

        /* Oracle begin */
        /* OOB[row_cnt] = resultset.getString("OLE_OBJECT_BLOB"); */
        /* Oracle end */

        /* MySQL begin */
        Blob datablob = resultset.getBlob("OLE_OBJECT_BLOB");

        InputStream in = datablob.getBinaryStream(); 

        try {
          bytecnt = in.read(OOB); 
          System.out.println(currentMethod + ". bytecnt= " + bytecnt);
        } catch (IOException e) {
          System.out.println("IOException error" + e.getMessage());
        } 
/*
        for (row_cnt = 0; row_cnt < bytecnt; row_cnt++) {
          System.out.println(currentMethod + ". Select from tidblob: " + OOB[row_cnt] + " pos " + row_cnt);
        }
*/

        for (int k = 0; k < bytecnt; k++) {
          blobdata = blobdata + (char) OOB[k];
        }

        /* MySQL end */

      }

    } catch (SQLException ex) {
      ex.printStackTrace();
      System.out.println(currentMethod + ex.toString());
    }

    /* end of PassPort table tidblob */

    /* Close the database connection */

    if (connection != null) {
      try {
        connection.close();
      } catch (SQLException ex) {
        ex.printStackTrace();
        System.out.println(currentMethod + ex.toString());
      }
    }

    /* end of database close connection */

    /* Get the output directory */

    System.out.println(currentMethod + ". Get the output directory.");
    
    File outdir = new File(".");
    String StorageDir = "StorageDir";
    try {
      StorageDir = outdir.getCanonicalPath() + "/output";
    } catch (Exception e) {
      e.printStackTrace();
    }

    System.out.println(currentMethod + ". Output directory: " + StorageDir);

    /* end of output directory */

    /* Convert OLE_OBJECT_BLOB from hex to decimal */

    System.out.println(currentMethod + ". Convert from hex to decimal.");

    String blobfile = "/" + OLEID + ".txt";

    /* Oracle begin */
    /* 
    String blobdata = ""; 

    for (int k = 1; k < row_cnt; k++) { 
      blobdata = blobdata + OOB[k];
    }
    */
    /* Oracle end */

    System.out.println(currentMethod + ". Length of OLE from tidblob: " +
      blobdata.length());
    System.out.println(currentMethod + ". blobdata= " + blobdata);

    int length      = blobdata.length();
    char[] blobchar = blobdata.toCharArray();

    if (length > 0) {

      byte[] outData = new byte[length/2];

      outData = decodeHex(blobchar);

      File AttFile = new File(StorageDir, blobfile);

      try {
        FileOutputStream outFile = new FileOutputStream(AttFile.toString(), true);
        outFile.write(outData);
        outFile.close();
      } catch (Exception e) {
        System.out.println(currentMethod + ". Error is " + e.toString());
      }
    }

    /* end of convert from hex to decimal */

  /* end of main */
  }


  /* Utility function to read a line from standard input. */

  static String readEntry (String prompt) {

    try {
      StringBuffer buffer = new StringBuffer();
      System.out.flush();
      int c = System.in.read();
      while (c != '\n' && c != -1) {
        buffer.append((char) c);
        c = System.in.read();
      }
      return buffer.toString().trim();
    } catch (IOException e) {
      return "";
    }

  }

  /* end of function readEntry */

  /** Apache Jakarta component codec.
   *  Converts an array of characters representing hexidecimal values into an
   *  array of bytes of those same values. The returned array will be half the 
   *  length of the passed array, as it takes two characters to represent any
   *  given byte. An exception is thrown if the passed char array has an odd
   *  number of elements.
   *
   *  @param data An array of characters containing hexidecimal digits
   *  @return A byte array containing binary data decoded from
   *          the supplied char array.
   *  @throws DecoderException Thrown if an odd number of illegal of characters
   *          is supplied.
   */

  /* public static byte[] decodeHex(char[] data) throws DecoderException { */
  public static byte[] decodeHex(char[] data) {

    int len              = data.length;
    String currentMethod = "decodeHex";
    
    if ((len & 0x01) != 0) {
      /* throw new DecoderException("Odd number of characters."); */
      System.out.println(currentMethod + ". Error: Odd number of characters.");
    }

    byte[] out = new byte[len >> 1];

    // two characters form the hex value
    for (int i=0, j=0; j<len; i++) {
      int f = toDigit(data[j], j) << 4;
      j++;
      f = f | toDigit(data[j], j);
      j++;
      out[i] = (byte) (f & 0xFF);
    }
   
    return out;
  }
    
  /** Apache Jakarta component codec.
   *  Converts a hexidecimal character to an integer.
   *
   *  @param ch A character to convert to an integer digit
   *  @param index The index of the character in the source
   *  @return An integer
   *  @throws DecoderException Thrown if ch is an illegal hex character 
   */

  /* protected static int toDigit(char ch, int index) throws DecoderException { */
  protected static int toDigit(char ch, int index) { 

    int digit            = Character.digit(ch, 16);
    String currentMethod = "toDigit";
   
    if (digit == -1) {
      /* throw new DecoderException("Illegal hexidecimal character " + ch +
         " at index " + index); */
      System.out.println(currentMethod + ". Error: Illegal hexidecimal character " +
       ch + " at index " + index);
    }

    return digit;
  }

/* end of class */

}