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) {
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.
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 ThursdayCategories:
Show-n-Tell Thursday_
