Unicode query strings

[GoogleCodeExporter]

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.

Original issue reported on code.google.com by jeremiah...@gmail.com on 28 Jan 2009 at 2:22

[GoogleCodeExporter]

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

Original comment by jeremiah...@gmail.com on 28 Jan 2009 at 2:42

[GoogleCodeExporter]

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.

Original comment by mkleehammer on 28 Jan 2009 at 3:07

• Changed state: Investigating

[GoogleCodeExporter]

I performed the trace. 

If I am reading this log correctly the interesting part is lines 390 - 395.

Original comment by jeremiah...@gmail.com on 28 Jan 2009 at 2:08

Attachments:

• trace.log

[GoogleCodeExporter]

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?

Original comment by mkleehammer on 18 Mar 2009 at 5:13

[GoogleCodeExporter]

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

Original comment by jeremiah...@gmail.com on 19 Mar 2009 at 8:05

[GoogleCodeExporter]

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.

Original comment by jeremiah...@gmail.com on 1 Oct 2009 at 4:02

[GoogleCodeExporter]

Can you please try this with 2.1.8.  I made a lot of Unicode changes for OS/X 
and 64-bit Linux.  Thanks.

Original comment by mkleehammer on 6 Sep 2010 at 6:13

[GoogleCodeExporter]

Original comment by mkleehammer on 24 Nov 2010 at 8:06

• Changed state: Hold

[GoogleCodeExporter]

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.

Original comment by sv1...@gmail.com on 2 Mar 2011 at 9:56

[GoogleCodeExporter]

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

Original comment by tal.lin...@gmail.com on 19 Feb 2012 at 11:48

[GoogleCodeExporter]

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.

Original comment by mkleehammer on 27 Sep 2012 at 9:59

• Changed state: Complete