DbVisualizer

This section describes how to connect DbVisualizer to Exasol and run an SQL statement. DbVisualizer Pro is the recommended SQL client by Exasol. To know more about the benefits of the Pro version, see Exasol Specific Features.

Environment

The following environment is used as an example:

  • DbVisualizer Pro Edition on Windows
  • Exasol SaaS

Prerequisite

  • DbVisualizer installed.
  • A running Exasol SaaS database (see Manage Databases and Clusters for more information).
  • Your local IP should be listed in the allowed IP list (see Network Security for more information).
  • Download the latest Exasol driver and set DbVisualizer to use that driver. If you have installed the JDBC 7.1 or later driver, you have the correct driver.

Get Drivers and Connection Details

Before you can proceed with connecting to your database from a tool, download the latest Exasol JDBC driver and get the connection details. Follow these steps to get your connection details: 

  1. Click Databases Databases in the web console to open the Databases page.
  2. Click More on the cluster you want to connect to and then click Connect via tools.
  3. In the Connect to cluster wizard, enter the IP address of the client or server which will connect to the database and click Add IP. Then click Next. If the IP address was already added, just click Next.
  4. If not already installed, download the latest JDBC driver for your operating system and install it.
  5. Click Next and proceed through the wizard until you see your connection details:

  6. Copy the HOST, PORT, USERNAME, and PASSWORD. You can generate a new Personal Access Token by clicking Generate. You will need this information to connect to your database.

Add the JDBC Driver

Connecting to a SaaS database is only possible using the Exasol 7.1 JDBC driver or later, which must be added to DbVisualizer manually. You only need to perform these steps once per DbVisualizer installation. Do the following to add the JDBC Driver to DbVisualizer:

  1. Open DbVisualizer.
  2. In the top navigation, select Tools > Driver Manager.
  3. To add a new driver, click the + icon in the toolbar.
  4. Enter the following details on the Driver Settings screen:
    • Name: A name for the driver. For example, Exasol 7.1.
    • URL Format: jdbc:exa:
    • Driver Class: Leave this field empty. It is automatically set in the next step.
  5. Click the folder icon in the below section of the screen and locate the JDBC driver file exajdbc.jar for the driver. On a Windows system, you will find the jar files at C:\Program Files\Exasol\EXASolution-7.1\JDBC. In other systems, navigate to the folder that contains the JDBC driver. Make sure you choose the exajdbc.jar file.
  6. The Driver Class field is automatically filled when you select the correct driver.
  7. Close the Driver Manager screen.

Connect to Your Database

Do the following to connect to your Exasol database from DbVisualizer:

  1. Open DbVisualizer and select Tools > Connection Wizard.
  2. Enter a name for the connection in the New connection Wizard(for example, Exasol) and click Next.

    3. Connect DbVisualizer to Exasol

  1. In the Select Database Driver screen, select the driver corresponding to the Exasol JDBC Driver you added in the Add the JDBC Driver step and click Next.

  2. In the next screen, enter the following connection details you copied from the Get Drivers and Connection Details procedure: 
    • Database Server: Enter the Host string you copied from the web console.
    • Database Port: Enter the port number 8563.
    • Database Userid: Enter your user name you copied from the web console.
    • Database Password: Enter your Personal Access Token you copied from web console.

    3. Connect DbVisualizer to Exasol - Connection Details

  3. To check your connection, click Ping Server .
  4. To complete the connection configuration, click Finish.

    After connecting to the Exasol database, Exasol is listed in the Databases tab under the Connections tree.

  5. To create a new SQL Commander, click SQL Commander > New SQL Commander.

    Connect DbVisualizer to Exasol - New SQL Commander

  6. Run your SQL statements in the new SQL Commander.

    Connect DbVisualizer to Exasol - Run SQL Statement

Recommended Settings

To remove issues with --/, click Tool Properties > General > SQL Commander > Statement Delimiters and select Allow SQL Dialects.

Troubleshooting

Problem DbVisualizer has problem with delimiting Scripts.

Solution: To tell DbVisualizer that a part of a script should be handled as a single statement, you can insert an SQL block begin-identifier just before the block and an end-identifier after the block. The delimiter must be the only text on the line. The default begin-identifier consists of two dashes followed by a forward slash (--/) and for the End Identifier it is a single slash (/).

Problem: New Schema isn't visible in SQL Editor drop-down.

Solution: If the newly created schema is not visible in the SQL Editor drop-down, right click the connection and select Reconnect.

Problem: R scripts sent from a Windows OS using DbVisualizer are not executed. This issue occurs because DbVisualizer uses the platforms linefeed convention CRLF for Windows and LF for all other platforms.

Solution:

  1. In DbVisualizer installation folder, open the file /resources/dbvis-custom.prefs in a text editor.
  2. Add dbvis.exasol.ReplaceCRLFwithLF=true as a new row and save the file.
  3. Restart DbVisualizer.

Next Step

After connecting DbVisualizer to Exasol, you can start loading data into the Exasol database. For more information, see Loading Data.

Further Information

For more information about DbVisualizer, see DbVisualizer Official Documentation.