Build a PostgreSQL Interface for Outlook Data using the CData JDBC Driver

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 Outlook data as a PostgreSQL database, use the CData JDBC Driver for Outlook and a JDBC foreign data wrapper (FDW). In this article, we compile the FDW, install it, and query Outlook data from PostgreSQL Server.

Connect to Outlook Data as a JDBC Data Source

To connect to Outlook 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.api.APIDriver
  

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

  Using OAuth Authentication

  Microsoft Graph API uses OAuth 2.0 for authentication. You must register an application in the Microsoft Azure Portal to obtain OAuth credentials (Client ID and Client Secret).

  Obtaining OAuth Credentials

  1. Log in to the Azure Portal.
  2. Navigate to Azure Active Directory > App registrations.
  3. Click New registration to create a new application.
  4. Enter an application name and select the appropriate account types.
  5. Set the Redirect URI to your application's callback URL (e.g., http://localhost:33333 for desktop apps).
  6. Click Register to create the application.
  7. On the application overview page, copy the Application (client) ID - this is your OAuthClientId.
  8. Navigate to Certificates & secrets and create a new client secret.
  9. Copy the client secret value - this is your OAuthClientSecret.
  10. Navigate to API permissions and add the required Microsoft Graph API permissions:
      • Mail.Read - For accessing email messages
      • Contacts.Read - For accessing contacts
      • Calendars.Read - For accessing calendar events
      • Tasks.Read - For accessing To Do tasks
      • offline_access - For obtaining refresh tokens
  11. Click Grant admin consent to grant these permissions.

  Connecting with OAuth

  After setting the following connection properties, you are ready to connect:

  • AuthScheme: Set this to OAuth.
  • InitiateOAuth: Set this to GETANDREFRESH. The CData API Profile for Outlook will automatically walk through the OAuth process in order to obtain the access token.
  • OAuthClientId: Set this to the Application (client) ID from Azure Portal.
  • OAuthClientSecret: Set this to the client secret value from Azure Portal.
  • TenantId: Set this to your Azure AD tenant identifier (GUID or domain name like 'contoso.onmicrosoft.com').
  • CallbackURL: Set this to the Redirect URI you specified in your app registration (e.g., http://localhost:33333 for desktop apps).

  Example connection string

  Profile=C:\profiles\Outlook.apip;AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=your_client_id;OAuthClientSecret=your_client_secret;TenantId=your_tenant_id;CallbackUrl=http://localhost:33333;
  

  Built-in Connection String Designer

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

  java -jar cdata.jdbc.api.jar
  

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

  👁 Using the built-in connection string designer to generate a JDBC URL (Salesforce is shown.)
  

  A typical JDBC URL is below:

  jdbc:api:Profile=C:\profiles\Outlook.apip;AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=your_client_id;OAuthClientSecret=your_client_secret;TenantId=your_tenant_id;CallbackUrl=http://localhost:33333;
  

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 Outlook Data as a PostgreSQL Database

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

1. Log into your database.
2. Load the extension for the database:

   CREATE EXTENSION jdbc2_fdw;
   

3. Create a server object for Outlook:

   CREATE SERVER API 
   FOREIGN DATA WRAPPER jdbc2_fdw OPTIONS (
   drivername 'cdata.jdbc.api.APIDriver',
   url 'jdbc:api:Profile=C:\profiles\Outlook.apip;AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=your_client_id;OAuthClientSecret=your_client_secret;TenantId=your_tenant_id;CallbackUrl=http://localhost:33333;',
   querytimeout '15',
   jarfile '/home/MyUser/CData/CData\ JDBC\ Driver\ for\ Salesforce MyDriverEdition/lib/cdata.jdbc.api.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 API OPTIONS (
   username 'admin', 
   password 'test');
   

5. Create a foreign table in your local database:

   postgres=# CREATE FOREIGN TABLE calendargroupcalendars (
   calendargroupcalendars_id text,
   calendargroupcalendars_ text,
   calendargroupcalendars_ numeric) 
   SERVER API OPTIONS (
   table_name 'calendargroupcalendars');
   

You can now execute SELECT commands to Outlook:

postgres=# SELECT * FROM calendargroupcalendars;