Cannot query from MSSQL INFORMATION_SCHEMA using FreeTDS 0.91

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Run the script:

import pyodbc

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

cursor = conn.cursor()

cursor.execute("SELECT * FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1] 
WHERE [COLUMNS_1].[TABLE_NAME] = 'A'")

print cursor.fetchall()

What is the expected output? 

should see a database row, or None

What do you see instead?

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

What version of the product are you using? On what operating system?

Pyodbc 2.1.9 (also with 2.1.4 though), FreeTDS 0.91, Mac OSX.   The issue does 
not occur with FreeTDS 0.82 and I apologize for posting a FreeTDS centric 
issue, but I'm not optimistic about getting help from them on this- they are 
likely to blame Pyodbc.

Original issue reported on code.google.com by zzz...@gmail.com on 8 Sep 2011 at 3:40

• Merged into: #211

[GoogleCodeExporter]

Apparently this issue is dependent on the table definition itself.  Here is 
that:

CREATE TABLE [A] (
    [ID] INTEGER NOT NULL IDENTITY(1,1), 
    col1 NTEXT NULL, 
    col2 VARCHAR(255) NULL, 
    PRIMARY KEY ([ID])
)

Original comment by zzz...@gmail.com on 8 Sep 2011 at 3:44

[GoogleCodeExporter]

actually any table will do.    Here's a much simpler version:

import pyodbc

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

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

cursor.execute("SELECT * FROM information_schema.columns where table_name = 
'a'")
print cursor.fetchall()

script crashes with MemoryError.

Original comment by zzz...@gmail.com on 8 Sep 2011 at 3:50

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Works for me (unixODBC 2.3.0, FreeTDS 0.91, pyodbc 2.1.9). Here's a dump from 
IPython session:

IPython 0.9.1 -- An enhanced Interactive Python.
?         -> Introduction and overview of IPython's features.
%quickref -> Quick reference.
help      -> Python's own help system.
object?   -> Details about 'object'. ?object also works, ?? prints more.

In [1]: import pyodbc

In [2]: conn = 
pyodbc.connect('DRIVER={FreeTDS};SERVER=xxxx;DATABASE=xxxx;UID=xxxx;PWD=xxxx;POR
T=2431')
---------------------------------------------------------------------------
Error                                     Traceback (most recent call last)

/usr/local/etc/<ipython console> in <module>()

Error: ('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to 
data source (0) (SQLDriverConnectW)')

In [3]: import os

In [4]: os.environ['TDSVER']='8.0'

In [5]: conn = 
pyodbc.connect('DRIVER={FreeTDS};SERVER=xxxx;DATABASE=xxxx;UID=xxxx;PWD=xxxx;POR
T=2431')

In [6]: cursor = conn.cursor()

In [7]: cursor.execute("SELECT * FROM [INFORMATION_SCHEMA].[COLUMNS] AS 
[COLUMNS_1] WHERE [COLUMNS_1].[TABLE_NAME] = 'A'")
Out[7]: <pyodbc.Cursor object at 0x88a2058>

In [8]: print cursor.fetc
cursor.fetchall   cursor.fetchmany  cursor.fetchone

In [8]: print cursor.fetchall()
------> print(cursor.fetchall())
[(u'SPEED', u'dbo', u'A', u'ID', 1, None, 'NO', u'int', None, None, 10, 10, 0, 
None, None, None, None, None, None, None, None, None, None), (u'SPEED', u'dbo', 
u'A', u'col1', 2, None, 'YES', u'ntext', 1073741823, 2147483646, None, None, 
None, None, None, None, u'UNICODE', None, None, 
u'SQL_Latin1_General_CP1_CI_AS', None, None, None), (u'SPEED', u'dbo', u'A', 
u'col2', 3, None, 'YES', u'varchar', 255, 255, None, None, None, None, None, 
None, u'iso_1', None, None, u'SQL_Latin1_General_CP1_CI_AS', None, None, None)]

In [9]: cursor.execute('drop table a')
Out[9]: <pyodbc.Cursor object at 0x88a2058>

In [10]: cursor.execute("""CREATE TABLE a (id INTEGER NOT NULL)""")
Out[10]: <pyodbc.Cursor object at 0x88a2058>

In [11]: cursor.execute("SELECT * FROM information_schema.columns where 
table_name = 'a'")
Out[11]: <pyodbc.Cursor object at 0x88a2058>

In [12]: print cursor.fetchall()
-------> print(cursor.fetchall())
[(u'SPEED', u'dbo', u'a', u'id', 1, None, 'NO', u'int', None, None, 10, 10, 0, 
None, None, None, None, None, None, None, None, None, None)]

Original comment by victor.o...@gmail.com on 8 Sep 2011 at 9:11

[GoogleCodeExporter]

The issue is directly NULL values in NVARCHAR columns cannot be selected.  The 
error with information schema is specifically the NULL value in COLUMN_DEFAULT. 
 Here is a new test, same error:

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

I'll continue investigating if some setting in my FreeTDS is doing this.   Also 
I need to remember how to create ODBC logs I'm sure that would help.
Or from a table:

    import pyodbc

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

    cursor = conn.cursor()
    cursor.execute("create table mytest (value nvarchar(10))")
    cursor.execute("insert into mytest (value) values (null)")
    cursor.execute("select * from mytest")
    print cursor.fetchall()

Original comment by zzz...@gmail.com on 18 Sep 2011 at 3:38

[GoogleCodeExporter]

The issue is directly NULL values in NVARCHAR columns cannot be selected.  The 
error with information schema is specifically the NULL value in COLUMN_DEFAULT. 
 Here is a new test, same error:

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

I'll continue investigating if some setting in my FreeTDS is doing this.   Also 
I need to remember how to create ODBC logs I'm sure that would help.
Or from a table:

    import pyodbc

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

    cursor = conn.cursor()
    cursor.execute("create table mytest (value nvarchar(10))")
    cursor.execute("insert into mytest (value) values (null)")
    cursor.execute("select * from mytest")
    print cursor.fetchall()

Original comment by zzz...@gmail.com on 18 Sep 2011 at 3:38

[GoogleCodeExporter]

you can close this issue I've created #211 with more specific and up to date 
information.

Original comment by zzz...@gmail.com on 18 Sep 2011 at 4:28

[GoogleCodeExporter]

Thanks.

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

• Changed state: Duplicate