Published on InterSystems Developer Community (https://community.intersystems.com)

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

Question
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. 

#InterSystems IRIS for Health
Product version: IRIS 2021.1

Source URL:https://community.intersystems.com/post/sql-outbound-adapter-execute-procedure-call-returns-nullundefined-output-value