| Issue 29: | Unicode query strings | |
| 3 people starred this issue and may be notified of changes. | Back to list |
I am unable to define a unicode query string to pass to the Cursor.execute(). The following code
illustrates my problem. I apologize if this is not a real issue and simply my ignorance.
What steps will reproduce the problem?
#!/usr/bin/env python
# encoding: utf-8
"""
pyodbc_unicode_test.py
environment
- Mac OSX 10.5
- Python 2.5.1
- latest pyodbc
- mssql
- latest Actual ODBC Driver
"""
import pyodbc as db
unicode = u'\ufffeselect * from wasp..locations'
string = 'select * from wasp..locations'
utf8 = unicode.encode('utf-8')
con = db.connect('DSN=xxx;UID=xxx;PWD=xxx')
cursor = con.cursor()
f = cursor.execute(utf8)
s = cursor.execute(string)
u = cursor.execute(unicode)
assert type(f) is db.Cursor, "executing utf8 fails: %s" % f
assert type(s) is db.Cursor, "executing string fails: %s" % s
assert type(u) is db.Cursor, "executing unicode fails: %s" % u
con.close()
#output
#######
#Traceback (most recent call last):
# File "pyodbc_unicode_test.py", line 29, in <module>
# assert type(u) is db.Cursor, "executing unicode fails: %s" % u
#AssertionError: executing unicode fails: -1
What is the expected output? What do you see instead?
I expected the u variable to be of type pyodbc.Cursor after the execute function was called.
Instead it returned a -1.
What version of the product are you using? On what operating system?
environment
- Mac OSX 10.5
- Python 2.5.1
- latest pyodbc
- mssql
- latest Actual ODBC Driver
Please provide any additional information below.
Thanks in advance for any help you can provide with this issue.
Jan 27, 2009
#1
jeremiah...@gmail.com
Jan 27, 2009
I'm concerned that there is a Unicode issue on Mac OS/X -- there is another issue open also. Unfortunately, I don't have immediate access to an OS/X environment and have to rely on someone else. I'll see if I can get some testing tomorrow. It is very odd since execute should either return the cursor or raise an exception -- only the old 2.0.x versions returned a rowcount. Can you provide an ODBC trace? That will tell us what pyodbc passed to the driver and what was returned.
Status:
Investigating
Jan 28, 2009
I performed the trace. If I am reading this log correctly the interesting part is lines 390 - 395.
Mar 18, 2009
I think this is fixed in 2.1.5-beta7 9c34c6a79c291a4ed17596f4bae72ce7ed04f314 (so it will be in 2.1.5). This is potentially serious: Apparently I forgot to change Cursor.execute to always return the Cursor when I made the 2.1.x branch! I'd like to not create the official 2.1.5 until I verify as many fixes as possible. Can you build from the given github hash or can I provide a source zip file for you to test?
Mar 19, 2009
I get a new error. Will be able to research more next week.
Traceback (most recent call last):
File "pyodbc_unicode_test.py", line 27, in <module>
u = cursor.execute(unicode)
pyodbc.ProgrammingError: ('42000', '[42000] [Actual][SQL Server] Statement(s) could not be prepared. (8180)
(SQLExecDirectW)')
Oct 1, 2009
I tested this problem on snow leopard + latest freetds stable + latest pyodbc from github. The above test case does not produce any errors. If you attempt to print results from each time I cursor.execute() is called I get the following error. Traceback (most recent call last): File " issue29 .py", line 22, in <module> print u.fetchone() pyodbc.ProgrammingError: No results. Previous SQL was not a query. The original problem I reported seems to have been solved with the fix you described in comment 4. The cursor stays the cursor and does not become a -1. I would still like to understand why unicode query strings seem to not work or if I am doing something wrong but I would say this main issue is resolved.
Sep 6, 2010
Can you please try this with 2.1.8. I made a lot of Unicode changes for OS/X and 64-bit Linux. Thanks.
Nov 24, 2010
(No comment was entered for this change.)
Status:
Hold
Mar 2, 2011
The problem still persists (2.1.8) and it's independent of the driver you use.
I have a simple table with columns:
fname_col varchar(100)
blob_col blob
size_col integer
I am on Ubuntu lucid 64bit.
The table gets populated from django, everything is utf-8: pages, db tables, py source files.
Testing filenames with latin only characters I get the following results:
With sqlite3 odbc the fname_col gets populated with chinese characters (literaly).
(Or so it appears to be)
With MySQL odbc only the first character of the filename gets stored.
If I make an encoding before my insert or update, like:
fname.encoding('ascii') (remember this is latin only filenames, so this works)
the fname_col gets populated nicely.
Feb 19, 2012
Is this problem solved? I'm having a similar issue on Mac OS X 10.6, Python 2.7.1, pyodbc 3.0.3. I'm connecting to an SQL Server Express 2008 R2 server (using freetds 0.91).
Sep 27, 2012
The freetdstests.py unit tests pass on OS/X 10.8 (Mountain Lion) connecting to a Windows 7 / SQL Server 2012 Express instance. Closing. The original issue (Cursor.execute returned the row count instead of the Cursor object) is definitely fixed. If the later problems still don't work, please open a new issue. Thanks.
Status:
Complete
|