Problem passing None as parameter

[Frank Millman]

Hi all

I am using 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 - don't know why -

>>> 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] [Microso
ft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be
prepared. (8180
)")
>>>

Any assistance will be appreciated.

Thanks

Frank Millman

[mkleehammer]

The error isn't actually related to the None. Note the "incorrect
syntax", which is what SQL Server reports for invalid SQL.

I'm pretty sure the issue here is using "is ?" instead of "= ?". You
would use "is" with NULL in normal SQL of course, but it wouldn't make
sense with a parameter. A parameter is not guaranteed to be NULL, so
I believe SQL Server considers this an invalid SQL Statement. Try
"today = ?" instead.

[Frank Millman]

Thanks for the response.

I tried '= null'. It does not give a syntax error, but it (correctly)
returns an empty result set, even though there are rows which contain
nulls. I can confirm that by typing the query directly into Sql Server
Management Studio. If I type 'is null' it returns three rows, if I
type '= null' it returns zero rows.

I don't know if this helps, but I ran an ODBC trace of the SQL
command. Here is the relevant part (sorry about the long lines, but
this is what it produces) -

================================
python cbc-c6c ENTER SQLPrepare
HSTMT 00C926B8
UCHAR * 0x00987BBC [ -3] "select * from
ctrl.dirusers where todate is ?\ 0"
SDWORD -3

python cbc-c6c EXIT SQLPrepare with return code 0
(SQL_SUCCESS)
HSTMT 00C926B8
UCHAR * 0x00987BBC [ -3] "select * from
ctrl.dirusers where todate is ?\ 0"
SDWORD -3

python cbc-c6c ENTER SQLNumParams
HSTMT 00C926B8
SWORD * 0x0021FCC4

python cbc-c6c EXIT SQLNumParams with return code 0
(SQL_SUCCESS)
HSTMT 00C926B8
SWORD * 0x0021FCC4 (1)

python cbc-c6c ENTER SQLDescribeParam
HSTMT 00C926B8
UWORD 1
SWORD * 0x00AC7528
SQLULEN * 0x0021FC58
SWORD * 0x0021FC54
SWORD * 0x0021FC50

python cbc-c6c EXIT SQLDescribeParam with return code -1
(SQL_ERROR)
HSTMT 00C926B8
UWORD 1
SWORD * 0x00AC7528
SQLULEN * 0x0021FC58
SWORD * 0x0021FC54
SWORD * 0x0021FC50

DIAG [07009] [Microsoft][ODBC SQL Server Driver]Invalid Descriptor
Index (0)

DIAG [42000] [Microsoft][ODBC SQL Server Driver]Syntax error or
access violation (0)
================================

I have found a workaround (I leave 'is null' in the SQL statement,
without trying to parameterise it), so there is no urgency on this,
but I would be interested to know if there is a solution.

For the record, psycopg2 with Postgresql handles this with no problem.

Thanks again

Frank

[mkleehammer]

I don't think I was clear. When you put a parameter marker (a ?) into
SQL it tells the database that you could pass *anything* later. If
you were *always* going to pass NULL, you wouldn't be using a marker
at all, you'd just use "is null" in your SQL like you tested with.
Since you do want to use a marker, it must mean that sometimes you are
going to pass NULL and sometimes (this is the important part) you are
going to pass a non-NULL value.

Think about that a moment -- since you are sometimes going to pass a
non-NULL value, then "is ?" can't be correct. Say you pass a "3",
then the SQL would be "is 3", which is not correct. I'm not saying
put "= null" in your SQL, I'm saying put "= ?". I think the rule
would be:

1) If you are hardcoding the value in your SQL, use "is" with NULL and
"=" with everything else.

select * from t where orderdate is null

2) If you are using a marker, always use "=".

select * from t where orderdate = ?

[Frank Millman]

mkleehammer wrote:

[...]

>
> 1) If you are hardcoding the value in your SQL, use "is" with NULL and
> "=" with everything else.
>
>    select * from t where orderdate is null
>
> 2) If you are using a marker, always use "=".
>
>   select * from t where orderdate = ?

Thanks for the explanation - it does make sense.

For the record, I got into this situation because I define my SQL
statements externally, and construct them dynamically at run-time. A
WHERE clause is defined using one or more rows of six columns -

Test (WHERE or AND or OR)
Left bracket (either present or blank)
Column Name
Operator
Expression
Right bracket (either present or blank)

I was replacing all expressions with '?' and placing the result of the
expression in the parameter list.

As you point out, if the expression is 'NULL', the operator can only
be 'IS', and vice-versa. I already had to test for an expression of
'NULL' and convert it to None. It was no hardship to change that so
that if the expression is 'NULL' I leave it in the SQL statement
unchanged. Problem solved.

Thanks

Frank