Wednesday, February 13, 2013

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.

1 comment:

  1. You could do this with Notrix if you run two jobs chained together. The first job would select records from Oracle and create new documents in Notes. Then the second job would reselect the new documents and export the date and UNID back to Oracle matching on a unique key in the Oracle table. This would take less than 30 minutes to setup without any programming. Notrix also comes with a REXX scripting language which could handle it as well.

    ReplyDelete