Returning a Stream in an ObjectScript Custom SQL Function

Question

Question

Jeffrey Drumm · Oct 23, 2020

#Ensemble #HealthShare #InterSystems IRIS for Health

I'm attempting to extract some very large messages from Interoperability/Ensemble, and I'm just not getting something right ...

The closest I've come to getting something useful is this:

ClassMethod GetHL7Msg(pId As %String) As %Stream.TmpCharacter [ SqlName = GetMsg, SqlProc ]
{
    Set tHl7 = ##class(EnsLib.HL7.Message).%OpenId(pId,,.tSC)
    Throw:$$$ISERR(tSC) ..GetErr(-400, "HL7 Message with ID "_pId_" Not Found.")
    Set tMsg = ##class(%Stream.TmpCharacter).%New()
    Set tSC = tHl7.OutputToLibraryStream(.tMsg)
    Do tHl7.%Close()
    Return tMsg
}

When I call this via an ODBC query:

SELECT ID, SourceConfigName, TargetConfigName, HICG.GetMsg(MessageBodyId) As Message FROM Ens.MessageHeader 
  WHERE 
    MessageBodyClassName = 'EnsLib.HL7.Message' AND
    TargetConfigName = 'OptumMdmOut'
  ORDER BY ID ASC

I get a single, properly formed row back (there should be 1,000), and an error in the xDBC Errors log in IRIS:

Any thoughts?

Discussion (15)1

Log in or sign up to continue

Robert Cemper · Oct 25, 2020

Hi Jeff;

all you have to do is to set the Stream location to a Global in *TEMP Database.
Example:

Ok ... so this actually works:

ClassMethod GetHL7Msg(pId As %String) As %Stream.GlobalCharacter [ SqlName = GetMsg, SqlProc ]
{
    Set tHl7 = ##class(EnsLib.HL7.Message).%OpenId(pId,,.tSC)
    Throw:$$$ISERR(tSC) ..GetErr(-400, "HL7 Message with ID "_pId_" Not Found.")
    Set tMsg = ##class(%Stream.GlobalCharacter).%New()
           SET tMsg.%Location="^mtemp.JEFF"
    Set tSC = tHl7.OutputToLibraryStream(.tMsg)
    Do tHl7.%Close()
    If tSC Set tSC=tMsg.%Save()
    Throw:$$$ISERR(tSC) ..GetErr(-400, $system.Status.GetErrorText(tSC))
    Return tMsg."%%OID"
}

>>>>>

3 0

score 2 · Answer 1 · 2020-10-23T15:12:53-04:00

to understand what is happening:
Do you get the same error using ?

Set tMsg = ##class(%Stream.GlobalCharacter).%New()

score 0 · Answer 2 · 2020-10-23T15:59:38-04:00

Jeffrey Drumm · Oct 23, 2020

Hi Robert,

Yes, I get the exact same error whether I use %Stream.GlobalCharacter or %Stream.TmpCharacter.

0 0

score 2 · Answer 3 · 2020-10-24T02:52:00-04:00

So my next try would be to add %Save of the Stream Object before closing tHl7

ClassMethod GetHL7Msg(pId As %String) As %Stream.TmpCharacter [ SqlName = GetMsg, SqlProc ]
{
    Set tHl7 = ##class(EnsLib.HL7.Message).%OpenId(pId,,.tSC)
    Throw:$$$ISERR(tSC) ..GetErr(-400, "HL7 Message with ID "_pId_" Not Found.")
    Set tMsg = ##class(%Stream.GlobalCharacter).%New()
    Set tSC = tHl7.OutputToLibraryStream(.tMsg)

        IF tSC SET tSC=tMsg.%Save()          

    Do tHl7.%Close()
    Return tMsg
}

score 0 · Answer 4 · 2020-10-24T08:03:00-04:00

Hi Robert,

Same result ... a single row and the same error in the IRIS xDBC log:

2020-10-24 01:35:59 [SQLCODE: <-412>:<General stream error>] [Error: <<INVALID OREF>PrepareStream+6^%SYS.SQLStreamSRV>] [Location: <ReadStreamODBC::PrepareStream>] [Client info: <Username: Jeff, Node Name: WIN10X64-VM01, IP Address: 10.208.8.90, Executable Name: HL7Spy.exe, Internal Function: AS>] [%protocol: <59>] $Id: //adhocs-iris/2020.1.0.217.1/HICG_LLC_001/kernel/common/src/aclass.c#1 $ 21256 11

The method works fine if I define the return type as %String, but only until the query fetches a row with a message larger than MAXSTRING in size. I of course have to Return tMsg.Read() to get the output. When it hits a message that's too large, I get a <MAXSTRING> error in the IRIS xDBC log, but no error is set for the ODBC (actually ADO) client.

score 2 · Answer 5 · 2020-10-24T08:59:00-04:00

%SYS.SQLStreamSRV.obj is a deployed routine / class so we can't look into its details.
<INVALID OREF> expects a (stream) Object, but it doesn't exist.
So an additional check might be required. Or you force some content for testing.

ClassMethod GetHL7Msg(pId As %String) As %Stream.TmpCharacter [ SqlName = GetMsg, SqlProc ]
{
    Set tHl7 = ##class(EnsLib.HL7.Message).%OpenId(pId,,.tSC)
    Throw:$$$ISERR(tSC) ..GetErr(-400, "HL7 Message with ID "_pId_" Not Found.")
    Set tMsg = ##class(%Stream.GlobalCharacter).%New()
    Set tSC = tHl7.OutputToLibraryStream(.tMsg)

     ;;  Throw:$$$ISERR(tSC) ..GetErr(-400, "HL7 Message with ID "_pId_" failed.") 
         IF 'tMsg.Size DO tMsg.Write("*** no message for ID:"-pId_" ***")
         SET tSC=tMsg.%Save()

    Do tHl7.%Close()
    Return tMsg
}

So you should have always a Stream Object

score 0 · Answer 6 · 2020-10-24T11:19:42-04:00

I'll give that a try, but I'm not optimistic. The data is there; the stream object is getting populated because I can Read() it and return the data as a %String ...

score 1 · Answer 7 · 2020-10-24T12:11:35-04:00

Jeff,
if this is still happening I suggest contacting WRC.
They have access to the sources and more diagnostic tools.

score 0 · Answer 8 · 2020-10-24T12:17:32-04:00

Also, I should add that although I had said that I wasn't seeing any errors on the client side (aside from not getting the entire result set), that's not exactly true. No errors were displayed in the client application's GUI, but it was logging them to its log file. In examining that log file I found what appears to be a stream-related entry in the connection string: StreamPrefetch=0. I can't find any documentation on it other than it being mentioned (without a description) in the Using .NET and the ADO.NET Managed Provider with Cache section. I'm assuming it takes a number of bytes(?) as an argument, since all the other boolean entries take True/False as values ...

score 2 · Answer 9 · 2020-10-24T15:26:00-04:00

StreamPrefetch looks related but it is totally related to the .NET end (googled SQL prefetch in .NET),
while <INVALID OREF> is clearly an issue @Ensemble end.
I'm not too deep in the external ODBC world but I assume that collecting the resultset and
transmitting it to .NET [ %SYS.SQLStreamSRV ] could happen asynchronously in different processes.
So I doubted %Stream.TempCharacter which is bound to a process private Global vs. a standard Global visible also to other processes. And only the final %Save nails it down.

I wonder if you experience the same problem if you run your query from
terminal prompt: do $system.SQL.Shell()
This can signal if the ODBC connection plays a role at all.

score 0 · Answer 10 · 2020-10-25T13:50:00-04:00

No errors running the query from $system.SQL.Shell(), but I get the stream OREF rather than the message in the query result:

ID      SourceConfigName        TargetConfigName        Message
191344  InEpicMdm_Router        OutOptumMdm     23@%Stream.GlobalCharacter
191348  InEpicMdm_Router        OutOptumMdm     25@%Stream.GlobalCharacter
191352  InEpicMdm_Router        OutOptumMdm     9@%Stream.GlobalCharacter
191356  InEpicMdm_Router        OutOptumMdm     23@%Stream.GlobalCharacter
191360  InEpicMdm_Router        OutOptumMdm     25@%Stream.GlobalCharacter
191364  InEpicMdm_Router        OutOptumMdm     9@%Stream.GlobalCharacter
191368  InEpicMdm_Router        OutOptumMdm     23@%Stream.GlobalCharacter
191372  InEpicMdm_Router        OutOptumMdm     25@%Stream.GlobalCharacter
191376  InEpicMdm_Router        OutOptumMdm     9@%Stream.GlobalCharacter
191380  InEpicMdm_Router        OutOptumMdm     23@%Stream.GlobalCharacter

ODBC seems to be doing a little magic in the background to return the stream data, but it's not good magic at the moment.

EDIT: Turns out the ODBC wrangler needs a persistent object's "%%OID" rather than an OREF to fetch the stream. See the eventual working method here.

score 0 · Answer 11 · 2020-10-25T12:22:00-04:00

Try this

Class test.SQL
{

ClassMethod GetMsg(length As %Integer) As %Stream.TmpCharacter [ SqlProc ]
{
    Set stream = ##class(%Stream.TmpCharacter).%New()
    Set chunkLength = 32000
    Set chunk = $tr($j("", chunkLength)," ", "A")
    
    If length>=chunkLength {
        For i=1:chunkLength:length {
            Do stream.Write(chunk)
        }
    }
    
    Set tailLength = length#chunkLength
    Do:tailLength>0 stream.Write($e(chunk, 1, tailLength))
    
    Set sc = stream.%Save()

    Quit stream  //."%%OID" <- also works for persisted streams
}
}

Worked for me with this SQL (in SMP):

SELECT test.SQL_GetMsg(10), test.SQL_GetMsg(1)
UNION
SELECT test.SQL_GetMsg(10), test.SQL_GetMsg(2)

score 0 · Answer 12 · 2020-10-25T13:37:45-04:00

Hi Eduard,

I'm having a bit of trouble applying your solution to my problem. I have only one method in EnsLib.HL7.Message that seems to populate a %Stream.TmpCharacter data type, and it doesn't allow me to "chunk" the data. In the code I posted, I should be returning a %Stream.TmpCharacter object functionally identical to yours. I think I am, but suspect that the ODBC handler in IRIS isn't finding the stream.

score 0 · Answer 13 · 2020-10-25T16:38:00-04:00

Ok ... so this actually works:

ClassMethod GetHL7Msg(pId As %String) As %Stream.GlobalCharacter [ SqlName = GetMsg, SqlProc ]
{
    Set tHl7 = ##class(EnsLib.HL7.Message).%OpenId(pId,,.tSC)
    Throw:$$$ISERR(tSC) ..GetErr(-400, "HL7 Message with ID "_pId_" Not Found.")
    Set tMsg = ##class(%Stream.GlobalCharacter).%New()
    Set tSC = tHl7.OutputToLibraryStream(.tMsg)
    Do tHl7.%Close()
    If tSC Set tSC=tMsg.%Save()
    Throw:$$$ISERR(tSC) ..GetErr(-400, $system.Status.GetErrorText(tSC))
    Return tMsg."%%OID"
}

I'm concerned that I'm using a persisted object. Does it automatically get killed once I leave the scope of the function (I'm assuming not, see the last paragraph)? Is there a way to force it to temp storage?

It's also very slow, but 1) I'm currently connecting to the database over a VPN connection, and 2) the average message size is around 30KB, with some messages up to 3MB. It took 5 minutes to return 1000 rows.

Looks like ^CacheStream is currently using almost 600MB.

score 0 · Answer 14 · 2020-10-25T22:55:26-04:00

That works, thanks!

So the method is now doing everything I need. In its previous incarnation I was just returning a %String, and although it got me into trouble with messages larger than MAXSTRING, it was a LOT faster ... easily 3x to 5x faster.