Problem with odbc (pywin32) and unicode

[Frank Millman]

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]

It is the combination of new style and unicode that does not work.

I realise that odbc is not fully DB-API compliant, but I have got away
with it up to now. Does anyone know of a solution to this problem?

Thanks

Frank Millman

ps to Fredrik, who just added this to a recent post -

"(just curious, but from where do people get the idea that arbitrary
data
just have to be inserted into the the SQL statement text all the time?
is
this some PHP misfeature?) "

I can't answer for anyone else, but this is my excuse.

Sequence of events -

learn a bit about Python - play in interpreter, write simple scripts,
etc.
learn a bit about SQL - play in an interactive session, run some
commands, etc.
try to combine the two - wow, it works!
read in the DB-API about parameter passing - frankly, don't
understand it.
therefore stick with what works for me.

Parameter passing seems to be one of those things that, if you
understand it, you can't imagine that there is anyone out there that
does not understand it, and therefore you think that the documentation
is perfectly clear. However, if you are one of those unfortunate souls
who have never come across such a thing, the docs simply do do not
spell it out clearly enough, so one's reaction is more 'huh?' than
'aha!'

[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

[Frank Millman]

Diez B. Roggisch wrote:

Frank Millman wrote:

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.

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!

Thanks, Diez - I tried it and it works.

Unicode is one of those grey areas that I know I will have to try to
understand one day, but I am putting off that day as long as possible!

When you say it causes trouble internally, do you mean internal to the
odbc module? Is it something that should be reported as a bug?

What should I do in the meantime? Make encode("utf-8") a permanent
feature of my program?

BTW, the reason I am getting unicode objects is that I use wxPython as
my front end. You can opt for a unicode or a non-unicode build. I
decided to go for unicode to cater for future requirements. This is the
first time it has bitten me - I am sure it will not be the last.

Many thanks for your help

Frank

[Diez B. Roggisch]

> Unicode is one of those grey areas that I know I will have to try to

understand one day, but I am putting off that day as long as possible!
I suggest you better start right away instead of stumbling over it all the
time. The most problems in that field don't come from the inherent
complexity of the matter itself, but form the profound lack of
understanding of developers thereof - worldwide, btw. You are not alone, as
Mr. Jackson sang...

When you say it causes trouble internally, do you mean internal to the
odbc module? Is it something that should be reported as a bug?
Not sure, but I guess: yes.
What should I do in the meantime? Make encode("utf-8") a permanent
feature of my program?

You should do it in a way that allows you to either pass the encoding
explicitly, or make it some sort of configurable thing, in the same spot
where you configure your connection settings, as these two are related.

Diez

[Frank Millman]

Diez B. Roggisch wrote:

Unicode is one of those grey areas that I know I will have to try to
understand one day, but I am putting off that day as long as possible!

I suggest you better start right away instead of stumbling over it all the
time. The most problems in that field don't come from the inherent
complexity of the matter itself, but form the profound lack of
understanding of developers thereof - worldwide, btw. You are not alone, as
Mr. Jackson sang...

In other words, that day has arrived. I will roll my sleeves up and get
stuck into the docs.

When you say it causes trouble internally, do you mean internal to the
odbc module? Is it something that should be reported as a bug?

Not sure, but I guess: yes.

What should I do in the meantime? Make encode("utf-8") a permanent
feature of my program?

You should do it in a way that allows you to either pass the encoding
explicitly, or make it some sort of configurable thing, in the same spot
where you configure your connection settings, as these two are related.

Diez

Many thanks for your help, Diez - I will take it from here.

Frank

[Frank Millman]

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]

It is the combination of new style and unicode that does not work.

I realise that odbc is not fully DB-API compliant, but I have got away
with it up to now. Does anyone know of a solution to this problem?

Thanks

Frank Millman

I am pleased to report that this has been fixed.

I have been liaising with Mark Hammond, and he has sent me a revised
version which behaves correctly.

This is from Mark -

"I'd be happy for you to mention this fix will be in build 208 - I just
can't tell you when that will be :)"

Frank