Wednesday, February 20, 2013

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
                 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();
          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()
         Document doc = db.createDocument();
          COPY DATA HERE
           String UNID = doc.getUniversalID();
                 doc = null;
           rs.updateString("NOTES_DOC_ID", UNID);
            rs.updateRow();  THIS GIVES EXCEPTION: Insufficient Priviledges 
          //Clean up after myself

      } catch(Exception e) {

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.

No comments:

Post a Comment