SnTT: JDBC in Notes

I just finished with my first use of JDBC in a Notes application to access DB2 and thought I'd share. Initially, I looked at ODBC, but that required setting up a connection in the ODBC Data Sources on the client machine and I really wanted something that would "just work" without having to do anything to the client machines. I had seen a while back some code to automatically create that Data Source but was never able to get it to work in our environment.

First is the Java agent that makes the connection and performs the query in DB2.

import lotus.domino.*;
import java.sql.*;
import java.util.*;
import javax.swing.JOptionPane;

public class DB2Lookup extends AgentBase {

 private static String dbuser = "userid";
 private static String dbpwd = "pswd";
 private static String _url = "jdbc:db2://fully.qualified.server:db2portid/dbname";
 private static String _drv = "com.ibm.db2.jcc.DB2Driver";
 private java.sql.ResultSet rs;
 private java.sql.Statement stmt;
 private java.sql.Connection con;

 public String[] NotesMain(String tableID, String custNum, String custName) {
  Vector vec = new Vector();
  try {
   Class.forName(_drv);
   Properties p = new Properties();
   p.put("user", dbuser);
   p.put("password", dbpwd);
   con = DriverManager.getConnection(_url, p);
   stmt = con.createStatement();
   rs = stmt.executeQuery("SELECT DISTINCT NAME,CUSTNUM FROM SCHEMA." + 
   tableID + " WHERE CUSTNUM='" + custNum + 
   "' AND UPPER(NAME) LIKE('%" + custName.toUpperCase() +
   "%') ORDER BY NAME");
    while (rs.next()) {
    String sN = rs.getString("NAME");
    String sO = rs.getString("OBLIGOR");
    vec.add(sN + "" + sO);
   }
  
  } catch (SQLException se) {
   // Inform user of any SQL errors
   System.out.println("SQL Exception: " + se.getMessage());
   se.printStackTrace(System.out);

  } catch (Exception e) {
   e.printStackTrace();

  } finally {
   try {
    rs.close();
    stmt.close();
    con.close();
   } catch (SQLException se) {
    System.out.println("SQL Exception: " + se.getMessage());
    se.printStackTrace(System.out);
   }
  }
 if (vec.isEmpty()) {
  vec.add("NOTHING");
 }
 String forms[] = (String []) vec.toArray( new String[vec.size()]);
 return forms;
 }
}

The agent takes parameters passed to it, executes the DB2 query, processes the result set and creates a string array to return to the caller. The biggest stumbling block I hit was handling queries that returned no records. Until I hit on adding the "NOTHING" entry if the vector came back empty, I kept crashing my Notes client. So beware!

IBM has a JDBC Type 4 driver available for free re-distribution as part of your application. You can find it here (IBM registration required, and this is for DB2 UDB, Cloudscape and Derby. UPDATE: The link points to the version is for Linux/Unix/Windows; not sure if there are drivers for other OSes). The db2jcc.jar and db2jcc_license_cu.jar archives need to be attached to your Java agent (use the Edit Project button to do this).

Here is the LotusScript agent that calls the Java agent.

' Declarations
Option Public
Option Declare
Use "DB2Lookup"
Uselsx "*javacon"

Sub Initialize
 Dim docThis As NotesDocument
 Dim myClass As JavaClass
 Dim myObject As JavaObject
 Dim mySession As JavaSession
 Dim ws As NotesUIWorkspace
 Dim IDs As Variant
 Dim stat As Variant

 Set ws = New NotesUIWorkspace
 Set docThis = ws.CurrentDocument.Document
 If docThis.CustName(0) = "" Or docThis.CustNum(0) = "" Or docThis.TableID(0) = "" Then
  Messagebox "You must have entries in the Customer Name, Customer Number
and Table fields before using this function",16,"Error"
  Exit Sub
 End If

 Set mySession = New JavaSession()
 Set myClass = mySession.GetClass("DB2Lookup")
 Set myObject = myClass.CreateObject
 IDs = myObject.NotesMain(docThis.TableID(0),docThis.CustNum(0),docThis.Borrower(0))

 If IDs(0) = "NOTHING" Then
  Messagebox "No customers found meeting your criteria; please revise and try again",
64,"Customer Lookup"
  Exit Sub
 End If
 If Ubound(IDs) > 0 Then
  stat= ws.Prompt(PROMPT_OKCANCELLIST,"Select Customer","Select the Customer 
from this list:","",IDs)
  If stat = "" Then Exit Sub
 Else
  stat = IDs(0)
 End If
 With docThis
  .CustName = Trim(Strleft(stat,""))
  .CustNum = Trim(Strright(stat,""))
 End With
 Call ws.CurrentDocument.Reload
End Sub

This LotusScript was converted to HTML using the ls2html routine,
provided by Julian Robichaux at nsftools.com.

The agent makes sure there are entries in all the fields passed for the JDBC call. If no records are found, the user is informed. If one record is found, the fields are updated automatically. If more than one record is found, a dialog box is presented from which the user can choose an entry.

Who says Notes/Domino is a closed environment? Now go forth and JDBC!

Technorati: Show-n-Tell Thursday
Categories: Show-n-Tell Thursday_