| Issue 159: | MS SQL Server: Unicode string parameter causes exception | |
| 3 people starred this issue and may be notified of changes. | Back to list |
This issue was originally reported by a different user on the list (https://groups.google.com/group/pyodbc/browse_thread/thread/572a4628eb28a863). I have opened a report here because I have more info and an ODBC trace. What steps will reproduce the problem? ====================================== Execute: import pyodbc conn = pyodbc.connect('DRIVER={SQL Server};SERVER=host_addr;' 'DATABASE=testdb;UID=username;PWD=password') cursor = conn.cursor() cursor.execute(u""" SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME] FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1] WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1]. [TABLE_SCHEMA] = ? ORDER BY [COLUMNS_1].[ORDINAL_POSITION] """,(u'tf_valid_species', u'dbo')) The parameter 'tf_valid_species' can be replaced by any Unicode string with len(s) > 10. len(s) <= 10 does not trigger this. Actual result ============= Traceback (most recent call last): File "./db.py", line 90, in <module> """,(u'tf_valid_species', u'dbo')) pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]The data types nvarchar and ntext are incompatible in the equal to operator. (402) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)') Expected result =============== The query completes without an exception being thrown. If the table exists, a result row is returned. (Even if the table doesn't exist, an empty row is returned). What version of the product are you using? On what operating system? ==================================================================== Client: pyodbc on Python 2.7 on Windows Server: Microsoft SQL Server 2008 on Windows In terms of when this issue was introduced, it was in the Unicode fixes between pyodbc 2.1.7 and 2.1.8: Bad: 691b6ba73 Reworked Unicode; changed version format; more Py_ssize_t Can't build: fc712713 Modified decimal param binding; Updated varchar/longvarchar fix. Good: 9ff534207d Issue 95 : "beta0" added when building from source distribution. Please provide any additional information below. ================================================ Without changing the server, changing the client ODBC driver changes whether or not this issue is encountered. Bad: DRIVER={SQL Server} # SQL Server 2000 features Bad: DRIVER={SQL Native Client} # SQL Server 2005 features Good: DRIVER={SQL Server Native Client 10.0} # SQL Server 2008 features Because of this, I can't say that pyodbc is to blame. Still, SQLAlchemy uses the {SQL Server} form, triggering this issue. Hence I'd argue it'd be useful for pyodbc (or possibly SQLAlchemy?) to work around this problem, if feasible. I have uploaded both a log from both a pyodbc version where the query works and from a version where it doesn't. In particular, note for the first parameter: [Bad pyodbc version, long table name 'tf_valid_species'] db eb4-17c8 ENTER SQLBindParameter HSTMT 00BC2990 UWORD 1 SWORD 1 <SQL_PARAM_INPUT> SWORD -8 <SQL_C_WCHAR> SWORD -10 <SQL_WLONGVARCHAR> SQLULEN 32 SWORD 0 PTR 0x00DB11A0 SQLLEN 4 SQLLEN * 0x00B762D8 [Good pyodbc version, long table name 'tf_valid_species'] db 1328-1700 ENTER SQLBindParameter HSTMT 00BC2990 UWORD 1 SWORD 1 <SQL_PARAM_INPUT> SWORD -8 <SQL_C_WCHAR> SWORD -9 <SQL_WVARCHAR> SQLULEN 16 SWORD 0 PTR 0x00B00430 SQLLEN 34 SQLLEN * 0x00B762D8 The example query also works if I use a bad pyodbc version but with a short (<= 10) table name. In this case, the log also shows the parameter being passed with type SQL_WVARCHAR. So maybe pyodbc is being given bad information on the maximum length of SQL_WVARCHAR which as of 2.1.8 it is honouring, and so pyodbc is using SQL_WLONGVARCHAR when SQL_WVARCHAR would have worked and SQL_WLONGVARCHAR doesn't.
Apr 14, 2011
#1
iain.ni...@cmascotland.com
Dec 26, 2011
The last commenter has the best answer. This is something completely out of the hands of pyodbc - it is simply passing along the arguments and types. Forcing the use of the latest driver is the best fix.
Status:
NoFix
Mar 11, 2012
Thanks, iain.ni...@cmascotland.com. The solution you provided works very well.
def connect():
return pyodbc.connect('DRIVER={SQL Server Native Client 10.0};Server=%s;Database=%s;UID=%s;PWD=%s;'%(host, database, username, password))
engine = create_engine('mssql://', creator=connect)
Apr 2, 2013
I'm still having this issue with Python 3 and FreeTDS 0.91, as well as with Python 2 if unicode strings are passed. Here's me emailing the FreeTDS list in vain: http://lists.ibiblio.org/pipermail/freetds/2011q3/027336.html unfortunately "SQL Server Native Client 10.0" is not an option for me here, as this is freeTDS. Doesn't happen with 0.82.
Apr 2, 2013
here's the test script:
import pyodbc
conn = pyodbc.connect(dsn="ms_2005", uid="scott", password="tiger")
cursor = conn.cursor()
# succeeds in py2k, py3k
cursor.execute("""SELECT [COLUMNS_1].[COLUMN_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?
""", ('short', 'dbo'))
# succeeds in py2k, fails in py3k
cursor.execute("""SELECT [COLUMNS_1].[COLUMN_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?
""", ('im_a_longer_named_table', 'dbo'))
# fails in both py2k and py3k
cursor.execute("""SELECT [COLUMNS_1].[COLUMN_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?
""", (u'im_a_longer_named_table', 'dbo'))
Apr 2, 2013
anyway, I'll see if I can build into SQLAlchemy this workaround, seems to work but need to see if it works with 0.82 as well:
cursor.execute("""SELECT [COLUMNS_1].[COLUMN_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE cast([COLUMNS_1].[TABLE_NAME] as nvarchar(max)) = cast(? as nvarchar(max)) AND [COLUMNS_1].[TABLE_SCHEMA] = ?
""", (u'im_a_longer_named_table', 'dbo'))
|