Share this article:

Vertica Integration with SQuirreL: 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.

Vertica and SQuirreL Software Versions

This document provides guidance using the software listed as follows:

Software Version
Partner Client

SQuirreL 3.7.1

Desktop Platform

Microsoft Server 2008 R2 Standard

Vertica Client

Vertica 9.0 JDBC driver

Vertica Server

Vertica Server version 9.0

Server Platform CentOS 6.7

SQuirreL SQL Client Overview

SQuirreL SQL Client is a graphical Java program that allows you to browse data and schema objects and issue SQL commands in a JDBC-compliant database.

SQuirreL is an open-source product. You can use plugins to extend the functionality of SQuirreL.

For information about SQuirreL, visit the SQuirreL website. If you are new to SQuirreL, see the Introduction document that is posted there.

Download and Install SQuirreL

SQuirreL is available for download on the SQuirreL website, Download the JAR file for the latest version of SQuirreL for your platform.

After you download the JAR, follow the instructions on the Download and Installation page to install SQuirreL.

Note The installer and SQuirreL SQL Client version 3.x require Java JRE 1.6.x or later.

Optional Plugins

The SQuirreL installer prompts you to select optional plugins. One of the plugins is for Vertica. You should install the Vertica plugin to obtain the following additional Vertica-specific information in SQuirreL:

  • Additional tabs for tables, including Content(+), Projections, and Source.
  • A Details tab for projections.

When you choose the Vertica plugin, the installer places the plugin file in the plugins directory under install_path. The plugin file is called vertica.jar.


When you choose the plugin during installation, it is activated automatically. If you later decide you do not want to use the plugin, you can deactivate it. After deactivation, you can reactivate it. Follow the instructions in Activating or Deactivating the Vertica Plugin.

Download and Install the Vertica Client Drivers

Before you can connect to Vertica using SQuirreL, you must install the Vertica client package. This package includes the JDBC client driver that SQuirreL uses to connect to Vertica.

Download Vertica Client Drivers

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

Note Vertica drivers are forward compatible, so you can connect to the Vertica server using previous versions of the client. For more information about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

Install Vertica Client Drivers

Based on the client package you downloaded, follow the installation steps in the Vertica documentation:

Install the JDBC driver in a location that is easily accessible by SQuirreL.

Connect SQuirreL to Vertica

To configure SQuirreL to connect to Vertica you must create a driver object and an alias object.

Create a Driver Object

  1. On the left side of the main SQuirreL window, select the Driver tab.
  2. Click the + icon at the top of the pane to open the Add Driver dialog box.
  3. In the Name field, enter a name for the driver. In the examples that follow, the driver name is Vertica_9.0.
  4. In the Example URL field, add the JDBC URL, including the driver type, IP address, port, and database name. For example:
  5. Go to the Extra Class Path tab, and click Add.
  6. Navigate to the location where you installed the Vertica JDBC client driver and select it.
  7. In the Class name field, enter the JDBC driver class:

  8. Click Ok.
  9. Check the status window at the bottom of the main screen to ensure that the driver was successfully registered. You should see a message like the following:
    Driver class com.vertica.jdbc.Driver successfully registered for driver definition: Vertica_9.0
  10. If you want to automatically connect to Vertica at startup, check the Connect at Startup box.

Create an Alias Object

  1. On the left side of the main SQuirreL window, select the Alias tab.
  2. Click the + icon at the top of the pane to open the Add Alias dialog box.
  3. In the Name field, enter a name for the alias.
  4. From the Driver drop-down list, select the Vertica driver object that you created previously. When you choose the driver, the URL field is filled with the URL from the driver definition.
  5. In the User Name field, enter the name of the Vertica dbadmin user.
  6. In the Password field, enter the password of the Vertica dbadmin user.
  7. If you want to log on to Vertica automatically, check the Auto Logon box.

    Note We recommend that you check the Auto Logon box so that the user ID and password are automatically populated in the following connection test. If you have to retype the user ID and password, you will not be fully testing the alias.

  8. Now you can make your final optional changes to the alias:
    • To set properties, click the Properties button.
    • To automatically connect to Vertica at startup, check Auto Logon.
    • To automatically connect to Vertica at SQuirreL startup, check Connect at Startup.
  9. Click Test to ensure that the alias can connect to Vertica using the driver you defined.

    When you click Test, a Test dialog box opens. The fields are populated with the values you specified for the alias. If you checked Auto Logon, SQuirreL immediately validates the connection and returns success or failure. If you did not check Auto Logon, then you must type the user name and password then click Test.

  10. When the connection is successful, click OK to close the Test dialog box.
  11. Click OK to save the alias configuration.
  12. On the Alias tab, select the alias you created.

    If you configured the alias with Auto Logon, SQuirreL is already connected to your database. The SQuirreL navigator displays the database design.

    If you did not check the Auto Logon box, then click the connection icon above the alias list and provide your credentials to connect to Vertica.

The SQuirreL navigator displays the alias name at the top level. The next level down is the database name. The levels below are the objects in the database. When you select a table, tabs that contain information about the table definition and data appear.


Activating or Deactivating the Vertica Plugin

You can activate or deactivate the Vertica plugin at any time. If you chose the plugin during installation, it is active by default

Activate the Vertica Plugin

  1. From the SQuirreL menu bar, select Plugins -> Summary to open the Plugin Summary dialog box.
  2. Locate the Vertica plugin, check the Load on Startup box, and click Ok.
  3. Restart SQuirreL.
  4. Return to the Plugin Summary dialog box. The Vertica plugin is now loaded, as shown in this example:


Deactivate the Vertica Plugin

To deactivate the Vertica plugin, clear the Vertica Plugin check box.

For more information, see the Vertica Plugin help topic in SQuirreL. Select Help on the SQuirreL main menu to find the topic.


Using Content or Content(+) Tabs on Large Tables

Issue: When you try to use the Content or Content(+) tabs on a large table, SQuirreL does not appear to respond and does not return results.

Action: When you encounter this issue, check the SQuirreL logs as follows:

  1. On the Windows menu in SQuirreL, select View SQuirreL Logs.
  2. Look for a message like this:
    Caused by: com.vertica.util.LRSException: [Vertica][VJDBC](100102) Statement "select * from (select 'VMart' as catalog_name, schema_name, table_name, column_name, case when sql_datetime_sub = 10 then 1111 else data_type end as data_type, data_type_name, column_size, buffer_length, decimal_digits, num_prec_radix, nullable, remarks, column_default as column_def, sql_type_id as sql_data_type, sql_datetime_sub, char_octet_length, ordinal_position, is_nullable, case is_identity when 't' then 'YES' when 'f' then 'NO' else null end as is_autoincrement, 0 as user_data_type from v_catalog.odbc_columns order by catalog_name, schema_name, table_name, ordinal_position) as vmd where CATALOG_NAME ilike 'VMart' escape E'\\' and SCHEMA_NAME ilike 'store' escape E'\\' and TABLE_NAME ilike 'store_orders_fact' escape E'\\' and COLUMN_NAME ilike '%' escape E'\\' " cannot execute because the driver has not finished reading the current open ResultSet. The driver cannot finish reading the current ResultSet because its buffer (8192 bytes) is full. The current ResultSet must be fully iterated through or closed before another statement can execute.

    The key string to look for is:

    The driver cannot finish reading the current ResultSet because its buffer (8192 bytes) is full. 

    Note The number of bytes may differ if you have modified ResultBufferSize.

Explanation: We have seen this error in cases where the client is issuing a new query on the connection while iterating through a result set from a previous query. Vertica only supports execution of a single query at a time on a single connection. Concurrent queries on a single connection only work when the driver's internal result buffer, which is controlled by ResultBufferSize, is not full. If you have too many results, or the results are too large, an error occurs.

Solution: To avoid this error, add the ResultBufferSIze property to the JDBC connection string in your SQuirreL alias. Set the value high enough to fit all results. The default value of ResultBufferSize is 8192 (8k, the limit noted in the error).


The value of Contents – Limit Rows in SQuirreL directly affects the number of rows returned and thus the required buffer size. If you increase the value of Contents – Limit Rows, you may have to increase the value of ResultBufferSize as well.

The Contents - Limit Rows option is available on the Object Tree tab. To navigate to the Object Tree tab, select New Session Properties under the SQuirreL File menu.

Creating Vertica Functions in the SQL Commander Window

Creating a Vertica function in the SQL Commander window may result in an error such asL

[CREATE - 0 rows, 0.001 secs] [Code: 4856, SQL State: 42601] [Vertica][VJDBC](4856) ERROR: Syntax error at or near "EOL"

This is because CREATE FUNCTION has multiple statement end characters as part of the CREATE command syntax. You need to change the Statement End character in Session Properties to something like the pipe symbol (|) temporarily. After you execute the SQL CREATE function, then set the delimiter back to the semicolon (;).

Known Limitations


Some DECIMAL values in Vertica appear rounded off to 5 decimal places in SQuirreL.

To resolve this issue:

  1. On the SQuirreL File menu, select Global Preferences.
  2. Select Data Type Controls.
  3. Set FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL (SQL Types 6,7,8,2,3) to Use default format(3.14159)



INTERVALSECONDS and INTERVALMONTH datatype values display as other in SQuirreL.

To resolve this issue:

  1. On the SQuirreL File menu, select Global Preferences.
  2. Select Data Type Controls.
  3. Check the boxes for the following:
    • SQL Other (SQL type 1111)
    • Unknown DataTypes (non-standard SQL type codes)


UUID Data Type

SQuirreL will need to implement a UUID data type to support the Vertica UUID data type natively in the Content tab and SQL window.

To render a Vertica UUID column in SQuirreL:

  1. Open the SQL window in SQuirreL client.
  2. Cast the UUID column to VARCHAR. For example:

    SELECT DataTypeSet,ValueDesc,cast(UUID_Column as VARCHAR) FROM DT1_0_6_OOB.UUID_Table;

Content(+) Tab

The Content(+) tab, added by the Vertica plugin, improves the efficiency of SQL queries and adds a LIMIT argument. Apparently, the Content(+) tab is getting the LIMIT value from the Content – Limit Objects property (noted in the Troubleshooting topic) instead of the SQL Result – Limit Rows property. This issue is under investigation.

For More Information

Share this article: