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){}
                   
       }
   }
}

1 comment:

  1. Thanks for the post, I am techno savvy. I believe you hit the nail right on the head. I am highly impressed with your blog. It is very nicely explained. Your article adds best knowledge to our Java EE Training in Chennai. or learn thru Java EE Training in Chennai Students.

    ReplyDelete