Accessing ODBC data sources from Lotus Notes and Domino

Contents

Introduction

Domino Enterprise Connection Services (DECS) lets Lotus Domino applications access data in non-Domino databases. The external data is then available to Lotus Notes client applications. By using DECS with an Easysoft ODBC driver, your Notes and Domino applications can access data in databases such as Microsoft SQL Server, Microsoft Access, Oracle, DB2, InterBase, and Sybase ASE.

For relational ODBC access to Domino databases from Linux and UNIX platforms, use the ODBC-ODBC Bridge with the Lotus NotesSQL ODBC driver.

Easysoft ODBC drivers have been tested with Notes and Domino 6 beta 3, the final Domino 6 release, and Domino 7. We believe our ODBC drivers should also work with version 5 too. We tested on Linux.

Installing an Easysoft ODBC driver

Download the Easysoft ODBC driver for your database.

Note If you are using a 64-bit version of Domino, you need to use it with a 64-bit Easysoft ODBC driver. If you are using a 32-bit version of Domino on a 64-bit platform, download the 32-bit Easysoft ODBC driver even if there is a 64-bit version available. 32-bit drivers will function correctly on a 64-bit platform.

For installation instructions, refer to the documentation for your Easysoft ODBC driver. Refer to the documentation to find out which environment variables you need to set.

Note When installing an Easysoft ODBC driver to run under Notes and Domino, we strongly recommend that you install the unixODBC Driver Manager that's included in the Easysoft distribution. Domino needs an ODBC Driver Manager. During the installation, make sure that you choose to install the Easysoft ODBC driver into unixODBC.

Domino 6 and 7

  1. Create your Easysoft ODBC driver data sources in /etc/odbc.ini.

    For information about creating Easysoft ODBC data sources, refer to the documentation for your Easysoft ODBC driver.

  2. Test the ODBC data sources locally first with unixODBC's isql command.
  3. Use Domino's dctest to test the database connection from Domino. You can find more information about this in IBM's "Lotus Domino 6 For Linux" red book.

    For our install, dctest was installed in /opt/lotus/notes/latest/linux. You may need to make sure libodbc.so is available in your dynamic link path. (For Linux and FreeBSD, the Easysoft ODBC driver installation script will do this for you if you installed it as root.) IBM recommend symbolically linking /opt/lotus/notes/latest/linux/libodbc.so to the real unixODBC libodbc.so, which will be in /usr/local/easysoft/unixODBC/lib for an Easysoft ODBC driver installation. For example:

    ln -s /usr/local/easysoft/unixODBC/lib/libodbc.so /opt/lotus/notes/latest/linux/libodbc.so

    We did not need to do this as the Easysoft ODBC driver installer for Linux adds /usr/local/easysoft/unixODBC/lib to /etc/ld.so.conf and runs /sbin/ldconfig to inform the dynamic linker of a new directory to look in for shared objects.

    However, when we ran dctest during Domino 6 testing, we did get a dynamic linker error for libnotes.so, which appeared to be in /opt/lotus/notes/60000/linux. To work around this, we set the LD_LIBRARY_PATH environment variable to /opt/lotus/notes/60000/linux and exported LD_LIBRARY_PATH. For example:

    LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/lotus/notes/60000/linux/;
export LD_LIBRARY_PATH

    After that dctest started successfully. You can choose ODBC and enter a data source, database user name, and database password. dctest then tests the connection. Make sure you test the ODBC connectivity with dctest before moving on to Lotus Notes.

  4. Make sure the DECS server is running.

    If the DECS task is not running, the design view in Lotus Notes for a Data Connection will not allow the browsing of metadata.

    In the Web Administrator (http://domino_server/webadmin.nsf), select Server then tasks. If you don't have a DECS task, select Tools, Task, Start, select DECS server from the list and choose Start.

    This failed to start for us and an error was output in the Domino server saying "EXTMGR_ADDINS=decsext" had to be added to the notes.ini file (which was in /local/notesdata for us). After we did this and restarted Domino, the DECS task started.

  5. When working in the Domino Designer in Lotus Notes, you should now be able to create a database with a Data Connection using ODBC. In the Data Connection dialog box, select Class:RDBMS, Type:ODBC and enter your data source name into the Data Source field. You may also need to provide a database user name and password in the Connectivity section.

Domino 6 beta 3

  1. The Lotus Notes installation is already set up to support ODBC. It only requires that a symbolic link called libodbc.so is added to the /opt/lotus/notes/latest/linux directory that points to the unixODBC Driver Manager.

    For the default Easysoft ODBC driver, unixODBC, and Lotus Notes installation settings, the following lines show the commands that you need to enter to do this:

    cd /opt/lotus/notes/latest/linux
ln -s /usr/local/easysoft/unixODBC/lib/libodbc.so libodbc.so
  2. Create your Easysoft ODBC driver data sources in /etc/odbc.ini.

    For information about creating Easysoft ODBC data sources, refer to the documentation for your Easysoft ODBC driver.

ODBC access to Domino databases from UNIX and Linux by using NotesSQL

NotesSQL is an ODBC driver for Notes and Domino, which allows applications that support ODBC to access and update data stored in Domino databases. A Domino database is not relational, but with NotesSQL, a Domino database looks like a relational data source to an ODBC-enabled application. You can then use the application to query and update your Domino data by using SQL.

The NotesSQL ODBC driver is only available on Windows platforms. For ODBC access to Domino databases from UNIX and Linux platforms, use the ODBC-ODBC Bridge to connect to the NotesSQL driver on Windows.

  1. On the machine where the NotesSQL ODBC driver is installed, add a NotesSQL System ODBC data source.

    For information about adding Lotus Notes ODBC data sources, refer to the NotesSQL documentation.

  2. Download the ODBC-ODBC Bridge client for your UNIX or Linux platform and the Windows ODBC-ODBC Bridge server.

    For installation instructions, refer to the ODBC-ODBC Bridge documentation. You should also refer to the ODBC-ODBC Bridge documentation for information about the environment variables you need to set (LD_LIBRARY_PATH, LIBPATH, LD_RUN_PATH, or SHLIB_PATH depending on the platform and linker).

  3. Create an ODBC-ODBC Bridge client data source in /etc/odbc.ini that points to your NotesSQL system data source: 
    [OOB_notessql]
Driver = OOB
ServerPort = mywindows_server:8888
LogonUser = mywindows_user_name
LogonAuth = mywindows_password
TargetDSN = notessql_systemdsn
TargetUser = notes_user_name
TargetAuth = notes_password

    where:

    • mywindows_server is the hostname or IP address of the machine where the ODBC-ODBC Bridge server is running.
    • mywindows_user_name and mywindows_password are a valid Windows user name and password that can be used to log into this machine.
    • notessql_systemdsn is the name of your NotesSQL System ODBC data source.
    • notes_username and notes_password are a valid Notes user ID and password that can be used to access the database.
  4. Use isql to test the new data source: 
    cd /usr/local/easysoft/unixODBC/bin
./isql -v OOB_notessql

    At the prompt, enter help to display a list of tables. To exit, press Return in an empty prompt line.