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 any changes in functionality or terms of use for the third-party software described in this section. For more information, refer to the respective software vendor’s website.

Environment

The following procedure uses DbVisualizer Pro Edition on Windows.

Prerequisites

  • A running Exasol database
  • Exasol JDBC driver must be installed. You can download drivers from the Exasol Downloads portal. For more information, see Drivers.
  • DbVisualizer is installed and has network access to the Exasol database

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 Exasol and click Next.

    Connect DbVisualizer to Exasol - Select Database Driver

  2. In the next screen, enter the following connection details: 
    • Database Server: Enter the Connection string.
    • Database Port: Enter the port number. The default port is 8563.
    • Database Userid: Enter your user name.
    • Database Password: Enter your Password.

    3. Connect DbVisualizer to Exasol - Connection Details

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

  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.

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 CTLR+.

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 learn 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.

Favorites or Bookmarks

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

In DBVisualizer, a similar feature is called Bookmarks. To learn 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 learn more about the client's commands, see Using Client-Side Commands in official DbVisualizer documentation.

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.