Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

Easysoft ODBC-JDBC Gateway

How do I connect an ODBC application to GemFire XD?

Article:
01093
Last Reviewed:
4th January 2024
Revision:
1

Use the ODBC-JDBC Gateway to connect Pivotal GemFire XD to an ODBC application, enabling you to work with Gemfire XD data in applications such as Access, BusinessObjects, Excel, MicroStrategy, Oracle, Perl, PHP , QlikView, SQL Server and Tableau.

GemFire XD is a Java application and includes a JDBC driver. A JDBC driver allows a Java application to access external data. The Gemfire XD JDBC driver allows Java applications to access data stored in Gemfire XD.

The applications listed in the first paragraph are not written in Java however. They use an ODBC driver, a different piece of database middleware, to access external data. The ODBC-JDBC Gateway connects an application that uses ODBC to an application that uses JDBC. To the application, the ODBC-JDBC Gateway is an ODBC driver. To the JDBC driver the ODBC-JDBC Gateway is a Java application.

Installing and Licensing the ODBC-JDBC Gateway

  1. Download the Windows ODBC-JDBC Gateway.
  2. Install and license the ODBC-JDBC Gateway on the Windows machine where the application you want to connect to Gemfire XD is installed.

    Note You do not have to install the ODBC-JDBC Gateway on the same machine as Gemfire XD. However you will need to install the recommended Server JRE on the ODBC-JDBC Gateway machine. If you cannot find the recommended JRE version on the main Java download page, try the Java archive instead. You will also need to copy the Gemfire XD JDBC driver (gemfirexd-client.jar) to the ODBC-JDBC Gateway machine.

    For installation instructions, see the ODBC-JDBC Gateway documentation.

  3. Use the ODBC-JDBC Gateway Setup Java Interface dialog box to select the JVM included in the recommended Server JRE distribution. At the time of writing, the JVM included in the 1.8 Java distribution did not work with the Gemfire XD JDBC driver.

Configuring an ODBC Data Source

Before you can use the ODBC-JDBC Gateway to connect your ODBC application to GemFire XD, you need to configure a System ODBC data source. An ODBC data source stores the connection details for the target database.

You configure ODBC data sources in ODBC Administrator, which is included with Windows. How you run ODBC Administrator depends on whether your version of Windows is 32-bit or 64-bit:

Use ODBC Administrator to create your ODBC-JDBC Gateway data source.

Creating a ODBC-JDBC Gateway ODBC Data Source for Gemfire XD

  1. Choose the System DSN tab, and then choose Add.
  2. In the Create New Data Source dialog box, choose ODBC-JDBC Gateway, and then choose Finish.
  3. Complete the ODBC-JDBC Gateway DSN Setup dialog box:
    Setting Value
    DSN Gemfire XD
    Driver Class com.pivotal.gemfirexd.jdbc.ClientDriver
    Class Path <path>\gemfirexd-client.jar

    For example:

    C:\Pivotal_GemFireXD_140_b50226_Windows\lib\gemfirexd-client.jar
    URL jdbc:gemfirexd://<gemfirexd_server_host>:>gemfirexd_server_port>/

    For example:

    jdbc:gemfirexd://localhost:1527/
  4. Use the Test button to verify that you can successfully connect to Gemfire XD.

You can now use the ODBC-JDBC Gateway Data Source to connect your ODBC application to Gemfire XD.

Example: How to connect Tableau to GemFire XD

The components necessary to connect Tableau to GemFire XD are:

Tableau <-> ODBC-JDBC Gateway <-> GemFire XD JDBC Driver <-> GemFire XD

Connecting Tableau to GemFire XD

  1. On the Gemfire XD machine, start the Gemfire XD locator and servers. For example: 
    C:\> set path=%path%;c:\Pivotal_GemFireXD_140_b50226_Windows\bin
C:\>mkdir GemfireXD
C:\>cd GemfireXD
C:\GemfireXD> mkdir locator1 server1 server2
C:\GemfireXD>gfxd locator start -peer-discovery-address=localhost -dir=locator1
    -jmx-manager-start=true -jmx-manager-http-port=7075
C:\GemfireXD gfxd server start -locators=localhost[10334] -bind-address=localhost -client-port=1528 -dir=server1
C:\GemfireXD gfxd server start -locators=localhost[10334] -bind-address=localhost -client-port=1529 -dir=server2
  2. Create some sample data. For example: 
    C:\GemfireXD> gfxd
gfxd> connect client 'localhost:1527';
gfxd> create table quicktable (id int generated always as identity, item char(25));
gfxd> insert into quicktable values (default, 'widget');
gfxd> insert into quicktable values (default, 'gadget');
  3. In Tableau Desktop, on the Data menu, choose Connect To Data.
  4. In the Connect To Data page's On a server section, choose Other Databases (ODBC).
  5. In the Generic ODBC Connection dialog box, choose the ODBC-JDBC Gateway data source from the DSN list, and then click Connect.
  6. In the Table box, type the name of the table you want to work with and then press RETURN. For example, QUICKTABLE.
  7. Drag the table to the right hand pane. Click Go to Worksheet.
  8. Note the capabilities reported by Tableau for the data source, and then choose OK.
  9. Drag the Gemfire Columns from the Data pane to either the Rows or Columns boxes in the right hand pane to see your Gemfire XD data in Tableau.

    Note You will not be able to use the View Data facility in Tableau (which is a data preview tool) with Gemfire XD. The query that View Data generates includes a LIMIT clause, which the Gemfire XD JDBC driver does not support.

Applies To

Knowledge Base Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)