[DB-SIG] mx.ODBC.Windows - execute() parameter substitution raisesException

Krjukov Victor VKryukov@ufg.com
Wed, 2 Oct 2002 14:40:46 +0400


> Could be a limitation of the MS SQL Server or its ODBC
> driver. The SQL you are using looks strange though: why
> would you want to nest identical SQL queries like the
> one above ?

This SQL statement is strange indeed: my initial version was, of course,
more complicated and includes two tables:

select * from table_news where news_id in=20
(select newsid from news where date between '1979-1-1' and '1979-2-1')

, where table_news has a foreign key news_id linked to primary key
newsid in table news. But I have simplified it in such a way that it
need only one table with only one field ;), to be sure it's a problem in
execute() rather in some other place (in my initial version this
exception occurs, too).

The problem is that I don't face the problem when I don't use subqueries
as you may find from two first execute()'s.

> BTW, have you modified mxODBC in some way ? The line number
> indicated in the error message doesn't map to a valid line
> in the C code.

No, I'm using the binary distribution version and haven't modified it
(actually I don't even know how to modify .pyd files ;).

I still don't understand what 'line in the C code' you are talking
about; should the error message from unmodified mxODBC contained some
information about line in C source in which error occurs? BTW, is it
possible that this binary distribution is NOT compiled with debugging
support? I've tried to use python -d switch to turn debugging as I have
read in your documentation, but it doesn't lead to mxODBC.log file
creation in current dir.

The line numbers from previous example come from executing this script
in Emacs and copy-pasting result from Emacs *Python-output* buffer;
sorry for that. If I execute this in shell I'll receive:
=3D=3D=3D>
ok
ok
ok
Traceback (most recent call last):
  File "S:\News parser\test_sql.py", line 21, in ?
    C.execute(sql, (1, 100))
mxODBC.ProgrammingError: ('37000', 0, '[Microsoft][ODBC SQL Server
Driver]Syntax
 error or access violation', 4469)
<=3D=3D=3D

Can you please explain how parameter substitution works in execute()?
Does this function do substitution itself or does it use some ODBC
driver facility? In former case, it seems much like execute() bug
because 3rd execute() contains statement that should occur after
parameter substitution.

Anyway thank you very much Marc-Andre for your useful set of utilities
like mx.ODBC.Windows and mx.DateTime which I use regularly.

>=20
> --=20
> Marc-Andre Lemburg
> CEO eGenix.com Software GmbH
> ______________________________________________________________
> _________
> eGenix.com -- Makers of the Python mx Extensions:=20
> mxDateTime,mxODBC,...
> Python Consulting:                              =20
> http://www.egenix.com/
> Python Software:                   =20
> http://www.egenix.com/files/python/
>=20
>=20