Timing of Database look up
We use a lot of external MS SQL calls to look up numerous things when it comes to HL7. I am running into an issue where the outbound calls are queued but the message is sent onto the next process before it gets a response. How do I get it to wait till the response gets back from the MS SQL call before sending it onto the next process?
Below is a screen shot of what I am trying to explain...Select Research Study executes at 8:33 the message is sent to the next process at 8:34, but the response of the MSSQL call doesn't come till 9:31.
.png)
Comments
Hi
You have to use the SendRequestSync() method for sending the request to your business operation from your business process.
Using the SendRequestSync() the business process will not process the next message until a response is received. Your messages will be queuing in the business process.
Additionally, I would suggest to check why the database response takes so long.
Regards
Stelios
so how would the code block look like for the following... How do I return the EnsLib.SQL.Snapshot?
Thanks
Scott
Can you explain further, I guess I am not following?
Thanks
Scott
Hi Scott,
I've accomplished this in the past by calling the SQL Operation with the SendRequestSync() method in a <code> action rather than using the <call> action. The default is to wait until the response is received.
This will be problematic if you must maintain FIFO (thus setting the Pool Size to 1), since the BPL will stop processing until the response is received. And increasing the pool size could have undesirable side effects other than loss of FIFO ... so you're likely to see horrible message latency at times given the metrics you're reporting.
EDIT: Stelios of course beat me to it ... I stepped away from the keyboard before hitting the Publish button and only realized it some hours later 
Probably something like this (I'm not sure of the full name of the osuwmc.PatientBilling... class, and you probably want to do some sort of exception handling or at least error checking):
setcallrequest=##class(osuwmc.PatientBilling.DataStructures.SelectResearchID).%New()
setcallresponse=##class(EnsLib.SQL.Snapshot).%New()
setcallrequest.ResearchID=context.RschMRN
doprocess.SendRequestSync("PatientBillingDBPoll", callrequest, .callresponse)
setcontext.ClarityRS=callresponse
The default behavior of SendRequestSync is to never timeout, but you can always include a -1 as the 4th parameter to be a bit more descriptive.
You have ASYNC calls to MS SQL operation in your Business process, right?
Add sync elements immediately after them.
It's explained pretty well in the documentation. It allows the synchronization of the call to be retried repeatedly when the timeout is reached. You specify the <call> to sync, the timeout, and whether resync (retry) is allowed. I'm not sure if there's any benefit to using it over the SendRequestSync() method, though.
After you sent ASYNC call in BP add sync activity next. Process would wait for ASYNC call to complete before going further.