NHCCVISREPORT Test Data

In the first step of the tutorial we created two table NHCVIS and NHCVISReport. We put a row of data into NHCVIS here, but have yet to put any data into the NHCCVISRReport table which contains the actual data to be mapped. We will do that now, by running the query below in the Filter component that we just created.

NHCCVISRReport was a rather large table so to insert data into so I took a little more care with the form of the SQL I used to populate the table:

local conn = db.connect{
   api=db.SQLITE,
   name='test', 
   live=true
}

function main(Data)   
   conn:execute{
      live=true,
      sql=[[REPLACE INTO NHCCVISREPORT(
      MESSAGE_ID, 
      MSHMessageControlID, 
      PIDPatientIDExternalIDID,
      PIDPatientIDInternalIDID,
      PIDPatientIDInternalIDCheckDigit,
      PIDPatientNameFamilyName,
      PIDPatientNameGivenName,
      PIDDateTimeofBirth,
      PIDSex,
      PIDPatientAccountNumberID,
      PVVisitNumberID, 
      PVAdmitDateTime,
      OBRFillerOrderNumberEntityIdentifier,
      OBRUniversalServiceIDIdentifier,
      OBRUniversalServiceIDText, 
      OBRObservationDateTime,
      OBRPlacerField,
      OBXSetIDOBX,
      OBXValueType,
      OBXObservationIdentifierIdentifier,
      OBXObservationValue,
      OBXObservResultStatus,
      OBXSetIDOBX1,
      OBXValueType1,
      OBXObservationIdentifierIdentifier1,
      OBXObservationValue1,
      OBXObservResultStatus1,
      OBXSetIDOBX2,
      OBXValueType2,
      OBXObservationIdentifierIdentifier2,
      OBXObservationIdentifierText2,
      OBXObservationValue2,
      OBXObservResultStatus2
   )
      VALUES(
      1,           -- MESSAGE_ID
      1,           -- MSHMessageControlID
      '44',        -- PIDPatientIDExternalIDID
      '343',       -- PIDPatientIDInternalIDID
      '43',        -- PIDPatientIDInternalIDCheckDigit
      'Smith',     -- PIDPatientNameFamilyName
      'Fred',      -- PIDPatientNameGivenName
      '19901212',  -- PIDDateTimeofBirth
      'M',         -- PIDSex
      '534',       -- PIDPatientAccountNumberID
      '434',       -- PVVisitNumberID
      '20101211',  -- PVAdmitDateTime
      '10',        -- OBRFillerOrderNumberEntityIdentifier
      '121',       -- OBRUniversalServiceIDIdentifier
      'Some text', -- OBRUniversalServiceIDText
      '20111210',  -- OBRObservationDateTime
      '3',         -- OBRPlacerField
      '1',         -- OBXSetIDOBX
      'TX',        -- OBXValueType
      '1',         -- OBXObservationIdentifierIdentifier
      'Wow',       -- OBXObservationValue
      'GOOD',      -- OBXObservResultStatus
      '1',         -- OBXSetIDOBX1
      'TX',        -- OBXValueType1
      '1231',      -- OBXObservationIdentifierIdentifier1
      '444',       -- OBXObservationValue1
      '434',       -- OBXObservResultStatus1
      '2323',      -- OBXSetIDOBX2
      '2323',      -- OBXValueType2
      'sdsd',      -- OBXObservationIdentifierIdentifier2
      'Text 2',    -- OBXObservationIdentifierText2
      'Another',   -- OBXObservationValue2
      'BAD'        -- OBXObservResultStatus2
   )
      ]]
   }

   -- check the insert worked
   conn:query('SELECT * FROM NHCCVISREPORT')
end

Notice I have again used the REPLACE keyword from SQLite and I have used Lua’s ability to represent a multiple line string using a [[…]] notation for convenience.

I included a SELECT statement to check that the data is inserted:

Once the insert was confirmed I replaced the the insert with something close to the real code for the Filter:

In the above screen shot I took advantage of the auto completion to see the resulting data coming back from the SELECT query. Notice how the query is generated dynamically off the the ‘1’ passed in the Data variable.

Here is the code in format you can copy and paste in:

local conn = db.connect{
   api=db.SQLITE,
   name='test', 
   live=true
}

function main(Data)
   local R = conn:query{
      live=true,
      sql="SELECT * FROM NHCCVISREPORT WHERE MESSAGE_ID = "..Data
   }
end

Next Step?

Now we have inserted some test data into the NHCCVISREPORT table. The next stage is to create an empty HL7 message and start mapping data into it.