Lotus Notes / Domino integration has high interest among developers and IT specialists.  We are publishing here small articles on Microsoft Business Solutions Great Plains, Microsoft CRM, Navision plus Oracle, SAP, PeopleSoft and Lotus customization and integration.  This is specialization of our company.

I would personally like to say thank you to Greg Griffiths and and http://builder.com.com  for this technical information

Andrew Karasev, Chief Technology Officer, Alba Spectrum Technologies, LLC ( http://www.albaspectrum.com ).  We serve clients in Chicago, New York, Detroit, Phoenix, Atlanta, San Francisco, Denver, Los Angeles, San Diego, Houston, Dallas, Miami, Montreal, UK, Germany, France, Russia, Brazil, Venezuela, Argentina, South Africa, Australia, New Zealand.  You can contact Andrew: help@albaspectrum.com

USA: 1-630-961-5918, 1-866-528-0577, Europe: +49 231 4387600

Alba Spectrum Technologies

Lotus Notes/Domino integration: ODBC connection – tips for programmer

by Greg Griffiths

Unlike other databases, such as Oracle and SQL Server, Notes doesn't have a relational database structure. This meant that I couldn't just create an ODBC connection from the ASP Web site I was creating and get access to the data that way. At least that's what I thought.

NotesSQL
While scouring the Lotus Notes Web site, among many others, to try and figure out how to accomplish my task, I came across a reference to an application called NotesSQL. NotesSQL is an ODBC driver for Lotus Notes databases, presenting them—at least at the basic level—in the same way as any relational database. The NotesSQL ODBC driver is available for download from the Notes SQL part of the Lotus Web site. There, you'll also find a Samples Kit with examples and detailed developer information.

Configuring NotesSQL
The NotesSQL application comes in two distinct components, both of which must be used in conjunction to get a working connection:

• A thick client Admin and Management tool
• The ODBC driver

The first couple of topics that we need to address are contained in the thick client Admin tool. This tool is concerned with the management of the Notes servers that are available for a connection and the Notes IDs that can be used to connect to these servers.

By default, the tool is installed into the Lotus Application/NotesSQL folder in the Programs menu. Your first step is to register the Notes.ini file with the Admin tool so that all the servers in that file can potentially be accessed via an ODBC connection, as shown in Figure A. If you want only a limited number of servers within your environment to be accessible in this way, you can create a Notes.ini file containing only those server names.

The next task is to select the Notes ID files that should be used to connect to those databases. Click on the Add User button and point the tool to the relevant Notes ID for that user (see Figure B). It's probably safest to create a new Notes ID file for each application you develop or each Notes system you want to interact with, and then grant that account the minimum rights—usually Read Only if all the application needs to do is retrieve data from a Notes database.

This ensures that you're not using a live user account in which the password could change and that you're using the minimum access on the account to achieve your requirements—any more could potentially be a security risk. Add as many users as are required using the Add User button. When you've completed the list, click the Save List button.

The final step is to create an ODBC connection object, or DataSource Name (DSN), in the normal way; your application will use this object to connect to the Lotus Notes database, as shown in Figure C. You can use the ODBC connection object in your code in the same way as you would use an ODBC connection to any other database, such as SQL Server or Oracle.

You must start by selecting the NotesSQL driver—the highlighted line in the dialog at the back of the screenshot. You are then presented with the dialog box in the foreground asking you to provide the details about the database this ODBC connection is designated for; the scanning of available Notes servers can take some time at this point.

Upon selecting the Notes server that you want to connect to, you may be asked to provide a Notes password for the account that has been listed in the Admin tool. Remember that only those IDs listed in the Admin tool can be used in ODBC connections. Finally, add a name by which your code can refer to this ODBC connection and a short description about what it is used for.

Configuring the Lotus Notes application
The NotesSQL driver will display all the Notes Views and Forms that it can see within the specified Notes database as simple database tables. This allows you to use standard SQL when querying the connection. As such, if you need to do anything fancy with your data, I strongly recommend that you either build it into your Notes View design or into the application you're creating rather than attempting to do it using complex SQL.

I decided that the best way to interact with the driver was to create a new View(s) specifically for my application. Because the driver uses the Programatic Name of each column, I had to ensure that these were all set and not left to the default values. The driver itself has some limitations, including:

• No handling of the UPPER function to do case conversions
• A limit of 256 characters for a string
• Inability to handle nonalphanumeric data in the View names

Creating an ASP page
The application I was creating was written in Classic ASP, so I had a relatively simple job to create an ASP script that would be able to interact with the Notes database in question. Connecting to a Notes database in this way is identical to connecting to any other database.

For example, if I need to retrieve the user name and password for a given user, and this information is stored in a Notes View, I can use the code shown in Listing A. Using an ASP similar to the one in the listing, I was able to connect and retrieve the information from the Notes database that was required for the first part of the project.

Server-side interaction
Although it's a little more work to set up, configure, and manage, you can connect to a Lotus Notes system and have it interact with some application code on the server side of a Web application, similar to the way you would in any standard relational database, such as SQL Server or Oracle.