DbVisualizer

This article explains how to use DbVisualizer with Exasol.

DbVisualizer Pro Edition is the SQL client recommended by Exasol and is used extensively in our documentation and training materials. To learn more about the benefits of using DbVisualizer Pro with Exasol, see Exasol Specific Features.

For more information about how to use DbVisualizer, refer to the DbVisualizer Official Documentation.

Exasol takes no responsibility for changes in functionality or terms of use for third-party tools. For more details about the compatibility and use of these tools with Exasol, refer to the respective software vendor’s website.

Environment

The following procedure uses DbVisualizer Pro Edition on Windows.

Prerequisites

  • Your Exasol SaaS database must be running. For more information, see Database Management.

  • The IP address of the host where the tool is running must be in the allowed IP address list. For more information, see Network Security.

  • Exasol JDBC driver is installed
  • DbVisualizer is installed

Get JDBC Driver and Connection Details

To connect to your Exasol SaaS database from a tool you need a valid connection string. You must also allow network access to the database from the tool, and make sure that the latest Exasol JDBC driver is installed.

  1. On the Databases page in the web console, click on Connect via tools on the cluster that you want to connect to.

    connect via tools

  2. In the wizard, enter the IP address of the client or server that will connect to the database and click Add IP, then click Next. This will add the IP address to the list of IP addresses that are allowed network access to the cluster. The allow list is found on the Security page in the web console. For more information, see Network Security.

    If the IP address is already present in the allow list, just click Next.

    enter ip address

  3. If the latest JDBC driver for your operating system is not already installed, select the driver type and operating system and click on Download to download the latest driver to your local system. For more information about how to install the driver, see JDBC Driver.

    download driver

  4. Click on Next to view your connection details:

    connection details

  5. Copy the Connection string, Port, and User name values from the Connect details dialog. You will need to enter these details in the tool that should connect to the Exasol SaaS database.

  6. To generate a new Personal Access Token (PAT) to use for authentication in the client, click on the Password row, then click on Copy to copy the PAT to your clipboard. For more information, see Personal Access Token.

    generated pat

    The generated PAT is only shown once and cannot be retrieved after you have closed the wizard.

  7. Click on Done to close the wizard.

Add the JDBC Driver in DbVisualizer

To be able to connect DbVisualizer to the Exasol database, the latest Exasol JDBC driver must be manually added in DbVisualizer. This only has to be done the first time you install 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 in DbVisualizer step and click Next.

  2. In the next screen, enter the following connection details you copied from the Get JDBC Driver 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.