pyODBC on mac

Guillaume

Hi,

It seems I cannot connect to Vertica via pyodbc on a mac. My exaxt same setup (modulo the paths) works fine on linux, though. I had no luck looking in these forums or anywhere else. Would anybody know what it going on ?

• Vertica client 6.1.2 or 6.1.3, installed as my user or root
• pyobc for all versions 3+

I create a virtualenv, pip install odbc.

my relevant files are:
/etc/odbc.ini:

[VerticaDWHSTG]
Description = VerticaDSN ODBC driver for DWH (staging)
Driver = Vertica
Database = stgdwh
Servername = vertica.stg
UserName = stguser
Password = ****
Port = 5433
ConnSettings =
Locale = en_US@collation=binary

[Driver]
ErrorMessagesPath = /opt/vertica/lib/
ODBCInstLib = /usr/local/Cellar/unixodbc/2.3.1/lib/libodbcinst.dylib
DriverManagerEncoding=UTF-16

/etc/odbcinst.ini

[Vertica]
Description=Vertica driver
Driver=/opt/vertica/lib/libverticaodbc.dylib
Driver64=/opt/vertica/lib/libverticaodbc.dylib
Threading=1

From then on, I run:
VERTICAINI=/etc/odbc.ini python -c 'import pyodbc; pyodbc.connect("DSN= VerticaDWHSTG ")'
raceback (most recent call last):  File "", line 1, in pyodbc.Error: ('HY000', '[]  (10360) (SQLDriverConnect)')

• If I give a wrong DSN, which does not exist, I have another error message (Data source name not found...). which means that the right odbc.ini file is found.
• If I do not set VERTICAINI, Queries do run but the output is not right, for instance, where all values are numbers, dates or string:

python -c 'import pyodbc; c=pyodbc.connect("DSN=VerticaDWHSTG"); print c.cursor().execute("SELECT * from meta.spil_users").fetchone()'

(u'g\x00u\x00i\x00l\x00l\x00a\x00u\x00m\x00e\x00.\x00r\x00o\x00g\x00e\x00r\x00', None, u's\x00p\x00i\x00l\x00', u'Z\x00e\x00B\x00o\x00s\x00s\x00', datetime.datetime(2013, 10, 15, 13, 57, 57, 100737), datetime.datetime(2013, 10, 15, 13, 57, 57, 100737), u'g\x00u\x00i\x00l\x00l\x00a\x00u\x00m\x00e\x00.\x00r\x00o\x00g\x00e\x00r\x00', u'd\x00e\x00f\x00a\x00u\x00l\x00t\x00', u'g\x00e\x00n\x00e\x00r\x00a\x00l\x00')

Is there a way around?
Thanks,

Daniel_Leybovic

1. Check ODBC encoding (UTF-32 for Mac )
You have:
DriverManagerEncoding=UTF-16
2. set locale for ODBC session
3. pyodbc 2.1.x has bug with encoding. Use in version 3.0.6 and above.
I tested this version (pyodbc 3.0.6).
Except a bug with PROFILE no issues found. 

https://my.vertica.com/docs/6.1.x/HTML/index.htm#13632.htm
https://my.vertica.com/docs/6.1.x/HTML/index.htm#16837.htm
https://my.vertica.com/docs/6.1.x/HTML/index.htm#17028.htm

If you can, install pyodbc from PIP and not distributive package.

Guillaume

Thanks a lot for you answer.

- I missed the DriverManagerEncoding, although setting it to UTF-32 did not make a difference.
- The Locale is properly set I believe (it works under linux, and does match the output of the locale command on my Mac)
- I am using pyodbc 3.0.7, from pip

Sadly this does not change  thing, I still have the same issues. I carry on looking.

Cheers!

Shinji_Suzuki

In the last case, the query seems to run fine. The output is what is expected when printing a tuple/list. Elements are printed using repr() rather than str().

Guillaume

You are completely right indeed.

The weird stuff then is that VERTICAINI does not seem to be needed on MacOs. I do have exactly the same output with or without it (as long as in the VERTICAINI file I am stipulating UTF-32 encoding. With VERTICAINI and UTF-16 the connection fails.).

Thanks!

kesten_broughto

UTF-32 for mac?
locale
LANG="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_CTYPE="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_ALL=

And XCode's default setting is utf-8

I got invalid locale setting errors when i tried utf-32

Can you point to any documentation about utf-32 on mac?

kesten_broughto

indeed, utf-32 was the key to getting past the HY000 error.  It seems that unixodbc works in utf-8 land whereas iODBC is utf-32. 

kobymeir

@Guillaume Where you able to resolve the issue ? and how?

Guillaume

My 'fix' is sadly to always use pyodbc from a Linux box... I had no luck with Mac.

kobymeir

Thanks

kesten_broughto

I did finally get pyodbc working on my mac.  One of the problems troubleshooting is that odbc driver seems to look in lots of places for the .ini file.  In the end, i found three places and was having problems because i had only made changes to two of the files.  UTF32 was definitely important too.

Locations:  /Library/ODBC, ~/ODBC, /usr/local/etc

ls /usr/local/etc

odbc.ini -> /Users/kbroughton/ODBC/odbc.ini

odbcinst.ini -> /Users/kbroughton/ODBC/odbcinst.ini

ls /Library/ODBC

odbc.ini -> /usr/local/etc/odbc.ini

odbcinst.ini -> /usr/local/etc/odbcinst.ini

Example entry in odbc.ini

[vertica_vagrant]

Driver = Vertica

Server=192.168.10.11

Servername=192.168.10.11

Port=5433

Description = vertica_vagrant

UID=dbadmin

PWD=dbamin

Locale=en_US

Database=dev

Debug=1

Host=192.168.10.11

DriverManagerEncoding=UTF-32

example odbcinst.ini

[Vertica]

Description = HP Vertica ODBC Driver

Driver = /opt/vertica/lib/libverticaodbc.dylib

Port=5433

Database=metrics

#Host=10.0.9.108

Host=192.168.10.11

DriverManagerEncoding=UTF-32

Locale=en_US

cat ~/ODBC/vertica.ini

[Driver]

DriverManagerEncoding=UTF-32

ODBCInstLib=/usr/local/lib/libodbcinst.dylib

ErrorMessagesPath=/opt/vertica/lib

LogLevel=4

LogPath=/opt/vertica/logs

Port=5433

Datbase=metrics

Host=10.0.9.108

make sure your LogPath and ErrorMessagePath exist.