Lotus Notes/Domino RDBMS integration using ODBC

June 23 20096 Commented

Categorized Under: Lotus Notes

This tutorial is based on the following software environment.

Windows XP Professional, Lotus Notes Client 6, Domino Server 6, MySQL 5, MySQL ODBC Driver 5

Let us start with a case study. The company’s employee information is kept in RDBMS. When a user opens Employee form in Notes and input employee id as key, the employee details such as name, age, designation etc are imported from RDBMS. 

ODBC stands for Open Database Connectivity developed by Microsoft. It’s a set of APIs where user can access any RDBMS without relying on vendor specific APIs. The vendors who provide support for ODBC distribute ODBC drivers which should be installed in windows system. It’s again Microsoft so may not work well in other OS environments. Some ODBC bridges in other OS like Linux are available which may not be quite stable.

I am using Windows XP Professional and Lotus Notes 6 client. There are so many RDBMS available in market some free, some other commercial. For our tutorial let us install MySQL which is a popular open source RDBMS.

Go to http://dev.mysql.com/downloads/ and download MySQL community edition for Windows. Select Windows Essentials.

Double click on the installer and click Run.

mysql_1

Click Next. Select setup type as Typical which is the default option. Click on Install. Click Next, Next and uncheck the option Register MySQL server now. Ensure that Configure the MySQL server option is selected. Click finish button.

mysql_2

Click Next and select Standard Configuration.

mysql_3

Ensure that Install as Windows service and Launch the MySQL server automatically options are selected. Click Next.

mysql_4

Enter password for new root password and confirm. Do not enter anything in Current root password. Click Next.

mysql_5

Click Execute. Finally click finish.

Now MySQL is installed in your machine. Now we need to ensure that MySQL service is running.

Go to Start–>Programs–>MySQL–>MySQL Server <version>–>SQL Command Line Client. It prompts password.

mysql_client

Enter password as password. If you have given any other password during MySQL installation, give the same. Now you are taken to MySQL console where you may execute various commands.Let us create a database. I am not getting into more details of database design, tables, field data types, relationship etc. If you are new to RDBMS, there are numerous sources available in net for your reference. Now enter the following command.

create database mydb;

Hit enter. Ensure that database is created by executed following command.

show databases;

If the list contain mydb, then our database is ready. Now switch to our database by executing

use mydb;

create_database

Create employee table. Execute the following command at console and hit enter.

create table employee (
employeeid int not null auto_increment, name varchar(100), age int,
designation varchar(100), primary key(employeeid) );

Make sure that table is created by executing the following command.

show tables;

The above command create employee table with four fields. employeeid is set as primary key with auto increment. This field is used to uniquely identify rows in table. Now create some records in table.

insert into employee (name, age, designation) values ("jack", 25, "programmer");

It insert one record in table. Please note that we have not included employeeid in insert command. This is set as auto increment so it will be set automatically. Now display the list of records by executing select command.

select * from employee;

create_table

Records are listed in table. Just note employeeid column. You may see integers starting from 1 in this column. Just note this field value. We may use this in our Notes form to fetch other column (field) values. Insert more records into table using insert command. Ok! Now our database is ready with one table and some records. Rather than using console client, you may use GUI based clients like SQLyog.

http://www.webyog.com/en/

ODBC driver is required for connecting Lotus Notes with MySQL. Lets download and install the same. Go to  http://dev.mysql.com/downloads/connector/ and download windows installer type.

Please note that ODBC driver should be installed in Lotus Notes Client machine not in server machine if your server is in different host.

Installation is self explanatory which do not require any change. After installation we have to ensure that driver is installed properly.

Go to Start–>Settings–>Control Panel–>Administrative Tools–>Data Sources (ODBC). This may be different for other Windows operating systems like vista. Ensure that your windows userid has got administrative rights. Go to drivers tab and check whether MySQL ODBC driver is listed.

drivers

Now we are going to create System Data Source Name. Go to System DSN tab and click Add button. Select MySQL Driver from list and click Finish. You are prompted to enter DSN details. Let us give mydatasource as Data Source Name. Enter server as localhost since MySQL is installed in our local machine. Enter root in User field and password as password (give the password you have given during MySQL installation). Select the database mydb from the list. Click on test button. If everything is Ok, you get “Connection successful” message. Click OK button to save the details. Click OK to exit from the window.

dsn

Let us get into Domino side. LS:DO, LotusScript Data Object is a LotusScript extension providing ODBC classes. There are mainly 3 classes which are used for ODBC.

  • ODBCConnection
  • ODBCQuery
  • ODBCResultSet

Refer Designer help db for knowing more about these classes.

Create a new database in server Go to File–>Database–>New. Give the file name as test.nsf

create_db2

Open the database in Domino designer. Create new form. In form properties set the Name as Employee. Create a table with four rows and two columns. In table properties set the width of cell as 2.0. Now enter text in left cells for field labels. Type the following as labels from top to bottom in left cells.

  • Employee ID
  • Name
  • Age
  • Designation

Now create fields in the following order from top to bottom in right cells.

  • EmployeeID (Number)
  • EmployeeName (Text)
  • EmployeeAge (Number)
  • EmployeeDesignation (Text)

We have to create one hotspot button for fetching employee details from MySQL. Place the cursor in first right cell just after EmployeeID field and go to Create a Hotspot Button. In button properties give the label as Go.

We need to load LotusScript ODBC extension. Click on button and under options write

Use "*lsxodbc"

options

Under click event select Run as Client then LotusScript. Enter the following code snippet.

go_script

Dim ui As New NotesUIWorkspace
Dim uidoc As NotesUIDocument
Dim doc As NotesDocument
Dim connection As ODBCConnection
Dim query As ODBCQuery
Dim result As ODBCResultSet
 
Set uidoc=ui.CurrentDocument
Set doc=uidoc.Document
Set connection = New ODBCConnection
Set query = New ODBCQuery
Set result = New ODBCResultSet
Set query.Connection = connection
Set result.Query = query
Call connection.ConnectTo("mydatasource", "root", "password")
If connection.IsConnected Then
     'get employee id and fetch corresponding record
     query.SQL="select * from employee where employeeid="+doc.EmployeeID(0)+""
     Call result.Execute
     If result.IsResultSetAvailable Then
          Do
               result.NextRow
               doc.EmployeeName=result.GetValue("name")
               doc.EmployeeAge=result.GetValue("age")
               doc.EmployeeDesignation=result.GetValue("designation")
          Loop Until result.IsEndOfData
     Else
          Msgbox "no record found"
     End If
     result.Close(DB_CLOSE)
     connection.Disconnect
Else
     Msgbox "connection could not be established"
End If

Create a new Action. Name it as Save&Close. Write the formula as

@Command([FileSave]);
@Command([FileCloseWindow]);

Save the form.

create_form2

Now open the form in Notes client. Input employeeid value (it will be integers starting from 1). Click on Go button. Name, Age and Designation values are imported from MySQL. Let us go through the code.

First section is declaring variables. Then we make a reference to current document. Initialize connection, query and result objects and set properties of query and result objects. Now we connect to data source using connectTo method. The first parameter is data source name, second parameter is username and third one is password. If connection is successfully established, we execute SQL query statement which fetches record where employeeid is the one we enter in notes field. If result is returned we get column values and populate corresponding Notes fields.

cheers!

6 responses to “Lotus Notes/Domino RDBMS integration using ODBC”

  1. Pratap says:

    I am getting this error. Help please?

    Error #720 Error msg: LS:DO – ODBC could not complete the requested operation. Error line no 13

  2. Johan van Blerk says:

    Hi thanks for this, how do I update a field in MySQL from a field in Lotus Domino, do you have a Lotus Script Example.

  3. Ashok says:

    Great tutorial thanks a lot….

  4. Jhenie326 says:

    Hi! Thanks a lot, I learned a lot. I was able to connect to MYSQL…atlast!!!again thanks 100000000000x

  5. Simple SEO says:

    Nice Tutorial with detailed screenshots, this helped me a lot, Thanks for sharing !!

Leave a Reply

*