[egenix-users] SELECTing with placeholders and NULL values

[Charles Bearden]

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.

Thanks in advance,

Chuck Bearden
Systems Analyst III
School of Health Information Sciences
University of Texas at Houston
713.500.3954 (voice)
713.500.3907 (fax)
Charles.F.Bearden at uth.tmc.edu