Dealing with NULL data from databases

Introduction

We frequently have to deal with issues relating to NULL values in databases. The most common issue is how to treat NULL and BLANK (empty) strings. Are they the same or are they different? The theoretical answer is “different”. The real world answer is that they are treated in many different ways on different systems.

The good news is that Iguana is flexible and can accomodate any treatment of NULLs.

This page shows you some helpful tricks when using NULLs with databases.

  • Using the :IsNull() method
  • Converting NULL to Another Value
  • Always use :nodeValue() to retrieve node data, using :S() or tostring() can corrupt data
  • Converting NULLs in a Node Tree, recursively, after mapping the data

Note: All the examples on this page use NULLs with strings, but the same techniques can be applied to other data types.

Before You Start [top]

A little bit of background on NULL. What it is supposed to mean theoretically. And how to deal with it in the real world…

Theoretically [top]

First: What NULL is supposed to mean from database theory, paraphased in plain(ish) english:

  • NULL is often described as “UNKNOWN”, unfortunately this doesn’t really help to understand how it works
  • A better way to describe NULL is “not set yet”, or “not yet KNOWN”, this gives us some idea of the meaning
  • Another description is “any valid value”, we just don’t know which value yet (until it is entered in the future)

Second: What NULL is not supposed to mean from database theory

  • NULL is not a value, it could be any value, we just don’t know which value it is (yet)
  • NULL is not 0 in a number column, it is a valid number, we just don’t know which number (yet)
  • NULL is not 0 in a date/time column, it is a valid date/time, we just don’t know which date/time(yet)
  • NULL is not an empty string in a text/char field, it is a valid string, we just don’t know that string (yet)

Summary: We don’t know what value NULL represents (remember NULL is not a value), so it could be any valid data value for the data type of the column.

Real World [top]

“NULL is not an empty string” is particularly contentious! Oracle treats NULL and Blank (empty string) as the same, Microsoft SQLServer treats them as different (theoretically correct). However many people (particularly Oracle users) argue that an empty string is a non-value, and is therefore just a “cheats way” to include NULL. There are many discussions about this on the internet.

Though less common NULL is frequently treated (or converted to) 0 in numeric and date/time fields. You will almost certainly come across other (mis)interpretations of NULL in your database travels.

The upshot is that NULL is not treated consistently. Fortunately Iguana is flexible, so you can write code for any treatment of NULLs.

How It Works [top]

Here are some tips on how to handle NULLs with databases.

Using the IsNull() Method [top]

A helpful API call to be aware of is the :isNull() method, we will use this in the other examples. It is used to detect when a leaf node (in a node tree) contains a NULL value.

When you are testing a table node tree, a query node tree or an HL7 node tree you can differentiate between a blank (”) and a NULL value:

As you can see the :isNull() returns “true” for a NULL and “false” for a blank (”).

For various reasons :isNull() does not work for the other three node tree types:

  • XML: The XML standard does not define a NULL representation, so you cannot test for it (:isNull() always returns false)
  • JSON: The :isNull() method does not work on JSON node trees (this is because Iguana uses Lua tables for JSON trees and methods do not work on Lua tables)
  • table grammar (chm legacy parse): NULL is represented as blank (”) in chm node trees, so you cannot differentiate between blank and NULL (:isNull() always returns false)

Sample Code

local function SQLiteTableCreation()
   -- Set "live = true" to delete the Patient table
   -- this can be used to delete a Patient table that is not
   -- working (probably because it has a the wrong fields)
   -- ALTERNATIVELY: if you need to *keep* the current Patient 
   -- table you can change the DB name in db.connect{} in main()  
   conn:execute{sql=[[DROP TABLE Patient;]],
      live = false}  -- change to "true" to delete Patient table
   
   -- create Patient table if it does not exist
   conn:execute{sql=[[
      CREATE TABLE IF NOT EXISTS Patient (
         Id TEXT(255) NOT NULL, LastName TEXT(255),
         GivenName TEXT(255), Race TEXT(255), PhoneHome TEXT(255),
         PhoneBusiness TEXT(255), Religion TEXT(255),
         MaritalStatus TEXT(255), Ssn TEXT(255),
         LicenseNumber TEXT(255), Dob TEXT(255),
         PRIMARY KEY (Id));
      ]],
      live = true}
end

-- create connection once when channel starts
conn = db.connect{   
   api=db.SQLITE, 
   name='test',
   user='root',         -- not required - ignored by SQLite
   password='password', -- not required - ignored by SQLite
   use_unicode = true,
   live = true
}

local function mapPatient(PID, t)
   -- map patient ID to Mrn
   t.patient[1].Id            = tonumber(PID[3][1][1]:nodeValue())
   t.patient[1].LastName      = PID[5][1][1][1]
   t.patient[1].GivenName     = PID[5][1][2]
   t.patient[1].Race          = PID[10][1][1]
   t.patient[1].PhoneHome     = PID[13][1][1]
   t.patient[1].PhoneBusiness = PID[14][1][1]
   t.patient[1].Religion      = PID[17][1]
   t.patient[1].MaritalStatus = PID[16][1]
   t.patient[1].Ssn           = PID[19]
   t.patient[1].LicenseNumber = PID[20][1]
   return t
end

function main(Data) 
   -- create Patient table if it does not exist 
   SQLiteTableCreation()
 
    -- change to "live=true" to delete all rows in Patient table
   conn:execute{sql='DELETE FROM Patient', live=false}

   local Msg, Name = hl7.parse{vmd='demo.vmd', data=Data}
 
   -- table node tree for a database
   local T = db.tables{vmd='demo.vmd',name=Name}  
   T = mapPatient(Msg.PID, T)
   T.patient[1].Race:nodeValue()
   T.patient[1].Race:isNull()
   T.patient[1].Race = nil
   T.patient[1].Race:isNull() -- NULL/nil value 
   T.patient[1].Race = ''
   T.patient[1].Race:isNull() -- empty string
 
   -- query node tree (query result set)
   local R = conn:query{sql="SELECT * from patient"}
   R[1].LastName:nodeValue()
   R[1].LastName:isNull()
   conn:execute{sql="UPDATE patient Set Race =NULL", live=true}
   R = conn:query{sql="SELECT * from patient"}
   R[1].Race:isNull() -- NULL value 
   conn:execute{sql="UPDATE patient Set Race =''", live=true}
   R = conn:query{sql="SELECT * from patient"}
   R[1].Race:isNull() -- empty string
 
   -- HL7 node tree
   local Out = hl7.message{vmd='demo.vmd', name=Name}
   Out:mapTree(Msg)
   Out.PID[10][1][1]:nodeValue()
   Out.PID[10][1][1]:isNull()
   Out.PID[10][1][2]=nil
   Out.PID[10][1][2]:isNull() -- NULL/nil value 
   Out.PID[10][1][2]=''
   Out.PID[10][1][2]:isNull() -- empty string
end

Converting NULL to Another Value [top]

The :isNull() method will tell you whether or not a node back from a database query is null. So we can use this to make a convenient node extension function to convert ‘NULL’ into “” or an empty string. This screenshot shows the technique:

In this case we are mapping to the string “” but as you can see one could choose to map to another value like ” (or even “no business phone” if it was a text field). It’s a convenient solution since the auto-completion in the Translator has no trouble presenting the presentButNull() method once you enter the “:” character.

Sample Code

local function SQLiteTableCreation()
   -- Set "live = true" to delete the Patient table
   -- this can be used to delete a Patient table that is not
   -- working (probably because it has a the wrong fields)
   -- ALTERNATIVELY: if you need to *keep* the current Patient 
   -- table you can change the DB name in db.connect{} in main()  
   conn:execute{sql=[[DROP TABLE Patient;]],
      live = false}  -- change to "true" to delete Patient table
   
   -- create Patient table if it does not exist
   conn:execute{sql=[[
      CREATE TABLE IF NOT EXISTS Patient (
         Id TEXT(255) NOT NULL, LastName TEXT(255),
         GivenName TEXT(255), Race TEXT(255), PhoneHome TEXT(255),
         PhoneBusiness TEXT(255), Religion TEXT(255),
         MaritalStatus TEXT(255), Ssn TEXT(255),
         LicenseNumber TEXT(255), Dob TEXT(255),
         PRIMARY KEY (Id));
      ]],
      live = true}
end

-- create connection once when channel starts
conn = db.connect{   
   api=db.SQLITE, 
   name='test',
   user='root',         -- not required - ignored by SQLite
   password='password', -- not required - ignored by SQLite
   use_unicode = true,
   live = true
}

function node.presentButNull(N)
   if N:isNull() then
      return'""'
      end
   return tostring(N)
end

function main(Data) 
   -- create Patient table if it does not exist 
   SQLiteTableCreation()
 
    -- change to "live=true" to delete all rows in Patient table
   conn:execute{sql='DELETE FROM Patient', live=false}

   local R = conn:query{sql="SELECT * from Patient"}
   R[1].Religion:isNull()
   local A = R[1].PhoneBusiness:presentButNull()
   local B = R[1].Ssn:presentButNull()       
end

Always use :nodeValue() to Retrieve Data [top]

You should always use :nodeValue() rather than :S() or tostring() to retrieve data from leaf nodes in a node tree. This is because :nodeValue() returns the exact string value, whereas :S() and tostring() will encode the data in a way that is “appropriate” to the the field type. Unfortunately this encoding is not always “appropriate”, and can producing undesired results. In this case using :S() or tostring() converts the NULL value to the string “NULL”, which is definitely not we want! We will demonstrate the issue and the solution below.

When you map a NULL value to a node the default behaviour is to clear the field:

In this case the Home Phone Number is NULL so the destination node is cleared.

Where you can run into trouble is when you are converting to string first and doing something like trimming white space.

If you use the :S() function the NULL value to the string “NULL”, which is definitely not we want! Using :nodeValue() converts the NULL value to an empty string, which is much nicer behaviour:

If you wish to preserve NULL values rather than convert them to empty strings, then you can add a condition, like this:

As you can see the target node is cleared, thereby preserving the NULL value.

Sample Code

local function SQLiteTableCreation()
   -- Set "live = true" to delete the Patient table
   -- this can be used to delete a Patient table that is not
   -- working (probably because it has a the wrong fields)
   -- ALTERNATIVELY: if you need to *keep* the current Patient 
   -- table you can change the DB name in db.connect{} in main()  
   conn:execute{sql=[[DROP TABLE Patient;]],
      live = false}  -- change to "true" to delete Patient table
   
   -- create Patient table if it does not exist
   conn:execute{sql=[[
      CREATE TABLE IF NOT EXISTS Patient (
         Id TEXT(255) NOT NULL, LastName TEXT(255),
         GivenName TEXT(255), Race TEXT(255), PhoneHome TEXT(255),
         PhoneBusiness TEXT(255), Religion TEXT(255),
         MaritalStatus TEXT(255), Ssn TEXT(255),
         LicenseNumber TEXT(255), Dob TEXT(255),
         PRIMARY KEY (Id));
      ]],
      live = true}
end

-- create connection once when channel starts
conn = db.connect{   
   api=db.SQLITE, 
   name='test',
   user='root',         -- not required - ignored by SQLite
   password='password', -- not required - ignored by SQLite
   use_unicode = true,
   live = true
}

function main(Data) 
   -- create Patient table if it does not exist 
   SQLiteTableCreation()
 
    -- change to "live=true" to delete all rows in Patient table
   conn:execute{sql='DELETE FROM Patient', live=false}

   local Msg, Name = hl7.parse{vmd='demo.vmd', data=Data}
   local R = conn:query{sql="SELECT * from Patient"}
   local T = db.tables{vmd='demo.vmd',name=Name}  
   local Out = hl7.message{vmd='demo.vmd', name=Name}
 
   -- map to TABLE node tree
   T.patient[1].Id        = R[1].Id
   T.patient[1].PhoneHome = R[1].PhoneHome:S():trimLWS() -- wrong
   T.patient[1].PhoneHome = R[1].PhoneHome:nodeValue():trimLWS() -- right
   T.patient[1].PhoneHome:isNull()
 
   -- map to HL7 node tree
   Out.PID[3][1][1]  = R[1].Id
   Out.PID[13][1][1] = R[1].PhoneHome:S():trimLWS() -- wrong
   Out.PID[13][1][1] = R[1].PhoneHome:nodeValue():trimLWS() -- right
   Out.PID[13][1][1]:isNull()
 
   -- map to variable gives the same error
   local Phone1 = R[1].PhoneHome:S():trimLWS() -- wrong
   local Phone2 = R[1].PhoneHome:nodeValue():trimLWS() -- right
   trace(Phone1, Phone2)
 
   -- condition to preserve/copy NULL values
   if R[1].PhoneHome:isNull() then
      T.patient[1].PhoneHome = R[1].PhoneHome -- preserves NULL
   else
      T.patient[1].PhoneHome = R[1].PhoneHome:nodeValue():trimLWS()   
   end
   T.patient[1].PhoneHome:isNull()
end

Converting NULLs in a Node Tree Recursively [top]

Another trick is to just map the data and then use a little helper function to iterate over the target structure to alter NULLs.

This example changes all the NULLs to empty strings:

As you can see all the NULL values are changed to “”. If you do not want to change all NULL values you could add logic to exclude various fields.

Note: The Kin table is unchanged because it contains no data, as indicated by the rowcount of “0 Rows”:

This is the result of replacing NULLs in an HL7 node tree:

As you can see empty Repeats are unchanged, because they contain no data.

Note: Repeats are similar to tables, and will often map to a database table.

Sample Code

local function SQLiteTableCreation()
   -- Set "live = true" to delete the Patient table
   -- this can be used to delete a Patient table that is not
   -- working (probably because it has a the wrong fields)
   -- ALTERNATIVELY: if you need to *keep* the current Patient 
   -- table you can change the DB name in db.connect{} in main()  
   conn:execute{sql=[[DROP TABLE Patient;]],
      live = false}  -- change to "true" to delete Patient table
   
   -- create Patient table if it does not exist
   conn:execute{sql=[[
      CREATE TABLE IF NOT EXISTS Patient (
         Id TEXT(255) NOT NULL, LastName TEXT(255),
         GivenName TEXT(255), Race TEXT(255), PhoneHome TEXT(255),
         PhoneBusiness TEXT(255), Religion TEXT(255),
         MaritalStatus TEXT(255), Ssn TEXT(255),
         LicenseNumber TEXT(255), Dob TEXT(255),
         PRIMARY KEY (Id));
      ]],
      live = true}
end

local function mapPatient(PID, t)
   t.patient[1].Id            = PID[3][1][1]:nodeValue()
   t.patient[1].LastName      = PID[5][1][1][1]
   t.patient[1].GivenName     = PID[5][1][2]
   t.patient[1].Race          = PID[10][1][1]
   t.patient[1].PhoneHome     = PID[13][1][1]
   t.patient[1].PhoneBusiness = PID[14][1][1]
   t.patient[1].Religion      = PID[17][1]
   t.patient[1].MaritalStatus = PID[16][1]
   t.patient[1].Ssn           = PID[19]
   t.patient[1].LicenseNumber = PID[20][1]
   return t
end

-- create connection once when channel starts
conn = db.connect{   
   api=db.SQLITE, 
   name='test',
   user='root',         -- not required - ignored by SQLite
   password='password', -- not required - ignored by SQLite
   use_unicode = true,
   live = true
}

-- non-leaf nodes with no data will have:
-- -- 0 length
-- -- NULL value
-- THEREFORE the :isLeaf() test was added (line 39)
-- (you can try removing to see the errors)
function node.removeNulls(Node)
   for i=1, #Node do
      if #Node[i] > 0 then
         Node[i]:removeNulls()   
      else
         if Node[i]:isNull() and Node[i]:isLeaf() then
               Node[i] = '""'
         end
      end
   end
end
 
function main(Data) 
   -- create Patient table if it does not exist 
   SQLiteTableCreation()
 
    -- change to "live=true" to delete all rows in Patient table
   conn:execute{sql='DELETE FROM Patient', live=false}

   local Msg, Name = hl7.parse{vmd='demo.vmd', data=Data}
   local Out = hl7.message{vmd='demo.vmd',name=Name} 
   local T = db.tables{vmd='demo.vmd',name=Name}  
 
   -- recursively remove all NULLs from a table node tree
   mapPatient(Msg.PID, T)
   T:removeNulls()
   trace(T)
 
   -- recursively remove all NULLs from the PID segment
   Out:mapTree(Msg)
   Out.PID:removeNulls()
   trace(Out.PID)
end

The removeNulls() method recursively goes down through the segment removing changing ‘NULL’ values to ‘””‘.

What’s Next? [top]

As you can see the Translator APIs give you some very powerful tools to choose exactly how to handle NULL database values.

You can easily adapt the code to your needs, for example, you might want to replace something else other than NULL values.

Please contact support at support@interfaceware.com if you need more help.

Tagged: