Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > Re: pyodbc - problem passing None as parameter

Thread Tools

Re: pyodbc - problem passing None as parameter

Tim Golden
Posts: n/a
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.

Reply With Quote

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
DataGrid - BorderStyle="None" and BorderWidth="None" doesn't work for Firefox David Freeman ASP .Net 8 02-16-2011 11:03 AM
Re: pyodbc - problem passing None as parameter Tim Golden Python 0 10-23-2009 07:57 AM
pyodbc - problem passing None as parameter Frank Millman Python 0 10-22-2009 02:01 PM
testing for valid reference: obj vs. None!=obs vs. obj is not None alf Python 9 12-09-2006 05:00 AM
Is there a built-in method for transforming (1,None,"Hello!") to 1,None,"Hello!"? Daniel Crespo Python 5 11-13-2005 12:52 PM