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

How do I connect an ODBC application to GemFire XD?

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.

nstalling 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 don't have to install the ODBC-JDBC Gateway on the same machine as Gemfire XD. However, you do need to install the recommended Server JRE on the ODBC-JDBC Gateway machine. If you can't find the recommended JRE version on the main Java download page, try the Java archive instead. You also need to copy the Gemfire XD JDBC driver (gemfirexd-client.jar) to the ODBC-JDBC Gateway machine.

    For installation instructions, refer to 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 Data Source Administrator, which is included with Windows.

There are two versions of ODBC Data Source Administrator on this platform. The version of ODBC Data Source Administrator that you need to run depends on whether the application you want to connect to GemfireXD is 32-bit or 64-bit. Refer to the following table to find out the architecture for some popular ODBC applications.

Access There is both a 32-bit and a 64-bit version of Access. To find out which version of Access you have, start Access, and then start Windows Task Manager. In Task Manager, choose the Processes tab. Search for MSACCESS.EXE in the list. If the process name is MSACCESS.EXE *32, Microsoft Access is 32-bit. If the process name is MSACCESS.EXE, Microsoft Access is 64-bit.
Excel There is both a 32-bit and a 64-bit version of Excel. To find out which version of Excel you have, start Excel, and then start Windows Task Manager. In Task Manager, choose the Processes tab. Search for Excel.exe in the list. If this process name is followed by *32, your version of Excel is 32-bit. Otherwise, your version of Excel is 64-bit.
Oracle The Oracle component that interacts with the MySQL ODBC driver is called DG4ODBC. There is both a 32-bit and a 64-bit version of DG4ODBC. To find out which version of DG4ODBC you have, start the Windows Task Manager and choose the Processes tab. In a Command Prompt window, type dg4odbc --help. In the Windows Task Manager, search for the DG4ODBC process. If the Image Name is dg4odbc.exe *32 DG4ODBC is 32-bit. If the Image Name is dg4odbc.exe DG4ODBC is 64-bit. Press CTRL+C in the Command Prompt window, when you have used the Windows Task Manager to find out DG4ODBC's architecture.
SQL Server There is both a 32-bit and a 64-bit version of SQL Server. To find out which version of SQL Server you have, connect to your SQL Server instance, and then run this SQL statement: 
select SERVERPROPERTY('edition')
GoodData CloudConnect Designer is a 64-bit application.
MicroStrategy MicroStrategy is a 32-bit application.
Oracle Business Intelligence Enterprise Edition (OBIEE) The OBIEE component that interacts with the ODBC driver is called the Oracle BI Administration Tool. The Oracle BI Administration Tool is 64-bit.
PHPRunner PHPRunner is a 32-bit application.
QlikView QlikView is a 32-bit application if the Force 32 Bit check box is selected. Otherwise, QlikView is a 64-bit application.
SAP BusinessObjects Central Management Server is a 64-bit application. Business View Manager, Information Design Tool and Web Intelligence Rich Client are 32-bit applications.
SAP Crystal Reports Crystal Reports is a 32-bit application.
Tableau Tableau is a 32-bit application.
TIBCO Spotfire TIBCO Spotfire is a 64-bit application.

If you have a 64-bit application, you need to run 64-bit version of ODBC Data Source Administrator. To do this, in the Windows Run dialog box, enter:

odbcad32.exe

If you have a 32-bit application, you need to run 32-bit version of ODBC Data Source Administrator. To do this, in the Windows Run dialog box, enter:

%windir%\syswow64\odbcad32.exe

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 choose Connect.
  6. In the Table box, enter the name of the table you want to work with and then press RETURN. For example, QUICKTABLE.
  7. Drag the table to the rightmost pane. Choose 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 rightmost pane to display your Gemfire XD data in Tableau.

    Note You can't 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 doesn't support.