Returning a Stream in an ObjectScript Custom SQL Function
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 ASCI get a single, properly formed row back (there should be 1,000), and an error in the xDBC Errors log in IRIS:
.png)
Any thoughts?
Comments
to understand what is happening:
Do you get the same error using ?
Set tMsg = ##class(%Stream.GlobalCharacter).%New() Hi Robert,
Yes, I get the exact same error whether I use %Stream.GlobalCharacter or %Stream.TmpCharacter.
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
}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.
%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
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 ...
Jeff,
if this is still happening I suggest contacting WRC.
They have access to the sources and more diagnostic tools.
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 ...
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.
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.
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) 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.
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.
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"
}>>>>>.png)
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.