Not support unicode sql statement in Chinese

[vincentwyshan]

SOURCE CODE:

# coding=utf8

import pyodbc

print pyodbc.version

sql = u"""SELECT
    N'我的' AS [Name]
"""


def test_success():
    conn = pyodbc.connect(u'Driver={SQL Server};Server=localhost;Database=mydb;Trusted_Connection=yes;',
        unicode_results=True)
    cursor = conn.cursor()

    cursor.execute(sql)
    print "test_success::"
    print "." * 80
    # data = cursor.fetchall()
    print "." * 80


test_success()

Got errors:

PS C:\Users\vincent\Documents\GitHub> python C:\Users\vincent\Downloads\pyodbctest.py
4.0.13
test_success::
................................................................................
................................................................................
Exception UnicodeEncodeError: UnicodeEncodeError('ascii', u"SELECT\n    N'\u6211\u7684' AS [Name]\n", 13, 15, 'ordinal not in range(128)') in <module 'threading' from 'C:\Python27\lib\threading.pyc'> ignored

Does anyone knows is it a bug or I just wrote wrong code?

[gordthompson]

Strange. With pyodbc 4.0.13 under Python 2.7.13 (Windows), .fetchall() fails ...

    cursor.execute(u"SELECT N'我的' AS [Name]")
    print cursor.fetchall()

... with ...

  File "C:/Users/gord/PycharmProjects/py2unicodetest/pyodbctest.py", line 43, in test_success
    print cursor.fetchall()
UnicodeEncodeError: 'ascii' codec can't encode characters in position 9-10: ordinal not in range(128)

Process finished with exit code 1

... but .fetchone() succeeds ...

    cursor.execute(u"SELECT N'我的' AS [Name]")
    print cursor.fetchone()

... although I do get the "Exception ... ignored" warning from threading.pyc:

(u'\u6211\u7684', )
Exception UnicodeEncodeError: UnicodeEncodeError('ascii', u"SELECT N'\u6211\u7684' AS [Name]", 9, 11, 'ordinal not in range(128)') in <module 'threading' from 'C:\Python27\Lib\threading.pyc'> ignored

Process finished with exit code 0

[vincentwyshan]

Hi, @gordthompson Thank you.

I just upgrade pyodbc from 3.0.7 to latest version, cause I got program crash for unknown reason from pyodbc, but unfortunately I got another issue.

[mkleehammer]

@gordthompson - what a good catch on the fetchone() vs fetchall().

I'm still looking into it but I thought you might be interested in what I've found so far: PyCodec_Encode() is returning a bytes object and setting an exception. The fetchall() code looks to see if an exception was set which is why it fails. I believe the exception is something the encode function sets incorrectly (or gets created but should be cleared) and I'll have to clear the exception to work around.

[mkleehammer]

I haven't made a release yet, but the fix has been pushed to master. Thanks everyone.

[mkleehammer]

This fix is in 4.0.14. Good luck.