Crash selecting NULL NVARCHAR value, FreeTDS 0.91/OSX, FULL TEST, ODBC AND FREETDS LOGS ARE ATTACHED

[GoogleCodeExporter]

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()

Output:

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

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.

Original issue reported on code.google.com by zzz...@gmail.com on 18 Sep 2011 at 4:27

Attachments:

• odbc_trace.txt
• freetds.log

[GoogleCodeExporter]

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).

Original comment by ke...@keithdevens.com on 14 Oct 2011 at 6:45

[GoogleCodeExporter]

Issue 210 has been merged into this issue.

Original comment by mkleehammer on 16 Oct 2011 at 12:41

[GoogleCodeExporter]

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.

Original comment by mkleehammer on 16 Oct 2011 at 12:46

• Changed state: Investigating

[GoogleCodeExporter]

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.    

Original comment by zzz...@gmail.com on 16 Oct 2011 at 1:42

[GoogleCodeExporter]

Original comment by mkleehammer on 3 Nov 2011 at 1:56

• Added labels: FreeTDS, OpSys-OSX

[GoogleCodeExporter]

Just wanted to report that this is still an issue under 3.0.2-beta01.

Original comment by ke...@keithdevens.com on 7 Nov 2011 at 7:18

[GoogleCodeExporter]

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

Original comment by davidp.r...@gmail.com on 9 Nov 2011 at 2:33

[GoogleCodeExporter]

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

Original comment by davidp.r...@gmail.com on 9 Nov 2011 at 2:33

[GoogleCodeExporter]

If confirmed as the fix for all of this, that is extremely interesting.   Not 
here at all: http://www.freetds.org/userguide/config.htm  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.

Original comment by zzz...@gmail.com on 9 Nov 2011 at 5:13

[GoogleCodeExporter]

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.

Original comment by davidp.r...@gmail.com on 10 Nov 2011 at 2:20

[GoogleCodeExporter]

Thanks Dave,

I just ran into this same issue and the `--enable-odbc-wide` options resolved 
the issue for me as well.

Original comment by justinwi...@gmail.com on 15 Nov 2011 at 11:08

[GoogleCodeExporter]

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()

returns:

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.

Original comment by zzz...@gmail.com on 15 Nov 2011 at 11:32

[GoogleCodeExporter]

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()

output:

[('', )]

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.   

Original comment by zzz...@gmail.com on 15 Nov 2011 at 11:47

[GoogleCodeExporter]

    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()

returns:

[(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...

Original comment by davidp.r...@gmail.com on 16 Nov 2011 at 3:14

[GoogleCodeExporter]

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())

result:

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

which looks correct.

Original comment by davidp.r...@gmail.com on 16 Nov 2011 at 3:47

[GoogleCodeExporter]

Mike, were your recent tests done with pyodbc 3 or 2?

Original comment by ke...@keithdevens.com on 16 Nov 2011 at 3:52

[GoogleCodeExporter]

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.

Original comment by zzz...@gmail.com on 16 Nov 2011 at 3:59

[GoogleCodeExporter]

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.

Original comment by zzz...@gmail.com on 16 Nov 2011 at 4:00

[GoogleCodeExporter]

sorry if I confused things.

Original comment by davidp.r...@gmail.com on 16 Nov 2011 at 4:07

[GoogleCodeExporter]

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.

Original comment by zzz...@gmail.com on 16 Nov 2011 at 4:31

[GoogleCodeExporter]

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.

Original comment by mkleehammer on 18 Dec 2011 at 9:54

[GoogleCodeExporter]

Correction, -1 is reserved for SQL_NULL_DATA.  Don't want to add any more 
confusion...

Original comment by mkleehammer on 18 Dec 2011 at 9:57

[GoogleCodeExporter]

Added a workaround in 3.0.2-beta03 for the previously mentioned FreeTDS bug.

Original comment by mkleehammer on 18 Dec 2011 at 10:20

• Changed state: Complete