SQL Outbound Adapter - Execute Procedure call returns null/undefined for Output value.
We have a stored procedure with one input parameter and one input/output parameter.
declare @hl7message nvarchar(max) = 'Pass the hl7 message here',
@output nvarchar(max);
EXEC sp_InsertHl7 @hl7message, @outputresult = @output OUTPUT;
print @output;
@output value will be "Pass" or "Fail"
Below code is inserting the HL7 successfully into the target DB, but not returning the output value as expected.
set arrParam(1,"IOType") = 1
set arrParam(1,"SqlType") = $$$SqlLongVarchar
set arrParam(1,"CType") = $$$SqlWChar
set arrParam(1,"Prec") = 8000
set arrParam(1,"LOB") = 1
set arrParam(1,"UseLOBLen")= 1
set arrParam(2) = ""
set arrParam(2,"IOType") = 2
set arrParam(2,"SqlType") = 12
Set tSC = ..Adapter.ExecuteProcedureParmArray(,.tOutput,tQuery,"ib",.arrParam)
Set sOut = tOutput.GetAt(1)
sOut is always null. I tried to change the second parameter to output (IO type - 4) instead input/output (IO type - 2). But no luck. sOut returns as 'undefined',
When I assigned a wrong data type to the first parameter other than $$$SqlLongVarchar, then the HL7 message was not parsed properly - in this case .tOutput returned the correct output value "Fail'.
But if I assign the second parameter values as above code (Which is correctly inserting HL7 into the DB) then .tOutput returns null.
Resolution - $$$SqlLongVarchar type of the input parameter was causing the issue as we need to pass the HL7 message as a %Stream.GlobalCharacter and LOB (larger object). But still not sure why it was affecting the output value. $$$SqlLongVarchar is the proper type to define the nvarchar(max) sql type.
I change the code as below for the input and input/output parameter; .tOutParams is returning the stored procedure output value as expected.
Set arrParam = 2
//Defining the input parameter
set arrParam(1)= pReq.RawContent
set arrParam(1,"SqlType") = 12
set arrParam(1,"CType") = $$$SqlWChar
set arrParam(1,"IOType") = 1
set arrParam(1,"Prec") = 8000
set arrParam(1,"SqlTypeName") = "TEXT"
//Defining the output parameter
set arrParam(2)=""
set arrParam(2,"SqlType") = $$$SqlVarchar
set arrParam(2,"IOType") = 2
set arrParam(2,"SqlTypeName") = "TEXT
//Stored procedure with the parameter definition
Set tQuery= "{call dbo.sp_InsertHL7(?,?)}"
Set tSC = ..Adapter.ExecuteProcedureParmArray(,.tOutParams,tQuery,"ib",.arrParam)
It should be pointed out that the RawContent property is not guaranteed to supply the entire message, as described here. Better to use the OutputToLibraryStream() method.
Thank you Jeffrey for the suggestion. As you said there is a max length limit for RawContent property. I modified the code as below;
Set tMessage = ##class(%Stream.GlobalCharacter).%New()
Set tHL7 = ##class(EnsLib.HL7.Message).%OpenId(pID)
Do tHL7.OutputToLibraryStream(.tMessage)