does anyone know the format of an odbc connection string for vertica?
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
Ramy6 Answers
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);
Jon G
AmitI 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
Al WYou 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=sekretLinux/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.)
Danny WhittHave 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....
thepip3rWhen 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.
geoffrobinsonOk, 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.
User contributions licensed under CC BY-SA 3.0