I've added a query to the HICG.HL7 class that lets you leverage Ensemble's HL7 Search Tables to select messages by the fields specified for indexing. The new version is available on the HL7 Spy website.

An example, using the default search table to select messages by PatientID/MRN:

-- region 8 - message rows by SearchTable

SELECT
    tbl.BodyId,
    msg.TimeCreated,
    msg.Name,
    msg.DocType,
    HICG.GetMsg(msg.Id) As Message
    FROM
        HICG.TblSrch('EnsLib.HL7.SearchTable','PatientID','4444') tbl
    INNER JOIN
        EnsLib_HL7.Message msg
    ON
        tbl.BodyId = msg.Id
-- endregion

So to clarify ... the class method isn't so much a stored procedure as a custom function. It should return an HL7 message as a  string, which will be represented as a column in the result set generated by a SELECT. So technically, the SQLCODE I'm getting back is correct for what I'm doing, which is calling a function against a column value in the query. If the function fails, the query fails, and the reason for the query failure is the function failure. That's how it's coming back to ADO/ODBC when I force a failure, and the reason I asked the question in the first place is that sometimes I'm just not too smart :D

Hi Eduard, and thanks!

 I had already tried that option, but discarded it because the SQL code returned is <-149>:<SQL Function encountered an error>, with my error code and text as a subordinate (child?) error. Is there any way to set the "parent" SQLCODE and associated error text? Or is that effectively the "query" error, with the child error generated by the SqlProc?

Execution details for routing rules are located in Ens.Rule.Log. The available values are ID, ActivityName, ConfigName, CurrentHeaderId, DebugId, EffectiveBegin, EffectiveEnd, ErrorMsg, IsError, Reason, ReturnValue, RuleName, RuleSet, SessionId, and TimeExecuted.

In the SQL facility, you can query Ens_Rule.log:

SELECT ID, ConfigName, CurrentHeaderId, RuleName, RuleSet, SessionId, TimeExecuted FROM Ens_Rule.Log

Assuming the stream contains the normal $C(13) segment delimiters:

Class User.HL7.Stream Extends %RegisteredObject
{

ClassMethod GetCounts(pStream As %Stream.FileCharacter, Output pMsgCount As %Numeric, Output pSegCounts As %ArrayOfDataTypes) As %Status
{
    Do pStream.Rewind()
    Set pStream.LineTerminator = $C(13)
    Set pSegCounts = 0
    While 'pStream.AtEnd
    {
        Set tLine = pStream.ReadLine()
        // Remove leading control characters
        Set tSeg = $ZSTRIP(tLine,"<C")
        // Get the segment name
        Set tSegName = $EXTRACT(tSeg,1,3)
        If tSegName '= ""
        {
            If '$DATA(pSegCounts(tSegName))
            {
                // We have a new subscript
                Set pSegCounts = pSegCounts + 1
                Set pSegCounts(tSegName) = 1
            }
            Else
            {
                Set pSegCounts(tSegName) = pSegCounts(tSegName) + 1
            }
        }
    }
    Set pMsgCount = pSegCounts("MSH")
    Return $$$OK
}

}


Call the classmethod with the stream as follows:

Do ##class(User.HL7.Stream).GetCounts(stream,.Msgs,.Counts)


Counts will be subscripted by the segment names found in the message stream. In the above example, you'll find the occurrence count of FT1 segments in Counts("FT1") and the number of messages will be in Counts("MSH"). The value returned in Msgs is the same as Counts("MSH").

If you have access to Caché terminal, you can run run queries that won't time out:

(the sample below assumes your namespace is "PROD"; just substitute whatever your production's namespace is for that).

USER> zn "PROD"

PROD> d $system.SQL.Shell()

SQL Command Line Shell
----------------------------------------------------

The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
PROD>>SELECT COUNT(*) AS AlertCount FROM Ens.MessageHeader WHERE MessageBodyClassName = 'Ens.AlertRequest'

AlertCount
2205

PROD>>Q

PROD>

So ... if you don't have BodiesToo checked, you most likely have lots of orphaned message bodies taking up database space. And KeepIntegrity is probably retaining a lot of message headers (and associated bodies) that you don't care about anymore. There are reasons you would not want to turn KeepIntegrity off in earlier versions of Caché/Ensemble, like pre-2015 releases. If you're on a release more modern than that and you don't need to worry about messages with parent/child relationships (certain batch types, for example), you can probably turn that off.

There are a couple of articles regarding the management of orphaned bodies here on DC. Might be worthwhile to peruse them :)

MAX(ID) isn't necessarily the record count. Try a "select count(*) from Ens.AlertRequest" query and see what you get. Compare that to "'select count(*) from Ens.MessageHeader where MessageBodyClassName = 'Ens.AlertRequest'"

If the numbers are in line with the MAX(ID), then my suspicion is that you either don't have "BodiesToo" checked or do have "KeepIntegrity" checked in your purge process configuration. Either of those may be keeping old Ens.AlertRequest bodies around.

If you could provide a little more detail on your use case, there may be a solution that doesn't involve developing a custom service.

For example, you could configure the service with a filename pattern that would retrieve all matching files in the target path, but discard, via a routing rule, any that do not meet the date criteria. The name of the file should be available in the Source property of the message object.

Define them as properties in your task class:

Class User.Task.MessageArchive Extends %SYS.Task.Definition
{
/// Base directory for the archived files
Property BaseDir As %String [ InitialExpression = "/hsf/archive/" ];
/// The date of the 24 hour period from which the messages will be selected (midnight to midnight)
Property DaysOld As %Integer [ InitialExpression = 23 ];
/// When selected, messages received from services will be archived
Property MessagesInbound As %Boolean [ InitialExpression = 1 ];
/// When selected, messages sent to operations will be archived
Property MessagesOutbound As %Boolean [ InitialExpression = 1 ];
/// Send Notification Email
Property NotifyByEmail As %Boolean [ InitialExpression = 0 ];

These properties can then be referenced in your task's methods with the .. prefix notation.