MS SQL Server: Unicode string parameter causes exception

[GoogleCodeExporter]

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.

Original issue reported on code.google.com by iain.ni...@cmascotland.com on 1 Mar 2011 at 3:25

Attachments:

• unicode-param-bad-pyodbc.log
• unicode-param-good-pyodbc.log

[GoogleCodeExporter]

For anyone else that comes across this, I've come across a good workaround.

pyodbc's create_engine takes a creator argument (see 
<http://www.sqlalchemy.org/docs/core/engines.html#custom-dbapi-connect-arguments
>).  Define this parameter to be a function which returns a pyodbc connection.  
By setting up this connection manually, instead of having SQLAlchemy do it, you 
can force

DRIVER={SQL Server Native Client 10.0},

which doesn't trigger the issue.

See 
<http://stackoverflow.com/questions/4493614/sqlalchemy-equivalent-of-pyodbc-conn
ect-string-using-freetds/4493770#4493770> for something very similar.

Original comment by iain.ni...@cmascotland.com on 14 Apr 2011 at 11:36

[GoogleCodeExporter]

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.

Original comment by mkleehammer on 27 Dec 2011 at 1:02

• Changed state: NoFix

[GoogleCodeExporter]

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)

Original comment by zwliu...@gmail.com on 11 Mar 2012 at 12:55

[GoogleCodeExporter]

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.

Original comment by zzz...@gmail.com on 2 Apr 2013 at 10:38

[GoogleCodeExporter]

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

Original comment by zzz...@gmail.com on 2 Apr 2013 at 10:38

[GoogleCodeExporter]

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

Original comment by zzz...@gmail.com on 2 Apr 2013 at 10:49