SQL Outbound Adapter - Execute Procedure call returns null/undefined for Output value.

Question

Question

Nirshanthini Ku... · Mar 23, 2022

#InterSystems IRIS for Health

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

Discussion (3)2

Log in or sign up to continue

Jeffrey Drumm · Mar 31, 2022

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.

0 0

score 0 · Answer 1 · 2022-03-31T00:49:39-04:00

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)

score 0 · Answer 2 · 2022-04-06T23:32:38-04:00

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