My favorites | Sign in
Project Home Downloads Wiki Issues Source
New issue   Search
  Advanced search   Search tips   Subscriptions
4 people starred this issue and may be notified of changes. Back to list
Status:  Complete
Owner:  ----
Closed:  Dec 2011

Sign in to add a comment
Reported by, Sep 18, 2011
FreeTDS 0.91 (error does not occur with FreeTDS 0.82)
Mac OSX 10.6
fresh libiconv 1.14 install
pyodbc 2.1.11
SQL Server 2008 (ignore that the DSN says '2005')
FreeTDS is configured with client charset=UTF-8, TDS version 8.0 (occurs with 7.0 as well)

As noted in #210, the test is now extremely simple:

    import pyodbc

    conn = pyodbc.connect("dsn=ms_2005;UID=scott;PWD=tiger")

    cursor = conn.cursor()
    cursor.execute("select cast(null as nvarchar)")
    print cursor.fetchall()


    classics-MacBook-Pro:sqlalchemy classic$ python 
    Traceback (most recent call last):
      File "", line 11, in <module>
        print cursor.fetchall()

Attached are the full ODBC log and FreeTDS log.   The error does not occur from a Linux backend, so there may be some library issues here, but I'd really appreciate some clues what else to check.   I was hoping a new libiconv would do it but no luck.

Please advise on this issue as a new SQLAlchemy release is being delayed until I can at least get some understanding on the scope of this issue.

14.0 KB   View   Download
158 KB   View   Download
Oct 14, 2011
I'm running into this as well. Same setup (dunno about libiconv). Thanks for tracking down the cause in more detail... all I knew is SQLAlchemy blew up with a MemoryError when I tried to reflect on a database.

I tried compiling freetds a bunch of different ways, with msdblib and without, with unixodbc vs iodbc (default).
Oct 16, 2011
Project Member #2 mkleehammer
 Issue 210  has been merged into this issue.
Oct 16, 2011
Project Member #3 mkleehammer
The first thing to look at is the UTF8.

A lot of driver writers don't seem to understand that ODBC only supports three encodings:
* Single byte controlled by a codepage (ASCII, latin1, etc.)
* UCS2
* UCS4
The later two depend on the size of SQL_WCHAR. 

Regardless of what the DB storage is, UTF8, EBCDIC, etc., the driver's job is to convert to one of the ODBC formats so everyone can communicate.  For some reason, lots of driver writers think they can just use a flag to tell you what the format is.  Problem is, it won't work since there is no standard for doing so.  There is no ODBC flag for "UTF8".

I realize might have to provide some codec override for this, but for the moment try setting your charset to UCS-2.
Status: Investigating
Oct 16, 2011
yeah I've been through this UCS-2 thing before when i was trying to get Easysoft's ODBC stuff to work.   If I use that, in this case the script returns:

pyodbc.Error: ('01000', '[01000] [FreeTDS][SQL Server]Unexpected EOF from the server (20017) (SQLDriverConnectW)')

The important thing to note in all these issues, is that *everything worked with FreeTDS 0.82*.  I run pyodbc + 0.82 in production on linux and runs great on osx as well, no unicode issues at all.   So FreeTDS has clearly made some major change - either there's some very obvious thing that needs to be done against it, or 0.91 has a huge bug which I'd like to report to them.   If the issue can be clearly understood I can publicize the crap out of it and get whatever needs to be fixed to occur.

As a secondary request, if there were some way libraries could not core dump/segfault/memoryerror when things aren't as they should be and instead raise an error, wow that would be awesome.  I won't hold my breath for that.    
Nov 2, 2011
Project Member #5 mkleehammer
(No comment was entered for this change.)
Labels: OpSys-OSX FreeTDS
Nov 7, 2011
Just wanted to report that this is still an issue under 3.0.2-beta01.
Nov 9, 2011

just a note that freeTDS needs to be compiled with --enable-odbc-wide to get all the unicode goodness.

Nov 9, 2011

just a note that freeTDS needs to be compiled with --enable-odbc-wide to get all the unicode goodness.

Nov 9, 2011
If confirmed as the fix for all of this, that is extremely interesting.   Not here at all:  and if it comes down to, "FreeTDS won't work correctly with unicode if you don't set this", that would be a major issue on their end.    I'd also ask why isn't this the default for them.
Nov 10, 2011

I found this option by doing a ./configure --help.

it is not the default since it is listed as 'experimental'.

So far, I have had no issues with it.

Nov 15, 2011
Thanks Dave,

I just ran into this same issue and the `--enable-odbc-wide` options resolved the issue for me as well.
Nov 15, 2011
I'd note that this *barely* fixes the problem.  The flag allows the "NULL" thing to work, but you still can't reasonably pass a u'' into execute() :

    import pyodbc

    conn = pyodbc.connect("dsn=ms_2005;UID=scott;PWD=tiger")

    cursor = conn.cursor()
    cursor.execute("select cast(? as nvarchar)", u'hi')
    print cursor.fetchall()


Segmentation Fault.

pops open the crash window, etc.    Occurs regardless of encoding in freetds.conf, including UCS-2, UTF8, no setting.

as always, make it 'hi' instead of u'hi', problem fixed.
Nov 15, 2011
If I change SQLAlchemy to do what it does with 0.82 and encode values, then it doesn't crash - then you get *blank* values back:

    #!coding: utf-8

    import pyodbc

    conn = pyodbc.connect("dsn=ms_2005;UID=scott;PWD=tiger")

    cursor = conn.cursor()
    cursor.execute("select ?", u'm’a réveillé.'.encode('utf-8'))
    print cursor.fetchall()


[('', )]

this same behavior occurs with round trip tests against table rows as well.  utf8/ucs2 blah blah makes no difference pyodbc is plainly very broken regarding unicode and freetds.

FreeTDS 0.82, of course works completely fine, getting back the same thing, encoded so we have to decode, but sure beats blank:

[('m\xe2\x80\x99a r\xc3\xa9veill\xc3\xa9.', )]

Sorry, there is absolutely no "unicode goodness" here.   I can keep attaching more ODBC/FreeTDS logs for all these cases.   
Nov 16, 2011
    import pyodbc

    conn = pyodbc.connect("dsn=ms_2005;UID=scott;PWD=tiger")

    cursor = conn.cursor()
    cursor.execute("select cast(? as nvarchar)", u'hi')
    print cursor.fetchall()


[(u'hi', )]

This example works as expected for me.

configuration I am using:
Arch Linux x86_64
Python 2.7.2 built with gcc 4.6.1
pyodbc 2.1.11
freeTDS 0.91 built with --enable-odbc-wide
unixODBC 2.3.0

for your next example: 

cursor.execute("select ?", u'm’a réveillé.'.encode('utf-8'))

I get the same results you post

output: [('', )]

so some works OK, some not so OK...

Nov 16, 2011

using python 3.2.2 and pyodbc 3.1.1 beta21, I get 

cursor.execute("select ?", 'm’a réveillé.'.encode('utf-8'))
print( cursor.fetchall())


[(b'm\xe2\x80\x99a r\xc3\xa9veill\xc3\xa9.',)]

which looks correct.

Nov 16, 2011
Mike, were your recent tests done with pyodbc 3 or 2?
Nov 16, 2011
hi David -

This ticket is also specifically targeting the Mac OSX platform, which uses IODBC.   I've also tried unixODBC on the mac and there's a lot more issues there not worth getting into, particularly since iODBC works perfectly fine with FreeTDS 0.82/pyodbc (as long as you encode all strings going in).

On Linux/UnixODBC 2.3.0/FreeTDS 0.91, PyODBC works a bit better, but still has enough data issues to lead us to stick with an older version of both unixodbc/freetds in production.

The Linux based issue I've posted regarding UnixODBC 2.3.0 and FreeTDS 0.91 remaining open is #212.  I also see someone posted #222 regarding that platform.
Nov 16, 2011
Also these are all against Py2K.   I'd love to be able to test PyODBC against py3K someday as well though I consider bulletproof unicode round trips on py2K to be a prerequisite for me to even go there.
Nov 16, 2011

sorry if I confused things.

Nov 16, 2011
No worries, I only wish I had the expanse of time to get into the FreeTDS/PyODBC internals and understand the encoding behavior once and for all, since I'm operating pretty much in the dark on my end.
Dec 18, 2011
Project Member #21 mkleehammer
Strangely, selecting "cast(NULL as nvarchar)" no longer fails in 3.0.1, but the freetds unit test test_unicode_null does fail when selecting a NULL value actually stored.

I *finally* got my OS/X box running again and the ODBC trace shows that SQLGetData is returning the length as -4.  The value -2 is reserved to mean "NULL", but there is no define for -2.  I'm tempted to accept -2 and -4 for the time being.

Dec 18, 2011
Project Member #22 mkleehammer
Correction, -1 is reserved for SQL_NULL_DATA.  Don't want to add any more confusion...

Dec 18, 2011
Project Member #23 mkleehammer
Added a workaround in 3.0.2-beta03 for the previously mentioned FreeTDS bug.

Status: Complete
Sign in to add a comment

Powered by Google Project Hosting