Frank Millman wrote:
I posted the following to the pyodbc google group, but got no reply - it
seems a bit quiet there. I hope someone here can help.
I am using pyodbc version 2.1.6 on Windows Server 2003, connecting to Sql
Server 2005.
This works -
cur.execute('select ?', None)
<pyodbc.Cursor object at 0x00A91368>
cur.fetchall()
[(None, )]
This does not work -
cur.execute('select * from ctrl.dirusers where todate is ?', None)
Traceback (most recent call last):
File "<stdin>", line 1, in <module> pyodbc.ProgrammingError: ('42000',
"[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax
near @P1'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server
Driver][SQL Server]Statement(s) could not be prepared. (8180)")
You may notice that the statement is not strictly DB-API 2.0 compliant.
pyodbc has an extension that allows you to supply arguments directly,
instead off putting them inside a tuple. I have tried with and without a
tuple - the result is the same.
I would estimate that it's because you're using
"where todate is ?" in your WHERE clause, which
can only possibly be followed by a NULL -- thus making
it a not-meaningfully parameterisable query.
Unfortunately, neither will using "where todate = ?" work
helpfully with a None. The dbapi doesn't specify what a
compliant module should do so you probably need to do this:
... WHERE todate = ? OR (todate IS NULL AND ? IS NULL)
or possibly:
... WHERE ISNULL (todate, '<domain-specific-null>') = ISNULL (?,
'<domain-specific-null>')
if you need this kind of functionality.
TJG
--
http://mail.python.org/mailman/listinfo/python-list
