Lotus Notes

Web Service is very useful method for the interaction with other machines over network.To leverage web services in Domino, a New feature called Web Service is added in R7 onwards. From R8 onwards this feature is further divided into two sections "Web Service Providers" and "Web Service Consumers". With both the options, you can create web service which can be read by other platform and vice versa. Domino can consume web services written on other platform.

In this post , I will create a web service using Domino 8.5 and will read that web service in my Eclipse application. Let's begin with the example.

I've a Lotus Notes application which keeps airlines travel availability configurations. Let's say my form name is "CheckMyTravelDate" with five fields; Departure date,Return date,From country,To country and availability status.Here is my form ,

Next, Create a view which will be used for look-up in our web service code.It will have two columns; First column is with key "Start Date-End Date-From-To" and second column is "Availability Status". We need to make first column categorized. Shown below,

Let's begin with Web Service.

Since we're going to provide Web Service, so click "New web service provider" button under "Web Service providers" section.

You will have two options to write web service ; Lotus Script and Java. Choose "Lotus Script" (default). Paste following code in your web service ,

Option Public Option Declare %INCLUDE "lsxsd.lss" Class getTravelStatus Function getTravelStatusString(StartDate As String, EndDate As String, Departure As String, Returns As String) As String Dim s As New NotesSession Dim db As NotesDatabase Dim view As NotesView Dim doc As NotesDocument Dim key As String Dim sd As New NotesDateTime(StartDate) Dim ed As New NotesDateTime(EndDate) Set db = s.CurrentDatabase If Not(db.IsOpen) Then getTravelStatusString = "Cannot open travel configuration database " Exit Function End If Set view = db.GetView("CheckMyTravelDate") If view Is Nothing Then getTravelStatusString = "Cannot open travel configuration view " Exit Function End If key = Cstr(sd.DateOnly)+"-"+Cstr(ed.DateOnly)+"-"+Departure+"-"+Returns Set doc = view.getDocumentByKey(key) If doc Is Nothing Then getTravelStatusString = "Sorry, Booking is not available with specified details" Exit Function Else getTravelStatusString=doc.GetItemValue("availabilityStatus")(0) End If End Function End Class

What my code does :

It's pretty simple program implemented using class called getTravelStatus which has one function called getTravelStatusString.This function takes four arguments;Travel start date,end date,departure country and return country. It builds the key , pass to the view,get the availability status and returns to the calling function.

Now when we try to save web service, we will get following warning


Warning says, your class name should match with PortTypeClass.Open web service property window and paste your class name in PortTypeClass section.


Go to third tab of web service property window and select Programming model as RPC and SOAP message format as RPC/encoded.At the end specify other details like Port type name , service element name and Service port name which are optional parameters.Save your changes.



Now you've to run your web service on browser using ?WSDL ( http://server/database/getTravelStatus?WSDL ) command to verify everything is fine.If everything is fine, you get screen like,


You've successfully created web service, now time to test.

There are various method to test web service . I'm going to use Eclipse and the Web Tools Platform (WTP). To do so , first install Eclipse and then configure WTP in your machine.

Once installed and WTP configured is done, Open Eclipse and go to Run->Launch the Web service explorer. Click on "WSDL" page icon on top right top open WSDL window.
(or follow the same step mentioned here)


Click on "WSDL main" at left panel and provide your web service path at right panel and click on "Go".


You can check the status at lower panel whether your web service is successfully opened or not.Click on your web service name under "Operations" section.You can find the form to enter , Travel start date, end date,to country and from country . Enter data and click "Go",


So these were the basic steps to implement and test web services in Lotus Notes.

Write me for any suggestions/comments.

It's always a challenging job to build a system which communicates with Oracle.From past few years I was working only on web applications.Two days back I've asked to suggest best method to move Lotus Notes documents to Oracle without using local or server level DSN.

I was very much familiar with Lotus Notes ODBC method , which is very simple and easy to implement. However it's not very good for large volume of data,though I was not suppose to consider because it needs DSN.Next,I've considered LC LSX but assumed it needs DSN too ( point me if I am wrong ),You don't need any DSN in LC LSX method(look at the comments section for more information).Finally I thought about JDBC which is Java based engine to connect Oracle database.Funny thing was that, I studied Java in my graduation and never ever used in working environment except some simple domino java agents. I've checked with my mates and over the net, but didn't got much to start. So, I had decided to do by myself and with help of my wife ( Rashmi, who is Java developer :) ).

My first task was to start looking correct versions of software which I need along with suitable drivers to connect them.Let's have a look what I've done.

First, My wife suggested me to setup Java environment in my workstation.I've done by knowing Domino itself built on Java.

Java JDK

Next, I had started looking to Oracle versions which should be lite and easy to use for newbie.I've chosen Oracle 10g express edition which was up to my expectation and could easily install in my machine.Here is the one,

Oracle 10g Express

Third, I needed correct jdbc driver which should be compatible with Oracle 10g . Here is the one which I've used,

JDBC drivers

Domino-Java coding starts from here,

Create new Java agent and click on "Edit Project" to link jar file in your agent. Find your .jar file and add in "Current Agent files" list at the right side.

Next, you need to start importing required classes for connectivity . Add below mentioned classes at the beginning of the agent along with "lotus.domino.*;" package.

import java.sql.*;
import oracle.jdbc.*;

Next step is to register our jdbc driver . To do so , Add this line in your code.

// Register your driver

DriverManager.registerDriver (new oracle.jdbc.OracleDriver());

Once driver is registered we need to establish a connection with the Oracle. Here is the code which establish connection with Oracle.This is the most important line which needs little bit knowledge of Oracle otherwise you might struggle like me :)

// Build connection with Oracle
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@SPRPRG020V.ap.vdgc.com:1521:XE",
"SYSTEM", "password");

Some important points about the above line,

* I am using thin driver which is also called level 4 driver. There are one more called OCI driver also known as level 2 driver. Oracle thin driver is entirely written in Java so no other software need to install to use this driver where as Oracle OCI driver requires Oracle client to be installed in order to use this driver.So,When you need to connect Oracle remotely without installing Client , go for Oracle thin driver.

* Passing "Host name:Port:SID"SID is nothing but a "Oracle System ID".I've spent 1 hour in this line to pass correct host name,port number and SID. To make it quicker and easier , Open "tnsnames.ora" file where Oracle runs . It can be found in C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN ( for my example ). E.g,

* Last argument is log-in user name and password of Oracle database.

Now your connection is established. To test this create table in Oracle and fire some SQL statements. Something like,

// Query the Customer table
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery ("SELECT cust_name FROM Customer");

// Read something and print to console , getString to get & setString to set
System.out.println("Printing existing records ...");
while (rset.next())
System.out.println (rset.getString(1));

System.out.println("Inserting new records ...");
// Insert example
stmt.executeUpdate("INSERT INTO Customer VALUES('001','Rishi')");

You can view the output in Java console ,

Even in Oracle web console ,

Here is the complete code,

import lotus.domino.*; import java.sql.*; import oracle.jdbc.*; public class JavaAgent extends AgentBase { public void NotesMain() { try { Session session = getSession(); AgentContext agentContext = session.getAgentContext(); lotus.domino.Session s = getSession(); lotus.domino.AgentContext agentContex = s.getAgentContext(); // Register your driver DriverManager.registerDriver (new oracle.jdbc.OracleDriver()); // Build connection with Oracle using thin driver Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@SPRPRG020V.ap.vdgc.com:1521:XE", "SYSTEM", "password"); // Query the Customer table Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery ("SELECT cust_name FROM Customer"); // Read something and print to console , getString to get & setString to set System.out.println("Printing existing records ..."); while (rset.next()) System.out.println (rset.getString(1)); System.out.println("Inserting new records ..."); // Insert example stmt.executeUpdate("INSERT INTO Customer VALUES('001','Rishi')"); // close the result set, statement, and the connection rset.close(); stmt.close(); conn.close(); } catch(Exception e) { e.printStackTrace(); } } }

One more thing which I forgot to mention is that I've used Eclipse to write my code because Domino doesn't provide type ahead for methods/property.

Though it's just a POC to kick off my project , But definitely got some confidence to start my first Java project in Domino. Hope I can write all my learning here to help other Domino developers to start quickly with Java. I would love to see your suggestions/comments.

Rob and pete has suggested me to use LC LSX method in comments section of this post . So, Written the sample code for connectivity using LC LSX and it works well . Here is the code who wish to use as a reference ,

'Adding LC LSX library Option Public Uselsx "*lsxlc" Sub Initialize On Error Goto handler Dim connect As New LCConnection ("Oracle") ' You can pass Db2 or odbc2 for SQL Server Dim fldLst As New LCFieldList Dim keyLst As New LCFieldList Dim fld As LCField Dim count As Integer 'connect.database="XE" 'Don't use this method because it doesn't work connect.Server = "XE" ' I've Oracle express edition 10g . To check server name open "tnsnames.ora" file and check SID connect.Userid = "SYSTEM" ' Id connect.Password = "password" ' Password connect.Metadata = "customer" ' Table name connect.Connect Print "Successfully connected to Oracle...." connect.FieldNames = "cust_no,cust_name" ' Two columns in customer table Set fld = keyLst.Append ("cust_no", LCTYPE_INT) fld.Flags = LCFIELDF_KEY_NE Or LCFIELDF_KEY fld.Value = 200 If (connect.Select (keyLst, 1, fldLst) = 0) Then Print "No data found." End End If Set fld = fldLst.Lookup ("cust_name") Print "The 'contact names' stored in the table are:" While (connect.Fetch (fldLst) > 0) count = count + 1 Msgbox " record #" & Cstr(count) & " = '" & fld.text(0) & "'" Wend If (count = 0) Then Print "The table contains no records." Exit Sub handler: Msgbox "Connection failed with error " & Err & ": " & Error Exit Sub End Sub

To be continued....