This white paper describes the process necessary to get started with NotesSQL. Specifically, this document covers the following points: what software is necessary to use NotesSQL, choosing your version of NotesSQL, OS Support Matrix, bitness, what IBM software should be installed locally for NotesSQL to use, troubleshooting, and additional NotesSQL resources.
|Overview, Requirements & Configuration|
What software is necessary to use NotesSQL?
Three main pieces of software are necessary:
- The calling application that will use NotesSQL to interact with data on your Domino server
- The NotesSQL driver itself (see below for details on which version to use)
- Either an IBM Notes client or an IBM Domino server, installed locally to the calling application and driver. This is necessary for NotesSQL to make the connection. See below for information on whether to install a Notes client or a Domino server.
Choosing your version of NotesSQL
NotesSQL is intended for use with any supported version of Notes or Domino, although generally you will have best results by picking a version of NotesSQL that is closest to your Notes/Domino environment. The primary concern is the operating system of the machine running NotesSQL release.
Operating System Support Matrix
|NotesSQL 8.0||Windows 2000, XP, 2003 Standard or Enterprise, Vista|
|NotesSQL 8.5||Windows 2000, XP, 2003 Standard or Enterprise|
|NotesSQL 8.5.1||Microsoft Windows 2000, or XP |
Microsoft Windows 2003 Server Standard Edition or Enterprise Edition
Microsoft Windows Vista
Microsoft Windows 2008
Microsoft Windows 7
|NotesSQL 8.5.3||Microsoft Windows 2000, XP, Vista, or 7|
Microsoft Windows 2003, or 2008 Server Standard Edition or Enterprise Edition
|NotesSQL 9.0 (Also called “IBM ODBC Driver for Notes/Domino 9.0”)||Microsoft Windows XP or 7|
Microsoft Windows 2008 Server Standard Edition or Enterprise Edition
How to determine what Bitness level to choose - 32-bit or 64-bit*
Determining the appropriate bitness level is entirely dependent on the application that will call the NotesSQL driver.
- If the calling application is 32-bit, you should install and use the 32-bit NotesSQL driver.
- If the calling application is 64-bit, you should install and use the 64-bit NotesSQL driver.
*Note that only NotesSQL versions 8.5.3 and 9.0 (called IBM ODBC) are available in a 64-bit option.
What IBM software should be installed locally for NotesSQL to use?
This depends on the bitness of the NotesSQL driver (see above).
- If NotesSQL is 32-bit, you should install a Notes Client of any supported release. Currently, all Notes Client installations are 32-bit, so a Notes Client installation is a good match. You could alternately use a 32-bit Domino server of any supported release, but a Notes Client is recommended.
- If NotesSQL is 64-bit, you will need to install a local 64-bit Domino Server, of any supported release, to power the connection. At the time of writing there is no 64-bit Notes Client available, so this is currently the only option when using 64-bit NotesSQL.
What applications can use NotesSQL to make a remote connection?
The most common applications that can use ODBC have been shown to function with NotesSQL. However, with the sheer number of third-party products that can make use of ODBC, we cannot produce a list of what products in particular work well with NotesSQL.
The yardstick for whether NotesSQL is installed and functional is Microsoft's ODBCTest utility. If you can connect using ODBCTest and can pull data using ODBCTest, but cannot connect from your calling application, then there is an issue with the calling application that is preventing usage with NotesSQL.
Sometimes ODBC compliance is an issue. For example, NotesSQL 8.0 is fully ODBC 2.0 compliant, while NotesSQL 8.5.1 and up are partially ODBC 3.5 compliant. If an application automatically senses the level of ODBC compliance, and attempts to use ODBC 3.5 requests with NotesSQL 8.5.1 or later, this can cause trouble (see the “Driver Not Capable” error below).
What other configurations should be set before NotesSQL can be used?
#1 - Update the Path environment variable
The Path variable must contain the path required to find the notes.ini of the Notes client or the Domino server that is being used to power the connection.
You can find the Path environment variable by going to Control Panel -> System -> Advanced -> Environment Variables button -> System Variables section. Because the Path variable is often quite lengthy, you may find it easier to copy it into Notepad or a similar editor to make changes, then pasting the updated statement back in.
Note that NotesSQL cannot handle spaces in the path. If your path to the notes.ini includes spaces, you will need to use a Windows shorthand format to avoid the space. For example, C:\Program Files\Lotus\Notes would become C:\PROGRA~1\Lotus\Notes.
The ~1 indicates that it is the first subdirectory to contain the listed characters, here “PROGRA”. Change the number as needed. (For example, on 64-bit operating systems, Notes appears under the “Program Files(x86)” directory, which is usually the second “Program Files” directory in line, and as such would use “PROGRA~2”.)
#2 - Create a System Data Source for NotesSQL in the appropriate ODBC Manager
Usually this is done by going to Control Panel -> Administrative Tools -> Data Sources (ODBC) -> System DSN tab -> Add button. From here you select your NotesSQL driver and fill out the Data Source appropriately.
If you are using 32-bit NotesSQL on a 64-bit operating system, the driver will not appear in the normal ODBC manager as described above. You will instead find it in the special 32-bit ODBC manager, which is located in C:\Windows\Syswow64\odbcad32.exe.
Normally you do not need to make any changes to your registry. However, if you have a Multi-User or Shared Notes Client installation, or if your Notes Client data is kept on a network drive, then you should ensure that your registry contains the correct path to find your notes.ini and other Notes data. This is stored in HKEY_LOCAL_MACHINE\Software\Lotus\Notes|DataPath
Note that unless you are resolving a problem, you should not make adjustments to your registry.
|Troubleshooting Common Error Messages & Issues|
Q1. You installed NotesSQL but it does not appear in the list of drivers in the ODBC Manager.
This most often occurs because 32-bit NotesSQL was installed on a 64-bit Windows operating system. While this is perfectly viable and supported, note that a 32-bit driver will not appear in the default ODBC Manager found under Control Panel -> Administrative Tools. You will need to configure a System data source in the 32-bit ODBC Manager, which you can find C:\Windows\SysWOW64\odbcad32.exe
Your 32-bit application will automatically use data sources from the 32-bit ODBC Manager; no further steps are necessary to make it “see” the data source.
Q2. Error: Driver Not Capable:
This can occur when there is an ODBC Compliance discrepancy. NotesSQL 8.5.1 and later meet most of the criteria for ODBC 3.5 compliance, but not all. Many applications will sense the ODBC Compliance of the driver they use, and will send requests to match. In some cases, they will attempt an ODBC 3.5 request over NotesSQL 8.5.1 or later, and throw a “Driver Not Capable” error.
If this happens, installing and using NotesSQL 8.0 instead will usually resolve the issue. NotesSQL 8.0 is fully ODBC 2.0 compliant, so there is no discrepancy when an application automatically detects and uses ODBC 2.0 requests.
Q3. "Set up routines for Lotus Notes SQL driver *.nsf ODBC driver could not be loaded due to system error code 126."
This error almost universally indicates a problem finding your local Notes client or Domino server that NotesSQL must use to make the connection. One of the following conditions is likely true.
- You do not have a locally installed Notes client or Domino server,
- Your system's Path= statement does not contain the path to the Notes or Domino program directory, specifically the directory that contains notes.ini,
- Your system's Path= statement contains an incorrect or obsolete path to such a directory, or
- NotesSQL is finding the wrong notes.ini file. It searches through the paths in the Path= statement in the order they appear, and it attempts to use the first notes.ini it finds. If that example is empty, obsolete or incorrect, it will cause this error.
To correct this issue once you've ensured you have the prerequisite software installed, you must follow step 1 given in the "What other configurations should be set" section of this document above, namely:
- Find the Path environment variable by going to Control Panel → System → Advanced → Environment Variables button → System Variables section.
- Add the full path to your local Notes client or Domino server's program directory.
- Note that NotesSQL cannot handle spaces in the path. If your path to the notes.ini includes spaces, you will need to use a Windows shorthand format to avoid the space. So C:\Program Files\Lotus\Notes would become C:\PROGRA~1\Lotus\Notes, C:\PROGRA~2\Lotus\Notes, or whichever specific path is appropriate for your environment.
- Search your system and eliminate any extraneous copies of notes.ini, if possible. We only want the active copy that your local Lotus software is using. As a safeguard against this problem, you can put the path to your Lotus program directory earlier in your Path statement, to ensure that NotesSQL will find the notes.ini here first even if there are other examples present.
Q4. My application will not let me choose NotesSQL metadata from a list
This can happen, depending on the application, and is not an issue with NotesSQL. If this is the case, you can usually pull your Notes data directly using a SELECT statement from your calling app. Even if the app does not let you browse your Notes data, if you are connected, you will have access to the data. This only requires you to know the name of the form or view you wish to retrieve, to use in your SQL statement.
- developerWorks article - IBM Lotus NotesSQL
06 September 2018