Thursday, June 22, 2006

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:
Categories: Show-n-Tell Thursday_

7 comments:

nick wall said...

Probably a simple question...I am getting a licensing error :

SQL Exception: The version of the IBM Universal JDBC driver in use is not licensed for connectivity to QAS databases. To connect to this DB2 server, please obtain a licensed copy of the IBM DB2 Universal Driver for JDBC and SQLJ.

From my initial Googling, it appears that the problem is that the db2jcc_license_cu.jar has not been included.

I have included this in my Java Project (Script library).

Any ideas?

Don McNally said...

Nick, I'm not sure if there is a simple answer. A couple of things to check though:
- The link to the IBM site that I included is for the driver for Linux/Unix/Windows. Is that the OS your DB2 server is on?
- I'm not sure how it happened because I didn't do it manually, but the db2jcc and license jars are in my CLASSPATH.

Beyond that I'm not sure what to say. Let me know if either of these things help and we can try to go from there.

Don

nick wall said...

I'm testing from a windows XP machine, and connecting to DB2 on an AS400.

I actually didn't put all of the error message in my last post, which was giving me the answer!:

SQL Exception: The version of the IBM Universal JDBC driver in use is not licensed for connectivity to QAS databases. To connect to this DB2 server, please obtain a licensed copy of the IBM DB2 Universal Driver for JDBC and SQLJ. An appropriate license file db2jcc_license_*.jar for this target platform must be installed to the application classpath. Connectivity to QAS databases is enabled by any of the following license files: { db2jcc_license_cisuz.jar }

Pretty sure this is telling me that I need to add db2jcc_license_cisuz.jar to my project, rather than the db2jcc_license_cu.jar

At : http://www-128.ibm.com/developerworks/db2/library/techarticle/0307zikopoulos/0307zikopoulos.html

under the Important section :

For DB2 UDB for iSeries® and z/OS servers (provided with DB2 Connect and DB2 Enterprise Server Edition): db2jcc_license_cisuz.jar

I have used the type 2 driver before, but in the FAQ on the page above it mentions that if DB2 on same machine, stick with Type 2, if on diff' machine they have basically the same performance.

General Ramble...
I do a lot of integration with our ERP system on an AS400, using LC LSX and a bunch of hand written. I wrote some test java code (type 2 driver), which was being called on a Win 2003 server, but this didn't do anything for me performance wise, so I am in the process of switching over to stored procedures...but...a week or so ago, I bumped into this post on the enterprise forum http://www-10.lotus.com/ldd/eiforum.nsf/DateAllThreadedweb/d7c755bb5e8d08618525718f0070bb5c?OpenDocument
where Charles has done way more than me in attempting to reduce dat a retreival times.

I was interested to see if the Type 4 driver could reduce my retreival time, but it looks like the FAQ mentioned above is telling me know.

...General Ramble over.

I should still try and get hold of the db2jcc_license_cu.jar though.

But thanks, great post Don.

Don McNally said...

Glad it seems like a simple solution. I'm not really sure of the performance of each of the driver types. I haven't had a need to go any deeper than what is in my post, and that seems to work for us at the moment. I agree that a stored procedure would be more efficient if there were reasonably complex operations to be performed on the data - assuming you could do it in a procedure - because there is less traffic. But every situation is different. Thanks for reading!

Anonymous said...

How can I get db2jcc_license_cisuz.jar file? WOuld you mind sending me a copy of that file to baijingyu at hotmail.com ? Thanks.

Don McNally said...

I haven't been able to find it. I found this at the IBM site: http://www-1.ibm.com/support/docview.wss?uid=swg21191319. You may be able to locate it through a search engine. Sorry but that is the best I can do.

Don

Willem Souwer said...

The link to the download page for the JDBC driver is no longer valid, it returns "2004-09-20 10:09:21.003415R download was not found in the database".
It happens.