//********************************************************************************************
// Class: OleTypeBWrite
// Given an OLE_OBJECT_ID and a file name, write OLE of type OLE_OBJECT_TYPE=B into
// PassPort table TIDBLOB.
// The file is expected to be a MS Word document.
// Program takes whole document into memory, so a large document will blow it. Eventually will
// be rewritten to read from a file and write to database in chunks.
// Code is for database MySQL using data type: blob. Oracle code using data type: long raw
// has been commented out.
//
// Runs in MS Windows command prompt.
//
// Corresponding entry in TIDOBLOK needs to look like following:
// insert into tidoblok (ole_object_id, ole_object_len, ole_object_class,
//                       ole_object_owner, ole_object_status, last_updated_by, last_updated_date,
//                       time_stamp)
//               values ('123456789012',0,'Word.Document','PassPortID','U','PassPortID','CCYYMMDD',
//                       'CCYY-MM-DD-HH.MM.SS.000000')
//
// Tidoblok.ole_object_class value must exist in PC's regedit (run at command prompt),
// HKEY_CLASSES_ROOT. The entry must have a key of CLSID and there has to be a value
// in the Data identifying an existing Windows application.
//
// Confirmed to work:
// ole_object_class = Word.Document and ExcelWorksheet 
//
// Table definition for MySQL:
//  CREATE TABLE tidblob (
//    ole_object_id    CHAR(12) NOT NULL
//   ,ole_object_type  CHAR(1)  NOT NULL
//   ,ole_object_blob  BLOB
//   ,gen_arg          INTEGER(38) NOT NULL
//   ,time_stamp       CHAR(26) NOT NULL
//   ,PRIMARY KEY (ole_object_id, gen_arg) )
//  ;
//
//********************************************************************************************

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

class OleTypeBWrite {

  public static void main(String[] args) {

    String currentMethod = "OleTypeBWrite";
    String Prompt        = "PROMPT";
    String UserID        = "UserID";
    String Password      = "Password";
    String Database      = "Database";
    String OLEID         = "OLEID";
    String Filein        = "Filein";

    /* 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 information to create OLE of type B.");

      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: ");
      Filein   = readEntry ("File drive+path+name or UNC: ");
    }

    /* end of getting arguments */


    /* Read file */

    // Get the size of the file
    File file = new File(Filein);
    long filelength = file.length();

    // Create the byte array to hold the data.
    // The array size matches the file size.
    byte[] inDatabyte = new byte[(int)filelength];

    try {
      
      FileInputStream fis = new FileInputStream(file);
      filelength = fis.read(inDatabyte);
      fis.close();

    } catch (UnsupportedEncodingException ee) {
      System.out.println("IOException error" + ee.getMessage());
    } catch (IOException e) {
      System.out.println("IOException error" + e.getMessage());
    }

    System.out.println("filelength = " + filelength);

    /* end Read file */


    /* Convert file data from decimal to hex */

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

    /* Convert from a byte array to a char array of hex values double in length. */
    char[] inDatachar = encodeHex(inDatabyte);

    System.out.println(currentMethod + ". inDatachar.length: " + inDatachar.length);
    System.out.println("inDatachar[0]=" + inDatachar[0]);
    System.out.println("inDatachar[1]=" + inDatachar[1]);

    String outData = String.copyValueOf(inDatachar);

    /* end of convert text from decimal to hex */    


    /* Connect to database */

    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();

      connection.setAutoCommit(false);

    } 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 */

    /* Write byte array data into tidblob in chunks. */
   
    System.out.println(currentMethod + ". Insert TIDBLOB data.");
    
    boolean _insertOK = true;
    int genarg        = 10000;
    /* Limitation of JDBC ? */
    int chksize       = 4000;
    int outsize       = 0;
    int rowcnt        = 1;

    while ((_insertOK) && (outsize < outData.length())) {

      /* Insert blob data to tidblob */

      StringBuffer stringbuffer = new StringBuffer();
      stringbuffer.append("Insert into " + Owner + ".tidblob ");
      stringbuffer.append(" (Ole_Object_Id, Ole_Object_Type, Ole_Object_Blob, Gen_Arg, Time_Stamp) ");
      stringbuffer.append(" values ( ?, 'B', ?, ?, '2006-12-29-00.00.00.000000'");
      stringbuffer.append(" ) ");
      String s     = stringbuffer.toString();

      try {
      
        PreparedStatement preparedstatement = connection.prepareStatement(s);
        preparedstatement.clearParameters();
        preparedstatement.setString(1, OLEID);
        /* Check for the last chunk */
        if ( (outData.length() - outsize) >= chksize) {
          System.out.println(currentMethod + ". Write row " + rowcnt +
            " into tidblob. From " + (outsize) + " to " +
            (outsize + chksize));
          preparedstatement.setString(2, outData.substring(outsize, outsize + chksize));
        } else {
          /* Last chunk of data */
          System.out.println(currentMethod + ". Write row " + rowcnt +
            " into tidblob. From " + (outsize) + " to " +
            (outData.length()) );
          preparedstatement.setString(2, outData.substring(outsize, outData.length()) );
        }   
        preparedstatement.setString(3, Integer.toString(genarg));

        int i = preparedstatement.executeUpdate();
        if (i == 1) {
          System.out.println("Successfully added the record.");
          _insertOK = true;
        }
        preparedstatement.close();

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

      /* end of insert */

      genarg  = genarg + 10000;
      outsize = outsize + chksize;
      rowcnt  = rowcnt + 1;

    }
 
    /* Commit or rollback */

    try {
      if (_insertOK) {
        connection.commit();
        System.out.println("Database changes commited.");
      } else {
        connection.rollback();
        System.out.println("Database changes rolledback.");
      }
    } catch (SQLException e1) {
      e1.printStackTrace();
      System.out.println(currentMethod + e1.toString());
    }

    /* end Commit or rollback */

    /* 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 */

  /* 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 commons codec.
   * Used to build output as Hex.
   */
  private static final char[] DIGITS = {
    '0', '1', '2', '3', '4', '5', '6', '7',
    '8', '9', 'a', 'b', 'c', 'd', 'e', 'f'
  };
  

  /* Apache Jakarta commons codec.
   * Converts an array of bytes into an array of characters representing the
   * hexidecimal values of each byte in order.
   * The returned array will be double the length of the passed array, as it takes
   * two characters to represent any given byte.
   *
   * @param data a byte[] to convert to Hex characters
   * @return a char[] containing hexidecimal characters
   */
  public static char[] encodeHex(byte[] data) {
  
    int datalen = data.length;
    char[] out = new char[datalen << 1];

    // two characters form the hex value.
    for (int i=0, j=0; i < datalen; i++) {
      out[j++] = DIGITS[ (0xF0 & data[i]) >>> 4 ];
      out[j++] = DIGITS[  0x0F & data[i] ];
    }
    return out;

  }

/* end of class */

}