pydbc via ODBC connection return null character between each letter in string value

I have the weirdest problem. I am using pyodbc 4.0.30 version via ODBC connection to a Vertica database.

Using also python version 2.7 on a MAC.

I am inserting string value for example: 'daniel', and when doing select I am getting a value with null characters between each letter.

This is my odbc.ini file

[ODBC Data Sources]
vertica = Vertica

[vertica]
Driver                  = /Library/Vertica/ODBC/lib/libverticaodbc.dylib
Database                = qa
ServerName              = serverName
Port                    = 5433
UID                     = dbadmin
PWD                     = pass
DriverStringConversions = NONE
ColumnsAsChar = true

[ODBC]
Trace         = 0
TraceAutoStop = 0
TraceFile     = /Users/daniel/sql.log
TraceDLL      =

the return value of the string suppose to be 'daniel' and I am getting the following value in ascii

[49, 0, 100, 0, 115, 0, 102, 0, 115, 0, 100, 0, 102, 0]

instead of

[49,100,115,102,115,100,102]

Tried all the DriverStringConversions config, and nothing change this. I know this is a long shot, but any ideas why this is happening?

Solution

That looks like an encoding issue. You're seeming to get UTF-16LE instead of UTF-8. In order to get my Vertica strings right back and forth, I have this configuration before I call any programs using ODBC against Vertica:

export ODBCSTACK=$HOME/odbcstack
export ODBCHOME=$ODBCSTACK/uodbc
export ODBCSYSINI=$ODBCHOME/etc
export ODBCINI=$ODBCSYSINI/odbc.ini
export VERTICAINI=$ODBCSYSINI/vertica.ini
# plus a few LD_LIBRARY_PATH and PATH settings

And my vertica.ini looks like this:

[Driver]
Locale = en_US
ODBCInstLib = /home/dbadmin/odbcstack/uodbc/lib/libodbcinst.so
ErrorMessagesPath = /opt/vertica
DriverManagerEncoding = UTF-16
LogPath = /tmp
LogNameSpace =
LogLevel = 0

Without vertica.ini, I don't get any sensible error messages from Vertica, and the character encoding can play tricks on me.

Have a go at that!