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

Answer 1

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);