Easysoft ODBC-JDBC Gateway

How do I connect an ODBC application to Neo4j?

Article:
01098
Last Reviewed:
30th January 2024
Revision:
1

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 / 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 do not have to install the ODBC-JDBC Gateway on the same machine as Neo4j. However, you will need to install Neo4j's recommended JDK distribution. You will 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, 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 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 Administrator, which is included with Windows.

There are two versions of ODBC Administrator. The version of ODBC 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.

Application Notes
Access There is both a 32-bit and a 64-bit version of Access. start Microsoft Access and then look for the Access process in Task Manager. 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. Look 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.
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')

If you have the 64-bit version of SQL Server and want to use a linked server with the ODBC-JDBC Gateway, your application is 64-bit.

If you have the 32-bit version of SQL Server or want to use SSIS with the ODBC-JDBC Gateway, your application is 32-bit.

Tableau Tableau is a 32-bit application.

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

%windir%\system32\odbcad32.exe

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

%windir%\syswow64\odbcad32.exe

Use ODBC 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 is not possible to use Excel's GUI tools (Data Connection Wizard / 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 click OK. Run the subroutine again.

Applies To

Knowledge Base Feedback

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

(* Required Fields)