#ceodbc

[tsworman]

ceODBC on 64 bit Linux - fixing stack smashing

  My original attempts to use ceODBC on 64 bit Linux were thwarted. I was able to install it okay by using pip, but when I went to actually execute a query, the module would fail claiming a “stack smash” error. This occured even with a simple select. 

And then I went down the rabbit hole. I never really wanted to learn how to debug Python libraries, but now I know where to start. I’ll spare you the details though. If you have the same error let’s check a few things first.

I’m using unixODBC. You should check if yours was built for 64 or 32 bit data types. To do this run “odbcinst -j” if the SQLULEN and SQLLEN sizes report 4, then you have a 32 bit unixODBC install and this document won’t help you. If your unixODBC install reports 8 here, you have a 64 bit install and the issue is ceODBC.

If you built ceODBC using pip or even with setup.py the default install includes a flag in the build called BUILD_LEGACY_64_BIT_MODE if you are not on the windows 32-bit platform. This is the issue. unixODBC and your 64 bit odbc drivers are returning types longer than the variables that ceODBC was being built to handle... hence the stack smash.

How do I fix this? Download the ceODBC sources, untar the file (tar -xvzf ceODBC*), change to the base directory and find the setup.py file. In my version 2.01 the line we want is located at Line 160. It reads: “defineMacros.append(("BUILD_LEGACY_64_BIT_MODE", None))”

Comment this out by adding a # in front of it. Now run your install of ceODBC

In my case I wanted it for Python3.2 so my command was... “sudo python3.2 setup.py install”

ceODBC will be built and installed with 64 bit types. Now when you import ceODBC into your software, you’ll be able to execute a SQL statement that returns data without smashing your stack. *HULK SMASH!*

[tsworman]

UnixODBC, Python3, Netezza, pyodbc/ceODBC = Hell

 Simple goal for the day.. I wanted to move a Python 3.2 application from Windows to Linux. It used ODBC via ceODBC on Windows.

ceODBC only officially works on Python 3.2 at the moment (4/26/2015). It seems to be okay on 3.4 once I had all my ducks in a row.

Bonus points here as I installed for 64 bit linux

Process: Install unixodbc (apt-get install unixodbc) Install Netezza Linux Drivers. First install the 32 bit drivers (run ./unpack in the lib dir), then install the 64 bit drivers. The 32 bit drivers come with the applications you really want to use, the 64 bit driver package just installs a 64bit ODBC driver and it will sym link that over the 32bit version. Once this is installed try to run nzsql from the /usr/local/nz/bin directory. You’ll probably be missing a few dependencies but I was able to find them with apt-get.

Setup your ODBC connection:

odbcinst -j

this will list out where unixodbc is looking for config files. Netezza includes sample config files but they have issues and don’t actually work.

We’ll setup some path variables now and then run the command again. Your setup should look something like the following:

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/nz/lib export PATH=$PATH:/usr/local/nz/bin export ODBCINI=$HOME/.odbc.ini export NZ_ODBC_INI_PATH=$HOME

What do these do? LD Library path is going to hopefully point anything linking to the Netezza drivers to the right libraries. The 64 bit library is soft linked to that folder. PATH puts the fancy new NZ tools you just installed in your path when you try to run things like NZSQL. ODBCINI, you’ll notice that I hardcode the file at the end. If you don’t then the ODBCManageDataSources tools will not work. You’ll notice that I set NZ_ODBC_INI_PATH to home. This is where the Netezza drivers will look for the odbc.ini file. Even if you use a custom .odbc.ini file, Netezza won’t look at it even when unixODBC is. You need a soft link from your .odbc.ini to a plain odbc.ini in the directory where you set NZ_ODBC_INI_PATH to. With your environment straightened out, you can proceed to configure these files. For the life of me I can’t get unixodbc to look at a local odbcinsta.ini to find the drivers. Documentation said it should work, but it doesn’t for me. I ended up editing the /etc/odbcinst.ini file.

Run odbcinst -j and see if you get something similar.

unixODBC 2.3.1 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini FILE DATA SOURCES..: /etc/ODBCDataSources USER DATA SOURCES..: /home/tworman/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8 Edit your /etc/odbcinst.ini file and populate it like this:

[ODBC Drivers] NetezzaSQL = Installed[NetezzaSQL] Driver           = /usr/local/nz/lib64/libnzodbc.so Setup            = /usr/local/nz/lib64/libnzodbc.so APILevel         = 1 ConnectFunctions = YYN Description      = Netezza ODBC driver DriverODBCVer    = 03.51 DebugLogging     = false LogPath          = /tmp UnicodeTranslationOption = utf16 CharacterTranslationOption = all PreFetch         = 25600 Socket           = 16384 You’ll notice a few differences here from the provided Netezza file. My libraries are pointed to the lib64 version. I did this so that I knew that I’m using the 64 bit drivers and didn’t have to question myself. This is optional. The next major change is the Unicode Translation Option. All the documentation I found said that this should be UTF8. I had some errors connecting with the isql tool with this setting (online solutions suggested changing TranslationDLL in connection configuration, this fixed nothing) and if you do this, then you will have major issues with Python. Set this value to UTF16. 

If you find that ceODBC crashes on connect and provides you with an error about “double free or corruption”, you need UTF16. If pyodbc generates errors when using executemany that often state 0 parameters provided in SQL but X parameters provided, you need UTF16. This is caused becase pyodbc isn’t seeing the ? token in your statements. If you use execute and directly try an insert, then you will receive an error similar to this: pyodbc.ProgrammingError: ('42000', '[42000] ERROR:  \'S\'\nerror    ^ found "I" (at char 1) expecting a keyword (27) (SQLExecDirectW)') See bug report here: https://code.google.com/p/pyodbc/issues/detail?id=381 

Unixodbc is going to use the file located in /etc/odbcinst.ini for configuration from it’s GUI and tools. The actual file used at runtime by netezza is the version located in your home directory as .odbcinst.ini. This is extremely inconsistent behavior and annoying to track down.

With this in place, configure your data source. User data sources can be in our home directory based on how we set the environment up. We put them in .odbc.ini (it’s softlinked to odbc.ini in the same folder, otherwise NZ drivers won’t use it)

My connection looks like this:

[ODBC Data Sources] NZSQL = NetezzaSQL

[NZSQL] Driver                = /usr/local/nz/lib64/libnzodbc.so Description           = NetezzaSQL ODBC Servername            = Port                  = 5480 Database              = Username              = Password              =

;Only Select SQL statements will be allowed if this field is checked.                                                                                         ReadOnly              = false FastSelect            = false ShowSystemTables      = false ;When set to true, use old (2.5) functionality for returning schemas in SQLTables.                                                                           LegacySQLTables       = false LoginTimeout          = 0 ;Specifies query timeout in seconds.                                                                                                                         QueryTimeout          = 0 ;Specifies date format as follows -                                                                                                                           ; 1: YMD                                                                                                                                                     ; 2: MDY                                                                                                                                                     ; 3: DMY                                                                                                                                                     DateFormat            = 1 ;When selected, driver treats SQL_C_NUMERIC buffers as SQL_C_CHAR buffers.                                                                                   NumericAsChar         = false ;Return SQL_BIT as "1"/"0" when char.                

SQLBitOneZero         = false ;When enabled, replaces all occurences of CRLF, CR and LF with a single space.                                                                               StripCRLF             = false ;Specifies level of security on the system                                                                                                                   securityLevel         = preferredUnSecured ;Name of the Root CA certificate.                                                                                                                             caCertFile            = ;Specifies maximum errors allowed. 0 value means no limit on loadMaxErrors value.                                                                             loadMaxErrors         = 1 ;If value is true, it rounds the data of numeric columns whose precision exceeds the precision defined.                                                       ;loadRoundFraction     = 0                                                                                                                                  

;Possible fixes for translation error that didn't work. UTF16 was the solution.                                                                               ;TranslationDLL=                                                                                                      ;TranslationName=         ;TranslationOption=

[ODBC] IANAAppCodePage=4 InstallDir=/opt/odbc32v51 Trace=0 TraceDll=/opt/odbc32v51/lib/odbctrac.so TraceFile=odbctrace.out UseCursorLib=0

Python - I spent a good 5-6 hours here. Unicode is a huge issue with Python. It’s slightly better in 3.3 but the documentation is horrible and figuring out what character set you are currently working with isn’t as intuitive as it was in Python2.x. I seem to run into problems every time I do database work. For this install it did not matter if my version of Python was compiled with UCS2 or UCS4. I tried this with both versions and got the same result. The present default in the latest Ubuntu is a UCS4 build. 

Pip installed ceODBC and pyodbc. With UTF16 set on the connection, both should be fine on Python 3.2 or 3.4. pyodbc had errors on import complaining that it was unable to import decimal or cdecimal. Manually importing decimal didn’t fix it and resulted in more errors. I used pip to install cdecimal and then no import was needed and things just worked.