Fri, 05 Aug 2016
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 OS X 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 need 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 sever 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.
- 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, type:
- Download the Easysoft JDBC-ODBC Bridge. (Registration required.)
- Install and license the Easysoft JDBC-ODBC Bridge on the machine where the DbVisualizer is installed.
For installation instructions, see the Easysoft JDBC-ODBC Bridge documentation.
- In DbVisualizer, choose Tools > Driver Manager.
The Driver Manager window is displayed.
- Choose Driver > Create Driver.
- 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:
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.
- 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
- Close the Driver Manager window.
- Choose Database > Create Database Connection.
- Choose Wizard when prompted.
The New Connection Wizard starts.
- Enter "Easysoft" when prompted to provide a connection alias.
- Choose Easysoft JDBC-ODBC Bridge from the list of drivers when prompted.
- 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
- Choose Finish.
- Choose SQL Commander > New SQL Commander.
- In the new pane, enter and then run a SQL Query to retrieve some data from you ODBC data source in DbVisualizer.