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.