Problem with odbc (pywin32) and unicode

[Diez B. Roggisch]

Frank Millman wrote:

> Hi all
> 
> I am using odbc from pywin32 to connect to MS SQL Server. I am changing
> my program from the old (incorrect) style of embedding values in the
> SQL command to the new (correct) style of passing the values as
> parameters. I have hit a problem.
> 
> The following all work -
>     cur.execute("select * from users where userid = '%s'" % 'frank')  #
> old style
>     cur.execute("select * from users where userid = '%s'" % u'frank')
> # old style
>     cur.execute("select * from users where userid = ?", ['frank'])  #
> new style
> 
> This does not work -
>     cur.execute("select * from users where userid = ?", [u'frank'])  #
> new style
> 
> I get the following error -
> OdbcError: Found an insert row that didn't have 1 columns [sic]

To me it looks as if your problem stems from not encoding the parameter as a
string, but pasing a unicode-object that maybe internally causes trouble -
and a misleading error message.

That things work in your second old-style case comes from youprobably not
coming from a country where non-ascii characters are usual. Otherwise you'd
have seen that e.g.

"select * from users where userid = '%s'" % u'Martin von Löwis'

will produce the notorious

UnicodeEncodeError: 'ascii' codec can't encode character u'\xf6' in position
12: ordinal not in range(128)

So it is silently converted to a string by the %-operator using the ascii
codec.

So I suggest you try this:

cur.execute("select * from users where userid = ?",
[u'frank'.encode("utf-8"]) 


If that works, we know the problem. Make sure that your DB is expecting
UTF-8, or use the encoding it wants instead!

Regards,

Diez