The Vertica Database Forums

Posted: **Wed May 07, 2014 10:58 pm**

I am trying to figure out how to connect to Vertica with pyODBC and am having some problems with the returned rows just being garbage.

Using:

Vertica 7.0
Python 2.4.3
pyODBC 2.1.6

From vsql:

Code: Select all

[dbadmin@vertica1 dan]$ vsql -c "select node_name, node_state from v_internal.vs_node_states"
    node_name    | node_state 
-----------------+------------
 v_test_node0004 | UP
 v_test_node0005 | UP
 v_test_node0006 | UP

From a short python program:

Code: Select all

#!/usr/bin/env python

import pyodbc

cnxn = pyodbc.connect("DSN=vertica",ansi=True)
cursor = cnxn.cursor()
cursor.execute("select * from test")

rows = cursor.fetchall()
for row in rows:
    print row

[dbadmin@vertica1 dan]$ ./t
(u'\x00\U00650074\U00740073\U006e005f\U0064006f\U00300065\U00300030', u'\x00')
(u'\x00\U00650074\U00740073\U006e005f\U0064006f\U00300065\U00300030', u'\x00')
(u'\x00\U00650074\U00740073\U006e005f\U0064006f\U00300065\U00300030', u'\x00')

Is there any way to make the output "usable"? Some setting I have not set?

my odbc.ini file:

[dbadmin@vertica1 dan]$ cat /etc/odbc.ini
[vertica]
Driver = /opt/vertica/lib64/libverticaodbc.so
Servername = 10.100.xxxx.xxx
Database = Test
Port = 5433
UserName = dbadmin
Password = xxxxxxx

my vertica.ini file

[dbadmin@vertica1 dan]$ cat /etc/vertica.ini
[Driver]
DriverManagerEncoding=UTF-16
ODBCInstLib=/usr/lib64/libodbcinst.so
ErrorMessagesPath=/opt/vertica/lib64
LogLevel=4
LogPath=/tmp

Posted: **Wed May 07, 2014 11:34 pm**

Are you running pyodbc on a mac by any chance?

I had the same problem with
vertica 6.1.2
pyodbc 3.0.7
unixodbc 2.3.2 (not the iODBC that comes with mac, couldn't get that to work)
macOSX maverics

Output which should have been numbers looked like
(u'\U00103030\udc8d\udc35\uddcc\ude2d\udc8b\ud

However it was fine using vsql or dbvisualizer.

I applied the patch by danerick at
https://code.google.com/p/pyodbc/issues ... ort=&id=78

Then cd to the location of setup.py in pyodbc3.0.7 folder.
# re-compile with the fixed getdata.cpp
python setup.py install
open a new shell
voila. Data as numbers.

Posted: **Thu May 08, 2014 10:50 am**

It is on a RHEL 5.6 server. I had just downloaded pyODBC yesterday. I had downloaded pyODBC 2.1.6 because that is what the Vertica docs says is the version supported.

I will give the patched version a try later today

Posted: **Fri Jun 06, 2014 6:15 pm**

Late followup:

Installed pyodbc 3.0.7 and the problem is no longer present.

The Vertica Database Forums

fetchall returning hex characters?

fetchall returning hex characters?

Re: fetchall returning hex characters?

Re: fetchall returning hex characters?

Re: fetchall returning hex characters?