How do I connect an ODBC application to Neo4j?

Use the ODBC-JDBC Gateway to connect Neo4j to an ODBC application, enabling you to work with Neo4j data in applications such as Access, Excel, SQL Server, and Tableau.

Neo4j is a Java application. A JDBC driver is available for Neo4j. A JDBC driver allows a Java application to access external data. The Neo4j JDBC driver allows Java applications to access data stored in Neo4j.

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.

Neo4j is a graph database rather than a relational database and uses cypher as its query language rather than SQL. The implication of this is that your ODBC application must let you execute pass-through SQL Statements, which are sent to the database without any processing or syntax checking by the application. Because they are passed to database in this way, it does not matter that you are executing cypher statements rather than SQL.

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 Neo4j is installed.

    Note You don't have to install the ODBC-JDBC Gateway on the same machine as Neo4j. However, you do need to install Neo4j's recommended JDK distribution. You also need to copy the Neo4j JDBC driver (neo4j-jdbc-version-SNAPSHOT-jar-with-dependencies.jar) to the ODBC-JDBC Gateway machine.

    For installation instructions, refer to the ODBC-JDBC Gateway documentation.

  3. Use the ODBC-JDBC Gateway's Setup Java Interface dialog box to select the JVM included in the recommended in Neo4j's recommended JDK distribution.

Configuring an ODBC Data Source

Before you can use the ODBC-JDBC Gateway to connect your ODBC application to Neo4j, 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. The version of ODBC Data Source Administrator that you need to run depends on whether the application you want to connect to Neo4j 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:

%windir%\system32\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

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

Creating a ODBC-JDBC Gateway ODBC Data Source for Neo4j

  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 Neo4j
    User Name neo4j
    Password my_neo4j_users_password
    Driver Class org.neo4j.jdbc.Driver
    Class Path
    path/neo4j-jdbc-version-SNAPSHOT-jar-with-dependencies.jar

    For example:

    C:/neo4j-jdbc-2.2/neo4j-jdbc-2.0.1-SNAPSHOT-jar-with-dependencies.jar
    URL jdbc:neo4j://localhost:7474/
  4. Use the Test button to verify that you can successfully connect to Neo4j.

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

Example: How to retrieve Neo4J data into Microsoft Excel

This example uses a Visual Basic for Applications (VBA) subroutine to retrieve nodes from sample Neo4j graph Movies. Note that it's not possible to use Excel's GUI tools (Data Connection Wizard or Microsoft Query) to work with Neo4j data. They are SQL-based applications and are not compatible with a cypher-based graph database.

  1. Create a new Excel spreadsheet.
  2. Press ALT+F11 to start the Visual Basic Editor.
  3. In the Visual Basic Editor, in the Project Pane, double-click Sheet1 in the list of Objects.
  4. In the Code window, add the following VBA code:
    Option Explicit
    
    Public Sub GraphNodesIntoExcel()
    
        Dim con             As New ADODB.Connection
        Dim rs              As New ADODB.Recordset
        Dim lngCounter      As Long
        Const strcQuery     As String = "MATCH (people:Person) RETURN people.name LIMIT 10"
    
        ' Replace Neo4j with the name of your ODBC data  source.
         con.Open "Neo4j"
         rs.Open strcQuery, con
    
         If rs.EOF Then Exit Sub
    
         With rs
             ActiveSheet.Range("A1").Offset(0, 0).Value = .Fields(0).Name
             lngCounter = 1
             Do Until .EOF
                 ActiveSheet.Range("A1").Offset(lngCounter, 0).Value = .Fields(0).Value
                 .MoveNext
                 lngCounter = lngCounter + 1
             Loop
         End With
    
        rs.close
        con.Close
    
        Set rs = nothing
        Set con = nothing
    
    End Sub
    
  5. On the Run menu, choose Run Sub/UserForm to run the new subroutine.

    If you get the error "User Defined type not defined.", on the Run menu, choose Reset. On the Tools menu, choose References. In the References dialog box, choose Microsoft Active X Data Objects n Library, and then choose OK. Run the subroutine again.