Re: pyodbc - problem passing None as parameter
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'.
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.
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.
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?
Thanks
Frank
--
http://mail.python.org/mailman/listinfo/python-list
Re: pyodbc - problem passing None as parameter
Tim Goldenwrote: Frank Millman wrote: 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 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). Thanks, Tim, for the detailed explanation. I appreciate your taking the time. It was difficult for me to use the code that you posted, because under my present setup I define my SQL statements externally, and the WHERE clause has to conform to 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 therefore used the workaround that I mentioned above, and it seems to work fine, for both pyodbc and psycopg2. Once again, many thanks. Frank -- http://mail.python.org/mailman/listinfo/python-list
Re: pyodbc - problem passing None as parameter
Frank Millman wrote: Thanks, Tim, for the detailed explanation. I appreciate your taking the time. It was difficult for me to use the code that you posted, because under my present setup I define my SQL statements externally, and the WHERE clause has to conform to 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 therefore used the workaround that I mentioned above, and it seems to work fine, for both pyodbc and psycopg2. I'm glad you managed to get something working. :) I do similar things when needs be, being more of a pragmatist than a purist most of the time. (The should-NULL-be-special debate is occasionally as fiery in the SQL world as the should-self-be-automatic or tabs-or-spaces quesetions are in the Python world). TJG -- http://mail.python.org/mailman/listinfo/python-list
pyodbc - problem passing None as parameter
Hi all
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.
Any assistance will be appreciated.
Thanks
Frank Millman
--
http://mail.python.org/mailman/listinfo/python-list
Re: pyodbc - problem passing None as parameter
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
--
http://mail.python.org/mailman/listinfo/python-list
