Nirshanthini Ku... · Mar 23, 2022

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 = 2

     set arrParam(1) = Message
     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 tQuery= "{call dbo.p_InsertHl7(?,?)}"
 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. 

Product version: IRIS 2021.1
0 297
Discussion (3)2
Log in or sign up to continue

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 pID = pReq.getId()
    Set tMessage = ##class(%Stream.GlobalCharacter).%New()
    Set tHL7 = ##class(EnsLib.HL7.Message).%OpenId(pID)
    Do tHL7.OutputToLibraryStream(.tMessage)

    set arrParam(1)= tMessage.Read()