does anyone know the format of an odbc connection string for vertica?

Question

I'm using the following:

DRIVER={Vertica ODBC Driver 4.1};
SERVER=lnxtabdb01.xxxx.com;
PORT=5433;
DATABASE=vertica;
USER=dbadmin;
PASSWORD=vertica;
OPTION=3;

i'm getting this error and I just wanted to make sure that my connection string was cool before I check other possible issues.

error:

EnvironmentError: System.Data.Odbc.OdbcException (0x80131937): ERROR [28000] FATAL: no Vertica user name specified in startup packet

UPDATE: For now i'm just using a System Data Source Name in Windows Vista that I can use. But i'd still like to know if there's an odbc connection string so that i don't have to set that up on every machine that will be connecting to the Vertica DB in this fashion.

well, I tried a postgresql connection string that looks like this:

Host=lnxtabdb01.xxxx.com;
Port=5433;
Database=vertica;
User ID=dbadmin;
Password=vertica;
Pooling=true;
OPTION=3;
Min Pool Size=0;
Max Pool Size=100;
Connection Lifetime=0;

now i'm getting this:

EnvironmentError: System.Data.Odbc.OdbcException (0x80131937): ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Yoshieyoshiko

Answer 1

I too have not seen any way to use ODBC without a DSN. Here's my DSN settings for my linux workstation.

[VerticaDSN]
Description = VerticaDSN ODBC driver
Driver = /opt/vertica/lib64//libverticaodbc_unixodbc.so
Database = Retail
Servername = localhost
UserName = vertica
Password =
Port = 5433

Magnify

see my answer further down for how to do this with a connection string, rather than a DSN – Nahuatl

Answer 2

The accepted answer describes a way to connect with the Vertica ODBC driver using a System DSN. It is possible to connect using just a connection string to directly configure the connection against the driver. The following connection string pattern has been tested against the Vertica ODBC Client Driver v6.1.2:

Driver=Vertica;Server=MyVerticaServer;Port=5433;Database=MyVerticaDB;UID=foo;PWD=bar

Port is optional:

Driver=Vertica;Server=MyVerticaServer;Database=MyVerticaDB;UID=foo;PWD=bar

Or, if you're doing this in .NET as I am, you can use this to format up the connection string from the necessary parameters:

var connectionString = string.Format(
            "Driver=Vertica;Server={0};{1}Database={2};UID={3};PWD={4}",
            server,
            port == null ? string.Empty : string.Format("Port={0};", port),
            database,
            username,
            password);

Nahuatl

This is probably the right answer but I no longer work with vertica so i can't verify that it works. Maybe someone else can verify this? – Yoshieyoshiko

Hi @Ramy, I've tested this against the mentioned version of the Vertica ODBC driver, in fact it's running regularly as part of an integration build against a Vertica test environment. – Nahuatl

I'm honestly not sure how this is supposed to work (stackoverflow-wise). The answer I accepted was the right answer at the time. I don't know if maybe the vertica drive changed at some point after I accepted the original answer. – Yoshieyoshiko

This worked for me too. Just to be extra clear for noobs like me, my driver portion looked like this: Driver=/usr/local/opt/vertica-mac64/lib64/libverticaodbc.dylib;. Tweak, as needed, of course. – Winnow

Thanks @Chords, this cleared up an issue I was having! – Catalepsy

This works great and should be the accepted answer! Thanks! – Tile

Would this lead to a faster query time then going through odbc system dsn? – Rathe

Answer 3

You can connect to a Vertica ODBC data source without configuring/specifying a Data Source Name (DSN) using a connection string that includes the following:

• Windows:

  Driver=Vertica ODBC Driver 4.1;Servername=hostname;Port=5433;Database=vertica;UserName=dbadmin;Password=sekret

• Linux/Unix

  Driver=Vertica;Servername=hostname;Port=5433;Database=vertica;UserName=dbadmin;Password=sekret

Replace each italicized value with those appropriate to your environment. (Note: the name=value pairs in the connection string seem to be case-sensitive.)

Phobos

Answer 4

Have you looked at http://www.connectionstrings.com/? It doesn't specifically have a Vertica DB listed but there dozens of other database types that might be similar enough to vertica that they will translate well....

Dimphia

This is more of a comment than an answer. – Semiaquatic

Answer 5

When using ODBC I was always using Windows and setting up a DSN. However, my only suggestion, and this would be a general suggestion for many different types of problems in Vertica, would be to try the ODBC format for PostgreSQL.

Mostly everything that isn't under-the-hood is based on PostgreSQL, especially SQL syntax and functions. So I would go to the aforementioned http://www.connectionstrings.com and look up however PostgreSQL does it.

Corrugate

Another question is your database instance called "vertica" or were you trying to specify the type of database. – Corrugate

yeah, DB instance is called "vertica" – Yoshieyoshiko

Just another quick question. Make sure you have the latest vertica driver is installed on your machine. I'm looking through some documentation to see if I see anything. – Corrugate

Answer 6

Ok, I'm searching through the Vertica documentation and I'm not seeing any way of connecting with ODBC without creating a DSN. JDBC appears to be a different matter. If there is a way to do it, I'm not seeing it.

The problem appears to be (assuming you have the driver) that the system doesn't know that your connection string should be handled by the Vertica driver. A DSN has that specified already, so that's why that works (my educated guess).

This is the example they give for JDBC:

"jdbc:vertica://server:port/db?user=username&password=password&ssl=true"

The JDBC connection string seems to let the code know that it should be using Vertica.

Let me post part of the pertinent document (forgive the formatting) regarding ODBC:

DSN Parameters

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

To edit DSN parameters:

* UNIX and Linux users can edit the odbc.ini file. (See Creating an ODBC DSN for Linux and Solaris Clients.) The location of this file is specific to the driver manager.
* Windows users can edit the DSN parameters directly by opening the DSN entry in the Windows registry (for example, at HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\vmartdb). However, the Vertica-preferred method is to follow the steps in Creating an ODBC DSN for Windows Clients.
* Parameters can be set while making the connection using SQLDriverConnect().

  sqlRet = SQLDriverConnect(sql_hDBC, 0, (SQLCHAR*)"DSN=VerticaSQL;BinaryDataTransfer=1",

  SQL_NTS, szDNS, 1024,&nSize, SQL_DRIVER_NOPROMPT);



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

  For Example:

  sqlRet = SQLDriverConnect(sql_hDBC, 0, (SQLCHAR*)"DSN=VerticaSQL;BinaryDataTransfer=1;ConnSettings=

  set+search_path+to+a,b,c%3 Bset+locale=ch;SSLMode=prefer", SQL_NTS,

  szDNS, 1024,&nSize, SQL_DRIVER_NOPROMPT);



* Parameters can also be set and retrieved after the connection has been made using SQLConnect(). Parameters can be set and retrieved using SQLSetConnectAttr(),SQLSetStmtAttr(), SQLGetConnectAttr() and SQLGetStmtAttr() API calls. 

Corrugate