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.