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

Connect to Cvent Data as a JDBC Data Source

To connect to Cvent 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.cvent.CventDriver
  

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

  Before you can authenticate to Cvent, you must create a workspace and an OAuth application.

  Creating a Workspace

  To create a workspace:

  1. Sign into Cvent and navigate to App Switcher (the blue button in the upper right corner of the page) >> Admin.
  2. In the Admin menu, navigate to Integrations >> REST API.
  3. A new tab launches for Developer Management. Click on Manage API Access in the new tab.
  4. Create a Workspace and name it. Select the scopes you would like your developers to have access to. Scopes control what data domains the developer can access.
     • Choose All to allow developers to choose any scope, and any future scopes added to the REST API.
     • Choose Custom to limit the scopes developers can choose for their OAuth apps to selected scopes. To access all tables exposed by the driver, you need to set the following scopes:

       event/attendees:read	event/attendees:write	event/contacts:read
       event/contacts:write	event/custom-fields:read	event/custom-fields:write
       event/events:read	event/events:write	event/sessions:delete
       event/sessions:read	event/sessions:write	event/speakers:delete
       event/speakers:read	event/speakers:write	budget/budget-items:read
       budget/budget-items:write	exhibitor/exhibitors:read	exhibitor/exhibitors:write
       survey/surveys:read	survey/surveys:write

  Creating an OAuth Application

  After you have set up a Workspace and invited them, developers can sign up and create a custom OAuth app. See the Creating a Custom OAuth Application section in the Help documentation for more information.

  Connecting to Cvent

  After creating an OAuth application, set the following connection properties to connect to Cvent:

  • InitiateOAuth: GETANDREFRESH. Used to automatically get and refresh the OAuthAccessToken.
  • OAuthClientId: The Client ID associated with the OAuth application. You can find this on the Applications page in the Cvent Developer Portal.
  • OAuthClientSecret: The Client secret associated with the OAuth application. You can find this on the Applications page in the Cvent Developer Portal.

  Built-in Connection String Designer

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

  java -jar cdata.jdbc.cvent.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:cvent:OAuthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;InitiateOAuth=GETANDREFRESH;
  

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

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

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

   CREATE EXTENSION jdbc2_fdw;
   

3. Create a server object for Cvent:

   CREATE SERVER Cvent 
   FOREIGN DATA WRAPPER jdbc2_fdw OPTIONS (
   drivername 'cdata.jdbc.cvent.CventDriver',
   url 'jdbc:cvent:OAuthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;InitiateOAuth=GETANDREFRESH;',
   querytimeout '15',
   jarfile '/home/MyUser/CData/CData\ JDBC\ Driver\ for\ Salesforce MyDriverEdition/lib/cdata.jdbc.cvent.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 Cvent OPTIONS (
   username 'admin', 
   password 'test');
   

5. Create a foreign table in your local database:

   postgres=# CREATE FOREIGN TABLE events (
   events_id text,
   events_Id text,
   events_Title numeric) 
   SERVER Cvent OPTIONS (
   table_name 'events');
   

You can now execute read/write commands to Cvent:

postgres=# SELECT * FROM events;