New post
Question
Scott Roth · Jun 1, 2022

Cache Query for HL7 NACK

#Business Operation #HL7 #SQL #Ensemble

I would like to capture any NACK's that is sent back to the Operation. The Operation is already setup to "Save Replies/IndexNotOK's", but I would like to see if we can query Cache and pull those NACK's into an extract.

Is this possible?

Thanks

Scott

Product version: Caché 2018.1
$ZV: Cache for UNIX (IBM AIX for System Power System-64) 2018.1.3 (Build 414U) Mon Oct 28 2019 11:24:02 EDT
0
0 136
Discussion (4)2
Log in or sign up to continue
Jeffrey Drumm · Jun 1, 2022

I've written a query for HL7Spy's SQL Loader that extracts ACKs/NAKs from the IOLog:

Here's the SQL in text form. The method HICG.GetMsg() is an ObjectScript stored procedure written specifically for HL7Spy that returns the message body as a stream for delivery via ODBC. You could adapt the SQL for use with the %SQL.Statement class to return a result set containing the message body IDs, Next() your way through that, open each message with %OpenId() and write it out to a file. Getting just the NAKs will require a bit of message parsing.

SELECT  
  head.ConfigName As ConfigName,
  body.DocType As DocumentType,
  body.Name As BodyName,
  HICG.GetMsg(body.ID) As Message
FROM
  Ens_Util.IOLogObj head
INNER JOIN
  EnsLib_HL7.Message body
ON
  head.InObjectId = body.%ID
WHERE  -- specify start and end time for search here
  head.ID >= (SELECT TOP 1 ID FROM Ens_Util.IOLogObj WHERE TimeReceived >='2022-06-01 00:00:00.000' ORDER BY TimeReceived ASC)
  AND head.ID <= (SELECT TOP 1 ID FROM Ens_Util.IOLogObj WHERE TimeReceived <='2022-06-02 00:00:00.000' ORDER BY TimeReceived DESC)
  AND head.IsInbound = 0
  AND head.InObjectClass = 'EnsLib.HL7.Message'
  AND head.ConfigName = 'To_Outbound_Operation' -- substitute operation name here
0 0
Scott Roth · Jun 2, 2022

Thanks the query is very helpful.

You wouldn't happen to have an example of how to create the query as a Stored Procedure within Cache? I know how to do it in MS SQL, I am just looking for an example on how to do it in Cache. 

0 0
Jeffrey Drumm · Jun 2, 2022

There are a number of examples at Defining and Using Stored Procedures | Using InterSystems SQL | InterSystems IRIS Data Platform 2021.2. The SP I wrote for fetching the message body using its object ID probably won't be much help, since it doesn't actually execute an SQL query ... it's just an ObjectScript classmethod that returns a string, and it's called for every row returned in the containing query. It's available at https://hl7spy.ca/downloads/HICG_HL7.xml if you want to look at it.

0 0
Edward Jalbert · Jun 1, 2022

Not sure if this has any value, its pretty primitive. 

You might be able to do simple query operation and write the RawContent out to a file. 

Rough sql sample

SELECT top 10 *
FROM EnsLib_HL7.Message
where DocType like '%ACK%'
and RawContent like '%MSA|AE%'

0 0