Issue 210: Cannot query from MSSQL INFORMATION_SCHEMA using FreeTDS 0.91
Status:  Duplicate
Merged:  issue 211
Owner: ----
Closed:  Oct 2011
Reported by zzz...@gmail.com, Sep 7, 2011
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.


Sep 7, 2011
#1 zzz...@gmail.com
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])
)



Sep 7, 2011
#2 zzz...@gmail.com
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.
Sep 8, 2011
#5 victor.o...@gmail.com
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;PORT=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;PORT=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)]

Sep 18, 2011
#6 zzz...@gmail.com
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()

Sep 18, 2011
#7 zzz...@gmail.com
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()

Sep 18, 2011
#8 zzz...@gmail.com
you can close this issue I've created #211 with more specific and up to date information.
Oct 16, 2011
Project Member #9 mkleehammer
Thanks.
Status: Duplicate
Mergedinto: 211