Question
Nirshanthini Ku... · Mar 23

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
0 167
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)

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()