Skip to Content.
Sympa Menu

freetds - [freetds] Simple queries against information schema

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Michael Bayer <mike_mp AT zzzcomputing.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: [freetds] Simple queries against information schema
  • Date: Thu, 8 Sep 2011 10:51:21 -0400

I'm having issues with FreeTDS 0.91 and Pyodbc. The issues are likely some
unfortunate combination of the two toolsets, so I apologize if this issue is
wrongly placed - however I'm just looking for clues at this point.

The issues all surround passing of unicode data to FreeTDS. In Python, we
generally have two ways of doing this - either pass an encoded (i.e. utf-8)
bytestring to the DBAPI, or pass a "python unicode" object and hope that the
DBAPI knows what to do with it as far as encoding. With PyODBC and FreeTDS
this has always been a tricky affair.

In 0.82, pretty much sending encoded bytestrings was the way to go, however
in 0.91 this doesn't seem to work as well - I have users reporting that u''
strings are now accepted from PyODBC-> FreeTDS once FreeTDS is at version
0.91 and I can confirm this is at least partially true, though not completely
as illustrated below. Note that PyODBC works fully with Python unicode
objects when run on Windows against regular microsoft ODBC drivers.

Was just wondering, in the test below, the differentiating factor in the line
that succeeds, and the line that fails, is the number of characters in the
bound parameter - u"abcdef" versus u"abcdefghijk". The second appears to
interpret the value as NTEXT based on that length - 11 seems a little low
(and arbitrary) for that, but overall what should be the best practice here ?
Is there some way for FreeTDS to handle this more gracefully ?


import pyodbc

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

cursor = conn.cursor()

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

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

# this passes, because note we are sending a bytestring, i.e. '', and not
u'', as the table name
cursor.execute("SELECT * FROM [INFORMATION_SCHEMA].[COLUMNS] WHERE
[TABLE_NAME]=? AND [TABLE_SCHEMA] =?", ('abcdefghijk', 'dbo'))




  • [freetds] Simple queries against information schema, Michael Bayer, 09/08/2011

Archive powered by MHonArc 2.6.24.

Top of Page