A Lotus Notes database can be connected to an application using an ODBC object with the help of the NotesSQL utility. This tutorial will show you how to set it up.

On a recent project for a client, I was asked to create a database-driven site where one of the databases involved was a Lotus Notes database. 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.

DATABASE GUIDES

New tutorials every Thusday with Database Jumping

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.


Figure A
Notes.ini

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.


Figure B
Notes ID

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.


Figure C
ODBC connection


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.

Related links

Comments

1

Shlomo Ginsburg - 22/12/04

Looks like IBM has removed the ODBC driver (noteSQL 3.02) from their web site.
Any idea how one can get it?
Thank you.

» Report offensive content

2

Ciby - 29/07/05

Trye here http://www.lotus.com/notessql
You will need an IBM id and pwd

» Report offensive content

3

Vadivelan - 25/01/06

Hi ,

I have installed NotesSQL Authentication List Manager 3.0.2.

I have configured the INI file and the .ID file and I have also created the System DSN.

I am not able to figure out where I can see the list of tables and the columns so that I can write a SQL Query...

Request you to treat this as high priority since I have to give a fesability report for one proposal...

» Report offensive content

4

Sam Johnston - 17/08/06

I had a lot of trouble finding it before but managed to download NotesSQL 3.0.2g today from https://www14.software.ibm.com/webapp/iwm/web/reg/download.do?source=ESD-SUPPORT&S_PKG=C81RQNA&cp=UTF-8 which was linked from http://www-142.ibm.com/software/sw-lotus/products/product4.nsf/wdocs/notessqlhome

» Report offensive content

5

Dan - 22/09/06

I have installed and am running everthing fine; however, one of my team mates is getting an error. She can install everything, but when she tries to go into the NotesSQL Authentication List Manager she gets the error message below. I am not sure why. She is running Notes V6.5.50.5334 which is supported. Any input would be appreciated!!

"NotesSQL Authentication List Manager Warning"

"The systems could not properly load the version of Notes it found on this machine."

"Please see the NotesSQL user documentation for a list of supported versions of Notes."

HELP!!!

» Report offensive content

6

ken - 06/10/06

Link no longer valid. On your page listed below to the NotesSQL page the IBM site no longer has what you pointed ot.

http://www.builderau.com.au/architect/database/soa/Create_an_ODBC_connection_to_a_Lotus_Notes_database/0,339024547,320282240,00.htm

» Report offensive content

7

Xishan Shigri - 07/11/06

I am using trial version of Lotus Notes and i am trying to use Lotus NotesSQL but i gives error "could not be loaded due to system error 126 "... can anybody help bee out there??

» Report offensive content

8

old hand - 15/11/06

To solve the error 126 problem, add the NotesSQL directory (usually c:\notessql) and the directory where your notes.ini files is located (Usually c:\program files\lotus\notes) to your path.

» Report offensive content

9

shashank barki - 24/11/06

I m also getting error 126 problem...I have full version of Lotus 6 and NotesSQL 3.02j.If I try to create System DSN in ODBC Data Administrator it shows "error 126".I have installed Lotus in "C:\Documents\......." and NOtesSQL in "C:\NotesSQL"...Please help me...

» Report offensive content

10

Garima Satle - 08/12/06

I want to retrive the Notes data for my window application developed in VB.NET, I am Facing the problems in initial conectivity. My connectivity is based on COM object reference

   Dim Session As New NotesSession()

    Dim Db As NotesDatabaseClass

    Dim view As NotesView

    Dim Doc As NotesDocument

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Session.Initialize()

        MsgBox(Session.ServerName, MsgBoxStyle.Information, "Server Name")

        Db = Session.GetDatabase("", "names.nsf")

        If Db.IsOpen Then

            If Db Is Nothing = False Then

                MsgBox(Db.AllDocuments.Count(), MsgBoxStyle.Information, "No of Documents within Names.nsf files")

                view = Db.GetView("Contacts")

                If view Is Nothing = True Then

                    MsgBox("No record in Database", MsgBoxStyle.Critical, "Problem")

                    End

                End If

                Doc = view.GetFirstDocument

                While Doc Is Nothing = False

                    ComboBox1.Items.Add(Doc.GetItemValue("Name")(0))

                    Doc = view.GetNextDocument(Doc)

                End While

                MsgBox("All Contacts are added into combo.", MsgBoxStyle.Information, "Information")

                Exit Sub

            End If

        Else

            MsgBox("Database is closed.", MsgBoxStyle.Information, "Problem...")

            Exit Sub

        End If

    End Sub

» Report offensive content

11

Garima Satle - 13/12/06

Now i have successfull Connect the Notes database with the ODBC driver and fetch the contacts of the person but unable to insert or modify the contact Please Help me.

Dim Con As ADODB.Connection

    Dim rs As ADODB.Recordset

    Dim i As Integer





Form Load Event

       Con = New ADODB.Connection()

        Con.Open("DSN=Test")

        rs = New ADODB.Recordset()

end of form load event





Button Click Event

 Dim StrInsert As String

        Dim StrDelete As String

        Dim StrUpdate As String

        Try

            StrInsert = "Insert into People values('"

» Report offensive content

12

Johannes Braunias - 29/12/06

Ad commen 9:
(error ... could not be loaded due to system error code 126)
Adding the path to the system variable path = ... really worked out.
However it is necessary to reopen the odbc dialog.
A miracle on a Friday!

» Report offensive content

13

Suhail A B - 13/02/07

Dear Sir
I am Suhail.I studying BCA.I am doing libray project.I want SRS and flow chart for library management.Thank You

» Report offensive content

14

Annon User - 13/04/07

I've never had to add system variables to xp before. To do this you need to right click my computer, select properties, advanced, environment variables, select path and edit. Append your settings and ok/apply.

» Report offensive content

15

williamwalance - 09/05/07

Garima Satle,
may i have your codes so that i can refer.
Thx.
Anyway, i'm facing some problem after i try to connect with Lotus Notes using ODBC.
Here is my error when i try to retrieve data from Notes database using C#.Net

ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause

Besides, did any 1 willing to tell other methods to connect with Lotus Notes instead of using .NET.
thank you. :)

» Report offensive content

16

luis - 18/05/07

I have the same problem as Dan, comment 5.
Does any one now how to correct this error.

"NotesSQL Authentication List Manager Warning"



"The systems could not properly load the version of Notes it found on this machine."



"Please see the NotesSQL user documentation for a list of supported versions of Notes."

» Report offensive content

17

Radha K - 08/06/07

I installed NotesSQL driver, added INI file and id file, then ODBC configuration and lnked server configuration but tables or views are not loaded to my sql linked server. any more configuration or steps required. please help.

» Report offensive content

18

Jerrod W - 10/06/07

I have installed lotus notes on my machine with no domino server.

I created an odbc connection to the .nsf file and used it inside of a .Net windows app with standard code.

This works fine, when I migrate that to an asp or asp.net app it gives the error Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed.

I have followed the tips on http://www-12.lotus.com/ldd/doc/notessql/3.0.2b/nsql302b.nsf/66208c256b4136a2852563c000646f8c/1bbf8eb10f20869b85256c2b00555464?OpenDocument but still no joy.

Anyone have this problem and overcome it?

» Report offensive content

19

Chris - 13/06/07

I was getting an error everytime I tried to add an ODBC connection for Lotus NotesSQL, ODBCAD32.exe would blow up.

This fixed my problem, just renamed the INI's to .BAD
Be sure to keep the one that Notes use's default is listed below

From builderau.com.au

rolofft (IS/IT--Management) 10 Aug 06 17:21
You may have duplicate Notes.INI files. Do a file search for "Notes.INI". You'll probably find one in C:\Prog Files\Notes and a duplicate under C:\Windows. Delete the duplicate.

» Report offensive content

20

sameer - 28/06/07

how can i connect lotus notes database with web page having coding in php and using apache server ??plz help

» Report offensive content

21

sameer - 28/06/07

how to connect the web page having the coding in php ,using apache server , to my lotus notes database???

» Report offensive content

22

Martin Zeman - 25/07/07

I had same problem as Dan (Item 5) and luis (Item 16). I found solution (for me, maybe it helps) : the problem was in missing notes.ini file in "C:\Program Files\lotus\notes" directory. After copying this file from another instalation everything worked.

Hope it will help

martin

» Report offensive content

23

deltavictor - 06/08/07

Even though the original post was quite old, I found this really useful. It saved me a terible trouble of trying to parse and process each field in a Notes database manually.

» Report offensive content

24

Pauk - 05/11/07

I connect the Lotus Notes server by using NotesSQL. But I got same problem as Item 18.Who can solve that problem?

» Report offensive content

25

ValeryS - 08/11/07

I have the same problem described in item 18 & 24: "[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed." ODBC connection and SSIS Data reader looks working, but I can't create SQL Linked server on Lotus Notes database. Internet search didn't provide solution for it. Did somobody finally resolved it?

P.S. Lotus Notes server, NotesSQL ODBC 3.02(i,j), MSSQL 2005, Linked server, SSIS.
http://www-12.lotus.com/ldd/doc/notessql/3.0.2b/nsql302b.nsf/66208c256b4136a2852563c000646f8c/1bbf8eb10f20869b85256c2b00555464?OpenDocument didn't help.
Thanks in advance.

» Report offensive content

26

Michael McKenna - 16/01/08

I finally resolved the SQLAllocHandle issue by moving the notes.ini and *.id file into the C:\NotesSQL directory.
(also updating the notes.ini to reflect this new path. i.e. DIRECTORY=C:\NotesSQL)

I had to go back through the GUI admin tool to remove existing config and change it to reference these new file locations.

» Report offensive content

27

abc - 22/02/08

how can i connect lotus notes database with J2ME

» Report offensive content

28

Jignesh - 15/07/08

hi, i have successfully connected with lotus notes through asp .net now i want to insert records in notes using asp .net. so please help me give me code for manipulating records in lotus notes using asp .net

» Report offensive content

29

burn - 16/07/08

Encountering General Error Invalid File while trying to connecct to a lotus notes domino server. It points to a DNS.dll. No idea here. Help please.

» Report offensive content

30

edspyder - 06/08/08

Hello,

I'm trying to search within notes to find email addresses. I have the notessql installed and everything is working fine. I'm attempting to accomplish this in VB. Has anyone done anything like this in VB, if so, could you provide example code.

Thanks,

» Report offensive content

31

Kevin - 12/09/08

i want notes data in oracle with different format

» Report offensive content

32

dhom - 07/11/08

This is the error Im getting in inserting in any Dialog List Type column in lotus notes.Including multi value or single value. I am sure that the record im inserting exists. I also tried copying existing record just to make sure but it still dont work.

"ERROR [HY000] [Lotus][ODBC Lotus Notes][Lotus Notes Server]Notes API driver error: Value does not exist in keyword list for field Participants"

Harry Potter is registered. I also tried creating record from Lotus Notes using Harry Potter as Participant and coppied the generated data from my Insert SQL Script but it still throws me an error. 

Insert into MainTopic(Participants) values('Harry Potter')

» Report offensive content

33

Mo - 18/12/08

Hello,
I am looking for a NotesSQL reference guide. The syntax is different to SQL I am used to .....

Thanks for your help

» Report offensive content

34

rabab - 22/04/09

please anybody can tell me how to connect .asp programe to notes databse using notes sql ?

here is my code below.
Any input would be appreciated!! 

<%Response.ContentType="text/vnd.wap.wml"%>

<%Response.write("<?xml version=""1.0""?><!DOCTYPE wml PUBLIC ""-//WAPFORUM//DTD WML 1.1//EN"" ""http://www.wapforum.org/DTD/wml_1.1.xml"">")%>

<wml>

<card>

<p align="right">Running <img src="movie.bmp" alt="logo" /></p><p>

<%

dim sql

dim conn

dim rs



set conn = server.createobject("adodb.connection")





conn.open ("DSN=test")



set rs = server.createobject("adodb.recordset")







sql="SELECT * FROM myNewView "

 



rs.open sql, conn, 2, 2

%>

<select name="tid" title="users">

<%Do while not rs.eof%>

<option value="<%=rs("userid")%>"><%=rs("password")%></option>

<%

rs.movenext

loop

rs.Close 

%>

</select>

</p>

<do type="accept" label="view sales">

<go href="Shows.asp" method="get" >

<postfield name="tid" value="$(tid)" />

<postfield name="mid" value="<%=Request.QueryString("mid")%>"/>



</go>

</do>



</card>

</wml>

» Report offensive content

35

Adrian Reason - 12/05/09

We have developed a Meta Data Modeler for Notes for open real time query of Lotus Notes using Notes SQL and also a Relational Data Bridge for Lotus Notes that maps notes data including muli-value fields to any relational database (The solution was initially developed for working with Cognos - but now uses the open OMG XMI meta data standards and can work with IBM IIS for Enterprise meta data management, and a wole range of oter BI tools including Business Objects and Crystal, Information Builders (including DB2 queery for iSeries) and Synaptris IntelliPrint

» Report offensive content

36

Paola - 24/07/09

Hello
I have a problem with Notes SQL and Vista. I've configurated it step by step, also, I've created a ODBC, but when I try to use it, It was failed
Is there anybody that use Windows Vista and NotesSQL?

I'll appreciate your help

» Report offensive content

37

Chaitanya Panchal - 10/09/09

I want to connect Lotus Notes 8.5 and third party application in real time....is this possible using NotesSQL????

if not, anybody here knows how to connect mysql and lotus notes?

Thank you in advance.....

» Report offensive content

38

Robert Gabriel - 22/09/09

Adrian Reason you have what I need. How can I contact you?

» Report offensive content

39

Steve Windsor - 26/10/09

Anyone help here ??

I've been using Notes SQL for sometime to import data into Excel using very simple queries. These work brilliantly all the time.

I am now setting up some new queries into other databases, but have a problem. The queries run fine and return the data, then when you save the file there's an option in Excel to save the query definitions in the Excel file, which means you don't have to select and load the query every time you run it - you 'refresh data' and the query runs.

Problem is when I try and save the new files with the Query Definitions, my processor goes flat out and the file won't save - I have to close the file with Task Manager.

Anyone know why the new files won't save ? The old files with the definitions save perfectly every time....

» Report offensive content

40

Jarema - 03/12/09

QS:
I got problem with russian language fields stored in a Lotus 6.5 database. In Lotus all values are displayed right. Accessing this database via ODBC(tried both 3.2 and 8.5 drivers) or using domobj.tlb gives me always "????" as content. The export to a UTF8 file works also right.
Any idea how to get the right content via ODBC or domobj?

thx

jarema

» Report offensive content

41

Subhash - 06/01/10

Adrian Reason , can you email me your product details/website

» Report offensive content

42

Tomas - 02/02/10

Hello, can any one help me with creating linked server to Lotus Notes. I can create the linked server and successfully test the connection. I get 7399 error when I try to use it. 

TITLE: Microsoft SQL Server Management Studio

------------------------------



Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)



For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476



------------------------------

ADDITIONAL INFORMATION:



An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)



------------------------------



The OLE DB provider "SQL Server" for linked server "(null)" reported an error. One or more arguments were reported invalid by the provider.

Cannot obtain the schema rowset "DBSCHEMA_CATALOGS" for OLE DB provider "SQL Server" for linked server "(null)". The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7399)



For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476



------------------------------

BUTTONS:



OK

------------------------------

» Report offensive content

Leave a comment

You must read and type the 6 chars within 0..9 and A..F

* indicates mandatory fields.

Latest comments

42

Tomas - 02/02/10

Hello, can any one help me with creating linked server to Lotus Notes. I can create the linked server and successfully ... more

41

Subhash - 01/06/10

Adrian Reason , can you email me your product details/website ... more

40

Jarema - 12/03/09

QS: I got problem with russian language fields stored in a Lotus 6.5 database. In Lotus all values are displayed right. Accessing ... more

Log in


Sign up | Forgot your password?

Blogs

RSS feed

  • Staff Microsoft shows off IE9 preview

    This week, highlights from Microsoft's MIX10 conference and more in the Roundup. Read more »

    -- posted by Staff

  • Chris Duckett IE9's H.264 vote killed Ogg

    In a split decision by the judges, the winner of the W3C/WHATWG video codec consensus is H.264, taking home the future of video playback on the internet while loser Ogg goes home with nothing but thoughts of what might have been. Read more »

    -- posted by Chris Duckett

  • Staff Google launches Apps Marketplace

    Google launches and app store, while Mozilla plans to re-write its open-source license. More of this week's news in the Roundup. Read more »

    -- posted by Staff

Most popular tags

What's on?

  • Optus Deal

    Broadband + home phone + PlayStation®3 in a single package price!