Frank Millman wrote:
Tim Golden wrote:
Frank Millman wrote:
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)")
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.
Thanks for the response, Tim.
Why do you say that this is not-meaningfully parameterisable?
I want the final WHERE clause to show 'WHERE todate IS NULL'.
Of course, I understand that. What I mean is that if a piece
of SQL say:
WHERE table.column IS ?
then the only possible (meaningful) value ? can have is
NULL (or None, in python-speak). In fact, the IS NULL is
really a sort of unary operator-expression, not an operator
with a value.
As I'm sure you're aware, ANSI treatment of NULLs demands
that neither:
WHERE table.column = NULL
nor
WHERE table.column <> NULL
will succeed, regardless of the value of table.column.
You might just have expected that some combination of an
RDBMS and its driver (ODBC or otherwise) and the Python
dbapi module could get their respective acts together and
-- when faced with a piece of parsed SQL like this:
WHERE table.column = ?
-- could translate that under the covers into:
WHERE table.column IS ?
when the parameter translates to NULL.
But pyodbc / mssql doesn't seem to do that, even if psycopg2
does. As I mentioned before, there doesn't seem to be anything
in the dbapi PEP which mandates or forbids such behaviour, so
I suppose it's implementation-dependent.
As I showed in my first example, pyodbc has no problem converting "'select
?', None" into "select NULL". I don't see why this should be any different.
I think I answered that above re semantics of comparing to NULL.
Also, note that pyodbc isn't itself string-processing your query;
it's calling several layers of db driver to ask MSSQL to do that,
given a combination of parameterised SQL and parameter values.
(
For the record, psycopg2 on Postgresql has no problem with this.
As a workaround, I suppose I could scan the argument list, and if I find a
None, substitute the ? with NULL in the SQL statement itself.
Well, the code I posted previously, although tedious taken to
extremes, will do that. (I have seen and used code like that in
a number of production systems).
It would be interesting to view the SQL statement that pyodbc passes to SQL
Server for execution. Does anyone know if it is possible to set a parameter
anywhere to enable this?
See my comment above about what pyodbc doesn't do to your SQL.
There's a bit of an explanation here, from the Microsoft end
of things:
http://technet.microsoft.com/en-us/library/ms175528.aspx
but this is not an MS-specific concept. It's how most (all?)
database engines work.
TJG
--
http://mail.python.org/mailman/listinfo/python-list
