A PostgreSQL Interface for HCL Domino Data

There are a vast number of PostgreSQL clients available on the Internet. From standard Drivers to BI and Analytics tools, PostgreSQL is a popular interface for data access. Using our JDBC Drivers, you can now create PostgreSQL entry-points that you can connect to from any standard client.

To access HCL Domino data as a PostgreSQL database, use the CData JDBC Driver for HCL Domino and a JDBC foreign data wrapper (FDW). In this article, we compile the FDW, install it, and query HCL Domino data from PostgreSQL Server.

Connect to HCL Domino Data as a JDBC Data Source

To connect to HCL Domino as a JDBC data source, you will need the following:

• Driver JAR path: The JAR is located in the lib subfolder of the installation directory.

• Driver class: cdata.jdbc.domino.DominoDriver

• JDBC URL: The URL must start with "jdbc:domino:" and can include any of the connection properties in name-value pairs separated with semicolons.

  Prerequisites

  The connector requires the Proton component to be installed. Normally, Proton is distributed as part of the AppDev pack. See the HCL documentation for instructions on acquiring and installing Proton or the AppDev pack.

  Once the Proton service is installed and running, you will also need to create a user account and download its Internet certificate. This certificate can be used to set the connector certificate connection properties.

  Authenticating to Domino

  • Server: The name or IP address of the server running Domino with the Proton service.
  • Port: The port number that the Proton service is listening on.
  • Database: The name of the database file, including the .nsf extension.
  • SSLClientCertType: This must match the format of the certificate file. Typically this will be either PEMKEY_FILE for .pem certificates or PFXFILE for .pfx certificates.
  • SSLClientCert: The path to the certificate file.
  • SSLServerCert: This can be set to (*) if you trust the server. This is usually the case, but if you want to perform SSL validation, you may provide a certificate or thumbprint instead. See the documentation for SSLServerCert for details.

  Additional Server Configuration

  The connector supports querying Domino views if any are defined. Before views can be queried by the connector they must be registered with the design catalog.

  Please refer to the Catalog Administration section of the AppDev pack documentation for details on how to do this.

  Built-in Connection String Designer

  For assistance in constructing the JDBC URL, use the connection string designer built into the HCL Domino JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

  java -jar cdata.jdbc.domino.jar

  Fill in the connection properties and copy the connection string to the clipboard.

  A typical JDBC URL is below:

  jdbc:domino:Server=https://domino.corp.com;Database=names.nsf;Port=3002;SSLClientCertType=PEMKEY_FILE;SSLClientCert=full_path_of_certificate.pem;SSLServerCert=*

Build the JDBC Foreign Data Wrapper

The Foreign Data Wrapper can be installed as an extension to PostgreSQL, without recompiling PostgreSQL. The jdbc2_fdw extension is used as an example (downloadable here).

1. Add a symlink from the shared object for your version of the JRE to /usr/lib/libjvm.so. For example: ln -s /usr/lib/jvm/java-6-openjdk/jre/lib/amd64/server/libjvm.so /usr/lib/libjvm.so
2. Start the build: make install USE_PGXS=1

Query HCL Domino Data as a PostgreSQL Database

After you have installed the extension, follow the steps below to start executing queries to HCL Domino data:

1. Log into your database.
2. Load the extension for the database: CREATE EXTENSION jdbc2_fdw;
3. Create a server object for HCL Domino: CREATE SERVER Domino FOREIGN DATA WRAPPER jdbc2_fdw OPTIONS ( drivername 'cdata.jdbc.domino.DominoDriver', url 'jdbc:domino:Server=https://domino.corp.com;Database=names.nsf;Port=3002;SSLClientCertType=PEMKEY_FILE;SSLClientCert=full_path_of_certificate.pem;SSLServerCert=*', querytimeout '15', jarfile '/home/MyUser/CData/CData\ JDBC\ Driver\ for\ Salesforce MyDriverEdition/lib/cdata.jdbc.domino.jar');
4. Create a user mapping for the username and password of a user known to the MySQL daemon. CREATE USER MAPPING for postgres SERVER Domino OPTIONS ( username 'admin', password 'test');
5. Create a foreign table in your local database: postgres=# CREATE FOREIGN TABLE byname ( byname_id text, byname_Name text, byname_Address numeric) SERVER Domino OPTIONS ( table_name 'byname');