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

Following environment is used as an example:

  • DbVisualizer Community Edition
  • Windows Operating System

Prerequisite

  • Exasol should be installed and the database instance should be online.
  • A public network where both Exasol database and the host containing DbVisualizer can use to communicate.
  • DbVisualizer (version 10.x) should be installed and should be able to ping and connect to Exasol database.

Procedure

No other connection in DbVisualizer

Do the following to connect to Exasol database from DbVisualizer:

  1. Open DbVisualizer. Launching DbVisualizer opens up the New Connection Wizard.
  2. Enter a connection alias (for example, Exasol) name and click Next.

    Connect DbVisualizer to Exasol

  1. Select Exasol from the Select Database Driver drop-down.

    Connect DbVisualizer to Exasol - Select Database Driver

  2. Enter the following details and click Finish to establish the connection:
    • Database server URL
    • Database port
    • Database user ID
    • Database password

    Connect DbVisualizer to Exasol - Connection Details

If the Exasol installation instance is new, the username is sys and the password is exasol.

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

  1. Select SQL CommanderNew SQL Commander to create a new SQL Commander.

    Connect DbVisualizer to Exasol - New SQL Commander

  1. Run your SQL statements in the new SQL commander.

    Connect DbVisualizer to Exasol - Run SQL Statement

Have other connections in DbVisualizer

Do the following if you already have connections in DbVisualizer and the connection wizard is not displayed:

  1. Open DbVisualizer.
  2. Select Databases > Create Database Connection.

    Connect DbVisualizer to Exasol - Create Database Connection

  1. Select Exasol from the Database Driver drop-down.

    Connect DbVisualizer to Exasol - Select Database Details

  1. Enter the following details and then click Connect to establish the connection:
    • Database server URL
    • Database port
    • Database user ID
    • Database password

Connect DbVisualizer to Exasol - Connection Details

If the Exasol installation instance is new, the username is sys and the password is exasol.

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

  1. Click SQL CommanderNew SQL Commander to create a new SQL Commander.
  1. Run your SQL statements in the new SQL commander.

EXAplus to DbVisualizer

Here are some best practices if you are moving from the old EXAplus client to DbVisualizer:

Keyboard Shortcuts

In EXAplus, the keyboard shortcuts for the statements start with CTRL+Enter. In DbVisualizer, it is CLTR+.

If you want to configure the shortcuts, see Changing Keyboard Shortcuts in DbVisualizer documentation.

Auto Complete

In EXAplus, to use the auto completion of table/column names you need to first type the schema name. In DbVisualizer, you can directly access the table or column name without the need to fully qualify the object. In DBVisualizer, if you are in the context of a schema, only the tables of that schema are shown in the auto completion. To know more about the feature, see Auto Completion in DbVisualizer documentation.

You can also customize the behavior of the auto-completion from Tools >Tool Properties>SQL Commander>Auto Completion.

Creating LUA Scripts and UDFs

In DBVisualizer, LUA scripts / UDFs should be created as an SQL block. The start of the SQL Block is --/ and the end of the block is defined with /. For example:

--/
CREATE LUA SCRIPT MY_CAT RETURNS TABLE AS

return query([[select * from cat]])

/

By default, the keywords in DbVisualizer are displayed in uppercase. So if you open an existing LUA script, keywords are automatically transformed to uppercase and they won't work. To change this setting:

  1. Go to Tool Properties > Tools > SQL Formatting.
  2. Select Keep Case As is in the Case drop-down.

Additionally, see Executing Complex Statements in official DbVisualizer official documentation.

Favorites or Bookmarks

In EXAplus, frequently used SQL Statements are stored in the favorite tab.

In DBVisualizer, a similar feature is called Bookmarks. To know more about the bookmarks feature, see Managing Frequently Used SQL in official DbVisualizer documentation:

EXAplus Commands

EXAplus commands like set autocommit off won't work in DbVisualizer. DbVisualizer has its own syntax for its client commands. The commands start with @, for example, @set autocommit off;

To know more about the client's commands, see Using Client-Side Commands in official DbVisualizer documentation.

Known Issues

Open Schema Call

DbVisualizer doesn't interpret the open schema call currently.

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

If the newly created schema is not visible in the SQL Editor drop-down, reconnect to the connection.

Workaround

Right click the connection and select Reconnect.

R scripts sent from a Windows OS using DbVisualizer are not executed

The issue occurs because DbVisualizer uses the platforms linefeed convention CRLF for Windows and LF for all other platforms.

Workaround:

  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.