Working with ODBC data in DbVisualizer

DbVisualizer provides an Integrated Development Environment (IDE) for writing and executing SQL scripts, with auto completion and syntax highlighting features provided to make life easier. If you don't know any SQL, a query builder tool is provided to automatically generate the SQL for you.

DbVisualizer allows you to export table data in the following formats: CSV, SQL, HTML, XML, Excel, JSON, and Text. You can also import table data from CSV files into existing tables or create tables from the imported data.

DbVisualizer is a multiplatform Java application, and will run on Windows, Linux, and macOS machines. The minimum version of Java required by DbVisualizer is 1.8. This has implications for the type of databases that DbVisualizer can access. To access a database for which only an ODBC driver is available, a Java application such as DbVisualizer needs a piece of middleware known as a JDBC-ODBC Bridge to translate between the data access technology the application supports (JDBC) and the one that the database supports (ODBC). For example, a JDBC-ODBC Bridge would be required to connect DbVisualizer to Microsoft Access by using the ODBC driver that Microsoft supply for this database. Java 1.7 and earlier included a JDBC-ODBC Bridge. This was removed in Java 1.8.

As an alternative solution, the Easysoft JDBC-ODBC Bridge is a client/server product that enables a Java application such as DbVisualizer to access an ODBC database such as Microsoft Access. The client part of the Easysoft JDBC-ODBC Bridge is a JDBC driver. On Windows, the server part is a Windows service that acts as an ODBC application.

The rest of this blog documents how to use the Easysoft JDBC-ODBC Bridge to access ODBC data from an ODBC database in DbVisualizer.

1. Configure a System ODBC data source for the database that you want to connect to in DbVisualizer.

   To do this, use the 32-bit version of ODBC Data Source Administrator on your DbVisualizer machine. On some versions of Windows, this is located in Control Panel > Administrative Tools. On some version of Windows, you need to search for "ODBC" in the taskbar search box. The 32-bit version of ODBC Data Source Administrator should be clearly labelled. If in doubt, in the Windows Run dialog box, enter:

   %windir%\syswow64\odbcad32.exe

2. Download the Easysoft JDBC-ODBC Bridge.
3. Install and license the Easysoft JDBC-ODBC Bridge on the machine where the DbVisualizer is installed.

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

1. In DbVisualizer, choose Tools > Driver Manager.

   The Driver Manager window is displayed.

2. Choose Driver > Create Driver.
3. In the Driver File Paths area, use the Folder button to browse for the Easysoft JDBC-ODBC Bridge JAR file, EJOB.jar.

   EJOB.jar is installed in the following location on the machine where you installed the Easysoft JDBC-ODBC Bridge:

   easysoft_install\Jars

   On 64-bit Windows, the default location for easysoft_install is drive:\Program Files (x86)\Easysoft Limited\Easysoft JDBC-ODBC Bridge.

   On 32-bit Windows, the default location for easysoft_install is drive:\Program Files\Easysoft Limited\Easysoft JDBC-ODBC Bridge.

4. Complete the Driver settings pane:

   Field	Value
   Driver Name	Easysoft JDBC-ODBC Bridge
   Driver	easysoft.sql.jobDriver
   URL Template	jdbc:easysoft://localhost/data_source:logonuser=windows_user:logonpassword=password

5. Close the Driver Manager window.
6. Choose Database > Create Database Connection.
7. Choose Wizard when prompted.

   The New Connection Wizard starts.

8. Enter Easysoft when prompted to provide a connection alias.
9. Choose Easysoft JDBC-ODBC Bridge from the list of drivers when prompted.
10. Complete the final screen of the wizard:

    Field	Value
    Database URL	jdbc:easysoft://localhost/data_source:logonuser=windows_user:logonpassword=password
    Database Userid	my_database_user Only complete this field if your database requires a password.
    Database Password	my_database_password

11. Choose Finish.
12. Choose SQL Commander > New SQL Commander.
13. In the new pane, enter and then run a SQL Query to retrieve some data from you ODBC data source in DbVisualizer.