Prevent NULL values from causing “unintentional” database deletions

Introduction

How does this work? This can happen when you are using using Conn:merge() to update a database. The Conn.merge() function works by writing the values from a (database) table node tree to matching rows in the database. If there are NULL values in some of the table node tree fields these will get written to the corresponding fields in the updated row (which is probably not what you want).

What do you mean by “unintentional” deletions? When NULL values are written to fields in a database table they will overwrite the data in those fields. While this is correct database behavior it is probably not what you intended, hence “unintentional” deletions.

How to I prevent these “unintentional deletions”? This is very simple. Whenever you are using Conn:merge() you must always use node:Remove() to delete the table node tree fields that you don’t want to update in the database. Any empty node tree fields that you do not remove will be set to NULL during the merge process, hence “deleting” any data in those fields in the database table.

When do I need to use this? The typical update scenario is that you want to update some but not all of the data in a row. Therefore you need to use this whenever you are using Conn:merge() to update a database.

Steps to prevent accidental updates [top]

  1. Create a table node tree using db.tables()
  2. Map your data into the node tree
  3. Use the node:remove() function to remove any nodes that you do not want to update in the database

Tip: We are demonstrating how to remove nodes containing NULL values, but this is not a requirement you can remove any node you like, based on any condition you like.

Advantage: Exact control over database updates [top]

Unfortunately the HL7 standard is not very clear when dealing NULLs and unchanged or undefined data ( to be fair this is a tricky area). The upshot however is that HL7 messages do not treat NULLs etc. consistently. Therefore different interfaces will need to deal with this area in a way that is appropriate for the messages that they receive. See Dealing with NULL data from databases for more information about NULL.

Using node:remove() gives you precise control over which fields to update. It makes the choice of which fields to update totally explicit and 100% under the control of person who really understands the interface, the interface programmer actually writing the interface.

This is a big improvement on Chameleon, our previous product, which tried to second guess what was required. See Orthogonal APIs: Rethinking Present but Null for more information.

Example: Mapping non-present data into databases [top]

If you have non-present data within an HL7 feed one useful trick to know with the Translator is the remove() method.

Note: “non-present” means an HL7 field is empty || or |””| so it contains no data (this is a slightly grey area in the HL7 standard).

This shows how one could use it when mapping an HL7 Date of Birth field that may sometimes be empty (not present). Here are couple of screen shots showing what happens when a date is present and what happens when the date is not present.

When a field is present it is mapped into the T.Dob field:

When it is not present it is removed:

Note: The “T” variable is a table node tree created using db.tables() and passed into MapPatient():

If you remove a column like this, there will be no associated SQL generated by the conn:merge{} command. As an interface coder this gives you highly precise control over the behaviour of your interfaces. See Orthogonal APIs – Rethinking Present but Null.

Note: in this case if you had not “removed” Dob then NULL would have been saved to the Dob field in the database (or a “zero date” value before Iguana 5.5.1, see Bug fix Iguana 5.5.1 – nil date values saved to a database by db.merge, they were converted to 1899-12-31.(Iguana 5 documentation) ).

How to remove multiple NULL valued nodes [top]

Typically when we update a database table we only want to update some of the columns, and leave the others unchanged. To preserve the data in other columns they have to be “remove”d for the purpose of conn:merge{}. If numerous columns have to be “remove”d then a Filter function can be used.

For example below code removes all columns specified in ColumnFilter:

local ColumnFilter = {'RemoveMe', 'AndMe'}

for j,ColName = ipairs(ColumnFilter) do
      T:remove(ColName)
end

This function removes all empty sub-nodes from the specified node in a node tree:

-- you can use this method if you are not comfortable with recursion
function Mapping.removeEmptyNodes(Node)
   for i = #Node, 1, -1 do
      if Node[i]:isNull() then
         Node:remove(i)
      end
   end
end

This function uses recursion to remove all empty nodes from node tree:

-- RECOMMENDED: method to remove all empty nodes
-- uses recursion
function Mapping.rRemoveEmptyNodes(Node)
   -- recursively remove empty nodes
   for i = #Node, 1, -1 do
      if Node[i]:isLeaf() then
         if Node[i]:isNull() then
            Node:remove(i)
         end
      else -- recursive call
         Mapping.rRemoveEmptyNodes(Node[i])
      end
   end
end

 

Another useful tool for snarly problems is doing a custom implementation of conn:merge{}.

Tagged: