pyodbc - problem passing None as parameter

[Tim Golden]

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