I'm always on the lookout for tools that make the development and testing of my interfaces more efficient. A couple of years ago I came across HL7 Spy, from Inner Harbour Software. It quickly became my go-to tool for running message comparison reports for interface engine migrations, message statistics gathering, and troubleshooting message receipt and delivery. It also offered enhanced functionality for things like fetching messages via sftp that other tools don't provide.
I've recently been working with HL7 Spy's author, Jon Reis, to enable support for fetching messages directly from the Ensemble message store. Its SQL Loader feature now has native Caché/IRIS support, and I've contributed a small server-side class to support the extraction of messages using it.
Version 2020.1 and higher, downloadable from this page, includes this native support along with a sampling of SQL Loader queries that facilitate the retrieval of messages from Ensemble. This article provides a walk-through for configuring the latest release and shows some selected queries.
Once you've installed the beta, locate the SQL Loader tab at the bottom of the window and select it:
The bottom pane will change to an SQL editor window with a Connect button in the upper left corner:
Click Connect, then create a new connection profile by clicking the Add button, and filling in the form. Note: If your admin has implemented SSL/TLS security on the SuperServer port, include the string Ssl=True in the Additional Parameters text box (#8 in the image below); also, the default ports (#7) are 51773 for IRIS and 1972 for Caché:
Next, click the Test button to verify that the connection works:
Finally, download the class HICG_HL7.xml, import it to your production namespace using Studio, and compile it. This class provides a SQL custom function called HICG.GetMsg() that takes a message body ID as its argument and populates a field in the returned result set with that ID's HL7 message.
That's it for installation and configuration. You can now take advantage of IRIS/Caché SQL to select messages based on values in Ens.MessageHeader or EnsLib.HL7.Message. A few examples:
1. Messages delivered to a Routing Process from a Service within a specified time frame
Click on each row in the bottom pane and HL7 Spy will display the related message in the upper pane.
2. Suspended Messages
You can drag column headers to the grey bar at the top of the table pane, and records will be grouped/sub-grouped by the distinct values in those columns:
4. Selecting by both Message Header and Body Properties
Tips and Caveats
- Selecting messages by body (EnsLib.HL7.Message) properties seems to require a bit of a hack. You must provide a WHERE clause that starts with a selection by ID, even something as simple as ID > 0; in the case of a JOIN, that can be either the Header or Body ID.
- There's no reasonably performant option for selection by body content (other than the Identifier and Name properties, which are derived from MSH:10 and MSH:9 respectively). You should use IDs and other indexed properties such as SourceConfigName/TargetConfigName and Status to fetch the messages, then use HL7 Spy's powerful selection and filtering tools to find the specific message(s) you're looking for.
- EDIT (10 January 2020): This is still generally a good idea, but I've incorporated the ability to do SearchTable queries as part of the message selection SQL query. That can speed things up ... see the comment below this article.
- Searching by a Date/Time range using TimeCreated is much faster if you use the method shown in example #1.
Finally ... I'll be updating this article as new discoveries are made and features added.
Thanks for reading