//********************************************************************************************
// Class: OleTypeTWrite
// Given an OLE_OBJECT_ID and a string of text, write OLE of type OLE_OBJECT_TYPE=T into
// PassPort table TIDBLOB.
// 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.
//
//********************************************************************************************

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

class OleTypeTWrite {

  public static void main(String[] args) {

    String currentMethod = "OleTypeTWrite";
    String Prompt        = "PROMPT";
    String UserID        = "UserID";
    String Password      = "Password";
    String Database      = "Database";
    String OLEID         = "OLEID";
    String Textin        = "Textin";

    /* 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 T.");

      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: ");
      Textin   = readEntry ("Text of max 100 characters: ");
    }

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

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

    /* Convert user input text from decimal to hex */

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

    byte[] inDatabyte = new byte[4000];

    try {
      inDatabyte = Textin.getBytes("UTF-8");
    } catch (UnsupportedEncodingException e) {
      String s2 = "Unexpected error when encoding data: " + e.getMessage();
      System.out.println(currentMethod + ". " + s2);
    }

    System.out.println(currentMethod + ". inDatabyte.length: " + inDatabyte.length);

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

    String outData = String.copyValueOf(inDatachar);

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

    /* Connect to database again */

    connection = null;

    try {

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

    /* Insert blob data from PassPort table tidblob. */
   
    System.out.println(currentMethod + ". Insert TIDBLOB data.");
    
    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 ( ?, 'T', ?, ?, '2006-12-29-00.00.00.000000'");
    stringbuffer.append(" ) ");
    String s     = stringbuffer.toString();
    boolean _insertOK = false;

    try {
      
      PreparedStatement preparedstatement = connection.prepareStatement(s);
      preparedstatement.clearParameters();
      preparedstatement.setString(1, OLEID);
      preparedstatement.setString(2, outData);
      preparedstatement.setString(3, "10000");

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

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

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

}