Vertica Integration with RazorSQL: Connection Guide

About Vertica Connection Guides

Vertica connection guides provide basic instructions for connecting a third-party partner product to Vertica. Connection guides are based on our testing with specific versions of Vertica and the partner product.

RazorSQL Overview

RazorSQL is a SQL editor, database query tool, database browser, and database administration tool. It has features like Visual tools for creating and modifying database objects and for importing and exporting data in various file formats. It is an SQL formatting tool, history tracking, and a favorite tool for storing frequently used SQL statements.

Vertica and RazorSQL: Latest Versions Tested

This document is based on our testing using the following versions:

Software Version
Partner Products RazorSQL 10.0.4
Partner Product Platform

Microsoft Windows Server 2019

Vertica Client

Vertica JDBC driver 11.1.1
Vertica Server

Vertica Server 11.1.1

Note We tested RazorSQL with both the named connector Vertica JDBC Driver 6.1.3 that is bundled with RazorSQL and Vertica JDBC Driver 11.1.1.

Installing RazorSQL

To find the RazorSQL platform requirements, installation instructions, and software downloads, navigate to the RazorSQL website.

Installing the Vertica Client Driver

RazorSQL includes Vertica as a supported database and provides the Vertica 6.1.3 JDBC driver by default. If you prefer to use a newer Vertica JDBC or ODBC driver, you can download one from the Vertica Client Drivers page. To download and install a Vertica client driver

  1. Go to the Vertica Client Drivers page.
  2. Download the Vertica client package that is compatible with your Vertica server version.

Note For more information about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

Follow the steps for installation in the Vertica documentation.

Connecting RazorSQL to Vertica

To create a RazorSQL connection profile and connect to your Vertica database

  1. Open RazorSQL.
  2. In the Connections tab, select Add Connection Profile.

    The Connection Wizard opens.

  3. Select Vertica and click Continue.

  1. In Connection Type, choose one of the following drivers.
    • JDBC (Vertica): The default driver.

      Note The default JDBC (Vertica) driver is bundled with the old 6.1.3 driver. To use the latest version of the driver, go to the next step.


    • JDBC: Allows you to use a JDBC driver of your choice.

    • ODBC: Allows you to use an ODBC driver of your choice.
  2. Enter your connection information in the corresponding fields.

    By default, Auto Commit is set to On and SQL Restrictions is set to None.

  3. Click Connect.

    RazorSQL connects to your Vertica database.

After successfully connecting to RazorSQL, the database browser interface opens. You can now explore the tools offered by RazorSQL.

Troubleshooting

When you right-click a function and select Execute Procedure it fails with a Driver not capable error. This error appears because Vertica functions are not intended to be run as standalone functions. Vertica functions are typically used within a SQL string and passed arguments.

4.PNG

Known Limitations

  • For TIME data type, milliseconds are truncated.

  • For TIMETZ data type, milliseconds and time zone values are not displayed.

  • For TIMESTAMPTZ, time zone is not displayed.

  • BINARY, VARBINARY, and LONGVARBINARY data types are not displayed correctly. Values are displayed in hexadecimal format.

  • In the object tree, all Vertica User-Defined Functions (UDFs) appear erroneously in the FUNCTIONS, SYSTEM TABLE, and VIEW folders. They should appear only in the FUNCTIONS folder. This is an issue in the GUI only; it causes no performance issues.

For More Information