Re: pyodbc - problem passing None as parameter

Discussion in 'Python' started by Tim Golden, Oct 22, 2009.

  1. Tim Golden

    Tim Golden Guest

    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
    Tim Golden, Oct 22, 2009
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Frank Millman

    pyodbc - problem passing None as parameter

    Frank Millman, Oct 22, 2009, in forum: Python
    Replies:
    0
    Views:
    279
    Frank Millman
    Oct 22, 2009
  2. Tim Golden
    Replies:
    0
    Views:
    607
    Tim Golden
    Oct 23, 2009
  3. length power
    Replies:
    2
    Views:
    59
    Rustom Mody
    Apr 10, 2014
  4. Skip Montanaro
    Replies:
    0
    Views:
    41
    Skip Montanaro
    Apr 10, 2014
  5. Johannes Schneider

    Re: why i have the output of [None, None, None]

    Johannes Schneider, Apr 10, 2014, in forum: Python
    Replies:
    0
    Views:
    37
    Johannes Schneider
    Apr 10, 2014
Loading...

Share This Page