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 = "jdbc:oracle:thin:@10oratst07.companyname.com:port#:database";
          DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
          Connection conn = DriverManager.getConnection(cs, "username", "password");
          
          //Quit if no connection, write status to console
          if(conn!=null){
                 System.out.println("Connected to Oracle");
          }         
          else {
                 System.out.println("Not connected to Oracle");
                 System.exit(0);
          }
          
          //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()
          while(rs.next()){
                Document doc = db.createDocument();
                doc.replaceItemValue("Form", "Oracle Invoice");
        COPY MORE COLUMNS TO FIELDS
        //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"); }
         doc.save();
         //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();
           updateStatement.executeUpdate(updateSQL);
           rows++;
                               
           //Recycle all local variables created in this loop
           invoiceDate.recycle();
                dueDate.recycle();
                doc.recycle();
                UNID = null;
                updateSQL1 = null;
                updateSQL2 = null;
                updateSQL = null;
                updateStatement.close();
                updateStatement = null;
       }

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

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

No comments:

Post a Comment