Issue 29: Unicode query strings
Status:  Complete
Owner: ----
Closed:  Sep 2012
Reported by jeremiah...@gmail.com, Jan 27, 2009

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

The line that reads: unicode = u'\ufffeselect * from wasp..locations'
Should have read: unicode = u'select * from wasp.dbo.locations'

Jan 27, 2009
Project Member #2 mkleehammer
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
#3 jeremiah...@gmail.com
I performed the trace. 

If I am reading this log correctly the interesting part is lines 390 - 395.
trace.log
13.7 KB   Download
Mar 18, 2009
Project Member #4 mkleehammer
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
#5 jeremiah...@gmail.com

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
#6 jeremiah...@gmail.com
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
Project Member #7 mkleehammer
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
Project Member #8 mkleehammer
(No comment was entered for this change.)
Status: Hold
Mar 2, 2011
#9 sv1...@gmail.com
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
#10 tal.lin...@gmail.com
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
Project Member #11 mkleehammer
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