Connecting to Vertica

• 1: Using vsql
• 1.1: Installing the vsql client
• 1.2: vsql usage on Windows
• 1.3: Connecting from the administration tools
• 1.4: Connecting from the command line
• 1.4.1: Command-line options
• 1.4.1.1: -A --no-align
• 1.4.1.2: -a --echo-all
• 1.4.1.3: -c --command
• 1.4.1.4: -d --dbname
• 1.4.1.5: -E
• 1.4.1.6: -e --echo-queries
• 1.4.1.7: -r --workload
• 1.4.1.8: -F --field-separator
• 1.4.1.9: -f --file
• 1.4.1.10: ? --help
• 1.4.1.11: -H --html
• 1.4.1.12: -h --host
• 1.4.1.13: -i -- timing
• 1.4.1.14: -g --label
• 1.4.1.15: -l --list
• 1.4.1.16: -m --sslmode
• 1.4.1.17: -n
• 1.4.1.18: -o --output
• 1.4.1.19: -P --pset
• 1.4.1.20: -p --port
• 1.4.1.21: -q --quiet
• 1.4.1.22: -R --record-separator
• 1.4.1.23: -S --single-line
• 1.4.1.24: -s --single-step
• 1.4.1.25: -T --table-attr
• 1.4.1.26: -t --tuples-only
• 1.4.1.27: -V --version
• 1.4.1.28: -v --variable --set
• 1.4.1.29: -X --no-vsqlrc
• 1.4.1.30: -x --expanded
• 1.4.2: Connecting from a non-cluster host
• 1.5: Meta-commands
• 1.5.1: Meta-commands quick reference
• 1.5.2: \connect
• 1.5.3: \d meta-commands
• 1.5.4: \edit
• 1.5.5: \i
• 1.5.6: \locale
• 1.5.7: \pset
• 1.5.8: \set
• 1.5.9: \timing
• 1.6: Variables
• 1.6.1: DBNAME
• 1.6.2: ECHO
• 1.6.3: ECHO_HIDDEN
• 1.6.4: ENCODING
• 1.6.5: HISTCONTROL
• 1.6.6: HISTSIZE
• 1.6.7: HOST
• 1.6.8: IGNOREEOF
• 1.6.9: ON_ERROR_STOP
• 1.6.10: PORT
• 1.6.11: PROMPT1 PROMPT2 PROMPT3
• 1.6.12: QUIET
• 1.6.13: ROWS_AT_A_TIME
• 1.6.14: SINGLELINE
• 1.6.15: SINGLESTEP
• 1.6.16: USER
• 1.6.17: VERBOSITY
• 1.6.18: VSQL_HOME
• 1.6.19: VSQL_SSLMODE
• 1.7: Prompting
• 1.8: Command line editing
• 1.9: vsql environment variables
• 1.10: Locales
• 1.11: Entering data with vsql
• 1.12: Files
• 1.13: Exporting data using vsql
• 1.14: Copying data using vsql
• 1.15: Output formatting examples
• 2: Client libraries
• 2.1: Client driver and server version compatibility
• 2.2: Client drivers
• 2.2.1: Installing and configuring client drivers
• 2.2.1.1: Windows client driver installer
• 2.2.1.1.1: System prerequisites
• 2.2.1.1.1.1: .NET framework
• 2.2.1.1.1.2: Microsoft SQL server
• 2.2.1.1.2: Uninstalling, modifying, or repairing the client drivers and tools
• 2.2.1.2: FIPS client drivers
• 2.2.1.2.1: Installing the FIPS client driver for JDBC
• 2.2.1.2.2: Installing the FIPS client driver for ODBC and vsql
• 2.2.1.3: JDBC client driver
• 2.2.1.3.1: Installing the JDBC client driver
• 2.2.1.3.2: Modifying the Java CLASSPATH
• 2.2.1.4: ODBC client driver
• 2.2.1.4.1: Installing the ODBC client driver
• 2.2.1.4.2: Upgrading and downgrading ODBC
• 2.2.1.4.3: Uninstalling ODBC
• 2.2.1.4.4: Creating an ODBC data source name (DSN)
• 2.2.1.4.4.1: Creating an ODBC DSN for Linux
• 2.2.1.4.4.1.1: Testing an ODBC DSN using isql
• 2.2.1.4.4.2: Creating an ODBC DSN for windows clients
• 2.2.1.4.4.2.1: Setting up an ODBC DSN
• 2.2.1.4.4.2.2: Encrypting passwords on ODBC DSN
• 2.2.1.4.4.2.3: Testing an ODBC DSN using Excel
• 2.2.1.4.4.3: Creating an ODBC DSN for macOS clients
• 2.2.1.4.4.3.1: Testing an ODBC DSN using iodbctest
• 2.2.1.4.4.4: ODBC DSN connection properties
• 2.2.1.4.4.5: Setting DSN connection properties
• 2.2.1.4.5: ODBC driver settings
• 2.2.1.4.6: Configuring ODBC logs
• 2.2.1.5: Python client drivers
• 2.2.1.5.1: Installing Python client drivers
• 2.2.1.6: Node.js client driver
• 2.2.1.7: Go client driver
• 2.2.1.8: OLE DB client driver
• 2.2.1.8.1: Installing the OLE DB client driver
• 2.2.1.8.1.1: OLE DB connection properties
• 2.2.1.8.1.2: Configuring OLE DB logs
• 2.2.1.9: ADO.NET client driver
• 2.2.1.9.1: Installing the ADO.NET client driver
• 2.2.1.9.2: Log properties
• 2.2.2: Upgrading the client drivers
• 2.2.3: Setting a client connection label
• 2.2.4: Using legacy drivers
• 2.3: Accessing Vertica
• 2.3.1: C/C++
• 2.3.1.1: ODBC architecture
• 2.3.1.2: ODBC feature support
• 2.3.1.3: Vertica and ODBC data type translation
• 2.3.1.4: ODBC header file
• 2.3.1.5: Canceling ODBC queries
• 2.3.1.6: Connecting to the database
• 2.3.1.7: Load balancing
• 2.3.1.8: Configuring TLS for ODBC Clients
• 2.3.1.9: Connection failover
• 2.3.1.10: Prompting windows users for missing connection properties
• 2.3.1.11: Prompting windows users for passwords
• 2.3.1.12: Setting the locale and encoding for ODBC sessions
• 2.3.1.13: AUTOCOMMIT and ODBC transactions
• 2.3.1.14: Retrieving data
• 2.3.1.15: Loading data
• 2.3.1.15.1: Using a single row insert
• 2.3.1.15.2: Using prepared statements
• 2.3.1.15.3: Using batch inserts
• 2.3.1.15.3.1: Tracking load status (ODBC)
• 2.3.1.15.3.2: Error handling during batch loads
• 2.3.1.15.4: Using the COPY statement
• 2.3.1.15.5: Streaming data from the client using COPY LOCAL
• 2.3.2: C#
• 2.3.2.1: ADO.NET data types
• 2.3.2.2: Setting the locale for ADO.NET sessions
• 2.3.2.3: Connecting to the database
• 2.3.2.3.1: Configuring TLS for ADO.NET
• 2.3.2.3.2: Opening and closing the database connection (ADO.NET)
• 2.3.2.3.3: ADO.NET connection properties
• 2.3.2.3.4: Load balancing in ADO.NET
• 2.3.2.3.5: ADO.NET connection failover
• 2.3.2.4: Querying the database using ADO.NET
• 2.3.2.4.1: Inserting data (ADO.NET)
• 2.3.2.4.1.1: Using parameters
• 2.3.2.4.1.2: Creating and rolling back transactions
• 2.3.2.4.1.2.1: Setting the transaction isolation level
• 2.3.2.4.2: Reading data (ADO.Net)
• 2.3.2.4.3: Loading data through ADO.Net
• 2.3.2.4.3.1: Using the Vertica data adapter
• 2.3.2.4.3.2: Using batch inserts and prepared statements
• 2.3.2.4.3.3: Streaming data via ADO.NET
• 2.3.2.4.3.3.1: Streaming from the client via VerticaCopyStream
• 2.3.2.4.3.3.2: Using copy with ADO.NET
• 2.3.2.5: Canceling ADO.NET queries
• 2.3.2.6: Handling messages
• 2.3.2.7: Getting table metadata
• 2.3.3: Go
• 2.3.4: Java
• 2.3.4.1: JDBC feature support
• 2.3.4.2: Creating and configuring a connection
• 2.3.4.2.1: JDBC connection properties
• 2.3.4.2.2: Setting and getting connection property values
• 2.3.4.2.3: Configuring TLS for JDBC clients
• 2.3.4.2.4: Setting and returning a client connection label
• 2.3.4.2.5: Setting the locale for JDBC sessions
• 2.3.4.2.6: Changing the transaction isolation level
• 2.3.4.2.7: JDBC connection pools
• 2.3.4.2.8: Load balancing in JDBC
• 2.3.4.2.9: JDBC connection failover
• 2.3.4.3: JDBC data types
• 2.3.4.3.1: The VerticaTypes class
• 2.3.4.3.2: Numeric data alias conversion
• 2.3.4.3.3: Using intervals with JDBC
• 2.3.4.3.4: UUID values
• 2.3.4.3.5: Complex types in JDBC
• 2.3.4.3.6: Date types in JDBC
• 2.3.4.4: Executing queries through JDBC
• 2.3.4.5: Canceling JDBC queries
• 2.3.4.6: Loading data through JDBC
• 2.3.4.6.1: Using a single row insert
• 2.3.4.6.2: Batch inserts using JDBC prepared statements
• 2.3.4.6.2.1: Error handling during batch loads
• 2.3.4.6.2.2: Identifying accepted and rejected rows (JDBC)
• 2.3.4.6.2.3: Rolling back batch loads on the server
• 2.3.4.6.3: Bulk loading using the COPY statement
• 2.3.4.6.4: Streaming data via JDBC
• 2.3.4.6.4.1: Using VerticaCopyStream
• 2.3.4.6.4.2: Using COPY LOCAL with JDBC
• 2.3.4.7: Handling errors
• 2.3.4.7.1: SQLState mapping to Java exception classes
• 2.3.4.8: Routing JDBC queries directly to a single node
• 2.3.4.8.1: Creating tables and projections for use with the routable query API
• 2.3.4.8.2: Creating a connection for routable queries
• 2.3.4.8.3: Defining the query for routable queries using the VerticaRoutableExecutor class
• 2.3.4.8.4: Defining the query for routable queries using the VGet class
• 2.3.4.8.5: Routable query performance and troubleshooting
• 2.3.4.8.6: Pre-segmenting data using VHash
• 2.3.4.9: Timezones and daylight savings time
• 2.3.5: JavaScript
• 2.3.6: Perl
• 2.3.6.1: Configuring a Perl development environment
• 2.3.6.2: Connecting to Vertica using Perl
• 2.3.6.2.1: Setting ODBC connection parameters in Perl
• 2.3.6.2.2: Setting Perl DBI connection attributes
• 2.3.6.2.3: Connecting from Perl without a DSN
• 2.3.6.3: Executing statements using Perl
• 2.3.6.4: Batch loading data using Perl
• 2.3.6.5: Using COPY LOCAL to load data in Perl
• 2.3.6.6: Querying using Perl
• 2.3.6.7: Conversions between Perl and Vertica data types
• 2.3.6.8: Perl unicode support
• 2.3.7: Python
• 2.3.7.1: Configuring the ODBC run-time environment on Linux
• 2.3.7.2: Querying the database with pyodbc
• 2.3.8: PHP
• 2.3.8.1: Configuring a PHP development environment
• 2.3.8.2: PHP unicode support
• 2.3.8.3: Querying the database using PHP
• 2.4: Managing query execution between the client and Vertica
• 2.4.1: ResultBufferSize
• 2.4.2: Multiple active result sets (MARS)
• 3: Management API
• 3.1: cURL
• 3.2: General API information
• 3.2.1: GET /
• 3.2.2: GET api
• 3.3: Rest APIs for the agent
• 3.3.1: VerticaAPIKey
• 3.3.2: Backup and restore
• 3.3.2.1: GET backups
• 3.3.2.2: POST backups/:config_script_base
• 3.3.2.3: GET backups/:config_script_base/:archive_id
• 3.3.2.4: POST restore/:archive_id
• 3.3.3: Databases
• 3.3.3.1: GET databases
• 3.3.3.2: POST databases
• 3.3.3.3: GET databases/:database_name
• 3.3.3.4: PUT databases/:database_name
• 3.3.3.5: DELETE databases/:database_name
• 3.3.3.6: GET databases/:database_name/configuration
• 3.3.3.7: PUT databases/:database_name/configuration
• 3.3.3.8: GET databases/:database_name/hosts
• 3.3.3.9: POST databases/:database_name/hosts
• 3.3.3.10: DELETE databases/:database_name/hosts/:host_id
• 3.3.3.11: POST databases/:database_name/hosts/:host_id/process
• 3.3.3.12: GET databases/:database_name/license
• 3.3.3.13: GET databases/:database_name/licenses
• 3.3.3.14: DELETE databases/:database_name/hosts/:host_id/process
• 3.3.3.15: POST databases/:database_name/hosts/:host_id/replace_with/:host_id_new
• 3.3.3.16: GET databases/:database_name/nodes
• 3.3.3.17: GET databases/:database_name/nodes/:node_id
• 3.3.3.18: POST databases/:database_name/process
• 3.3.3.19: GET databases/:database_name/process
• 3.3.3.20: DELETE databases/:database_name/process
• 3.3.3.21: POST databases/:database_name/rebalance/process
• 3.3.3.22: POST databases/:database_name/Workload analyzer/process
• 3.3.4: Hosts
• 3.3.4.1: GET hosts
• 3.3.4.2: GET hosts/:hostid
• 3.3.5: Jobs
• 3.3.5.1: GET jobs
• 3.3.5.2: GET jobs/:id
• 3.3.6: Licenses
• 3.3.6.1: POST licenses
• 3.3.6.2: GET licenses
• 3.3.7: Nodes
• 3.3.7.1: GET nodes
• 3.3.7.2: GET nodes/:nodeid
• 3.3.8: Webhooks
• 3.3.8.1: GET webhooks
• 3.3.8.2: POST webhooks/subscribe
• 3.3.8.3: DELETE webhooks/:subscriber_id
• 3.4: Rest APIs for the Management Console
• 3.4.1: MC-User-ApiKey
• 3.4.2: GET alerts
• 3.4.3: GET mcTimeInfo
• 3.4.4: Thresholds category filter
• 3.4.5: Database name category filter
• 3.4.6: Combining sub-category filters with category filters
• 4: Connect with an SSH tunnel
• 5: SQLTools Vertica driver

1 - Using vsql

vsql is a character-based, interactive, front-end utility that lets you type SQL statements and see the results. It also provides a number of meta-commands and various shell-like features that facilitate writing scripts and automating a variety of tasks.

If you are using the vsql client installed on the server, then you can connect from the:

• Administration Tools

• Linux command line

You can also install the vsql client for other supported platforms.

General notes

• SQL statements can be spread over several lines for clarity.

• vsql can handle input and output in UTF-8 encoding. The terminal emulator running vsql must be set up to display the UTF-8 characters correctly. The following example shows the settings in PuTTy:

  

  See also Best Practices for Working with Locales.

• Cancel SQL statements by typing Ctrl+C.

• Traverse command history by typing Ctrl+R.

• When you disconnect a user session, any transactions in progress are automatically rolled back.

• To view wide result sets, use the Linux less utility to truncate long lines.

  1. Before connecting to the database, specify that you want to use less for query output:

     $ export PAGER=less
     

  2. Connect to the database.

  3. Query a wide table:

     => select * from wide_table;
     

  4. At the less prompt, type:

     -S
     

  If a shell running vsql fails (crashes or freezes), the vsql processes continue to run even if you stop the database. In that case, log in as root on the machine on which the shell was running and manually terminate the vsql process. For example: $ ps -ef | grep vertica ... fred 2401 1 0 06:02 pts/1 00:00:00 /opt/vertica/bin/vsql -p 5433 -h test01_site01 quick_start_single ... $ kill -9 2401

Enabling autocommit

By default, you must COMMIT to save changes made in a transaction. To enable automatic commits, see SET SESSION AUTOCOMMIT.

1.1 - Installing the vsql client

This page covers a non-FIPS installation. To install on a FIPS-compliant system, see Installing the FIPS client driver for ODBC and vsql.

Linux and macOS

To install vsql manually on another system:

1. Download vsql.

2. Extract or install vsql:

   • If you downloaded the .tar, create the /opt/vertica/ directory if it does not already exist, copy the .tar to it, navigate to it, and extract the .tar:

     
     $ mkdir -p /opt/vertica/
     $ cp driver_name.tar.gz /opt/vertica/
     $ tar vzxf driver_name.tar.gz
     

   • If you downloaded the .rpm, install it with:

     $ rpm -Uvh driver_name.rpm
     

3. Optionally add the vsql directory to your PATH. For example:

   $ export PATH=$PATH:\opt\vertica\bin
   

4. Make the vsql client executable. For example, to allow all users to run vsql:

   $ chmod ugo+x /path/to/vsql
   

5. Set your shell locale to a locale supported by vsql (which ones?). For example, in your .profile, add:

   export LANG=end_US.UTF-8
   

Windows

To install the vsql client:

1. Download the Windows client driver installer. For details on the drivers included in this installer, see Windows client driver installer.

2. Run the installer and follow the prompts to install the drivers.

3. Reboot your system.

After installing the driver, you can optionally add the vsql directory to your PATH. For example, to append the vsql directory to your PATH with Windows PowerShell for the current session:

PS C:\> $Env:PATH += ";C:\Program Files\Vertica Systems\VSQL64\"

You can verify that the vsql directory is in your PATH by running vsql -?:

PS C:\> vsql -?
This is vsql, the Vertica Analytic Database interactive terminal.

Usage:
  vsql [OPTIONS]... [DBNAME [USERNAME]]

For usage details, see vsql usage on Windows

1.2 - vsql usage on Windows

Font

The default raster font does not work well with the ANSI code page. Set the console font to "Lucida Console."

Console encoding

vsql is built as a "console application." The Windows console windows use a different encoding than the rest of the system, so take care when you use 8-bit characters within vsql. If vsql detects a problematic console code page, it warns you at startup.

To change the console code page, set the code page by entering cmd.exe /c chcp 1252.

Running under cygwin

Verify that your cygwin.bat file does not include the "tty" flag. If the "tty" flag is included in your cywgin.bat file, then banners and prompts are not displayed in vsql.

To verify, enter:

set CYGWIN=binmode tty ntsec

To remove the "tty" flag, enter:

set CYGWIN=binmode ntsec

Additionally, when running under Cygwin, vsql uses Cygwin shell conventions as opposed to Windows console conventions.

Tab completion

Tab completion is a function of the shell, not vsql. Because of this, tab completion does not work the same way in Windows vsql as it does on Linux versions of vsql.

On Windows, instead of using tab-completion, press F7 to pop-up a history window of commands. You can also press F8 after typing a few letters of a command to cycle through commands in the history buffer which begin with the same letters.

1.3 - Connecting from the administration tools

You can use the Administration tools to connect to a database using vsql on any node in the cluster.

1. Log in as the database administrator user; for example, dbadmin.

   Note

   Vertica does not allow users with root privileges to connect to a database for security reasons.

2. Run the Administration Tools.

   $ /opt/vertica/bin/admintools
   

3. On the Main Menu, select Connect to Database.

   

4. If prompted, enter the database password:

   Password:
   

   When you create a new user with the CREATE USER command, you can configure the password or leave it empty. You cannot bypass the password if the user was created with a password configured. You can change a user's password using the ALTER USER command.

5. The Administration Tools connect to the database and transfer control to vsql.

   Welcome to vsql, the Vertica Analytic Database interactive terminal.
   Type:  \h or \? for help with vsql commands
          \g or terminate with semicolon to execute query
          \q to quit
   
   =>
   

1.4 - Connecting from the command line

You can connect to a database using vsql from the command line on multiple client platforms.

If the connection cannot be made for any reason—for example, you have insufficient privileges, or the server is not running on the targeted host—vsql returns an error and terminates.

Syntax

/opt/vertica/bin/vsql [-h host] [ -p port ] [ option...] [ dbname [ username ] ]

Parameters

host

Optional if you connect to a local server. You can provide an IPv4 or IPv6 IP address or a host name.

For Vertica servers that have both IPv4 and IPv6 addressed and you have provided a host name instead of an IP address, you can prefer to use an IPv4 address with the -4 option and to use the IPv6 adddress with the -6 option if the DNS is configured to provide both IPv4 and IPv6 addresses. If you are using IPv6 and provide an IP address, you must append the address with an %interface name.

port

The database server port.

Default: 5433

option

One or more vsql command-line options.

If the database is password protected, you must specify the -w or --password command line option.

dbname

The name of the target database. If unspecified, vsql automatically connects to the database on the specified host and port.

username

A database username, by default your system username.

Exit codes

vsql returns 0 to the shell when it terminates normally. Otherwise, it returns one of the following:

• 1: A fatal error occurred—for example, out of memory or file not found.

• 2: The connection to the server went bad and the session was not interactive

• 3: An error occurred in a script and the variable ON_ERROR_STOP was set.

• Unrecognized words in the command line might be interpreted as database or user names.

Examples

The following example shows how to capture error messages by redirecting vsql output to the output file retail_queries.out:

$ vsql --echo-all < retail_queries.sql > retail_queries.out 2>&1

1.4.1 - Command-line options

This section contains the command-line options for vsql.

General options

--command command
-c command

Runs one command and exits. This command is useful in shell scripts.

Variables set with -v are not processed when referenced in a -c command. To use variables, create a .sql file that references the variable and pass it to vsql with the -f option.

--dbname dbname
-d dbname

Specifies the name of the database to which you want to connect. Using this command is equivalent to specifying dbname as the first non-option argument on the command line.

--file filename
-f filename

Uses the filename as the source of commands instead of reading commands interactively. After the file is processed, vsql terminates.

--help

Displays help about vsql command line arguments and exits.

--timing
-i

Enables the \timing meta-command.

--list
-l

Returns all available databases, then exits. Other non-connection options are ignored. This command is similar to the internal command \list.

--set assignment
--variable assignment
-v assignment

Performs a variable assignment, like the vsql command\set.

--version -V

Prints the vsql version and exits.

--no-vsqlr
-X

Disables all command line editing and history functionality.

Connection options

-4

When resolving hostnames in dual stack environments, prefer IPv4 addresses.

-6

When resolving hostnames in dual stack environments, prefer IPv6 addresses.

-B server:port[,...]

Sets connection backup server/port. Use comma-separated multiple hosts (default: not set). If using an IPv6 address, enclose the address in brackets ([, ]) and place the port outside of the brackets. For example \B [2620:0:a13:8a4:9d9f:e0e3:1181:7f51]:5433

--enable-connection
-load-balance -C

Enables connection load balancing (default: not enabled).

Note

You can only use load balancing with one address family in dual stack environments. For example, if you've configured load balancing for IPv6 addresses, then when an IPv4 client connects and requests load balancing the server does not allow it.

--host hostname
-h hostname

Specifies the host name of the machine on which the server is running.

-k krb-service

Provides the service name portion of the Kerberos principal (default: vertica). Using -k is equivalent to using the drivers' KerberosServiceName connection string.

-K krb-host

Provides the instance or host name portion of the Kerberos principal. -K is equivalent to the drivers' KerberosHostName connection string.

-g client-label
--label client-label

Sets the client label for the connection.

--sslmode
-m

Specifies the policy for making SSL connections to the server. Options are require, prefer, allow, and disable. You can also set the VSQL_SSLMODE variable to achieve the same effect. If the variable is set, the command-line option overrides it.

--port port
-p port

Specifies the TCP port or the local socket file extension on which the server is listening for connections. Defaults to port 5433.

`--username` *`username`*
`-U` *`username`*

Connects to the database as the user username instead of the default.

-w password

Specifies the password for a database user.

Note

Using this command-line option displays the database password in plain text. Use it with care, particularly if you are connecting as the database administrator, to avoid exposing sensitive information.

--password
-W

Forces vsql to prompt for a password before connecting to a database.The password is not displayed on the screen. This option remains set for the entire session, even if you change the database connection with the meta-command \connect.

Output formatting

--no-align
-A

Switches to unaligned output mode. (The default output mode is aligned.)

-b

Beep on command completion.

--field-separator separator
-F separator

Specifies the field separator for unaligned output (default: "|") (-P fieldsep=). (See -A --no-align.) Using this command is equivalent to \pset fieldsep or \f.

--html
-H

Turns on HTML tabular output. Using this command is equivalent to using the \pset format html or the \H command.

--pset assignment
-P assignment

Lets you specify printing options in the style of \pset on the command line. You must separate the name and value with an equals (=) sign instead of a space. Thus, to set the output format to LaTeX, you could write -P format=latex.

-Q

Turns on trailing record separator. Use \pset trailingrecordsep to toggle the trailing record separator on or off.

--record-separator separator
-R separator

Uses separator as the record separator. Using this command is equivalent to using the \pset recordsep command.

--tuples-only
-t

Disables printing of column names, result row count footers, and so on. This is equivalent to the vsql meta-command \t.

--table-attr options
-T options

Allows you to specify options to be placed within the HTML table tag. See \pset for details.

--expanded
-x

Enables extended table formatting mode. This is equivalent to the vsql meta-command \x.

Input and output options

--echo-all
-a

Prints all input lines to standard output as they are read. This approach is more useful for script processing than interactive mode. It is the same as setting the variable ECHO to all.

--echo-queries
-e

Copies all SQL commands sent to the server to standard output. Using this command is equivalent to setting the variable ECHO to queries.

-E

Displays queries generated by internal commands.

-n

Disables command line editing.

--output filename
-o filename

Writes all query output to filename. Using this command is equivalent to using the vsql meta-command \o.

--quiet
-q

Specifies that vsql do its work quietly (without informational output, such as welcome messages). This command is useful with the -c option. Within vsql you can also set the QUIET variable to achieve the same effect.

--single-step
-s

Runs in single-step mode for debugging scripts. Forces vsql to prompt before each statement is sent to the database and allows you to cancel execution.

--single-line -S

Runs in single-line mode where a newline terminates a SQL command, as if you are using a semicolon.

Note

This mode is provided only by customer request. Vertica recommends that you not use single-line mode in cases where you mix SQL and meta-commands on a line. In single-line mode, the order of execution might be unclear to the inexperienced user.

1.4.1.1 - -A --no-align

-A or --no-align switches to unaligned output mode. The default output mode is aligned.

1.4.1.2 - -a --echo-all

-a or --echo-all prints all input lines to standard output as they are read. This is more useful for script processing than interactive mode. It is equivalent to setting the variable ECHO to all.

1.4.1.3 - -c --command

-c command or--command command runs one command and exits. This is useful in shell scripts.

Use either:

• A command string that can be completely parsed by the server that does not contain features specific to vsql

• A single meta-command

You cannot mix SQL and vsql meta-commands. You can, however, pipe the string into vsql as shown:

echo "\\timing\\\\select * from t" | ../Linux64/bin/vsql
                Timing is on.
                i | c | v
                ---+---+---
                (0 rows)

1.4.1.4 - -d --dbname

-d db-name or --dbname db-name specifies the name of the database to connect to. This is equivalent to specifying db-name as the first non-option argument on the command line.

1.4.1.5 - -E

-E displays queries generated by internal commands.

1.4.1.6 - -e --echo-queries

-e --echo-queries copies all SQL commands sent to the server to standard output as well. This is equivalent to setting the variable ECHO to queries.

1.4.1.7 - -r --workload

-r --workload lets you specify the name of a workload. If a routing rule is associated with that workload, your connection is routed to the subcluster reserved for that workload. For details, see Workload routing.

You can also specify a workload with the VSQL_WORKLOAD environment variable. For details, see vsql environment variables.

1.4.1.8 - -F --field-separator

-F separator or --field-separator separator specifies the field separator for unaligned output (default: "|") (-P fieldsep=). (See -A --no-align.) This is equivalent to \pset fieldsep or \f.

To set the field separator value to a control character, use your shell's control character escape notation. In Bash, you specify a control character in an argument using a dollar sign ($) followed by a string contained in single quotes. This string can contain C-string escapes (such as \t for tab), or a backslash () followed by an octal value for the character you want to use.

The following example demonstrates setting the separator character to tab (\t), vertical tab (\v) and the octal value of vertical tab (\013).

$ vsql -At -c "SELECT * FROM testtable;"
A|1|2|3
B|4|5|6

$ vsql -F $'\t' -At -c "SELECT * FROM testtable;"
A       1       2       3
B       4       5       6

$ vsql -F $'\v' -At -c "SELECT * FROM testtable;"
A
 1
  2
   3
B
 4
  5
   6
$ vsql -F $'\013' -At -c "SELECT * FROM testtable;"
A
 1
  2
   3
B
 4
  5
   6

1.4.1.9 - -f --file

-f filename or --file filename uses filename as the source of commands instead of reading commands interactively. After the file is processed, vsql terminates.

If filename is a hyphen (-), standard input is read.

Using this option is different from writing vsql < filename. Using -f enables some additional features such as error messages with line numbers. Conversely, the variant using the shell's input redirection should always yield exactly the same output that you would have gotten had you entered everything manually.

1.4.1.10 - ? --help

-? --help displays help about vsql command line arguments and exits.

1.4.1.11 - -H --html

-H --html turns on HTML tabular output. This is equivalent to \pset format html or the \H command.

1.4.1.12 - -h --host

-h hostname or --host hostname specifies the host name of the machine on which the server is running. Use this flag to connect to Vertica remotely.

The following requirements and restrictions apply:

• If you use client authentication with a Kerberos connection method of either gss or krb5, you must specify -h hostname.

• Use the -h option if you want to connect to Vertica from a local connection, but want to use the an authentication record with the access method HOST (rather than LOCAL).

1.4.1.13 - -i -- timing

Enables the \timing meta-command. You can only use this command with the -c --command and -f --file commands:

$VSQL -h host1 -U user1 -d VMart -p 15 -w ****** -i -f transactions.sql

You can only use -i with the -c (command) and -f (filename) commands. For more information see Command-line options.

From the command line enter the -i option before running a session to turn timing on. For example:

$VSQL -h host1 -U user1 -d VMart -p 15 -w ****** -i -f transactions.sql

$VSQL-h host1 -U user1 -d VMart -p 15 -w ****** -i -c "SELECT user_name,
ssl_state, authentication_method, client_authentication_name, client_type FROM sessions
WHERE session_id=(SELECT session_id FROM current_session);"

1.4.1.14 - -g --label

Assigns a client label to the connection at the start of the session. Client connections and their labels appear in the SESSIONS and some Data collector tables like DC_REQUESTS_ISSUED. Client labels set with this option appear in DC_SESSION_STARTS.

If used, this option takes precedence over the VSQL_CLIENT_LABEL environment variable.

To set client labels for ongoing sessions, use SET_CLIENT_LABEL.

1.4.1.15 - -l --list

-l or --list returns all available databases, then exits. Other non-connection options are ignored. This command is similar to the internal command \list.

1.4.1.16 - -m --sslmode

-m or --sslmode specifies the policy for making SSL connections to the server. Options are verify_full, verify_ca require, prefer, allow, and disable. You can also set the VSQL_SSLMODE variable to achieve the same effect. If the variable is set, the command-line option overrides it.

For information on these modes see Configuring TLS for ODBC Clients.

1.4.1.17 - -n

-n disables command line editing.

1.4.1.18 - -o --output

-o filename or --output filename writes all query output into file filename. This is equivalent to the vsql meta-command \o.

1.4.1.19 - -P --pset

-P assignment or --pset assignment lets you specify printing options in the style of \pset on the command line. Note that you have to separate name and value with an equal sign instead of a space. Thus to set the output format to LaTeX, you could write -P format=latex.

1.4.1.20 - -p --port

-p port or--port port specifies the TCP port or the local socket file extension on which the server is listening for connections. Defaults to port 5433.

1.4.1.21 - -q --quiet

-q or --quiet specifies that vsql do its work quietly. By default, it prints welcome messages and various informational output. If this option is used, none of this appears. This is useful with the -c option. Within vsql you can also set the QUIET variable to achieve the same effect.

1.4.1.22 - -R --record-separator

-R separator or --record-separator separator specifies separator as the record separator. This is equivalent to the \pset recordsep command.

1.4.1.23 - -S --single-line

-S --single-line runs in single-line mode where a newline terminates a SQL command, like the semicolon does.

1.4.1.24 - -s --single-step

-s --single-step runs in single-step mode for debugging scripts. Forces vsql to prompt before each statement is sent to the database and allows you to cancel execution.

1.4.1.25 - -T --table-attr

-T table-options or --table-attr table-options lets you specify options to be placed within the HTML table tag. See \pset for details.

1.4.1.26 - -t --tuples-only

-t or --tuples-only disables printing of column names, result row count footers, and so on. This is equivalent to the vsql meta-command \t.

1.4.1.27 - -V --version

-V or --version prints the vsql version and exits.

1.4.1.28 - -v --variable --set

-v assignment, --variable assignment, and --set assignment perform a variable assignment, like the vsql meta-command \set.

To unset a variable, omit the equal sign. To set a variable without a value, use the equals sign but omit the value. Make these assignments at a very early stage of start-up, so that variables reserved for internal purposes can get overwritten later.

1.4.1.29 - -X --no-vsqlrc

-X --no-vsqlrc prevents the start-up file from being read: the system-wide vsqlrc file or the user's ~/.vsqlrc file.

1.4.1.30 - -x --expanded

-x or --expanded enables extended table formatting mode. This is equivalent to the vsql meta-command \x.

1.4.2 - Connecting from a non-cluster host

You can use the Vertica vsql executable image on a non-cluster Linux host to connect to a Vertica database.

• On Red Hat, CentOS, and SUSE systems, you can install the client driver RPM, which includes the vsql executable. See Installing the vsql client for details.

• If the non-cluster host is running the same version of Linux as the cluster, copy the image file to the remote system. For example:

  $ scp host01:/opt/vertica/bin/vsql .$ ./vsql
  

• If the non-cluster host is running a different distribution or version of Linux than your cluster hosts, you must install the Vertica server RPM in order to get vsql:

  1. Download the appropriate RPM package by browsing to Vertica website. On the Support tab, select Customer Downloads.

  2. If the system you used to download the RPM is not the non-cluster host, transfer the file to the non-cluster host.

  3. Log into the non-cluster host as root and install the RPM package using the command:

     # rpm -Uvh filename
     

     Where filename is the package you downloaded. Note that you do not have to run the install_vertica script on the non-cluster host to use vsql.

Notes

• Use the same Command-line options that you would on a cluster host.

• You cannot run vsql on a Cygwin bash shell (Windows). Use ssh to connect to a cluster host, then run vsql.

1.5 - Meta-commands

Anything you enter in vsql that begins with an unquoted backslash is a vsql meta-command that is processed by vsql itself. These commands help make vsql more useful for administration or scripting. Meta-commands are more commonly called slash or backslash commands.

The format of a vsql command is the backslash, followed immediately by a command verb, then any arguments. The arguments are separated from the command verb and each other by any number of whitespace characters.

To include whitespace into an argument you can quote it with a single quote. To include a single quote into such an argument, precede it by a backslash. Anything contained in single quotes is furthermore subject to C-like substitutions for \n (new line), \t (tab), \digits, \0digits, and \0xdigits (the character with the given decimal, octal, or hexadecimal code).

If an unquoted argument begins with a colon (:), it is taken as a vsql variable and the value of the variable is used as the argument instead.

Arguments that are enclosed in backquotes (```) are taken as a command line that is passed to the shell. The output of the command (with any trailing newline removed) is taken as the argument value. The above escape sequences also apply in backquotes.

Some commands take a SQL identifier (such as a table name) as argument. These arguments follow the syntax rules of SQL: Unquoted letters are forced to lowercase, while double quotes (") protect letters from case conversion and allow incorporation of whitespace into the identifier. Within double quotes, paired double quotes reduce to a single double quote in the resulting name. For example, FOO"BAR"BAZ is interpreted as fooBARbaz, and "A weird"" name" becomes A weird" name.

Parsing for arguments stops when another unquoted backslash occurs. This is taken as the beginning of a new meta-command. The special sequence \\ (two backslashes) marks the end of arguments and continues parsing SQL commands, if any. That way SQL and vsql commands can be freely mixed on a line. But in any case, the arguments of a meta-command cannot continue beyond the end of the line.

1.5.1 - Meta-commands quick reference

> \z *.*myseq*
     Access privileges for database "dbadmin"
 Grantee | Grantor | Privileges | Schema |  Name
---------+---------+------------+--------+--------
 dbadmin | dbadmin | SELECT*    | public | mySeq
 dbadmin | dbadmin | SELECT*    | public | mySeq2
(2 rows)

1.5.2 - \connect

Establishes a connection to database db, under the specified user user-name. The previous connection is closed. If you omit specifying a database name, Vertica connects to the current database. If you omit specifying a user name argument, Vertica assumes the current user.

Syntax

\c[connect] [db [user-name]]

Error handling

Errors that prevent execution include specifying an unknown user and denial of access to the specified database. Vertica handles errors differently, depending on whether this command is executed interactively in vsql, or in a script:

• VSQL handling: The current connection is maintained.

• Script: Processing immediately stops with an error. This is prevents scripts from acting on the wrong database.

1.5.3 - \d meta-commands

Vertica supports a number of \d commands, which return information on different categories of database objects. For a full list, see \d Reference below.

Syntax

Unless otherwise noted, \d commands generally conform to the following syntax:

\dCommand [ [schema.]pattern ]

Arguments

You can supply most \d commands with a string pattern argument, which filters the results that the command returns. The pattern can optionally be qualified by a schema name.

schema

Valid for most \d commands, restricts output to only database objects in schema. For example, the following \dp command obtains privileges information for all V_MONITOR tables that contain the string resource:

=> \dp V_MONITOR.*resource*
                Access privileges for database "dbadmin"
 Grantee | Grantor | Privileges |  Schema   |            Name
---------+---------+------------+-----------+----------------------------
 public  | dbadmin | SELECT     | v_monitor | resource_rejections
 public  | dbadmin | SELECT     | v_monitor | disk_resource_rejections
 public  | dbadmin | SELECT     | v_monitor | resource_usage
 public  | dbadmin | SELECT     | v_monitor | resource_acquisitions
 public  | dbadmin | SELECT     | v_monitor | resource_rejection_details
 public  | dbadmin | SELECT     | v_monitor | resource_pool_move
 public  | dbadmin | SELECT     | v_monitor | host_resources
 public  | dbadmin | SELECT     | v_monitor | node_resources
 public  | dbadmin | SELECT     | v_monitor | resource_queues
 public  | dbadmin | SELECT     | v_monitor | resource_pool_status
(10 rows)
  

pattern

Returns only the database objects that match the specified string. Pattern strings can include the following wildcards:

• * (asterisk): zero or more characters.

• ? (question mark): any single character.

For example, the following \dt command returns tables that start with the string store:

=> \dt store*
                     List of tables
 Schema |       Name        | Kind  |  Owner  | Comment
--------+-------------------+-------+---------+---------
 public | store_orders      | table | dbadmin |
 public | store_orders_2018 | table | dbadmin |
 public | store_overseas    | table | dbadmin |
 store  | store_dimension   | table | dbadmin |
 store  | store_orders_fact | table | dbadmin |
 store  | store_sales_fact  | table | dbadmin |
(6 rows)
  

\d reference

\d

Unqualified by a pattern argument, returns all tables with their schema names, owners, and comments. If qualified by a pattern argument, \d returns all matching tables and all columns in each table, with details about each column, such as data type, size, and default value.

\df

Returns all function names, the function return data type, and the function argument data type. Also returns the procedure names and arguments for all procedures that are available to the user.

\dj

Returns all projections showing the schema, projection name, owner, and node. The returned rows include superprojections, live aggregate projections, Top-K projections, and projections with expressions.

\dn

Returns the schema names and schema owner.

\dp

Returns a summary of privileges on all objects in system table V_CATALOG.GRANTS: grantee, grantor, privileges, schema, and object name (equivalent to \z).

\dS

Unqualified by a pattern argument, returns all V_CATALOG and V_MONITOR system tables. To obtain system tables for just one schema, qualify the command with the schema name, as follows:

\dS { V_CATALOG | V_MONITOR }.*

\ds

Returns sequences and their parameters.

\dT

Returns all data types that Vertica supports.

Note

\dT returns no results if qualified with a pattern argument.

\dt

Unqualified by a pattern argument, returns the same information as an unqualified \d command. If qualified by a pattern argument, \dt returns matching tables with the same level of detail as an unqualified \dt command.

\dtv

Returns tables and views.

\du

Returns database users and whether they are superusers.

\dv

Unqualified by a pattern argument, returns all views with their schema names, owners, and comments. If qualified by a pattern argument, \dv returns all matching views and the columns in each view, with each column's data type and size.

1.5.4 - \edit

Edits the query buffer (or specified file) with an external editor. When the editor exits, its content is copied back to the query buffer. If no argument is given, the current query buffer is copied to a temporary file which is then edited in the same fashion.

The new query buffer is then re-parsed according to the normal rules of vsql, where the whole buffer up to the first semicolon is treated as a single line. (Thus you cannot make scripts this way. Use \i for that.) If there is no semicolon, vsql waits for one to be entered (it does not execute the query buffer).

Syntax

\e[dit] [ file ]

1.5.5 - \i

Reads and executes input from the specified file.

Syntax

\i filename

Examples

The Vertica vsql client on Linux supports backquote (backtick) expansion. For example:

1. Set an environment variable to a path that contains scripts you want to run:

   $ export MYSCRIPTS=/home/dbadmin/testscripts
   

2. Issue the vsql command.

   $ vsql
   

3. Use backquote expansion to include the path for running an existing script—for example, sample.sql.

   => \i `echo $MYSCRIPTS/sample.sql`
   

1.5.6 - \locale

Displays or sets the locale setting for the current session.

Syntax

\locale [locale-identifier]

Arguments

locale-identifier

Specifies the ICU locale identifier to use, by default set to:

en_US@collation=binary

If set to an empty string, Vertica sets locale to en_US_POSIX.

If you omit this argument, \locale returns the current locale setting.

For details on identifier options, see About locale. For a complete list of locale identifiers, see the ICU Project.

Examples

View the current locale setting:

=> \locale
en_US@collation=binary

Change the default locale for this session:

=> \locale en_GBINFO:
INFO 2567:  Canonical locale: 'en_GBINFO:'
Standard collation: 'LEN'
English (GBINFO:)

Notes

The server locale settings impact only the collation behavior for server-side query processing. The client application is responsible for ensuring that the correct locale is set in order to display the characters correctly. Below are the best practices recommended by Vertica to ensure predictable results:

• The locale setting in the terminal emulator for vsql (POSIX) should be set to be equivalent to session locale setting on server side (ICU) so data is collated correctly on the server and displayed correctly on the client.

• The vsql locale should be set using the POSIX LANG environment variable in terminal emulator. Refer to the documentation of your terminal emulator for how to set locale.

• Server session locale should be set using the set as described in Specify the default locale for the database.

• All input data for vsql should be in UTF-8 and all output data is encoded in UTF-8.

• Non UTF-8 encodings and associated locale values are not supported.

1.5.7 - \pset

Sets options that control how Vertica formats query result output.

Syntax

\pset output-option

Output options

format format-option

Sets output format, where format-option is one of the following:

• u[naligned] writes all column data of each row on a single line, where each field is separated only by the current separator character. Use this output for use as input to other programs—for example, comma-delimited fields for CSV input.

• a[ligned] (default): Renders column-aligned output.

• h[tml]: Renders output in HTML markup as a table.

• l[atex]: Renders output in LaTex markup.

border int

Valid only if output format is set to html, specifies the table border, where int specifies the border type.

expanded

Toggles between regular and expanded format. When expanded format is enabled, all output has two columns with the column name on the left and the data on the right. This mode is especially useful for wide tables.

fieldsep 'arg'

Valid only if output format is set to unaligned, specifies the field separator, by default | (vertical bar).

For example, to specify tab as the field separator:

\pset fieldsep '\t'

footer

Toggles display of the default footer:

(int rows)

null 'string'

Specifies to represent column null values as string. By default, Vertica renders null values as an empty field, which might be mistaken as an empty string.

For example:

\pset null '(null)'

pager [always]

Toggles use of a pager for query and vsql help output. If the environment variable PAGER is set, the output is piped to the specified program. Otherwise a platform-dependent default (such as more) is used.

When the pager is off, the pager is not used. When the pager is on, the pager is used only when appropriate; that is, the output is to a terminal and does not fit on the screen. (vsql does not do a perfect job of estimating when to use the pager.)

If qualified with the argument always, the pager is always used.

recordsep 'char'

Valid only if output format is set to unaligned, specifies the character used to delimit table records (tuples), by default a newline character.

tableattr html-attribute[...]

Specifies attributes to be placed inside the HTML table tag—for example, cellpadding or bgcolor.

title ['title-str']

Sets a title that precedes query result output, to title-str. HTML output renders this as follows:

<caption>title-str</caption>

To remove the title, reissue the command omit the title-str argument.

trailingrecordsep

Toggles on or off the trailing record separator to use in unaligned output mode.

t[uples_only]

Toggles between tuples only and full display. Full display might show extra information such as column headers, titles, and various footers. In tuples only mode, only actual table data is shown.

Shortcuts

The following \pset commands have short-cuts:

\pset expanded

\x

\pset fieldsep 'arg'

\f

\pset format aligned

\a

\pset format html

\H

\pset tableattr html-attribute[...]

\T html-attribute[...]

\pset title title-str

\C ['title-str']

\pset tuples_only

\t

Examples

See Output formatting examples.

1.5.8 - \set

Sets an internal variable to one or more values. If multiple values are specified, they are concantenated. An unqualified \set command lists all internal variables.

To unset a variable, use vsql meta-command\unset.

Syntax

\set [var [value]...]

Arguments

var

The name of an internal variable to set. Valid variable names are case sensitive and can contain characters, digits, and underscores. vsql treats several variables as special, which are described in Variables.

value

A value to set in variable var. If no value is specified, the variable is set to no value.

If set to an empty string, the variable is set to no value. If you omit this argument, \set returns all internal variables.

If no arguments are supplied, \set returns all internal variables. For example:

=> \set
VERSION = 'vsql'
AUTOCOMMIT = 'off'
VERBOSITY = 'default'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
ROWS_AT_A_TIME = '1000'
DBNAME = 'dbadmin'
USER = 'dbadmin'
PORT = '5433'
LOCALE = 'en_US@collation=binary'
HISTSIZE = '500'

1.5.9 - \timing

If set to on, returns how long (in milliseconds) each SQL statement runs. Results include:

• Length of time required to fetch the first block of rows

• Total time until the last block is formatted.

Unqualified, \timing toggles timing on and off. You can explicitly turn timing on and off by qualifying the command with options ON and OFF, respectively.

Syntax

\timing [ON | OFF]

Examples

The following unqualified \timing commands toggle timing on and off:

=> \timing
Timing is on
=> \timing
Timing is off

The following example shows a SQL command with timing on:

=> \timing
Timing is on.
=> SELECT user_name, ssl_state, authentication_method, client_authentication_name,
     client_type FROM sessions WHERE session_id=(SELECT session_id FROM current_session);
 user_name | ssl_state | authentication_method | client_authentication_name | client_type
-----------+-----------+-----------------------+----------------------------+-------------
 dbadmin   | None      | ImpTrust              | default: Implicit Trust    | vsql
(1 row)

Time: First fetch (1 row): 73.684 ms. All rows formatted: 73.770 ms

1.6 - Variables

vsql provides variable substitution features similar to common Linux command shells. Variables are name/value pairs, where the value can be a string of any length. To set variables, use the vsql meta-command \set. For example, the following statement sets the variable fact to the value dim:

=> \set fact dim

If you call \set on a variable and supply no value, the variable is set to an empty string.

Getting variables

To retrieve the content of a given variable, precede the name with a colon and use it as the argument of any slash command. For example:

=> \echo :fact
dim

An unqualified \set command returns all current variables and their values:

dbadmin=> \set
VERSION = 'vsql'
AUTOCOMMIT = 'off'
VERBOSITY = 'default'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
ROWS_AT_A_TIME = '1000'
DBNAME = 'dbadmin'
USER = 'dbadmin'
PORT = '5433'
LOCALE = 'en_US@collation=binary'
HISTSIZE = '500'

Deleting variables

To unset (or delete) a variable, use the vsql meta-command \unset.

Variable naming conventions

vsql internal variable names can contain letters, numbers, and underscores in any order and any number. Some variables are treated specially by vsql. They indicate certain option settings that can be changed at run time by altering the value of the variable or represent some state of the application. Although you can use these variables for any other purpose, this is not recommended. By convention, all specially treated variables consist of all upper-case letters (and possibly numbers and underscores). To ensure maximum compatibility in the future, avoid using such variable names for your own purposes.

SQL interpolation

You can substitute ("interpolate") vsql variables into regular SQL statements. You do so by prepending the variable name with a colon (:). For example, the following statements query the table my_table:

=> \set fact 'my_table'
=> SELECT * FROM :fact;

The value of the variable is copied literally, so it can even contain unbalanced quotes or backslash commands. Make sure that it makes sense where you put it. Variable interpolation is not performed into quoted SQL entities. One exception applies: the contents of backquoted strings (````) are passed to a system shell, and replaced with the shell's output. See Using Backquotes to Read System Variables below.

Using backquotes to read system variables

In vsql, the contents of backquotes are passed to the system shell to be interpreted (the same behavior as many UNIX shells). This is particularly useful in setting internal vsql variables, since you may want to access UNIX system variables (such as HOME or TMPDIR) rather than hard-code values.

For example, to set an internal variable to the full path for a file in your UNIX user directory, you can use backquotes to get the content of the system HOME variable, which is the full path to your user directory:

=> \set inputfile `echo $HOME`/myinput.txt=> \echo :inputfile
/home/dbadmin/myinput.txt

The contents of the backquotes are replaced with the results of running the contents in a system shell interpreter. In this case, the echo $HOME command returns the contents of the HOME system variable.

1.6.1 - DBNAME

The name of the database to which you are currently connected. DBNAME is set every time you connect to a database (including program startup), but it can be unset.

1.6.2 - ECHO

If set to all, all lines entered from the keyboard or from a script are written to the standard output before they are parsed or run.

To select this behavior on program start-up, use the switch -a. If set to queries, vsql merely prints all queries as they are sent to the server. The switch for this is -e.

1.6.3 - ECHO_HIDDEN

When this variable is set and a backslash command queries the database, the query is first shown. This way you can study the Vertica internals and provide similar functionality in your own programs. (To select this behavior on program start-up, use the switch -E.)

If you set the variable to the value noexec, the queries are just shown but are not actually sent to the server and run.

1.6.4 - ENCODING

The current client character set encoding.

1.6.5 - HISTCONTROL

If this variable is set to ignorespace, lines that begin with a space are not entered into the history list. If set to a value of ignoredups, lines matching the previous history line are not entered. A value of ignoreboth combines the two options. If unset, or if set to any other value than those previously mentioned, all lines read in interactive mode are saved on the history list.

Source: Bash.

1.6.6 - HISTSIZE

Specifies how much storage space is allocated to store the history of SQL statements issued in the current vsql session. vsql uses this setting, by default 500, to calculate the size of the history buffer:

HISTSIZE * 50 (bytes)

where 50 bytes approximates the average length of a SQL statement. The actual length of SQL statements in the current session determines how many statements vsql stores.

HISTSIZE has no effect on the history that is stored in .vsql_history.

Source: Bash

1.6.7 - HOST

The database server host you are currently connected to. This is set every time you connect to a database (including program startup), but can be unset.

1.6.8 - IGNOREEOF

If unset, sending an EOF character (usually Control+D) to an interactive session of vsql terminates the application. If set to a numeric value, that many EOF characters are ignored before the application terminates. If the variable is set but has no numeric value, the default is 10.

Source: Bash.

1.6.9 - ON_ERROR_STOP

By default, if a script command results in an error, for example, because of a malformed command or invalid data format, processing continues. If you set ON_ERROR_STOP to ON in a script and an error occurs during processing, the script terminates immediately.

For example:

=> \set ON_ERROR_STOP ON

1.6.10 - PORT

The database server port to which you are currently connected. This is set every time you connect to a database (including program start-up), but can be unset.

1.6.11 - PROMPT1 PROMPT2 PROMPT3

These specify what the prompts vsql issues look like. See Prompting for details.

1.6.12 - QUIET

This variable is equivalent to the command line option -q. It is probably not too useful in interactive mode.

1.6.13 - ROWS_AT_A_TIME

ROWS_AT_A_TIME is set by default to 1000, and retrieves results as blocks of rows of that size. The column formatting for the first block is used for all blocks, so in later blocks some entries could overflow.

When formatting results, Vertica buffers ROWS_AT_A_TIME rows in memory to calculate the maximum column widths. It is possible that rows after this initial fetch are not properly aligned if any of the field values are longer than those see in the first ROWS_AT_A_TIME rows. ROWS_AT_A_TIME can be unset with vsql meta-command\unset to guarantee perfect alignment. However, this requires re-buffering the entire result set in memory and might cause vsql to fail if the result set is too big.

1.6.14 - SINGLELINE

This variable is equivalent to the command line option -S.

1.6.15 - SINGLESTEP

This variable is equivalent to the command line option -s.

1.6.16 - USER

The database user you are currently connected as. This is set every time you connect to a database (including program startup), but can be unset.

1.6.17 - VERBOSITY

This variable can be set to the values default, verbose, or terse to control the verbosity of error reports.

1.6.18 - VSQL_HOME

By default, the vsql program reads configuration files from the user's home directory. In cases where this is not desirable, the configuration file location can be overridden by setting the VSQL_HOME environment variable in a way that does not require modifying a shared resource.

In the following example, vsql reads configuration information out of /tmp/jsmith rather than out of ~.

# Make an alternate configuration file in /tmp/jsmith
mkdir -p /tmp/jsmith
echo "\\echo Using VSQLRC in tmp/jsmith" > /tmp/jsmith/.vsqlrc
# Note that nothing is echoed when invoked normally
vsql
# Note that the .vsqlrc is read and the following is
# displayed before the vsql prompt
#
# Using VSQLRC in tmp/jsmith
VSQL_HOME=/tmp/jsmith vsql

1.6.19 - VSQL_SSLMODE

VSQL_SSLMODE specifies how (or whether) clients (like admintools) use SSL when connecting to servers. The default value is prefer, meaning to use SSL if the server offers it. Legal values are require, prefer, allow, and disable. This variable is equivalent to the command-line -m option (or --sslmode).

1.7 - Prompting

The prompts vsql issues can be customized to your preference. The three variables PROMPT1, PROMPT2, and PROMPT3 contain strings and special escape sequences that describe the appearance of the prompt. Prompt 1 is the normal prompt that is issued when vsql requests a new command. Prompt 2 is issued when more input is expected during command input because the command was not terminated with a semicolon or a quote was not closed. Prompt 3 is issued when you run a SQL COPY command and you are expected to type in the row values on the terminal.

The value of the selected prompt variable is printed literally, except where a percent sign (%) is encountered. Depending on the next character, certain other text is substituted instead. Defined substitutions are:

%M

The full host name (with domain name) of the database server, or [local] if the connection is over a socket, or [local:/dir/name], if the socket is not at the compiled in default location.

%m

The host name of the database server, truncated at the first dot, or [local].

%>

The port number at which the database server is listening.

%n

The database session user name.

%/

The name of the current database.

%~

Like %/, but the output is ~ (tilde) if the database is your default database.

%#

If the session user is a database superuser, then a #, otherwise a >. (The expansion of this value might change during a database session as the result of the command SET SESSION AUTHORIZATION.)

%R

In prompt 1 normally =, but ^ if in single-line mode, and ! if the session is disconnected from the database (which can happen if \connect fails). In prompt 2 the sequence is replaced by -, *, a single quote, a double quote, or a dollar sign, depending on whether vsql expects more input because the command wasn't terminated yet, because you are inside a /* ... */ comment, or because you are inside a quoted or dollar-escaped string. In prompt 3 the sequence doesn't produce anything.

%x

Transaction status: an empty string when not in a transaction block, or * when in a transaction block, or ! when in a failed transaction block, or ? when the transaction state is indeterminate (for example, because there is no connection).

%digits

The character with the indicated numeric code is substituted. If digits starts with 0x the rest of the characters are interpreted as hexadecimal; otherwise if the first digit is 0 the digits are interpreted as octal; otherwise the digits are read as a decimal number.

%:name:

The value of the vsql variable name. See the section Variables for details.

%`command`

The output of command, similar to ordinary "back- tick" substitution.

%[ ... %]

Prompts may contain terminal control characters which, for example, change the color, background, or style of the prompt text, or change the title of the terminal window. In order for the line editing features of Readline to work properly, these non-printing control characters must be designated as invisible by surrounding them with %[ and %]. Multiple pairs of these may occur within the prompt. The following example results in a boldfaced (1;) yellow-on-black (33;40) prompt on VT100-compatible, color-capable terminals:

testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%#%[%033[0m%] '

To insert a percent sign into your prompt, write %%. The default prompts are '%/%R%# ' for prompts 1 and 2, and '>> ' for prompt 3.

Note: See the specification for terminal control sequences (applicable to gnome-terminal and xterm).

1.8 - Command line editing

vsql supports the tecla library for line editing and retrieval. You can define a tecla configuration with the following files:

• ~/.vsqlrc (user)

• /opt/vertica/config/vsqlrc (global)

For details, see the tecla documentation.

Command history is automatically saved in ~/.vsql_history when vsql exits and is reloaded when vsql starts.

Disabling tab completion

To disable tab completion, add the following to .vsqlrc:

\bind ^I

Key bindings

Key bindings are read from a global configuration at /opt/vertica/config/vsqlrc, if present. To override key bindings, add definitions to ~/.vsqlrc.

Key bindings must be prefixed with a backslash (\). For example, the following definition binds the "backward-word" action to Ctrl+B:

\bind ^B backward-word

The following key bindings are specific to vsql:

• Insert switches between insert mode (the default) and overwrite mode.

• Delete deletes the character to the right of the cursor.

• Home moves the cursor to the front of the line.

• End moves the cursor to the end of the line.

• ^R Performs a history backwards search.

Implementation differences

The vsql implementation of the tecla library deviates from the tecla documentation in the following ways:

• Unlike the standard tecla library, which saves all executed lines in the command history, vsql only saves unique non-empty lines.

• vsql standardizes the name and location of the history file (~/.vsql_history).

• vsql does not support 8-bit meta characters. This can affect international character sets, meta keys, and locales. You can verify that a meta character sends an escape by setting the EightBitInput X resource to False. You can do this in the following ways:

  • Add the following to ~/.Xdefaults:

    XTerm*EightBitInput: False
    

  • Start an xterm session with the -xrm '*EightBitInput: False'.

1.9 - vsql environment variables

Set one or more of the following environment variables to be used by the defined properties automatically, each time you start vsql:

PAGER

If the query results do not fit on the screen, they are piped through this command. Typical values are more or less. The default is platform-dependent. Use the \pset command to enable/disable the pager.

VSQL_CLIENT_LABEL

The label to identify the vsql client in various system tables like SESSIONS. This is an alternative to setting the client label with the --label option or SET_CLIENT_LABEL, but if either of these is used, they take precedence over VSQL_CLIENT_LABEL.

VSQL_DATABASE

The database to which you are connecting. For example, VMart.

TMPDIR

Directory for storing temporary files. The default is platform-dependent. On Unix-like systems the default is /tmp.

VSQL_EDITOR
EDITOR
VISUAL

Editor used by the \e command. The variables are examined in the order listed; the first that is set is used.

VSQL_HOME

By default, the vsql program reads configuration files from the user's home directory. In cases where this is not desirable, the configuration file location can be overridden by setting the VSQL_HOME environment variable in a way that does not require modifying a shared resource.

VSQL_HOST

Host name or IP address of the Vertica node.

VSQL_PASSWORD

The database password. Using this environment variable increases site security by precluding the need to enter the database password on the command line.

VSQL_PORT

Port to use for the connection.

VSQL_SSLMODE

Specifies whether and how clients such as admintools use SSL when connecting to servers.

VSQL_USER

User name to use for the connection.

VSQL_WORKLOAD

The workload to use for the connection.

1.10 - Locales

The default terminal emulator under Linux is gnome-terminal, although xterm can also be used.

Vertica recommends that you use gnome-terminal with vsql in UTF-8 mode, which is its default.

To change settings on Linux

1. From the tabs at the top of the vsql screen, select Terminal.

2. Click Set Character Encoding.

3. Select Unicode (UTF-8).

   Note

   This works well for standard keyboards. xterm has a similar UTF-8 option.

To change settings on Windows using PuTTy

1. Right click the vsql screen title bar and select Change Settings.

2. Click Window and click Translation.

3. Select UTF-8 in the drop-down menu on the right.

Notes

• vsql has no way of knowing how you have set your terminal emulator options.

• The tecla library is prepared to do POSIX-type translations from a local encoding to UTF-8 on interactive input, using the POSIX LANG, etc., environment variables. This could be useful to international users who have a non-UTF-8 keyboard. See the tecla documentation for details.

  Vertica recommends the following (or whatever other .UTF-8 locale setting you find appropriate):

  export LANG=en_US.UTF-8
  

• The vsql \locale command invokes and tracks the server SET LOCALE TO command, described. vsql itself currently does nothing with this locale setting, but rather treats its input (from files or from tecla), all its output, and all its interactions with the server as UTF-8. vsql ignores the POSIX locale variables, except for any "automatic" uses in printf, and so on.

1.11 - Entering data with vsql

You often need to insert literal data when using vsql. For example:

• Adding a row of data to a table using an INSERT statement.

• Adding multiple rows of data through a COPY FROM STDIN statement.

The following table lists the data types that Vertica supports, and the format you use to enter that data in queries when using vsql.

1.12 - Files

Before starting up, vsql attempts to read and execute commands from the system-wide vsqlrc file and the user's ~/.vsqlrc file. The command-line history is stored in the file ~/.vsql_history.

1.13 - Exporting data using vsql

You can use vsql for simple data-export tasks by changing its output format options so the output is suitable for importing into other systems (tab-delimited or comma-separated files, for example). These options can be set either from within an interactive vsql session, or through command-line arguments to the vsql command (making the export process suitable for automation through scripting). After you have set vsql's options so it outputs the data in a format your target system can read, you run a query and capture the result in a text file.

The following table lists the meta-commands and command-line options that are useful for changing the format of vsql's output.

The following example demonstrates disabling padding and column headers in the output, and setting a field separator to dump a table to a tab-separated text file within an interactive session.

=> SELECT * FROM my_table;
 a |   b   | c
---+-------+---
 a | one   | 1
 b | two   | 2
 c | three | 3
 d | four  | 4
 e | five  | 5
(5 rows)
=> \a
Output format is unaligned.
=> \t
Showing only tuples.
=> \pset fieldsep '\t'
Field separator is "    ".
=> \o dumpfile.txt
=> select * from my_table;
=> \o
=> \! cat dumpfile.txt
a       one     1
b       two     2
c       three   3
d       four    4
e       five    5

When logged into one of the database nodes, you can create the same output file directly from the command line by passing the right parameters to vsql:

$ vsql -U username -F $'\t' -At -o dumpfile.txt -c "SELECT * FROM my_table;"
Password:
$ cat dumpfile.txt
a       one     1
b       two     2
c       three   3
d       four    4
e       five    5

If you want to convert null values to a unique string as mentioned earlier, you can add the argument -P null='NULLNULLNULL' (or whatever unique string you choose).

By adding the -w vsql command-line option to the example command line, you could use the command within a batch script to automate the data export. However, the script would contain the database password as plain text. If you take this approach, you should prevent unauthorized access to the batch script, and also have the script use a database user account that has limited access.

To set the field separator value to a control character, use your shell's control character escape notation. In Bash, you specify a control character in an argument using a dollar sign ($) followed by a string contained in single quotes. This string can contain C-string escapes (such as \t for tab), or a backslash () followed by an octal value for the character you want to use.

The following example demonstrates setting the separator character to tab (\t), vertical tab (\v) and the octal value of vertical tab (\013).

$ vsql -At -c "SELECT * FROM testtable;"
A|1|2|3
B|4|5|6

$ vsql -F $'\t' -At -c "SELECT * FROM testtable;"
A       1       2       3
B       4       5       6

$ vsql -F $'\v' -At -c "SELECT * FROM testtable;"
A
 1
  2
   3
B
 4
  5
   6
$ vsql -F $'\013' -At -c "SELECT * FROM testtable;"
A
 1
  2
   3
B
 4
  5
   6

1.14 - Copying data using vsql

You can use vsql to copy data between two Vertica databases. This technique is similar to the technique explained in Exporting data using vsql, except instead of having vsql save data to a file for export, you pipe one vsql's output to the input of another vsql command that runs a COPY statement from STDIN. This technique can also work for other databases or applications that accept data from an input stream.

The easiest way to copy using vsql is to log in to a node of the target database, then issue a vsql command that connects to the source Vertica database to dump the data you want. For example, the following command copies the store.store_sales_fact table from the vmart database on node testdb01 to the vmart database on the node you are logged into:

vsql -U username -w passwd -h testdb01 -d vmart -At -c "SELECT * from store.store_sales_fact"  \
| vsql -U username -w passwd -d vmart -c "COPY store.store_sales_fact FROM STDIN DELIMITER '|';"

If you are using the Bash shell, you can escape special delimiter characters. For example, DELIMITER E'\t' specifies tab. Shells other than Bash may have other string-literal syntax.

Monitoring progress (optional)

You may want some way of monitoring progress when copying large amounts of data between Vertica databases. One way of monitoring the progress of the copy operation is to use a utility such as Pipe Viewer that pipes its input directly to its output while displaying the amount and speed of data it passes along. Pipe Viewer can even display a progress bar if you give it the total number of bytes or lines you expect to be processed. You can get the number of lines to be processed by running a separate vsql command that executes a SELECT COUNT query.

The following command demonstrates how you can use Pipe Viewer to monitor the progress of the copy shown in the prior example. The command is complicated by the need to get the number of rows that will be copied, which is done using a separate vsql command within a Bash backquote string, which executes the string's contents and inserts the output of the command into the command line. This vsql command just counts the number of rows in the store.store_sales_fact table.

vsql -U username -w passwd -h testdb01 -d vmart -At -c "SELECT * from store.store_sales_fact"  \
| pv -lpetr -s `vsql -U username -w passwd -h testdb01 -d vmart -At -c "SELECT COUNT (*) FROM store.store_sales_fact;"` \
| vsql -U username -w passwd -d vmart -c "COPY store.store_sales_fact FROM STDIN DELIMITER '|';"

While running, the above command displays a progress bar that looks like this:

0:00:39 [12.6M/s] [=============================>                             ] 50% ETA 00:00:40

1.15 - Output formatting examples

By default, Vertica formats query output as follows:

=> SELECT DISTINCT category_description FROM product_dimension ORDER BY category_description;
       category_description
----------------------------------
 Food
 Medical
 Misc
 Non-food
(4 rows)

You can control the format of query output in various ways with the \pset command—for example, change the border:

=> \pset border 2
Border style is 2.
=> SELECT DISTINCT category_description FROM product_dimension ORDER BY category_description;
+----------------------------------+
|       category_description       |
+----------------------------------+
| Food                             |
| Medical                          |
| Misc                             |
| Non-food                         |
+----------------------------------+
(4 rows)
=> \pset border 0
Border style is 0.
=> SELECT DISTINCT category_description FROM product_dimension ORDER BY category_description;
      category_description
--------------------------------
Food
Medical
Misc
Non-food
(4 rows)

The following sequence of pset commands change query output in several ways:

• Set border style to 1.

• Remove column alignment.

• Change the field separator to a comma.

• Remove column headings

=> \pset border 1
Border style is 1.
=> \pset format unaligned
Output format is unaligned.
=> \pset fieldsep ','
Field separator is ",".
=> \pset tuples_only
Showing only tuples.
=> SELECT product_key, product_description, category_description FROM product_dimension LIMIT 10;
1,Brand #2 bagels,Food
1,Brand #1 butter,Food
2,Brand #6 chicken noodle soup,Food
3,Brand #11 vanilla ice cream,Food
4,Brand #14 chocolate chip cookies,Food
4,Brand #12 rash ointment,Medical
6,Brand #18 bananas,Food
7,Brand #25 basketball,Misc
8,Brand #27 french bread,Food
9,Brand #32 clams,Food

The following example uses meta-commands to toggle output format—in this case, \a (alignment), \t (tuples only), and -x (extended display):

=> \a \t \x
Output format is aligned.
Tuples only is off.
Expanded display is off.
=> SELECT product_key, product_description, category_description FROM product_dimension LIMIT 10;
 product_key |       product_description        |       category_description
-------------+----------------------------------+----------------------------------
           1 | Brand #2 bagels                  | Food
           1 | Brand #1 butter                  | Food
           2 | Brand #6 chicken noodle soup     | Food
           3 | Brand #11 vanilla ice cream      | Food
           4 | Brand #14 chocolate chip cookies | Food
           4 | Brand #12 rash ointment          | Medical
           6 | Brand #18 bananas                | Food
           7 | Brand #25 basketball             | Misc
           8 | Brand #27 french bread           | Food
           9 | Brand #32 clams                  | Food
(10 rows)

The following example sets output format to HTML, so Vertica renders query results in HTML markup as a table:

=> \pset format html
Output format is html.
=> \pset tableattr 'border="2" cellpadding="3"'
Table attribute is "border="2" cellpadding="3"".
=> SELECT product_key, product_description, category_description FROM product_dimension LIMIT 2;
<table border="1" border="2" cellpadding="3">
  <tr>
    <th align="center">product_key</th>
    <th align="center">product_description</th>
    <th align="center">category_description</th>
  </tr>
  <tr valign="top">
    <td align="right">1</td>
    <td align="left">Brand #2 bagels</td>
    <td align="left">Food                            </td>
  </tr>
  <tr valign="top">
    <td align="right">1</td>
    <td align="left">Brand #1 butter</td>
    <td align="left">Food                            </td>
  </tr>
</table>
<p>(2 rows)<br />
</p>

2 - Client libraries

The Vertica client driver libraries provide interfaces for connecting your client applications (or third-party applications such as Cognos and MicroStrategy) to your Vertica database. The drivers simplify exchanging data for loading, report generation, and other common database tasks.

There are three separate client drivers:

• Open Database Connectivity (ODBC)—the most commonly-used interface for third-party applications and clients written in C, Python, PHP, Perl, and most other languages.

• Java Database Connectivity (JDBC)—used by clients written in the Java programming language.

• ActiveX Data Objects for .NET (ADO.NET)—used by clients developed using Microsoft's .NET Framework and written in C#, Visual Basic .NET, and other .NET languages.

Client driver standards

The Vertica client drivers are compatible with the following driver standards:

• The ODBC driver complies with version 3.5.1 of the ODBC standard.

• The version of JDBC used depends on the version of your JVM. For details, see JDBC feature support.

• ADO.NET drivers conform to .NET framework 3.0 specifications.

The drivers do not support some of the optional features in the standards. See ODBC feature support and JDBC feature support and Using ADO.NET for details.

2.1 - Client driver and server version compatibility

Backward compatibility between Vertica server and client drivers works in both directions.

The Vertica server is compatible with all previous versions of client drivers, and all new client drivers are compatible with most versions of Vertica server. This compatibility lets you upgrade your Vertica server without having to immediately upgrade your client software, and use new client software with older versions of Vertica. Occasionally, however, individual features of a new server version might be unavailable through older drivers.

2.2 - Client drivers

You must install the Vertica client drivers to access Vertica from your client application. The drivers create and maintain connections to the database and provide APIs that your applications use to access your data. The client drivers support connections using JDBC, ODBC, and ADO.NET.

Client driver standards

The client drivers support the following standards:

• ODBC drivers conform to ODBC 3.5.1 specifications.

• JDBC drivers conform to JDK 5 specifications.

• ADO.NET drivers conform to .NET framework 3.0 specifications.

2.2.1 - Installing and configuring client drivers

You can access your Vertica database with various programming languages and tools by installing the appropriate client driver. The following table lists the required client drivers for each access method:

2.2.1.1 - Windows client driver installer

All available client drivers for Windows are included in the Vertica Client Drivers and Tools installer. This installs the following components on systems that meet the prerequisites. The individual components may require additional configuration before use, so navigate to their pages linked below for more information:

• ODBC

• JDBC

• OLE DB

• vsql

2.2.1.1.1 - System prerequisites

The Vertica Client Drivers and Tools for Windows has basic system prerequisite requirements. The pack also requires that specific Microsoft components be installed for full integration.

For a list of all prerequisites, see Client drivers support in the Supported Platforms document.

Fully update your system

Before you install the Vertica driver package, verify that your system is fully up to date with all Windows updates and patches. See the documentation for your version of Windows for instructions on how to run Windows update. The Vertica client libraries and vsql executable install updated Windows libraries that depend on Windows service packs. Be sure to resolve any issues that block the installation of Windows updates.

If your system is not fully up-to-date, you may receive error messages about missing libraries such as api-ms-win-crt-runtime-l1-1-0.dll when starting vsql.

2.2.1.1.1.1 - .NET framework

The Vertica Client Drivers and Tools for Windows requires and prompts you to install the Microsoft .NET Framework 4.6 if it is not installed.

To manually install the Microsoft .NET Framework 4.6, see the Microsoft documentation.

2.2.1.1.1.2 - Microsoft SQL server

Use SQL Server 2012, 2014 or 2016. The Vertica Client Drivers and Tools for Windows installer enables support for the following:

• SQL Server 2012, 2014, and 2016:

  • SQL Server Integration Services (SSIS)

  • SQL Server Reporting Services (SSRS)

  • SQL Server Analysis Services (SSAS)

• SQL Server using 2012, 2013, and 2015—SQL Server Data Tool - Business Intelligence (SSDT-BI)

  Note

  For SQL Server 2012, you can use either SQL Server 2012 or SQL Server 2012 SP1.

To use the enhanced Vertica .NET support, you must first install SQL Server. Then, you can install the Client Drivers and Tools for Windows. The following components must be installed on the SQL server:

2.2.1.1.2 - Uninstalling, modifying, or repairing the client drivers and tools

To uninstall, modify, or repair the client drivers and tools, run the Client Drivers and Tools for Windows installer.

The installer provides three options:

Silently uninstall the client drivers and tools

1. As a Windows Administrator, open a command-line session, and change directory to the folder that contains the installer.

2. Run the command:

   VerticaSetup.exe -q -uninstall
   

The client drivers and tools are silently uninstalled.

2.2.1.2 - FIPS client drivers

Vertica offers a FIPS-compliant version of the ODBC and JDBC client drivers.

2.2.1.2.1 - Installing the FIPS client driver for JDBC

Vertica offers a JDBC client driver that is compliant with the Federal Information Processing Standard (FIPS). Use this JDBC client driver to access systems that are FIPS-compatible. For more information on FIPS, see Federal information processing standard.

Implementing FIPS on a JDBC client requires a third-party JRE extension called BouncyCastle, a collection of APIs used for cryptography. Use BouncyCastle APIs with JDK 1.7 and 1.8, and a supported FIPS-compliant operating system.

The following procedure adds the FIPS BouncyCastle .jar as a JVM JSSE provider:

1. Download the BouncyCastle FIPS .jar file bc-fips-1.0.0.jar.

2. Add bc-fips-1.0.0.jar as a JRE library extension:

   path/to/jre/lib/ext/bc-fips-1.0.0.jar
   

3. Add BouncyCastle as an SSL security provider in <path to jre>/lib/security/java.security:

   security.provider.1=org.bouncycastle.jcajce.provider.BouncyCastle FipsProvider
   security.provider.2=com.sun.net.ssl.internal.ssl.Provider BCFIPS
   security.provider.3=sun.security.provider.Sun
   

4. Use the following JVM java -D system property command arguments to set the KeyStore and TrustStore files to BCFIPS:

   export JAVA_OPTS="$JAVA_OPTS -Djavax.net.ssl.keyStoreProvider=BCFIPS
   export JAVA_OPTS="$JAVA_OPTS -Djavax.net.ssl.trustStoreProvider=BCFIPS
   

5. Set the default type for the KeyStore implementation to BCFKS in path/to/jre/lib/security/java.security:

   keystore type=BCFKS
   ssl.keystore.type=BCFKS
   

   Note

   If you are using FIPS with BouncyCastle, you must create all client keys and certificates with the BCFKS store type, including the Vertica-to-Kafka keys and certificates.

6. Create the BCFKS-type keystore and truststore:

   cd path/to/jre
   -storetype BCFKS
   -providername BCFIPS
   -providerclass org.bouncycastle.jcajce.provider.BouncyCastleFipsProvider
   -provider org.bouncycastle.jcajce.provider.BouncyCastleFipsProvider
   -providerpath bc-fips-1.0.0.jar
   -alias CARoot
   -import -file path/to/server.crt.der
   

7. When prompted, enter the keystore password. The following message is displayed to confirm that a certificate was added to the keystore:

   "Certificate was added to the keystore"
   

8. Run the Java program with SSL DB:

   1. Copy the vertica.kafka.keystore.bcfks keyStore from path/to/jre/lib/ext/ to the Java program folder.

   2. Convert the Vertica server certificate to a form that Java understands:

      $ path/to/java/bin/keytool -keystore verticastore -keypasswd -storepass password
                              -importkeystore -noprompt -alias verticasql -import -file server.crt.der
      

   3. Install JDBC.

9. Test the implementation:

   $ java -Djavax.net.debug=ssl -Djavax.net.ssl.keyStore='vertica.kafka.keystore.bcfks'
   -Djavax.net.ssl.keyStorePassword='password'
   -Djavax.net.ssl.trustStore='path/to/verticastore'
   -Djavax.net.ssl.trustStorePassword='password'
   -cp .:vertica-jdbc-12.0.0-0.jar FIPSTest
   

2.2.1.2.2 - Installing the FIPS client driver for ODBC and vsql

Vertica offers a FIPS client for FIPS-compatible systems. A FIPS-compatible system is FIPS-enabled and includes the OpenSSL libraries.

The FIPS client supports ODBC and vsql and is offered in 64-bit only.

Prerequisites

Verify that your host system is running a FIPS-compliant operating system that Vertica supports.

The FIPS client installer checks your host system for the value of the sysctl parameter, crypto.fips_enabled. You must set this parameter to 1 (enabled). If your host is not enabled, the client does not install.

Installing the FIPS client

To install the FIPS client driver package:

1. Download the FIPS client package from the Vertica driver downloads page.

2. Log in to the client system as root.

3. Install the RPM package that you downloaded:

   # rpm -Uvh package_name.rpm
   

For ODBC, after you have installed the client package, create a DSN and set some additional configuration parameters. For more information, see:

• Creating an ODBC DSN for Linux
• ODBC driver settings

You can optionally add the vsql client to your PATH environment variable so that you do not need to enter its full path to run it. To do so, add the following to the .profile file in your home directory or the global /etc/profile file:

export PATH=$PATH:/opt/vertica/bin

How the client searches for OpenSSL libraries

When you launch the client application to connect to the server, the client searches for and loads the OpenSSL libraries libcrypto.so.10 and libssl.so.10 for supported OpenSSL versions:

• The client first checks to see if LD_LIBRARY_PATH is set.

• If the LD_LIBARY_PATH location does not include the libraries, it checks RunPath, either /opt/vertica/lib or within the ODBC or vsql directory structure (../lib).

The following figure depicts the search process for the OpenSSL libraries:

2.2.1.3 - JDBC client driver

The Vertica JDBC client driver conforms to JDK 5 specifications and provides an interface for communicating with the Vertica database with Java. For details on this and other APIs, see API Reference.

To install the JDBC client driver, see Installing the JDBC client driver.

2.2.1.3.1 - Installing the JDBC client driver

The JDBC client driver conforms to JDK 5 specifications. Download the JDBC client driver according to your environment and requirements. If you need a FIPS-compliant driver, see Installing the FIPS client driver for JDBC.

Installing Vertica from the RPM automatically installs the JDBC client driver. To use the JDBC client driver, you just need to add the Vertica JDBC .jar to your CLASSPATH.

To manually install the JDBC client driver:

1. Download the version of the JDBC client driver from the Client Drivers downloads page compatible with your version of Vertica.

2. Copy the .jar file to a directory in your Java CLASSPATH on every client system with which you want to access Vertica. You can either:

   • Copy the .jar file to its own directory (such as /opt/vertica/java/lib) and then add that directory to your CLASSPATH (recommended). See Modifying the Java CLASSPATH for details.

   • Copy the .jar file to directory that is already in your CLASSPATH (for example, a directory where you have placed other .jar files on which your application depends).

   • Copy the .jar file to the system-wide Java Extensions directory. The exact location differs between operating systems. Some examples include:

     • Windows: C:\Program Files\Java\jrex.x.x_x\lib\ext\

     • Mac OS: /Library/Java/Extensions or /Users/username/Library/Java/Extensions

3. Create a connection to test your configuration.

2.2.1.3.2 - Modifying the Java CLASSPATH

The CLASSPATH environment variable contains a list of directories where the Java runtime looks for library class files. For your Java client code to access Vertica, you must add to the CLASSPATH the directory containing the Vertica JDBC .jar.

Using symbolic links for the CLASSPATH

You can optionally add to the CLASSPATH a symbolic link vertica-jdbc-x.x.x.jar (where x.x.x is a version number) that points to the JDBC library .jar file, rather than the .jar file itself.

Using the symbolic link ensures that any updates to the JDBC library .jar file (which will use a different filename) will not invalidate your CLASSPATH setting, since the symbolic link's filename will remain the same. You just need to update the symbolic link to point at the new .jar file.

Linux and OS X

The following examples use a POSIX-compliant shell.

To set the CLASSPATH for the current session:

$ export CLASSPATH=$CLASSPATH:/opt/vertica/java/lib/vertica-jdbc-x.x.x.jar

To set the CLASSPATH for every session, add the following to your start-up file (such as ~/.profile or /etc/profile:

$ export CLASSPATH=$CLASSPATH:/opt/vertica/java/lib/vertica-jdbc-x.x.x.jar

Windows

Provide the class paths to the .jar, .zip, or .class files.

C:> SET CLASSPATH=classpath1;classpath2...

For example:

C:> SET CLASSPATH=C:\java\MyClasses\vertica-jdbc-x.x.x.jar

As with the Linux/UNIX settings, this setting only lasts for the current session. To set the CLASSPATH permanently, set an environment variable:

1. On the Windows Control Panel, click System.

2. Click Advanced or Advanced Systems Settings.

3. Click Environment Variables.

4. Under User variables, click New.

5. In the Variable name box, type CLASSPATH.

6. In the Variable value box, type the path to the Vertica JDBC .jar file on your system (for example, C:\Program Files (x86)\Vertica\JDBC\vertica-jdbc-x.x.x.jar)

Specifying the library directory in the Java command

Another, OS-agnostic way to tell the Java runtime where to find the Vertica JDBC driver is to explicitly add the directory containing the .jar file to the Java command line using either the -cp or -classpath argument. For example, you can start your client application with:

java -classpath /opt/vertica/java/lib/vertica-jdbc-x.x.x.jar myapplication.class

Your Java IDE may also let you add directories to your CLASSPATH, or let you import the Vertica JDBC driver into your project. See your IDE documentation for details.

2.2.1.4 - ODBC client driver

The Vertica ODBC client driver provides an interface for creating client applications with several languages:

• C/C++

• Python

• Perl

• PHP

To install ODBC, see Installing the ODBC client driver.

2.2.1.4.1 - Installing the ODBC client driver

To install ODBC, follow the instructions according to your platform. For a list of supported platforms, see Client drivers support.

This page covers a non-FIPS installation. To install ODBC on a FIPS-compliant system, see Installing the FIPS client driver for ODBC and vsql.

Installing on Linux

Installing Vertica from the RPM automatically installs the ODBC client driver, so you do not need to install them again on the machine running Vertica. To use the ODBC client driver in this case, create a DSN.

To install the ODBC client driver manually on other machines:

1. Log in to the client system as root.

2. Verify that your system has a supported ODBC driver manager.

3. Download the ODBC client driver for Linux in the format appropriate for your distribution.

4. Install or extract the driver:

   • If you downloaded the .rpm, install the driver:

     Note

     If the client driver is already installed on your system (either from a manual installation or from automatic installation from the Vertica RPM) and you attempt to reinstall them manually, you will receive error messages. To bypass these errors and overwrite the existing driver installations, use the --force flag.

     $ rpm -Uvh driver_name.rpm
     

   • If you downloaded the .tar, create the /opt/vertica/ directory if it does not already exist, copy the .tar to it, navigate to it, and extract the .tar:

     
     $ mkdir -p /opt/vertica/
     $ cp driver_name.tar.gz /opt/vertica/
     $ tar vzxf driver_name.tar.gz
     

     This creates two directories:

     • /opt/vertica/include: Contains the header file.

     • /opt/vertica/lib64/ (64-bit) or /opt/vertica/lib/ (32-bit): Contains library files.

5. Set the following ODBC driver settings in vertica.ini. For details on each, see ODBC driver settings:

   • ErrorMessagesPath: Required, the path of the directory containing the ODBC driver's error message files.

   • ODBCInstLib: The path to the ODBC installer library. This is only required if the driver manager's installation library is not in the environment variables LD_LIBRARY_PATH or LIB_PATH.

   • DriverManagerEncoding: The UTF encoding standard used by the driver manager. This is only required if your driver manager does not use UTF-8.

   The following is an example configuration in vertica.ini:

   • Use encoding for the 64-bit UNIXODBC driver manager.

   • Use the error messages defined in the standard Vertica 64-bit ODBC driver installation directory.

   • Log all warnings and more severe messages to log files in /tmp/

     [Driver]
     DriverManagerEncoding=UTF-16
     ODBCInstLib=/usr/lib64/libodbcinst.so
     ErrorMessagesPath=/opt/vertica
     LogLevel=4
     LogPath=/tmp
     

6. Create a DSN.

Installing on macOS

To install the ODBC client driver on macOS:

1. Verify that your system has a compatible driver manager. The driver is designed to be used with the standard iODBC Driver Manager that ships with macOS. You can also use unixODBC.

2. Download the ODBC client driver.

3. If you installed a previous version of the ODBC driver, your system might already have a registered driver named "Vertica". You must remove or rename this older version of the driver before installing a new version from the .pkg installer. Renaming the older version allows you to retain the old version after you install the new one.

4. Run the installer.

5. Create a DSN.

Installing silently

1. Log into the client macOS in one of two ways:

   • As an administrator account if you are installing the driver for system-wide use.

   • As the user who needs to use the Vertica ODBC driver.

2. Open a terminal.

3. Install the .pkg file containing the ODBC driver using the command:

   sudo installer -pkg path/to/client/driver/vertica-odbc-xx.x.x-x.pkg -target /
   

Installing on Windows

To install the ODBC client driver on Windows:

1. Download the client driver installer for Windows.

2. Run the installer.

3. Create a DSN.

Installing silently

1. Open a terminal as an Administrator.

2. Run the following command to silently install the drivers to C:\Program Files\Vertica Systems:

   VerticaSetup.exe -q -install InstallFolder="C:\Program Files\Vertica Systems"
   

2.2.1.4.2 - Upgrading and downgrading ODBC

Linux

To upgrade ODBC:

1. Uninstall the current version of the driver.

2. Install the new version of the driver.

macOS

To upgrade or downgrade ODBC:

• Upgrade: Newly installed versions of the Vertica ODBC driver for macOS automatically upgrade the relevant driver system settings. Any DSNs associated with a previous version of the driver are not affected, except that they begin using the newer version of the driver.

• Downgrade: Run the uninstall script to remove the current version of the Vertica ODBC driver for macOS. Complete this step before installing an older driver version.

Windows

1. Download the Windows client driver installer.

2. Run the installer and follow the prompts to upgrade the driver. The installer upgrades existing drivers in place.

3. Reboot your system.

2.2.1.4.3 - Uninstalling ODBC

Linux

If you installed ODBC with the .rpm:

$ rpm -e package_name

If you installed ODBC with the .tar, delete the directory manually.

macOS

Uninstalling the macOS ODBC Client-Driver does not remove any existing DSNs associated with the driver.

To uninstall:

1. Open a terminal window.

2. Run the command:

   sudo /Library/Vertica/ODBC/bin/Uninstall
   

Windows

1. Open the Add or Remove Programs menu.

2. EIther uninstall the Vertica Client Installer to remove all client drivers from the system or, to only uninstall ODBC, uninstall the following applications:

   • Vertica ODBC Driver (32 Bit)

   • Vertica ODBC Driver (64 Bit)

2.2.1.4.4 - Creating an ODBC data source name (DSN)

A Data Source Name (DSN) is the logical name that is used by Open Database Connectivity (ODBC) to refer to the driver and other information that is required to access data from a data source. Whether you are developing your own ODBC client code or you are using a third-party tool that needs to access Vertica using ODBC, you need to configure and test a DSN. The method you use depends upon the client operating system you are using.

Refer to the following sections for information specific to your client operating system.

2.2.1.4.4.1 - Creating an ODBC DSN for Linux

You define DSN on Linux and other UNIX-like platforms in a text file. Your client's driver manager reads this file to determine how to connect to your Vertica database. The driver manager usually looks for the DSN definitions in two places:

• /etc/odbc.ini

• ~/.odbc.ini (a file named .odbc.ini in the user's home directory)

Users must be able to read the odbc.ini file in order to use it to connect to the database. If you use a global odbc.ini file, consider creating a UNIX group with read access to the file. Then, add the users who need to use the DSN to this group.

The structure of these files is the same—only their location differs. If both files are present, the ~/.odbc.ini file usually overrides the system-wide /etc/odbc.ini file.

odbc.ini file structure

The odbc.ini is a text file that contains two types of lines:

• Section definitions, which are text strings enclosed in square brackets.

• Parameter definitions, which contain a parameter name, an equals sign (=), and then the parameter's value.

The first section of the file is always named [ODBC Data Sources], and contains a list of all the DSNs that the odbc.ini file defines. The parameters in this section are the names of the DSNs, which appear as section definitions later in the file. The value is a text description of the DSN and has no function. For example, an odbc.ini file that defines a single DSN named Vertica DSN could have this ODBC Data Sources section:

[ODBC Data Sources]
VerticaDSN = "vmartdb"

Appearing after the ODBC data sources section are sections that define each DSN. The name of a DSN section must match one of the names defined in the ODBC Data Sources section.

Configuring the odbc.ini file:

To create or edit the DSN definition file:

1. Using the text editor of your choice, open odbc.ini or ~/.odbc.ini.

2. Create an ODBC Data Sources section and define a parameter:

   • Whose name is the name of the DSN you want to create

   • Whose value is a description of the DSN

   For example, to create a DSN named VMart, you would enter:

   [ODBC Data Sources]
   VMart = "VMart database on Vertica"
   

3. Create a section whose name matches the DSN name you defined in step 2. In this section, you add parameters that define the DSN's settings. The most commonly-defined parameters are:

   • Description – Additional information about the data source.

   • Driver – The location and designation of the Vertica ODBC driver, or the name of a driver defined in the odbcinst.ini file (see below). For future compatibility, use the name of the symbolic link in the library directory, rather than the library file:

     • ( /opt/vertica/lib, on 32-bit clients

     • /opt/vertica/lib64, on 64-bit clients

     For example, the symbolic link for the 64-bit ODBC driver library is:

     /opt/vertica/lib64/libverticaodbc.so
     

     The symbolic link always points to the most up-to-date version of the Vertica client ODBC library. Use this link so that you do not need to update all of your DSNs when you update your client drivers.

   • Database – The name of the database running on the server. This example uses vmartdb for the vmartdb.

   • ServerName — The name of the server where Vertica is installed. Use localhost if Vertica is installed on the same machine.

     You can provide an IPv4 address, IPv6 address, or host name.

     In mixed IPv4/IPv6 networks, the DNS server configuration determines which IP version address is sent first. Use the PreferredAddressFamily option to force the connection to use either IPv4 or IPv6.

   • UID — Either the database superuser (same name as database administrator account) or a user that the superuser has created and granted privileges. This example uses the user name dbadmin.

   • PWD —The password for the specified user name. This example leaves the password field blank.

   • Port — The port number on which Vertica listens for ODBC connections. For example, 5433.

   • ConnSettings — Can contain SQL commands separated by a semicolon. These commands can be run immediately after connecting to the server.

   • SSLKeyFile — The file path and name of the client's private key. This file can reside anywhere on the system.

   • SSLCertFile —The file path and name of the client's public certificate. This file can reside anywhere on the system.

   • Locale — The default locale used for the session. By default, the locale for the database is: en_US@collation=binary (English as in the United States of America). Specify the locale as an ICU Locale. See the ICU User Guide (http://userguide.icu-project.org/locale) for a complete list of parameters that can be used to specify a locale.

   • PreferredAddressFamily:

     The IP version to use if the client and server have both IPv4 and IPv6 addresses and you have provided a host name, one of the following:

     • ipv4: Connect to the server using IPv4.

     • ipv6: Connect to the server using IPv6.

     • none: Use the IP address provided by the DNS server.

For example:

[VMart]
Description = Vmart Database
Driver = /opt/vertica/lib64/libverticaodbc.so
Database = vmartdb
Servername = host01
UID = dbadmin
PWD =
Port = 5433
ConnSettings =
AutoCommit = 0
SSLKeyFile = /home/dbadmin/client.key
SSLCertFile = /home/dbadmin/client.crt
Locale = en_US@collation=binary

See ODBC DSN connection properties for a complete list of parameters including Vertica-specific ones.

Using an odbcinst.ini file

Instead of giving the path of the ODBC driver library in your DSN definitions, you can use the name of a driver defined in the odbcinst.ini file. This method is useful method if you have many DSNs and often need to update them to point to new driver libraries. It also allows you to set some additional ODBC parameters, such as the threading model.

Just as in the odbc.ini file, odbcinst.ini has sections. Each section defines an ODBC driver that can be referenced in the odbc.ini files.

In a section, you can define the following parameters:

• Description— Additional information about the data source.

• Driver— The location and designation of the Vertica ODBC driver, such as /opt/vertica/lib64/libverticaodbc.so

For example:

[Vertica]
Description = Vertica ODBC Driver
Driver = /opt/vertica/lib64/libverticaodbc.so

Then, in your odbc.ini file, use the name of the section you created in the odbcinst.ini file that describes the driver you want to use. For example:

[VMart]
Description = Vertica Vmart database
Driver = Vertica

If you are using the unixODBC driver manager, you should also add an ODBC section to override its standard threading settings. By default, unixODBC serializes all SQL calls through ODBC, which prevents multiple parallel loads. To change this default behavior, add the following to your odbcinst.ini file:

[ODBC]
Threading = 1

Configuring additional ODBC settings

On Linux and UNIX systems, you need to configure some additional driver settings before you can use your DSN. See ODBC driver settings for details.

2.2.1.4.4.1.1 - Testing an ODBC DSN using isql

The unixODBC driver manager includes a utility named isql, which is a simple ODBC command-line client. It lets you to connect to a DSN to send commands and receive results, similarly to vsql.

To use isql to test a DSN connection:

1. Run the following command:

   $ isql –v DSNname
   

   Where DSNname is the name of the DSN you created.

   A connection message and a SQL prompt display. If they do not, you could have a configuration problem or you could be using the wrong user name or password.

2. Try a simple SQL statement. For example:

   SQL> SELECT table_name FROM tables;
   

   The isql tool returns the results of your SQL statement.

2.2.1.4.4.2 - Creating an ODBC DSN for windows clients

To create a DSN for Microsoft Windows clients, you must perform the following tasks:

2.2.1.4.4.2.1 - Setting up an ODBC DSN

A Data Source Name (DSN) is the ODBC logical name for the drive and other information the database needs to access data. The name is used by Internet Information Services (IIS) for a connection to an ODBC data source.

This section describes how to use the Vertica ODBC Driver to set up an ODBC DSN. This topic assumes that the driver is already installed, as described in Installing Client Drivers on Windows.

To set up a DSN

1. Open the ODBC Administrator. For example, you could navigate to Start > Control Panel > Administrative Tools > Data Sources (ODBC).

   Note

   The method you use to open the ODBC Administrator depends on your version of Windows. Differences between Windows versions and Start Menu customizations could require you to take a different action to open the ODBC Administrator.

2. Decide if you want all users on your client system to be able to access to the DSN for the Vertica database.

   • If you want all users to have access, then click the System DSN tab.

   • Otherwise, click the User DSN tab to create a DSN that is only usable by your Windows user account.

3. Click Add to create a new DSN to connect to the Vertica database.

4. Scroll through the list of drivers in the Create a New Data Source dialog box to locate the Vertica driver. Select the driver, and then click Finish.

   Note

   If you have installed more than one version of the Vertica client drivers on your Windows client system, you may see multiple versions of the driver in this list. Choose the version that you know is compatible with your client application and Vertica Analytic Database server. If you are unsure, use the latest version of the driver.

   The Vertica ODBC DSN configuration dialog box appears.

5. Click the More >>> button to view a description of the field you are editing and the connection string defined by the DSN.

6. Enter the information for your DSN. The following fields are required:

   • DSN Name — The name for the DSN. Clients use this name to identify the DSN to which they want to connect. The DSN name must satisfy the following requirements:

     • Its maximum length is 32 characters.

     • It is composed of ASCII characters except for the following: { } , ; ? * = ! @ \

     • It contains no spaces.

   • Server — The host name or IP address of the Vertica server to which you want to connect. Use localhost, if Vertica is installed on the same machine.

     You can provide an IPv4 address, IPv6 address, or host name.

     In mixed IPv4/IPv6 networks, the DNS server configuration determines which IP version address is sent first. Use the PreferredAddressFamily option to force the connection to use either IPv4 or IPv6.

     The PreferredAddressFamily option is available on the Client Settings tab.

   • Backup Servers — A comma-separated list of host names or IP addresses used to connect to if the server specified by the Server field is down. Optional.

   • Database —The name of the Vertica database.

   • User Name — The name of the user account to use when connecting to the database. If the application does not supply its own user name when connecting to the DSN, this account name is used to log into the database.

   The rest of the fields are optional. See DSN Parameters for detailed information about the DSN parameters you can define.

7. If you want to test your connection:

   1. Enter at least a valid DSN name, Server name, Database, and either User name or select Windows authentication.

   2. If you have not selected Windows authentication, you can enter a password in the Password box. Alternately, you can select Password for missing password to have the driver prompt you for a password when connecting.

      Caution

      Passwords entered into the Password box are saved, in plaintext, to the Windows registry.

   3. Click Test Connection.

8. When you have finished editing and testing the DSN, click OK. The Vertica ODBC DSN configuration window closes, and your new DSN is listed in the ODBC Data Source Administrator window.

9. Click OK to close the ODBC Data Source Administrator.

After creating the DSN, you can test it using Microsoft Excel 2007.

Setting up a 32-Bit DSN on 64-Bit versions of Microsoft windows

On 64-bit versions of Windows, the default ODBC Data Source Administrator creates and edits DSNs that are associated with the 64-bit Vertica ODBC library.

Attempting to use these 64-bit DSNs with a 32-bit client application results in an architecture mismatch error. Instead, you must create a specific 32-bit DSN for 32-bit clients by running the 32-bit ODBC Administrator usually located at:

c:\Windows\SysWOW64\odbcad32.exe

This administrator window edits a set of DSNs that are associated with the 32-bit ODBC library. You can then use your 32-bit client applications with the DSNs you create with this version of the ODBC administrator.

2.2.1.4.4.2.2 - Encrypting passwords on ODBC DSN

When you install an ODBC driver and create a Data Source Name (DSN) the DSN settings are stored in the registry, including the password. Encrypting passwords on ODBC DSN applies only to Windows systems.

Encrypting passwords on an ODBC data source name (DSN) provides security against unauthorized database access. The password is not encrypted by default and is stored in plain-text.

Enable password encryption

Use the EncryptPassword parameter to enable or disable password encryption for an ODBC DSN:

• EncryptPassword = true enables password encryption

• EncryptPassword = false (default) disables password encryption

Set EncryptPassword in the Windows registry - HKEY_LOCAL_MACHINE > Software > Vertica > ODBC > Driver EncryptPassword=<true/false>.

Encrypted passwords get updated in the following registry locations:

For a user DSN:

HKEY_CURRENT_USER-> Software -> ODBC -> ODBC.INI -> DSNNAME -> PWD

For a system DSN:

HKEY_LOCAL_MACHINE-> Software -> ODBC -> ODBC.INI -> DSNNAME -> PWD

Verify password encryption

Use Windows Registry editor to determine if password encryption is enabled based on the value of EncryptPassword. Depending on the type of DSN you installed, check the following:

For a user DSN: HKEY_CURRENT_USER > Software > ODBC > ODBC.INI > dsn name > isPasswordEncrypted=<1/0>

For a system DSN: HKEY_LOCAL_MACHINE > Software > ODBC > ODBC.INI > dsn name > isPasswordEncrypted=<1/0>

For each DSN, the value of the isPasswordEncrypted parameter indicates the status of the password encryption, where 1 indicates an encrypted password and 0 indicates an unencrypted password.

2.2.1.4.4.2.3 - Testing an ODBC DSN using Excel

You can use Microsoft Excel to verify that an application can connect to an ODBC data source or other ODBC application.

1. Open Microsoft Excel, and select Data > Get External Data > From Other Sources > From Microsoft Query.

2. When the Choose Data Source dialog box opens:

   1. Select New Data Source, and click OK.

   2. Enter the name of the data source.

   3. Select the Vertica driver.

   4. Click Connect.

3. When the Vertica Connection Dialog box opens, enter the connection information for the DSN, and click OK.

4. Click OK on the Create New Data Source dialog box to return to the Choose Data Source dialog box.

5. Select VMart_Schema*, and verify that the Use the Query Wizard check box is deselected. Click OK.

6. When the Add Tables dialog box opens, click Close.

7. When the Microsoft Query window opens, click the SQL button.

8. In the SQL window, write any simple query to test your connection. For example:

   SELECT DISTINCT calendar_year FROM date_dimension;
   

9. * If you see the caution, "SQL Query can't be represented graphically. Continue anyway?" click **OK**.      * The data values 2003, 2004, 2005, 2006, 2007 indicate that you successfully connected to and ran a query through ODBC.
   

10. Select File > Return Data to Microsoft Office Excel.

11. In the Import Data dialog box, click OK.

    The data is now available for use in an Excel worksheet.

2.2.1.4.4.3 - Creating an ODBC DSN for macOS clients

You can use the Vertica ODBC Driver to set up an ODBC DSN. This procedure assumes that the driver is already installed, as described in Installing the ODBC client driver.

Setting up a DSN

1. Using your web browser, download and install the Apple ODBC Administrator Tool.

2. Locate and open the ODBC Administrator Tool after installation:

   1. Navigate to Finder > Applications > Utilities.

   2. Open the ODBC Administrator Tool.

3. Click the Drivers tab, and verify that the Vertica driver is installed.
   

4. Specify if you want all users on your client system to be able to access the DSN for the Vertica database:

   • If you want all users to have access, then click the System DSN tab.

   • Otherwise, click the User DSN tab to create a DSN that is only usable by your Macintosh user account.

5. Click Add... to create a new DSN to connect to the Vertica database.

6. Scroll through the list of drivers in the Choose A Driver dialog box to locate the Vertica driver. Select the driver, and then click OK. A dialog box opens that requests DSN parameter information.

7. In the dialog box, enter the Data Source Name (DSN) and an optional Description. To do so, click Add to insert keywords (parameters) and values that define the settings needed to connect to your database, including database name, server host, database user name (such as dbadamin), database password, and port. Then, click OK.

8. In the ODBC Administrator dialog box, click Apply.

   See ODBC DSN connection properties for a complete list of parameters including those specific to Vertica.

After configuring the ODBC Administrator Tool, you may need to configure additional driver settings before you can use your DSN, depending on your environment. See Additional ODBC Driver Configuration Settings for details.

2.2.1.4.4.3.1 - Testing an ODBC DSN using iodbctest

The standard iODBC Driver Manager on OS X includes a utility named iodbctest that lets you test a DSN to verify that it is correctly configured. You pass this command a connection string in the same format that you would use to open an ODBC database connection. After configuring your DSN connection, you can run a query to verify that the connection works.

For example:

# iodbctest "DSN=VerticaDSN;UID=dbadmin;PWD=password"
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0607.1008
Driver: 07.01.0200 (verticaodbcw.so)
SQL> SELECT table_name FROM tables;
table_name
--------------------------------------------------------------------------------------------------------------------------------
customer_dimension
product_dimension
promotion_dimension
date_dimension
vendor_dimension
employee_dimension
shipping_dimension
warehouse_dimension
inventory_fact
store_dimension
store_sales_fact
store_orders_fact
online_page_dimension
call_center_dimension
online_sales_fact
numbers
result set 1 returned 16 rows.

2.2.1.4.4.4 - ODBC DSN connection properties

The following tables list the connection properties you can set in the DSNs for use with Vertica's ODBC driver. To set these parameters, see Setting DSN connection properties.

Required connection properties

These connection properties are the minimum required to create a functioning DSN.

Optional properties

Advanced settings

Identification

OAuth connection properties

The following connection properties pertain to OAuth in ODBC.

Encryption

Third-party compatibility

Kerberos connection properties

Use the following properties for client authentication using Kerberos.

See also

2.2.1.4.4.5 - Setting DSN connection properties

The properties in the following tables are common for all user and system DSN entries. The examples provided are for Windows clients.

To edit DSN properties:

• On UNIX and Linux client platforms, you can edit the odbc.ini file. The location of this file is specific to the driver manager. See Creating an ODBC DSN for Linux.

• On Windows client platforms, you can edit some DSN properties using the Vertica ODBC client driver interface. See Creating an ODBC DSN for windows clients.

• You can also edit the DSN properties directly by opening the DSN entry in the Windows registry (for example, at HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\DSNname). Directly editing the registry can be risky, so you should only use this method for properties that cannot be set through the ODBC driver's user interface, or via your client code.

• You can set properties in the connection string when opening a connection using the SQLDriverConnect() function:

  sqlRet = SQLDriverConnect(sql_hDBC, 0, (SQLCHAR*)"DSN=DSNName;Locale=en_GB@collation=binary", SQL_NTS, szDNS, 1024,&nSize, SQL_DRIVER_NOPROMPT);
  

  Note

  In the connection string ';' is a reserved symbol. If you need to set multiple properties as part of the ConnSettings property use '%3B' in place of ';'. Also use '+' instead of spaces.

  For example:

  sqlRet = SQLDriverConnect(sql_hDBC, 0, (SQLCHAR*)"DSN=Vertica SQL;ConnSettings=set+search_path+to+a,b,c%3Bset+locale=ch;SSLMode=prefer", SQL_NTS,
  szDNS, 1024,&nSize, SQL_DRIVER_NOPROMPT);
  

• Your client code can retrieve DSN property values after a connection has been made to Vertica using the SQLGetConnectAttr() and SQLGetStmtAttr() API calls. Some properties can be set and using SQLSetConnectAttr() and SQLSetStmtAttr().

  For details of the list of properties specific to Vertica see ODBC Header Files specific to Vertica.

2.2.1.4.5 - ODBC driver settings

• DriverManagerEncoding: The UTF encoding standard used by the driver manager. This can be one of the following:

  • UTF-8

  • UTF-16

  • UTF-32

  The ODBC driver encoding must match that of your driver manager. The following table lists default encodings for various platforms that take effect if you do not set this parameter. If the defaults do not match the encoding used by your driver manager, you must set it manually. Consult your driver manager's documentation for details on its encoding.

  Note

  While both UTF-16 and UTF-8 are valid settings for the DataDirect driver manager, UTF-16 is recommended.

  Client Platform	Default Encoding
  Linux 32-bit	UTF-32
  Linux 64-bit	UTF-32
  Linux Itanium 64-bit	UTF-32
  OS X	UTF-32
  Windows 32-bit	UTF-16
  Windows 64-bit	UTF-16

• ErrorMessagesPath: Required, the path of the directory containing the ODBC driver's error message files. These files (ODBCMessages.xml and VerticaMessages.xml) are stored in the same directory as the Vertica ODBC driver files (for example, opt/vertica/en-US in the downloaded .tar).

• ODBCInstLib: The path to the ODBC installer library. This setting is only required if the directory containing the library is not set in the LD_LIBRARY_PATH or LIB_PATH environment variables. The library files for the major driver managers are:

• UnixODBC: libodbcinst.so

• iODBC: libiodbcinst.so (libiodbcinst.2.dylib on macOS)

• DataDirect: libodbcinst.so

You can also control client-server message logging for both ODBC and ADO.NET. For details, see Configuring ODBC logs.

Linux and macOS

To set these parameters on Linux or macOS:

1. Create a file vertica.ini anywhere on the client system. Common locations are in /etc/ for a shared configuration, or the home directory for a per-user configuration.

2. Verify that users of the ODBC driver have read privileges on the file.

3. Set the VERTICAINI environment variable to the path of vertica.ini. For example:

$ export VERTICAINI=/etc/vertica.ini

1. Create a section called [Driver] in vertica.ini:

[Driver]

1. Under [Driver], set parameters with the following format. Each parameter must have its own line:

[Driver]
DriverManagerEncoding=UTF-16
ODBCInstLib=/usr/lib64/libodbcinst.so

Windows

The Windows client driver installer automatically configures all necessary settings for the ODBC driver. Settings are stored in the registry in HKEY_LOCAL_MACHINE\SOFTWARE\Vertica\ODBC\Driver.

If you want to configure ODBC further, use the ODBC Data Sources program.

2.2.1.4.6 - Configuring ODBC logs

The following parameters control whether and how the ODBC client driver logs messages between the client and server.

The way you set these parameters differs between operating systems:

• On Linux and macOS, edit vertica.ini you created during the installation. For example, to log all warnings and more severe messages to log files in /tmp/:

  [Driver]
  LogLevel=4
  LogPath=/tmp
  

• On Windows, edit the keys in the Windows Registry under HKEY_LOCAL_MACHINE\SOFTWARE\Vertica\ODBC\Driver.

Parameters

• LogLevel: The severity of messages that are logged between the client and the server. The valid values are:

  • 0: No logging

  • 1: Fatal errors

  • 2: Errors

  • 3: Warnings

  • 4: Info

  • 5: Debug

  • 6: Trace (all messages)

  The value you specify for this setting sets the minimum severity for a message to be logged. For example, setting LogLevel to 3 means that the client driver logs all warnings, errors, and fatal errors.

• LogPath: The absolute path of a directory to store log files. For example: /var/log/verticaodbc

Diverting log entires to ETW (windows)

On Windows clients, ODBC log entries can be sent to Event Tracing for Windows (ETW) so they appear in the Windows Event Viewer:

• Register the driver as a Windows Event Log provider and enable the logs.

• Activate ETW by adding a string value LogType with data ETW to your Windows Registry.

• Understand how Vertica compresses log levels for the Windows Event Viewer.

• Know where to find the logs within Event Viewer.

• Understand the meaning of the Event IDs in your log entries.

Registering the ODBC driver as a windows event log provider

To use ETW logging, you must register the ODBC driver as a Windows Event Log provider. You can choose to register either the 32-bit or 64-bit driver. After you have registered the driver, you must enable the logs.

1. Open a command prompt window as Administrator, or launch the command prompt with the Run as Administrator option.

   Important

   You must have administrator privileges to successfully complete the next step.

2. Run the command wevtutil im to register either the 32-bit or 64-bit version of the driver.

   • For the 64-bit ODBC driver, run:

     wevtutil im "c:\Program Files\Vertica Systems\ODBC64\lib\VerticaODBC64.man"
     /resourceFilePath:"c:\Program Files\Vertica Systems\ODBC64\lib\vertica_9.1_odbc_3.5.dll"
     /messageFilePath:"c:\Program Files\Vertica Systems\ODBC64\lib\vertica_9.1_odbc_3.5.dll"
     

   • For the 32-bit ODBC driver, run:

     wevtutil im "c:\Program Files (x86)\Vertica Systems\ODBC32\lib\VerticaODBC32.man"
     /resourceFilePath:"c:\Program Files (x86)\Vertica Systems\ODBC32\lib\vertica_9.1_odbc_3.5.dll"
     /messageFilePath:"c:\Program Files (x86)\Vertica Systems\ODBC32\lib\vertica_9.1_odbc_3.5.dll"
     

3. Run the command wevtutil sl to enable the logs.

   • For 64-bit ODBC driver logs, run:

     wevtutil sl VerticaODBC64/e:true
     

   • For the 32-bit ODBC driver logs, run:

     wevtutil sl VerticaODBC32/e:true
     

   Note

   Should you want to later disable the logs, you can use the same wevtutil sl command, substituting /e:false in place of /e:true when you issue the statement. Alternatively, you can enable or disable logs within the Windows Event Viewer itself.

Add the string value LogType

By default, Vertica does not send ODBC log entries to ETW. To activate ETW, add the string LogType to your Windows registry, and set its value to ETW.

1. Start the registry editor by typing regedit.exe in the Windows Run command box.

2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Vertica\ODBC\Driver in the registry.

3. Right-click in the right pane of the Registry Editor window.

4. Select New, then select String Value.

5. Change the name of the string value from New Value #1 to LogType.

6. Double-click the new LogType entry. When prompted for a new value, enter ETW.

7. Exit the registry editor.

ETW is disabled by default. When ETW is enabled, you can disable it by clearing the value ETW from the LogType string.

LogLevel in the windows event viewer

While LogLevel ranges from 0 through 6, this range is compressed for the Windows Event Viewer to a range of 0 through 3.

The following examples show how LogLevel is converted when displayed in the Windows Event Viewer.

• A LogLevel of 5 sends fatal errors, errors, warnings, info and debug log level entries to Event Viewer as Level 4 (Information).

• A LogLevel of 6 sends fatal errors, errors, warnings, debug and trace log level entries to Event Viewer as Level 4.

Finding logs in the event viewer

1. Launch the Windows Event Viewer.

2. From Event Viewer (Local), expand Applications and Services Logs.

3. Expand the folder that contains the log you want to review (for example, VerticaODBC64).

4. Select the Vertica ODBC log under the folder. Entries appear in the right pane.

5. Note the value in the Event ID field. Each Event Log entry includes one of four Event IDs:

   • 0: Informational (debug, info, and trace events)

   • 1: Error

   • 2: Fatal event

   • 3: Warning

2.2.1.5 - Python client drivers

Vertica supports several Python drivers for creating client applications.

Prerequisites

To create Python client applications, you must install the required drivers.

2.2.1.5.1 - Installing Python client drivers

Vertica supports several Python client drivers.

Installing vertica-python

See the vertica-python repository for installation and usage instructions.

Installing pyodbc

The pyodbc module interacts with the Vertica ODBC client driver. To install it:

1. Install the ODBC client driver.

2. Install compatible versions of Python and pyodbc.

2.2.1.6 - Node.js client driver

The open-source vertica-nodejs client driver lets you interact with your database with JavaScript. For details, see the vertica-nodejs package on npm.

2.2.1.7 - Go client driver

The open-source vertica-sql-go driver lets you interact with your database with Go. For details, see vertica-sql-go.

2.2.1.8 - OLE DB client driver

The OLE DB client driver is an interface for C# client applications to interact with your Vertica database.

2.2.1.8.1 - Installing the OLE DB client driver

To install the Vertica OLE DB client driver:

1. Download the Windows client driver installer. For details on the drivers included in this installer, see Windows client driver installer.

2. Run the installer and follow the prompts to install the drivers.

3. Reboot your system.

After installing the OLE DB client driver, you can configure ETW logging.

For a list of connection properties, see OLE DB connection properties.

2.2.1.8.1.1 - OLE DB connection properties

Use the Connection Manager to set the OLE DB connection string properties, which define your connection. You access the Connection Manager from within Visual Studio.

These connection parameters appear on the Connection page.

The All page from the Connection Manager dialog box includes all possible connection string properties for the provider.

The table that follows lists the connection parameters for the All page.

For OLE DB properties information specific to Microsoft, see the Microsoft documentation OLE DB Properties.

2.2.1.8.1.2 - Configuring OLE DB logs

The following parameters control how the OLE DB client driver logs messages between the client and server. To set them, edit the keys in the Windows Registry under HKEY_LOCAL_MACHINE\SOFTWARE\Vertica\OLEDB\Driver:

• LogLevel: The severity of messages that are logged between the client and the server. The valid values are:

  • 0: No logging

  • 1: Fatal errors

  • 2: Errors

  • 3: Warnings

  • 4: Info

  • 5: Debug

  • 6: Trace (all messages)

  The value you specify for this setting sets the minimum severity for a message to be logged. For example, setting LogLevel to 3 means that the client driver logs all warnings, errors, and fatal errors.

• LogPath: The absolute path of a directory to store log files. For example: /var/log/verticaoledb

Diverting OLE DB log entries to ETW

On Windows clients, you can direct Vertica to send OLE DB log entries to Event Tracing for Windows (ETW). Once set, OLE DB log entries appear in the Windows Event Viewer. To use ETW:

• Register the driver as a Windows Event Log provider, and enable the logs.

• Activate ETW by adding a string value to your Windows Registry.

• Understand how Vertica compresses log levels for the Windows Event Viewer.

• Know where to find the logs within Event Viewer.

• Understand the meaning of the Event IDs in your log entries.

Registering the OLE DB driver as a windows event log provider

To use ETW logging, you must register the OLE DB driver as a Windows Event Log provider. You can choose to register either the 32-bit or 64-bit driver. Once you have registered the driver, you must enable the logs.

1. Open a command prompt window as Administrator, or launch the command prompt with the Run as Administrator option.

   Important

   You must have administrator privileges to successfully complete the next step.

2. Run the command wevtutil im to register either the 32-bit or 64-bit version of the driver.

   1. For the 64-bit OLE DB driver, run:

      wevtutil im "c:\Program Files\Vertica Systems\OLEDB64\lib\VerticaOLEDB64.man"
      /resourceFilePath:"c:\Program Files\Vertica Systems\OLEDB64\lib\vertica_8.1_oledb.dll"
      /messageFilePath:"c:\Program Files\Vertica Systems\OLEDB64\lib\vertica_8.1_oledb.dll"
      

   2. For the 32-bit OLE DB driver, run:

      wevtutil im "c:\Program Files (x86)\Vertica Systems\OLEDB32\lib\VerticaOLEDB32.man"
      /resourceFilePath:"c:\Program Files (x86)\Vertica Systems\OLEDB32\lib\vertica_8.1_oledb.dll"
      /messageFilePath:"c:\Program Files (x86)\Vertica Systems\OLEDB32\lib\vertica_8.1_oledb.dll"
      

3. Run the command wevtutil sl to enable the logs.

   1. For 64-bit OLE DB driver logs, run:

      wevtutil sl VerticaOLEDB64/e:true
      

   2. For the 32-bit ODBC driver logs, run:

      wevtutil sl VerticaOLEDB32/e:true
      

   Note

   Should you want to later disable the logs, you can use the same wevtutil sl command, substituting /e:false in place of /e:true when you issue the statement. Alternatively, you can enable or disable logs within the Windows Event Viewer itself.

Add the string value LogType

By default, Vertica does not send OLE DB log entries to ETW. To activate ETW, add the string LogType to your Windows registry, and set its value to ETW.

1. Start the registry editor by typing regedit.exe in the Windows Run command box.

2. Navigate, in the registry, to: HKEY_LOCAL_MACHINE\SOFTWARE\Vertica\OLEDB\Driver.

3. Right-click in the right pane of the Registry Editor window.

4. Select New, then select String Value.

5. Change the name of the string value from New Value #1 to LogType.

6. Double-click the new LogType entry. When prompted for a new value, enter ETW.

7. Exit the registry editor.

ETW is off by default. When ETW is activated, you can subsequently turn it off by clearing the value ETW from the LogType string.

LogLevel in the windows event viewer

While LogLevel ranges from 0 through 6, this range is compressed for the Windows Event Viewer to a range of 0 through 3.

The following examples show how LogLevel is converted when displayed in the Windows Event Viewer.

• A LogLevel of 5 sends fatal errors, errors, warnings, info and debug log level entries to Event Viewer as Level 4 (Information).

• A LogLevel of 6 sends fatal errors, errors, warnings, debug and trace log level entries to Event Viewer as Level 4.

Finding logs in the event viewer

1. Launch the Windows Event Viewer.

2. From Event Viewer (Local), expand Applications and Services Logs.

3. Expand the folder that contains the log you want to review (for example, VerticaOLEDB64).

4. Select the Vertica ODBC log under the folder. Entries appear in the right pane.

5. Note the value in the Event ID field. Each Event Log entry includes one of four Event IDs:

   • 0: Informational (debug, info, and trace events)

   • 1: Error

   • 2: Fatal event

   • 3: Warning

2.2.1.9 - ADO.NET client driver

The Vertica ADO.NET driver lets you access Vertica with C# .

2.2.1.9.1 - Installing the ADO.NET client driver

Prerequisites

The ADO.NET client driver requires the following:

• A supported operating system.

• At least 512MB of memory

• A supported version of .NET Core or .NET Framework. For details, see the Microsoft documentation:

  • Install .NET on Windows
  • Install .NET on macOS
  • Install .NET on Linux

Installation

For a sample application that uses and demonstrates all of these installation methods, see the client-application-examples repository.

Package reference

The ADO.NET client driver is available on NuGet and should be installed with a package reference.

To reference the package, add the following to your .csproj. For an example .csproj file, see SampleApp.csproj:

<ItemGroup>
  <PackageReference Include="Vertica.Data" Version="24.1.0" />
</ItemGroup>

Local package reference

You can also download the Vertica.Data package and reference it locally.

On Windows platforms, you can install the ADO.NET driver with the Windows installer and then reference the .dll. This is required for certain applications like TIBCO Spotfire to interact with the driver:

<ItemGroup>
  <Reference Include="Vertica.Data">
    <HintPath>path\to\Program Files\Vertica Systems\ADO.NET64\Vertica.Data.dll</HintPath>
  </Reference>
</ItemGroup>

2.2.1.9.2 - Log properties

Config-level Settings

The following parameters control how messages between the client and server are logged. If they are not set, then the client library does not log any messages.

To set these parameters, create and edit the configuration file Vertica.Data.dll.config in one of the following locations. If the file exists in both locations, the one in the home directory takes priority:

• Home directory
• Project directory

LogLevel

The minimum severity of a message for it to be logged, one of the following:

• 0: No logging
• 1: Fatal errors
• 2: Errors
• 3: Warnings
• 4: Info
• 5: Debug
• 6: Trace (all messages)

For example, a LogLevel of 3 means that the client driver logs messages with severities 1, 2, and 3.

LogPath

The absolute path of the log file. For example: /var/log/verticaadonet/ado.log.

LogNamespace

Limits logging to messages generated by certain objects in the client driver.

Example configuration file

The following example configuration file uses the default values for each configuration setting:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <appSettings>
        <add key="Logging.LogLevel" value="None" />
        <add key="Logging.LogPath" value="" />
        <add key="Logging.LogNamespace" value="" />
    </appSettings>
</configuration> 

VerticaLogProperties

You can set the log properties of the ADO.NET driver with the VerticaLogProperties class, which includes the following methods:

• SetLogPath(String path)
• SetLogNamespace(String lognamespace)
• SetLogLevel(VerticaLogLevel loglevel)

Logs are created when the first connection is opened, so you cannot change the log path with SetLogPath() after the connection starts. You can change the log level and log namespace at any time.

Changes made by these functions last for the lifetime of the application. To make permanent changes, use Vertica.Data.dll.config.

SetLogPath()

The SetLogPath() method takes as an argument a String path containing the path to the log file. If the path string contains only a directory path, then the log file is created with the name vdp-driver-MM-dd_HH.mm.ss.log (where MM-dd_HH.mm.ss is the date and time the log was created). If the path ends in a filename, such as log.txt or log.log, then the log is created with that filename.

If SetLogPath() is called with an empty string for the path argument, then the client executable's current directory is used as the log path.

If SetLogPath() is not called and entry exists for the log path in Vertica.Data.dll.config, and you have called any of the other VerticaLogProperties methods, then the client executable's current directory is used as the log path.

For example:

//set the log path
string path = "C:\\log";
VerticaLogProperties.SetLogPath(path);

SetLogNamespace()

The SetLogNamespace() method takes as an argument a String lognamespace containing the namespace to log. The namespace string to log can be one of the following:

• Vertica
• Vertica.Data.VerticaClient
• Vertica.Data.Internal.IO
• Vertica.Data.Internal.DataEngine
• Vertica.Data.Internal.Core

Namespaces can be truncated to include child namespaces. For example, you can specify Vertica.Data.Internal to log for all of the Vertica.Data.Internal namespaces.

If a log namespace is not set, and no value is stored in Vertica.Data.dll.config, then the Vertica namespace is used for logging.

For example:

//set namespace to log
string lognamespace = "Vertica.Data.VerticaClient";
VerticaLogProperties.SetLogNamespace(lognamespace);

SetLogLevel()

The SetLogLevel() method takes as an argument a VerticaLogLevel loglevel, one of the following:

• VerticaLogLevel.None
• VerticaLogLevel.Fatal
• VerticaLogLevel.Error
• VerticaLogLevel.Warning
• VerticaLogLevel.Info
• VerticaLogLevel.Debug
• VerticaLogLevel.Trace

If a log level is not set, and no value is stored in Vertica.Data.dll.config, then VerticaLogLevel.None is used.

For example:

//set log level
VerticaLogLevel level = VerticaLogLevel.Debug;
VerticaLogProperties.SetLogLevel(level);

Getting log properties

You can retrieve the values for the following properties with the VerticaLogProperties class:

• LogPath
• LogNamespace
• LogLevel

For example:

//get current log settings
string logpath = VerticaLogProperties.LogPath;
VerticaLogLevel loglevel = VerticaLogProperties.LogLevel;
string logns = VerticaLogProperties.LogNamespace;
Console.WriteLine("Current Log Settings:");
Console.WriteLine("Log Path: " + logpath);
Console.WriteLine("Log Level: " + loglevel);
Console.WriteLine("Log Namespace: " + logns);

Examples

This complete example shows how to get and set log properties:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Vertica.Data.VerticaClient;
namespace ConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            //configure connection properties
            VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
            builder.Host = "192.168.1.10";
            builder.Database = "VMart";
            builder.User = "dbadmin";
            
            //get current log settings
            string logpath = VerticaLogProperties.LogPath;
            VerticaLogLevel loglevel = VerticaLogProperties.LogLevel;
            string logns = VerticaLogProperties.LogNamespace;
            Console.WriteLine("\nOld Log Settings:");
            Console.WriteLine("Log Path: " + logpath);
            Console.WriteLine("Log Level: " + loglevel);
            Console.WriteLine("Log Namespace: " + logns);

            //set the log path
            string path = "C:\\log";
            VerticaLogProperties.SetLogPath(path);

            // set log level
            VerticaLogLevel level = VerticaLogLevel.Debug;
            VerticaLogProperties.SetLogLevel(level);

            //set namespace to log
            string lognamespace = "Vertica";
            VerticaLogProperties.SetLogNamespace(lognamespace);
            
            //open the connection
            VerticaConnection _conn = new VerticaConnection(builder.ToString());
            _conn.Open();

            //get new log settings
            logpath = VerticaLogProperties.LogPath;
            loglevel = VerticaLogProperties.LogLevel;
            logns = VerticaLogProperties.LogNamespace;
            Console.WriteLine("\nNew Log Settings:");
            Console.WriteLine("Log Path: " + logpath);
            Console.WriteLine("Log Level: " + loglevel);
            Console.WriteLine("Log Namespace: " + logns);

            //close the connection
            _conn.Close();
        }
    }
}

The example produces the following output:

Old Log Settings:
Log Path:
Log Level: None
Log Namespace:
New Log Settings:
Log Path: C:\log
Log Level: Debug
Log Namespace: Vertica

2.2.2 - Upgrading the client drivers

The Vertica client drivers are usually updated for each new release of the Vertica server. The client driver installation packages include the version number of the corresponding Vertica server release. Usually, the drivers are forward-compatible with the next release, so your client applications are still be able to connect using the older drivers after you upgrade to the next version of Vertica Analytics Platform server. See Client driver and server version compatibility for details on which client driver versions work with each version of Vertica server.

You should upgrade your clients as soon as possible after upgrading your server to take advantage of new features and to maintain maximum compatibility with the server.

To upgrade your drivers, follow the same procedure you used to install them in the first place. The new installation will overwrite the old. See the specific instructions for installing the drivers on your client platform for any special instructions regarding upgrades.

2.2.3 - Setting a client connection label

A client connection label identifies a connection to the database with a user-defined string. You can view the label for an existing session with GET_CLIENT_LABEL:

=> SELECT GET_CLIENT_LABEL();
      GET_CLIENT_LABEL
----------------------------
 my_client_connection_label
(1 row)

New connections

In JDBC, ODBC, and ADO.NET, you use each client driver's "Label" connection property to set the client label before connecting to the database. Setting the label before you connect ensures that the connection is associated with the label in all system and Data collector tables. Examples of these tables include SESSIONS and DC_SESSION_STARTS.

• JDBC connection properties
• ODBC DSN connection properties
• ADO.NET connection properties

You can also preemptively set the client label with vsql by using the --label option. For details, see -g --label

Existing connections

You can set a client connection label after you connect to a Vertica database with SET_CLIENT_LABEL:

=> SELECT SET_CLIENT_LABEL('py_data_load_application');
               SET_CLIENT_LABEL
----------------------------------------------
 client_label set to py_data_load_application
(1 row)

=> SELECT GET_CLIENT_LABEL();
     GET_CLIENT_LABEL
--------------------------
 py_data_load_application
(1 row)

Certain client drivers, like JDBC, have dedicated functions for setting the client connection label for existing connections. For details, see Setting and returning a client connection label.

2.2.4 - Using legacy drivers

The Vertica server supports connections from previous versions of the client drivers. For detailed information the compatibility between versions of the Vertica server and Vertica client, see Client driver and server version compatibility.

2.3 - Accessing Vertica

The following table shows which client drivers you have to set up to access Vertica with a supported programming language:

2.3.1 - C/C++

Vertica provides an Open Database Connectivity (ODBC) driver that allows applications to connect to the Vertica database. This driver can be used by custom-written client applications that use the ODBC API to interact with Vertica. ODBC is also used by many third-party applications to connect to Vertica, including business intelligence applications and extract, transform, and load (ETL) applications.

This section details the process for configuring the Vertica ODBC driver. It also explains how to use the ODBC API to connect to Vertica in your own client applications.

While client applications written in C, C++, Perl, PHP, etc. all use the ODBC client driver to connect to Vertica, this section only concerns C and C++ applications.

2.3.1.1 - ODBC architecture

The ODBC architecture has four layers:

• Client Application

  Is an application that opens a data source through a Data Source Name (DSN). It then sends requests to the data source, and receives the results of those requests. Requests are made in the form of calls to ODBC functions.

• Driver Manager

  Is a library on the client system that acts as an intermediary between a client application and one or more drivers. The driver manager:

  • Resolves the DSN provided by the client application.

  • Loads the driver required to access the specific database defined within the DSN.

  • Processes ODBC function calls from the client or passing them to the driver.

  • Retrieves results from the driver.

  • Unloads drivers when they are no longer needed.

  On Windows and macOS client systems, the driver manager is provided by the operating system. On Linux systems, you usually need to install a driver manager. See Client drivers support for a list of driver managers that can be used with Vertica on your client platform.

• Driver

  A library on the client system that provides access to a specific database. It translates requests into the format expected by the database, and translates results back into the format required by the client application.

• Database

  The database processes requests initiated at the client application and returns results.

2.3.1.2 - ODBC feature support

The ODBC driver for Vertica supports the most of the features defined in the Microsoft ODBC 3.5 specifications. The following features are not supported:

• Updatable result sets

• Backwards scrolling cursors

• Cursor attributes

• More than one open statement per connection. Simultaneously executing statements must each belong to a different connection. For example, you cannot execute a new statement while another statement has a result set open. To execute another statement with the same connection/session, wait for the current statement to finish executing and close its result set, then execute the new statement.

• Keysets

• Bookmarks

The Vertica ODBC driver accurately reports its capabilities. If you need to determine whether it complies with a specific feature, you should query the driver's capabilities directly using the SQLGetInfo() function.

2.3.1.3 - Vertica and ODBC data type translation

Most data types are transparently converted between Vertica and ODBC. This section explains several data types require special handling.

Notes

• The GEOMETRY and GEOGRAPHY data types are treated as LONG VARCHAR data by the ODBC driver.

• Vertica supports the standard interval data types supported by ODBC. See Interval Data Types in Microsoft's ODBC reference.

• Vertica version 9.0.0 introduced the UUID data type, including JDBC support for UUIDs. The Vertica ADO.NET, ODBC, and OLE DB clients added full support for UUIDs in version 9.0.1. Vertica maintains backwards compatibility with older supported client driver versions that do not support the UUID data type, as follows:

  When an older client...	Vertica...
  Queries tables with UUID columns	Translates the native UUID values to CHAR values.
  Inserts data into a UUID column	Converts the CHAR value sent by the client into a native UUID value.
  Queries a UUID column's metadata	Reports its data type as CHAR.

See also

• Data types
• Using LONG VARCHAR and LONG VARBINARY data types with ODBC
• Using GEOMETRY and GEOGRAPHY data types in ODBC

• SQL Data Types in the Microsoft ODBC reference documentation

2.3.1.4 - ODBC header file

The Vertica ODBC driver provides a C header file named verticaodbc.h that defines several useful constants that you can use in your applications. These constants let you access and alter settings specific to Vertica.

This file's location depends on your client operating system:

• /opt/vertica/include on Linux and UNIX systems.

• C:\Program Files (x86)\Vertica\ODBC\include on Windows systems.

The constants defined in this file are listed below.

SQLSetConnectAttr()
SQLGetConnectAttr()

SQLSetConnectAttr()
SQLSetStmtAttr()
SQLGetConnectAttr()
SQLGetStmtAttr()

SQLSetConnectAttr()
SQLGetConnectAttr()

2.3.1.5 - Canceling ODBC queries

You can cancel ODBC queries with the SQLCancel() function.

The following example:

1. Creates a table odbccanceltest
2. Queries odbccanceltest three times, canceling the third query
3. Runs another query on dual to show that the cancelation succeeded

// Example of calling SQLCancel() during SQLFetch()
#include <stdio.h>
#include <stdlib.h>

// Only needed for Windows clients
// #include <windows.h>

// SQL data types and ODBC API functions
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>

int main()
{
    SQLRETURN ret;   // Stores return value from ODBC API calls
    SQLHENV hdlEnv;  // Handle for the SQL environment object
    // Allocate an a SQL environment object
    ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hdlEnv);
	if(!SQL_SUCCEEDED(ret)) {
        printf("Could not allocate a handle.\n");
        exit(EXIT_FAILURE);
    } else {
        printf("Allocated an environment handle.\n");
    }
	
    // Set the ODBC version we are going to use to 3.
    ret = SQLSetEnvAttr(hdlEnv, SQL_ATTR_ODBC_VERSION,
            (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER);
	if(!SQL_SUCCEEDED(ret)) {
         printf("Could not set application version to ODBC 3.\n");
         exit(EXIT_FAILURE);
    } else {
         printf("Application version set to ODBC 3.\n");
    }

    // Allocate a database handle.
    SQLHDBC hdlDbc;
    ret = SQLAllocHandle(SQL_HANDLE_DBC, hdlEnv, &hdlDbc);
    assert(SQL_SUCCEEDED(ret));

	if(!SQL_SUCCEEDED(ret)) {
          printf("Could not allocate database handle.\n");
          exit(EXIT_FAILURE);
     } else {
          printf("Database handle allocated.\n");
     }

	// Connect to the database using
    // SQL Connect
    printf("Connecting to database.\n");
    const char *dsnName = "ExampleDB";
    const char* userID = "ExampleUser";
    const char* passwd = "password123";
    ret = SQLConnect(hdlDbc, (SQLCHAR*)dsnName,
        SQL_NTS,(SQLCHAR*)userID,SQL_NTS,
        (SQLCHAR*)passwd, SQL_NTS);
    if(!SQL_SUCCEEDED(ret)) {
        printf("Could not connect to database.\n");
        exit(EXIT_FAILURE);
    } else {
        printf("Connected to database.\n");
    }
	
    // Query the v_monitor.current_session table to find the name of the node we've connected to.

    // Set up a statement handle
    SQLHSTMT hdlStmt;
    SQLAllocHandle(SQL_HANDLE_STMT, hdlDbc, &hdlStmt);
    assert(SQL_SUCCEEDED(ret));

	// Create and populate the sampel table odbccanceltest to test SQLCancel()
	SQLExecDirect(hdlStmt, (SQLCHAR *)"CREATE TABLE odbccanceltest(id INTEGER, time TIMESTAMP)",
			  SQL_NTS);             
	SQLExecDirect(hdlStmt,
			(SQLCHAR *)"INSERT INTO odbccanceltest SELECT row_number() "
					   "OVER(), slice_time FROM(SELECT slice_time FROM( "
					   "SELECT  '2021-01-01'::timestamp s UNION ALL SELECT "
					   "'2022-01-01'::timestamp s) sq TIMESERIES "
					   "slice_time AS '1 second' OVER(ORDER BY s)) sq2;",
			SQL_NTS);
			
	ret = SQLPrepare(hdlStmt, (SQLCHAR *)"SELECT id, time FROM "
							"odbccanceltest LIMIT 5000000", SQL_NTS) ;
	
    if(!SQL_SUCCEEDED(ret)) {
        printf("Could not create prepared statement\n");
        SQLFreeHandle(SQL_HANDLE_STMT, hdlStmt);
        SQLFreeHandle(SQL_HANDLE_DBC, hdlDbc);
        SQLFreeHandle(SQL_HANDLE_ENV, hdlEnv);
        exit(EXIT_FAILURE);
    } else {
        printf("Ceated prepared statement.\n");
    }	
	
	SQLINTEGER d;
    size_t count = 0;
    while (SQLFetch(hdlStmt) == SQL_SUCCESS) {
        ++count;
        SQLGetData(hdlStmt, 1, SQL_C_SLONG, (SQLPOINTER)&d, sizeof(d), NULL);
		// Cancel the third query
        if (count > 3) {
            SQLCancel(hdlStmt);
            break;
        }
    }
	
	// Run a follow-up query  
    ret = SQLPrepare(hdlStmt, (SQLCHAR *)"SELECT 1 FROM dual", SQL_NTS);
    ret = SQLExecute(hdlStmt)

    if (!SQL_SUCCEEDED(ret)) {
        printf("Error in SQLExecute.\n");
        exit(EXIT_FAILURE);
    }

	while (SQLFetch(hdlStmt) == SQL_SUCCESS) {
		;
	}
	
	// Free handles
    printf("Disconnecting and freeing handles.\n");
    ret = SQLDisconnect( hdlDbc );
    if(!SQL_SUCCEEDED(ret)) {
        printf("Error disconnecting from database. Transaction might still be open.\n");
        exit(EXIT_FAILURE);
    }

    SQLFreeHandle(SQL_HANDLE_DBC, hdlDbc);
    SQLFreeHandle(SQL_HANDLE_ENV, hdlEnv);
    exit(EXIT_SUCCESS);
}

2.3.1.6 - Connecting to the database

The first step in any ODBC application is to connect to the database. When you create the connection to a data source using ODBC, you use the name of the DSN that contains the details of the driver to use, the database host, and other basic information about connecting to the data source.

There are 4 steps your application needs to take to connect to a database:

1. Call SQLAllocHandle() to allocate a handle for the ODBC environment. This handle is used to create connection objects and to set application-wide settings.

2. Use the environment handle to set the version of ODBC that your application wants to use. This ensures that the data source knows which API your application will use to interact with it.

3. Allocate a database connection handle by calling SQLAllocHandle(). This handle represents a connection to a specific data source.

4. Use the SQLConnect() or SQLDriverConnect() functions to open the connection to the database.

   Note

   If you specify a locale either in the connection string or in the DSN, the call to the connection function returns SQL_SUCCESS_WITH_INFO on a successful connection, with messages about the state of the locale.

When creating the connection to the database, use SQLConnect() when the only options you need to set at connection time is the username and password. Use SQLDriverConnect() when you want to change connection options, such as the locale.

The following example demonstrates connecting to a database using a DSN named ExampleDB. After it creates the connection successfully, this example simply closes it.

// Demonstrate connecting to Vertica using ODBC.
// Standard i/o library
#include <stdio.h>
#include <stdlib.h>
// Only needed for Windows clients
// #include <windows.h>
// SQL include files that define data types and ODBC API
// functions
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
int main()
{
    SQLRETURN ret;   // Stores return value from ODBC API calls
    SQLHENV hdlEnv;  // Handle for the SQL environment object
    // Allocate an a SQL environment object
    ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hdlEnv);
    if(!SQL_SUCCEEDED(ret)) {
        printf("Could not allocate a handle.\n");
        exit(EXIT_FAILURE);
    } else {
        printf("Allocated an environment handle.\n");
    }

    // Set the ODBC version we are going to use to
    // 3.
    ret = SQLSetEnvAttr(hdlEnv, SQL_ATTR_ODBC_VERSION,
            (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER);
    if(!SQL_SUCCEEDED(ret)) {
         printf("Could not set application version to ODBC 3.\n");
         exit(EXIT_FAILURE);
    } else {
         printf("Set application version to ODBC 3.\n");
    }
    // Allocate a database handle.
    SQLHDBC hdlDbc;
     ret = SQLAllocHandle(SQL_HANDLE_DBC, hdlEnv, &hdlDbc);
     if(!SQL_SUCCEEDED(ret)) {
          printf("Could not allocate database handle.\n");
          exit(EXIT_FAILURE);
     } else {
          printf("Allocated Database handle.\n");
     }
    // Connect to the database using
    // SQL Connect
    printf("Connecting to database.\n");
    const char *dsnName = "ExampleDB";
    const char* userID = "ExampleUser";
    const char* passwd = "password123";
    ret = SQLConnect(hdlDbc, (SQLCHAR*)dsnName,
        SQL_NTS,(SQLCHAR*)userID,SQL_NTS,
        (SQLCHAR*)passwd, SQL_NTS);
    if(!SQL_SUCCEEDED(ret)) {
        printf("Could not connect to database.\n");
        exit(EXIT_FAILURE);
    } else {
        printf("Connected to database.\n");
    }
    // We're connected. You can do real
    // work here

    // When done, free all of the handles to close them
    // in an orderly fashion.
    printf("Disconnecting and freeing handles.\n");
    ret = SQLDisconnect( hdlDbc );
    if(!SQL_SUCCEEDED(ret)) {
        printf("Error disconnecting from database. Transaction still open?\n");
        exit(EXIT_FAILURE);
    }

    SQLFreeHandle(SQL_HANDLE_DBC, hdlDbc);
    SQLFreeHandle(SQL_HANDLE_ENV, hdlEnv);
    exit(EXIT_SUCCESS);
}

Running the above code prints the following:

Allocated an environment handle.
Set application version to ODBC 3.
Allocated Database handle.
Connecting to database.
Connected to database.
Disconnecting and freeing handles.

See Setting the locale and encoding for ODBC sessions for an example of using SQLDriverConnect to connect to the database.

Notes

• If you use the DataDirect^® driver manager, you should always use the SQL_DRIVER_NOPROMPT value for the SQLDriverConnect function's DriverCompletion parameter (the final parameter in the function call) when connecting to Vertica. Vertica's ODBC driver on Linux and UNIX platforms does not contain a UI, and therefore cannot prompt users for a password.

• On Windows client platforms, the ODBC driver can prompt users for connection information. See Prompting windows users for missing connection properties for more information.

• If your database does not comply with your Vertica license agreement, your application receives a warning message in the return value of the SQLConnect() function. Always have your application examine this return value to see if it is SQL_SUCCESS_WITH_INFO. If it is, have your application extract and display the message to the user.

2.3.1.7 - Load balancing

Native connection load balancing

Native connection load balancing helps spread the overhead caused by client connections on the hosts in the Vertica database. Both the server and the client must enable native connection load balancing. If enabled by both, then when the client initially connects to a host in the database, the host picks a host to handle the client connection from a list of the currently up hosts in the database, and informs the client which host it has chosen.

If the initially-contacted host does not choose itself to handle the connection, the client disconnects, then opens a second connection to the host selected by the first host. The connection process to this second host proceeds as usual—if SSL is enabled, then SSL negotiations begin, otherwise the client begins the authentication process. See About native connection load balancing for details.

To enable native load balancing on your client, set the ConnectionLoadBalance connection parameter to true either in the DSN entry or in the connection string. The following example demonstrates connecting to the database several times with native connection load balancing enabled, and fetching the name of the node handling the connection from the V_MONITOR.CURRENT_SESSION system table.

// Demonstrate enabling native load connection balancing.
// Standard i/o library
#include <stdlib.h>
#include <iostream>
#include <assert.h>
// Only needed for Windows clients
// #include <windows.h>
// SQL include files that define data types and ODBC API
// functions
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>

using namespace std;
int main()
{
    SQLRETURN ret;   // Stores return value from ODBC API calls
    SQLHENV hdlEnv;  // Handle for the SQL environment object
    // Allocate an a SQL environment object
    ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hdlEnv);
    assert(SQL_SUCCEEDED(ret));

    // Set the ODBC version we are going to use to
    // 3.
    ret = SQLSetEnvAttr(hdlEnv, SQL_ATTR_ODBC_VERSION,
            (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER);
    assert(SQL_SUCCEEDED(ret));

    // Allocate a database handle.
    SQLHDBC hdlDbc;
    ret = SQLAllocHandle(SQL_HANDLE_DBC, hdlEnv, &hdlDbc);
    assert(SQL_SUCCEEDED(ret));

    // Connect four times. If load balancing is on, client should
    // connect to different nodes.
    for (int x=1; x <= 4; x++) {

        // Connect to the database using SQLDriverConnect. Set
        // ConnectionLoadBalance to 1 (true) to enable load
        // balancing.
        cout << endl << "Connection attempt #" << x << "... ";
        const char *connStr = "DSN=VMart;ConnectionLoadBalance=1;"
            "UID=ExampleUser;PWD=password123";


        ret = SQLDriverConnect(hdlDbc, NULL, (SQLCHAR*)connStr, SQL_NTS,
               NULL, 0, NULL, SQL_DRIVER_NOPROMPT );
        if(!SQL_SUCCEEDED(ret)) {
            cout << "failed. Exiting." << endl;
            exit(EXIT_FAILURE);
        } else {
            cout << "succeeded" << endl;
        }
        // We're connected. Query the v_monitor.current_session table to
        // find the name of the node we've connected to.

        // Set up a statement handle
        SQLHSTMT hdlStmt;
        SQLAllocHandle(SQL_HANDLE_STMT, hdlDbc, &hdlStmt);
        assert(SQL_SUCCEEDED(ret));

        ret = SQLExecDirect( hdlStmt, (SQLCHAR*)"SELECT node_name FROM "
            "V_MONITOR.CURRENT_SESSION;", SQL_NTS );

        if(SQL_SUCCEEDED(ret)) {
            // Bind varible to column in result set.
            SQLTCHAR node_name[256];
            ret = SQLBindCol(hdlStmt, 1, SQL_C_TCHAR, (SQLPOINTER)node_name,
                sizeof(node_name), NULL);
            while(SQL_SUCCEEDED(ret = SQLFetchScroll(hdlStmt, SQL_FETCH_NEXT,1))) {
                // Print the bound variables, which now contain the values from the
                // fetched row.
                cout << "Connected to node " << node_name << endl;
            }
        }
        // Free statement handle
        SQLFreeHandle(SQL_HANDLE_STMT,hdlStmt);
        cout << "Disconnecting." << endl;
        ret = SQLDisconnect( hdlDbc );
        assert(SQL_SUCCEEDED(ret));
    }
    // When done, free all of the handles to close them
    // in an orderly fashion.
    cout << endl << "Freeing handles..." << endl;
    SQLFreeHandle(SQL_HANDLE_DBC, hdlDbc);
    SQLFreeHandle(SQL_HANDLE_ENV, hdlEnv);
    cout << "Done!" << endl;
    exit(EXIT_SUCCESS);
}