Friday, February 22, 2013

Notes to Oracle Using JDBC - The End Result

It took way longer than I expected but I have finally completed the agent to load data from Oracle to Notes using JDBC.   I am pleased with how it all turned out.   This agent is written such that if there was a crash (hardware or software) in the middle of running then no data will be lost, and it will pick up where it left off the next time it is run.

I know this is long, but there is a lot of comments.  I am the only one at my company that knows Java so I wanted to make sure it could be followed easily. (I know we are supposed to do this anyway).  I also removed repetitive lines, and any company specific references.

import lotus.domino.*;
import java.sql.*;
import java.util.Date;

public class JavaAgent extends AgentBase {

    public void NotesMain() {

      try {
          //Standard code for every Notes java agent
     Session session = getSession();
          AgentContext agentContext = session.getAgentContext();

          //Establish connection to Oracle
          System.out.println("Import agent starting...");
          String cs = "";
          DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
          Connection conn = DriverManager.getConnection(cs, "username", "password");
          //Quit if no connection, write status to console
                 System.out.println("Connected to Oracle");
          else {
                 System.out.println("Not connected to Oracle");
          //Get handle to current database, where invoices will be written to
          Database db = session.getCurrentDatabase();
          //Get handle to config database and create two view entry collections for using later
          Database configDb = session.getDatabase("Notes Server", "config.nsf");
          View configViewStore = configDb.getView("(StoreLookupByNumber)");
          View configViewDept = configDb.getView("(DeptLookupByNumber)");
          ViewEntryCollection colStore = configViewStore.getAllEntries();
          ViewEntryCollection colDept = configViewDept.getAllEntries();
          ViewEntry ve = null;
          //Break very long query into readable strings
          String fetch1 = "select lots of columns, ";
          String fetch2 = "a lot more columns ";
          String fetch3 = "even more columns
          String fetch4 = "from Oracle View where sent_to_estore is null and NOTES_DOC_ID is null";
          String fetch5 = " and rownum < 10001";  
          String fetch = fetch1 + fetch2 + fetch3 + fetch4 + fetch5;
          //Fetch result set, must set to updatable.  Default is read only.
          Statement statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
          ResultSet rs = statement.executeQuery(fetch);
          int rows = 0;
          //With Oracle, the only way to navigate through a result is forward using next()
                Document doc = db.createDocument();
                doc.replaceItemValue("Form", "Oracle Invoice");
        //The below line uses a Double wrapper class to pass the value to Notes
        //The method only accepts an Object, not a primitive.  Wrapper object is anonymous.
        //This is done for all Number data types.
        doc.replaceItemValue("InvoiceAmount", new Double(rs.getDouble("INVOICE_AMOUNT")));
          doc.replaceItemValue("InvoiceNum", rs.getString("INVOICE_NUM"));
          //Format the store number to 2 digits  (Note:  automatic casting happening here)
          String storeNumber = "00000000" + new Double(rs.getDouble("STORE_NBR")).intValue();
          String storeNumberString = storeNumber.substring(storeNumber.length() - 2);
          doc.replaceItemValue("StoreNumber", storeNumberString);
          //Get the Store name from the config database
                ve = colStore.getFirstEntry();
                String storeName = "";
          while(ve != null){
            //note:  to compare two strings the '==' operator doesn't work, you need to use compareTo
            //number is in first column (0) and name is in second column (1)
                 if(storeNumberString.compareTo(ve.getColumnValues().elementAt(0).toString()) == 0){
           storeName = ve.getColumnValues().elementAt(1).toString();
           ve = colStore.getNextEntry(ve);
           if(storeName == ""){
           storeName = "No Store Found";  //This should never happen
           doc.replaceItemValue("Store", storeName);
           ve = null; //set null after each usage
           //Format the dept number to 4 digits 
           String deptNumber = "00000000" + new Double(rs.getDouble("DEPT")).intValue();
           String deptNumberString = deptNumber.substring(deptNumber.length() - 4);
           doc.replaceItemValue("DeptNumber", deptNumber.substring(deptNumber.length() - 4));

           //Get the Dept name from the config database
                  ve = colDept.getFirstEntry();
                  String deptName = "";
                  while(ve != null){
              if(deptNumberString.compareTo(ve.getColumnValues().elementAt(0).toString()) == 0){
             deptName = ve.getColumnValues().elementAt(1).toString();
          ve = colDept.getNextEntry(ve);
           if(deptName == ""){
           deptName = "No Department Listed";  //This will often be the case
           doc.replaceItemValue("Dept", deptName);
           ve = null;  //set null after each usage
           //I was getting runtime exceptions when setting java.sql.Date object back to Notes using replaceItemValue
           //To get around this I create DateTime object using static method of session
              DateTime invoiceDate = session.createDateTime(rs.getDate("INVOICE_DATE"));
                DateTime dueDate = session.createDateTime(rs.getDate("DUE_DATE"));
                //Convert dates to text and then remove time value
                doc.replaceItemValue("InvoiceDate", invoiceDate.toString().substring(0, 10));
                doc.replaceItemValue("DueDate", dueDate.toString().substring(0, 10));
          doc.replaceItemValue("ImportTime", new java.util.Date().toString());
         //used to create InvoiceType
         double invoiceAmount = new Double(rs.getDouble("INVOICE_AMOUNT")).doubleValue();
         if(invoiceAmount >= 0){ doc.replaceItemValue("InvoiceType", "Invoice"); }
         if(invoiceAmount < 0){ doc.replaceItemValue("InvoiceType", "Credit"); };
         //Notes document must be saved prior to grabbing UNID
         String UNID = doc.getUniversalID();
         //Create SQL statement to perform update
           String updateSQL1 = "update Oracle view set notes_doc_id =" + "'" + UNID + "' ";
           String updateSQL2 = "where invoice_num = '" + rs.getString("INVOICE_NUM") + "' and key_nbr = '" 
+ rs.getString("KEY_NBR") + "'";
           String updateSQL = updateSQL1 + updateSQL2;
           Statement updateStatement = conn.createStatement();
           //Recycle all local variables created in this loop
                UNID = null;
                updateSQL1 = null;
                updateSQL2 = null;
                updateSQL = null;
                updateStatement = null;

          //Clean up after myself
          System.out.println("End Import agent.... " + rows + " rows have been processed.");

      } catch(Exception e) {
      Session session = getSession();
    Database db = session.getCurrentDatabase();
    Agent agent = db.getAgent("Send Email upon agent failure");;
    catch(Exception f){}

No comments:

Post a Comment