Does anyone know the odbc connection string format for vertica?

Question

I am using the following:

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

I get this error and I just wanted to make sure my connection string was cool before I check for other possible problems.

Mistake:

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

UPDATE: For now, I'm just using the system data source name in Windows Vista that I can use. But I still would like to know if there is an odbc connection string, so I do not need to install this on every machine that will connect to Vertica DB in this way.

Well, I tried the postgresql connection string, which 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 get 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

I also did not see any way to use ODBC without DSN. Here are 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 

Answer 2

The accepted answer describes how to connect using the Vertica ODBC driver using System DSN . To directly configure the connection with the driver, you can only connect using the connection string. The following connection string template has been tested with 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 do this in .NET like me, you can use this to format the connection string from the required 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); 

Answer 3

You can connect to a Vertica ODBC data source without configuring / specifying a data source name (DSN) using a connection string, which includes the following:

• Window:

  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 one that matches your environment. (Note: the name=value pairs in the connection string seem to be case sensitive.)

Answer 4

Have you looked at http://www.connectionstrings.com/ ? It specifically does not contain a list of Vertica DB, but there are dozens of other types of databases that can be quite similar to vertica that they translate well.

Answer 5

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

Basically, everything that is not under the hood is based on PostgreSQL, especially the syntax and functions of SQL. So I would like to go to the above http://www.connectionstrings.com and see how PostgreSQL does it.

Answer 6

Well, I am looking through the Vertica documentation, and I see no way to connect to ODBC without creating a DSN. JDBC seems to be a different matter. If there is a way to do this, I do not see it.

The problem (it is assumed that you have a driver) is that the system does not know that your connection string should be processed by the Vertica driver. The DSN already indicates this, so why does it work (my educated guess).

This is the example they give for JDBC:

"JDBC: Vertica: // server: port / db user = username and amplifier, password = password & = SSL true"

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

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

DSN Parameters

The parameters in the following tables are common to all user and system DSN entries. The above examples 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.