| Issue 210: | Cannot query from MSSQL INFORMATION_SCHEMA using FreeTDS 0.91 | |
| 1 person starred this issue and may be notified of changes. | Back to list |
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
Sep 7, 2011
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
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
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
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
you can close this issue I've created #211 with more specific and up to date information.
Oct 16, 2011
Thanks.
Status:
Duplicate
Mergedinto: 211 |