7

I've read the iODBC documentation regarding the odbc.ini, and the Vertica documentation. I also saw a question with the same error, but can't get the connection string to work as suggested in the previous question's answer.

What I currently have:

/etc/odbcinst.ini

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

/etc/odbc.ini

[ODBC Data Sources]
VerticaDB1 = db1 database on HP Vertica

[VerticaDB1]
Description = db1 database on HP Vertica
Driver = HPVertica
Database = db1
Servername = 10.0.0.67
UID = dbadmin
PWD = 
Port = 5433
Locale = en_GB

[ODBC]
Threading = 1

~/.odbc.ini

[DEFAULT]

Driver = VerticaDB1

Testing with isql

[root@ip-10-0-0-67 /]# echo "select 1;" | isql -v VerticaDB1
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select 1;
+---------------------+
| ?column?            |
+---------------------+
| 1                   |
+---------------------+
SQLRowCount returns 1
1 rows fetched

odbcinst -j outputs:

unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

In Python using "VerticaDB1":

>>> import pyodbc
>>> conn = pyodbc.connect("DRIVER={VerticaDB1};UID={dbadmin};PWD={...}")
Traceback (most recent call last):
  File "", line 1, in 
pyodbc.Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not 
found, and no default driver specified (0) (SQLDriverConnect)')

In Python using "HPVertica":

>>> import pyodbc
>>> conn = pyodbc.connect("DRIVER={HPVertica};UID={dbadmin};PWD={...}")
Traceback (most recent call last):
  File "", line 1, in 
pyodbc.Error: ('HY000', '[HY000] [unixODBC][Vertica][ODBC] (10430) Not enough 
information provided to establish a connection to the data source and specified 
to not prompt for more information. (10430) (SQLDriverConnect)')
Community
  • 1
  • 1
Kermit
  • 33,827
  • 13
  • 85
  • 121

1 Answers1

12

Try connecting using DSN: 

conn = pyodbc.connect("DSN=VerticaDB1;UID=dbadmin;PWD=mypassword")

Alternatively, you can connect using DRIVER, but you need to supply more information, like which database, host, and port: 

conn = pyodbc.connect("DRIVER=HPVertica;SERVER=10.0.0.67;DATABASE=db1;PORT=5433;UID=dbadmin;PWD=mypassword")
woot
  • 7,406
  • 2
  • 36
  • 55
  • Using the `DSN` results in the same *Data source name not found...* error. Using `DRIVER` works :) – Kermit Jun 05 '14 at 00:38
  • 3
    On a side note, pyodbc accepts kwargs as well as a string. eg. instead of the first example, you could type: `conn = pyodbc.connect(DSN="VerticaDB1", UID="dbadmin", PWD="mypassword")`. I find this more readable, but the 2 are exactly equivalent. – Guillaume Jun 05 '14 at 05:31
  • This post references some [common odbc/vertica error messages and root causes](http://thisdwhguy.com/2014/04/29/vertica-odbc-error-messages-and-solutions), it might help as I find weird that using `DRIVER` does not work. – Guillaume Jun 05 '14 at 05:33
  • @Guillaume the link is dead unfortunately. – alecxe Aug 10 '16 at 23:04
  • 2
    @alecxe I cannot update the old comment but here is the live link: https://thisdataguy.com/2014/04/29/vertica-odbc-error-messages-and-solutions/ – Guillaume Aug 12 '16 at 05:04