The SQL contains 0 parameter markers, but 1 parameters were supplied

[John Hampton]

I get the message when I try to use a bind param. Digging into it, it
appears that if my query string is a unicode string, then it barfs with
the error in the subject. If it's a normal string, then the query succeeds.

Setup:
* OS X 10.5.7
* iODBC (whatever comes stock with OS X)
* FreeTDS 0.82
* pyODBC 2.1.6-beta0 (tested with 2.1.5 also)

Python 2.5.1 (r251:54863, Feb 6 2009, 19:02:12)
[GCC 4.0.1 (Apple Inc. build 5465)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
Tabbed Completion Enabled
>>> import pyodbc
>>> pyodbc.version
'2.1.6-beta0'

--- Normal String
>>> cnx = pyodbc.connect("DSN=XXXX;UID=sa;PWD=XXXX")
>>> cur = cnx.cursor()
>>> cur.execute('select * from pk010033 where manufactureorder_i = ?',
('MO-MK25',))
<pyodbc.Cursor object at 0x5a5d40>
>>> r = cur.fetchone()

--- Unicode
>>> cur.execute(u'select * from pk010033 where manufactureorder_i = ?',
(u'MO-MK25',))
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 1
parameters were supplied', 'HY000')

Any help or ideas of where to dig in more are greatly appreciated.

-John

[John Hampton]

John Hampton wrote:
> I get the message when I try to use a bind param. Digging into it, it
> appears that if my query string is a unicode string, then it barfs with
> the error in the subject. If it's a normal string, then the query succeeds.

Don't you just love it when you answer your own question? ;)

So, it looks as though the the unicode string needs to be explicitly
encoded to UTF-8[1]

If I do the following:

>>> cur.execute(u'select * from pk010033 where manufactureorder_i =

?'.encode('utf-8'), (u'MO-MK25'.encode('utf-8'),))

then it succeeds.

Is there any kind of setting to have pyodbc do this conversion for me?

-John

[1] I'm guessing that some other encodings may work, I only tested with
utf-8, however.

[mkleehammer]

> So, it looks as though the the unicode string needs to be explicitly
> encoded to UTF-8[1]

> Is there any kind of setting to have pyodbc do this conversion for me?

There aren't any settings because it's supposed to do it by itself. I
need to revisit OS/X Unicode conversions -- it is the odd-man out
since it has wchar_t of 2 but Py_UNICODE of 4 bytes.

I have someone that can build and test this, but first we have to
reproduce it. The odd thing is I don't see any characters over 255 in
your query. Are there some that I can't see (e.g. different
whitespace or something)? Does this occur for all Unicode query
strings?

Also, if my tester isn't available, are you able to test if I make
some beta builds?

Sorry for the trouble.
Michael Kleehammer

[John Hampton]

mkleehammer wrote:
> I have someone that can build and test this, but first we have to
> reproduce it. The odd thing is I don't see any characters over 255 in
> your query. Are there some that I can't see (e.g. different
> whitespace or something)? Does this occur for all Unicode query
> strings?

All unicode queries I performed. I'll admit that it's not an exhaustive
set, however.

Also, the query you see is the query I issued. No extra whitespace or
anything.

> Also, if my tester isn't available, are you able to test if I make
> some beta builds?

I'd be happy to help out building and testing.

-John