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 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?
to understand what is happening:
Do you get the same error using ?
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
Hi Robert,
Same result ... a single row and the same error in the IRIS xDBC log:
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.
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:
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
Worked for me with this SQL (in SMP):
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:
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:
>>>>>
.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.
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue