Insufficient Access Exception when adding new column to SQL statement

I had a strange thing happen today.   I needed to add a new column to an existing java agent that uses JDBC to pull from Oracle.  

I had two agents that do the same thing except pull from different tables.   Yesterday I added the new column to the first agent and everything ran fine.   Today I was fixing the second agent, and after adding the new column, I got  an "Insufficient Access Exception" from Oracle.   I added it the exact same way as I did to the first agent. 

After trying all sorts of things and talking with the DBA, he moved the column to a different place with the statement and it worked.   Crazy!!   Before changing it, the statement worked when you copied it and ran in an TOra Oracle client.   For some reason, changing the order fixed the issues and the column read fine and caused no exceptions.  

When I first added the column, I added to the end.  Moving towards the beginning of the statement fixed the issue.   Note:  there was no comma after the last column, and there was a space before the FROM.  The syntax was correct.

Lesson:   In JDBC, changing column order can sometimes fix strange exceptions.   Why:  who knows.

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

updateRow() just does not work

I spent the last few days trying be able to update to Oracle.  I spend a lot of time reading the Oracle site and following instructions on how to write to a result set.   The reason why this was necessary was that the data I was writing to didn't really have a primary key.   I was reading a view, not a table.   I did learn quite a bit more about Oracle, and JDBC as I kept trying everything to get it to work.   After working closely with the DBA, I was able to convince him that he needed to add a unique identifier to the view so I could use an UPDATE statement as opposed to using a cursor.

I am going to use this post to explain what didn't work, and when complete post the working code in another post.  If someone finds this post, looking for the resolution, I am sorry to inform you that we were not able to fix it, but create a workaround that works better.   I created this Stack Overflow to get help, but no one was able to give me an answer:

My goal here was to update a Notes Unique ID back to the table as a way of tying the two records together as well as to mark that I had pulled the row.

Here is a stripped down version of what I tried to do:


import lotus.domino.*;
import java.sql.*;
import java.util.Calendar;
import java.util.Date;
import oracle.jdbc.*;

public class JavaAgent extends AgentBase {

    public void NotesMain() {

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

          //Establish connection to Oracle
          System.out.println("Import agent starting...");
          String cs = 'Connection string here'
          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();
        
          String fetch = SQL statement;  
                    
          //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);
                                       
          //With Oracle, the only way to navigate through a result is forward using next()
          while(rs.next()){
         Document doc = db.createDocument();
          COPY DATA HERE
           doc.save();
         
           String UNID = doc.getUniversalID();
                 doc = null;
                       
           rs.updateString("NOTES_DOC_ID", UNID);
            rs.updateRow();  THIS GIVES EXCEPTION: Insufficient Priviledges 
          System.exit(0);
          }
                    
          //Clean up after myself
          rs.close();
          statement.close();
          conn.close();

      } catch(Exception e) {
          e.printStackTrace();
       }
   }
}

The exception that I got was java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges.
It looks like a security exception, but I was ensured that I had full access to write.  At one point, I was given DBA access and it still gave the same error.  I spend a two days trying many different things before we changed our approach.

Updating a date column in Oracle using JDBC

Here is something bizarre that I discovered today:   When you need to update an Oracle date type field in a JDBC result set, you must feed it a java.sql.Date object and not a java.util.Date object.

I couldn't figure out why my code wouldn't compile, the compiler said it was expecting a String and a Date, which is what I was giving it.  A google search revealed that it is expecting a different Date object   Unlike the java.util.Date, the java.sql.Date only contains the Year/Month/Day.

So, after figuring all this out, I got eclipse to compile and then went and talked to the DBA, who then says, ohh, I should of told you that you don't need to send the date, I will create it when you sent the UNID.   This blog post is the result of 90 minutes spent doing something that I didn't need to do, but I certainly learned something from it.  Hopefully someone down the road can benefit from this.

Here is the code:

How to construct a java.sql.Date.  Note: that you need to be specific which Date you are constructing.
Calendar cal = Calendar.getInstance();
java.sql.Date today = java.sql.Date.valueOf(cal.get(cal.YEAR) + ":" + cal.get(cal.MONTH) + ":" + cal.get(cal.DATE));


Using the java.sql.Date object:  (This compiles!)
rs.updateDate("ORACLE_DATE_COLUMN", today);

I guess if you needed the date and time, you would have to write to two columns using updateTime().

Connecting Notes to Oracle using JDBC

Today I learned something about Oracle and other relational databases that I didn't know before, I will get that at the end.   I am currently in the process of writing a java agent using JDBC to pull data from an Oracle view.   We decided that Notrix wasn't robust enough for what we wanted.   I am going to write the date and UNID of the document back to each row after I pull the data. and we couldn't do it with Notrix the way we wanted.  I was glad because it gave me a chance to write more java.  I resisted the suggestion to use LotusScript with LSDO to accomplish this.  I also resisted using ODBC which was already setup on the server.  I found the DZone Refcard for JDBC as a great reference and explained the different connection types.  I wanted to use a Type 4 connection, and after talking to the DBA he agreed that this was the best approach.

Before I got too far, I started having strange out of memory exceptions.   I thought this was because in my agent I included too many jars in my archive folder, so I trimmed them down to the one I needed and this seemed to help.   It ran unsuccessfully a couple times (which was expected) then started giving the "out of memory" exceptions again in the console.  The Notes agent log told me:  "JVM: Attempt to retrieve Java agent attachments failed"   So naturally I googled the message, and found this StackOverflow that solved my problem.  I can't say enough about how helpful stack overflow is.   I followed the instructions of Simon O'Doherty and added the ojdbc6.jar file to the Notes jvm\lib\ext folder and restarted Notes and that fixed that issue.

I am happy to say that I was able to get a working connection before noon on my first day working on this.    Once I made the connection I started playing and retrieving queries and writing them to the console.   I used the info on this page as a reference.   On this I tried was to user the last()method to test the result set.  This caused an exception that read "Invalid operation for forward only resultset".  I was only able to navigate the result set using the next() method.  Later I asked the DBA why this was the case, and he gave me a 30 minutes explanation of how Oracle works, and how other databases like DB2 and SQL Server work, good stuff.  Basically, Oracle stores the blocks of information in a haphazard way all over the storage space.   There are benefits to this, in that you can read rows that are open for updating.  The reason I got the error message is that you simply can't get to the last row directly in Oracle, you have to go forward one at a time.   This was how I was planning on reading the result set anyway, but I now have a much better understanding of how Oracle works.

I am right in the middle of coding but I intend on posting my code in another blog post.   BTW: I found this blog post very useful for getting started.