How to launch an attachment from a view control

I need to show a visual indicator whether a document in a view control had an attachment.   I wanted the visual indicator to act as a link open the attachment.   I could not find anyone on the net who did this just the way that I needed it, so I had to make it myself.

Here is how it works:

The document view control looks like this to the user.  I download the paper clip from a free clip art site.

When the user clicks the link, it launches the attachment.  Amazingly it actually works better in Internet Explorer.  It launches a blank window in the background, and the the File Download dialog box with the option to Open Save or Cancel.  I made the blank window to be height=120,width=650.

In Chrome, the same window is launched, but looks like this.   The user has to choose to download the file, then launch.   If I didn't make the window 650 pixels wide then the user would have to expand the window to see the Keep/Discard question.  The user would then have to double click on the file to open it.

How did I code it:

There are two main places where I had to add code.

1) Before I added anything, I needed to give a variable name in the view control.   This gives me a handle to each particular row.

2) The column data is bound to a null value ("").  This necessary to prevent errors.

3) Add the following function to your SSJS script library.  Credit for this belongs to Stephan Wissel.  I found this on his blog.  I modified it a bit to account for storing my data in a separate database.

function getAttachmentURL(docID:java.lang.String, attachmentName:java.lang.String, databaseName:java.lang.String) {
    var base = databaseName
    var middle = "/xsp/.ibmmodres/domino/OpenAttachment/";
    if (base.substr(0,4) == "/xsp") {
        middle += base.substr(4);
    } else {
        middle += base;
    }
    var result = base + middle + "/" + docID + "/$File/" + attachmentName + "?Open";
    return result;
}


4)  In the View Column properties under Display, I added code to the Column Image.  In my case the filename is always the same.   This only shows the image if the attachment is present.   In this project, the attachment is required, but I wanted to make it reusable for cases when it isn't required.


var doc:NotesDocument = rowValue.getDocument()
var attachment = doc.getAttachment("storetransfer.pdf")

if (attachment === null ){
return ""
}
else{
return "/attachment.gif"
}


5)  In the onClick event of the column, you have the following code tied to a Full Update.  If you don't know the postScript method, then definitely read up on it.   It allows you to run clientside javascript after serverside javascript processes.  It is a very handy tool in your toolbox.


var doc:NotesDocument = rowValue.getDocument()
var attachment = doc.getAttachment("storetransfer.pdf")

if (attachment === null ){
//print("no attachment")
//do nothing here
//theoretically this should never happen since attachment is required
//in addition, the paperclip icon is hidden.
}
else{
var unid = rowValue.getUniversalID()
var url = getAttachmentURL(unid, "storetransfer.pdf", "database.nsf")
url = "/" + url + ";"
view.postScript("window.open('" + url + "', '_blank', 'height=120,width=650,top=10,left=10,resizable=yes');");
}

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.

Hello World

Welcome to my new blog where I will post about what I am working on in IBM Notes.   Notice that I didn't say Lotus Notes.  It is now known as IBM Notes.   The name change was long overdue in my opinion.

I never really considered having a blog before but I was inspired by David Leedy who outlined several reasons why I should have one in episode 100 of NotesIn9.  He wasn't talking specifically to me, but I took it that way.   I recently just figured out something at work, that took me a while to do, and it crossed my mind that it would be good for me to share what I have done.

I am not new to blogging in that my family has/had a blog for a few years.   In a nutshell, my family spent 14 months travelling the USA in an RV and during that time we maintained a blog to keep friends, family, and often total strangers up to date on our adventures.   My wife did 99% of the writing, and I did all of the layout.   We gained much knowledge from reading blogs of previous fulltiming RV families, that one of our goals with the family blog was to write in a way that would help others who came after us.   It is that same spirit that inspires me to make this blog.

Steve Zavocki