mx.ODBC.Windows - execute() parameters substitution strange behaviour - SOLVED.

[vtail]

vtail at yandex.ru (vtail) wrote in message news:<9ff6b66.0210020033.4a746ae at posting.google.com>...
> Greetings again,
> 
> Could someone please explain me why the following simple script failes
> on 4th execute?
> 
> Or is there any simple way to find which sql string execute() really
> send to sql server after receiving two parameters.
> 
> I'm running:
> 
> mx.ODBC.Windows 2.0.5
> Python 2.2.1c2 (#33, Mar 26 2002, 13:04:18) [MSC 32 bit (Intel)] on
> win32
> Microsoft SQL Server 2000
> Windows 2000
> 
> Any help will be appreciated.
> 
> Victor.
> 
> ===>test_sql.py
> import mx.ODBC.Windows
> 
> DB = mx.ODBC.Windows.Connect('db-companies')
> C = DB.cursor()
> 
> sql = """select newsid from news where newsid between 1 and 2"""
> C.execute(sql)
> print 'ok'
> 
> sql = """select newsid from news where newsid between ? and ?"""
> C.execute(sql, (1, 100))
> print 'ok'
> 
> sql = """select newsid from news where newsid in (select newsid from
> news
> where newsid between 1 and 100)"""
> C.execute(sql)
> print 'ok'
> 
> sql = """select newsid from news where newsid in (select newsid from
> news
> where newsid between ? and ?)"""
> C.execute(sql, (1, 100))
> print 'ok'
> <=== end of test_sql.py
> ===> result
> ok
> ok
> ok
> Traceback (most recent call last):
>   File "<stdin>", line 21, in ?
> mxODBC.ProgrammingError: ('37000', 0, '[Microsoft][ODBC SQL Server
> Driver]Syntax error or access violation', 4469)
> <=== end of result

This is by design behaviour of Microsoft ODBC Driver - it doesn't
accept parameters in sub-queries. See discussion in the db-sig for
more details.

Victor.