freetds 0.91 - does not appear to work at all

[Mike Bayer]

get unit tests to pass with 0.91 if possible.

[Mike Bayer]

Here's one test:

from sqlalchemy import create_engine

e = create_engine('mssql+pyodbc://scott:tiger@ms_2005/', echo=True)

# passes
e.has_table('abcdef')

# fails: The data types nvarchar and ntext are incompatible in the equal to operator.
# differentiating factor is the number of characters in the name.
e.has_table('abcdefghijkl')

[Mike Bayer]

another:

import pyodbc

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

cursor = conn.cursor()
cursor.execute("""CREATE TABLE a (id INTEGER NOT NULL)""")

# MemoryError - program crashes unconditionally
cursor.execute("SELECT * FROM information_schema.columns where table_name = 'a'")
print cursor.fetchall()

[Mike Bayer]

pyodbc version of the "too many characters" test

import pyodbc

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

cursor = conn.cursor()

# passes
cursor.execute("SELECT * FROM [INFORMATION_SCHEMA](INFORMATION_SCHEMA).[COLUMNS](COLUMNS) WHERE [TABLE_NAME](TABLE_NAME)=? AND [TABLE_SCHEMA](TABLE_SCHEMA) =?", (u'abcdef', u'dbo'))

# passes
cursor.execute("SELECT * FROM [INFORMATION_SCHEMA](INFORMATION_SCHEMA).[COLUMNS](COLUMNS) WHERE [TABLE_NAME](TABLE_NAME)=? AND [TABLE_SCHEMA](TABLE_SCHEMA) =?", ('abcdefghijk', 'dbo'))

# fails: [FreeTDS](FreeTDS)[Server](SQL)The data types nvarchar and ntext are incompatible in the equal to operator. (402) (SQLParamData)')
cursor.execute("SELECT * FROM [INFORMATION_SCHEMA](INFORMATION_SCHEMA).[COLUMNS](COLUMNS) WHERE [TABLE_NAME](TABLE_NAME)=? AND [TABLE_SCHEMA](TABLE_SCHEMA) =?", (u'abcdefghijk', u'dbo'))

[Mike Bayer]

patch which would turn on "unicode binds" for freetds 0.91, but the above info schema queries would need to be fixed:

diff -r 07179d2aae12bb4e72eb1e494a870eefada8320a lib/sqlalchemy/connectors/pyodbc.py
--- a/lib/sqlalchemy/connectors/pyodbc.py   Tue Sep 06 09:51:18 2011 -0400
+++ b/lib/sqlalchemy/connectors/pyodbc.py   Thu Sep 08 10:09:49 2011 -0400
@@ -29,6 +29,10 @@
     # if the freetds.so is detected
     freetds = False

+    # will be set to the string version of
+    # the FreeTDS driver if freetds is detected
+    freetds_driver_version = None
+
     # will be set to True after initialize()
     # if the libessqlsrv.so is detected
     easysoft = False
@@ -108,11 +112,15 @@
         self.easysoft = bool(re.match(r".*libessqlsrv.*\.so", _sql_driver_name
                             ))

+        if self.freetds:
+            self.freetds_driver_version = dbapi_con.getinfo(pyodbc.SQL_DRIVER_VER)
+
         # the "Py2K only" part here is theoretical.
         # have not tried pyodbc + python3.1 yet.
         # Py2K
         self.supports_unicode_statements = not self.freetds and not self.easysoft
-        self.supports_unicode_binds = not self.freetds and not self.easysoft
+        self.supports_unicode_binds =  (not self.freetds or 
+                                            self.freetds_driver_version >= '0.91') and not self.easysoft
         # end Py2K

         # run other initialization which asks for user name, etc.

[Mike Bayer]

57ae8672a6be786c32d79faca90666d5b654bce6 has a patch that gets 0.91 somewhat working on linux, though I still get core dumps with many kinds of queries on linux. On OSX, 0.91 is not usable at all, and currently I can't even get 0.82 to build at all on OSX 10.6 but that may be partially my fault (I still have my old 0.82 binaries, which were built on 10.5, and those still work).

I can't do much here, FreeTDS is a terribly low quality library with zero help on their list, the state of ODBC on unix remains very, very poor.

I expect this ticket to remain open for many months as I'm not getting responses from Pyodbc or freetds and I've blown many days on this.

[Former user]

I did not understand the issue, but if helps, I'm using sqlachemy 0.7.3, with freetds 0.91 (compiled from source).

I'm using ubuntu 11.04, 64 bits, pyodbc 3.0.3.

Hope it helps. If you need more test, just tell me. This is in production already!

cheers!

[Mike Bayer]

I'm grumpy because things work poorly on OSX, particularly unicode data.

I need to get 0.7.6 out ASAP, preferably today...for this issue, it just means working through the flags for pyodbc + python 3. There's some small fixes needed.

[Mike Bayer]

oh sorry, this is a different issue. Yes, Pyodbc has improved things in the latest versions. Still having issues with unicode, see http://code.google.com/p/pyodbc/issues/detail?id=247

[Mike Bayer]

I still have major issues with freetds0.91 + pyodbc on the mac, but things have improved and people do use 0.91 so there's not much happening here.