[egenix-users] SELECTing with placeholders and NULL values
sholden at holdenweb.com
Wed Jun 23 11:34:03 CEST 2004
Charles Bearden wrote:
>I'm writing functions to query database tables, passing in values as
>arguments. I am formulating the SELECT statements with '?' placeholders
>so that mx.ODBC handles the quoting etc. I'm having problems returning
>wanted rows when one of the values passed in and interpolated in a
>placeholder is a None (i.e. an SQL NULL, according to the Python DB API
>2.0). Here is a sample function:
>def getPersNameId(f_167_00_abc, f_167_00_d):
> stmnt = '''
> SELECT ID
> FROM bibPersonName
> WHERE f_167_00_abc = ?
> AND f_167_00_d = ?
> mload._cur.execute(stmnt, (f_167_00_abc, f_167_00_d))
> pnid = mload._cur.fetchone()
> if pnid:
> return pnid
> return None
>The value of 'f_167_00_d' is usually but not always NULL (passed in as
>Python None). In this case, the function fails to return the row with
>the wanted value in 'f_167_00_abc' and NULL in 'f_167_00_d'.
>'f_167_00_abc' is not a unique value, and the 'f_167_00_d' (whether NULL
>or some string) is essential for qualifying the row uniquely.
>'f_167_00_d' is a non-trivial value, whether NULL or some string.
>How can NULL values be used with the standard placeholders in mx.ODBC?
>I'm probably missing something obvious.
I'm not sure you are. The point about NULL is that it introduces a
three-valued logic into SQl comparisons. An explicit comparison with
NULL can never be true - as far as I know, even "NULL = NULL" will
evaluate to NULL. In fact, SQL Query Analyzer proves this:
if NULL = NULL
So, for example, in a SQL Server table near me:
SELECT COUNT(*) FROM OnePlus WHERE TGUID = NULL
returns zero, even though
SELECT COUNT(*) FROM OnePlus WHERE TGUID IS NULL
In other words, the only explicit test allowable for NULL values is "IS
NULL", and you are going to have to phrase your queries to allow that.
The only alternative would be to retrieve the rows and then perform the
testing in Python, which is likely to be much less efficient. Of course,
you know much more about the problem than me, so you may be able to find
some way to easily get what ytou want, but I suspect you may end up
havikng to use different statements when arguments are None and non-None.
More information about the egenix-users