[egenix-users] SELECTing with placeholders and NULL values

Steve Holden 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[0]
>  else:
>    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
SELECT 1
ELSE
SELECT 2

returns 2.

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

returns 38!

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.

regards
 Steve





More information about the egenix-users mailing list