Resolution/workaround  - https://community.intersystems.com/post/sql-outbound-adapter-execute-pro... 

No, tOutParams  returns null because I'm passing null value during the input/output parameter definition. So it is just returning the initial value. Somehow I need to define this parameter to grab the SP output value.

Stored Procedure - EXEC sp_insertMessage @message, @result = @output OUTPUT;  

     //define input/output parameter
               set arrParam(2) = ""
               set params(2, "SqlType") = $$$SqlLongVarchar
         
             //define the query without return value syntax. No return value defined in SP
             Set tQuery="{call dbo.sp_insertMessage(?,?) }"
             
         Set tSC = ..Adapter.ExecuteProcedureParmArray(,.tOutParams,tQuery,"ib",.arrParam)    $$$TRACE(tSC)
   
   //Here I would like to get the Stored procedure's output value - NOT WORKING currently
      set sOut = tOutParams.GetNext(.key)
   $$$TRACE("output " _sOut)

To execute the SP we need to write sql query like below;

EXEC sp_insertMessage @message, @result = @output OUTPUT; 

print @result

@result parameter returns string "Failed" or "Passed"

So my input/out parameter in the code should return the SP OUTPUT value.

Hi Scott,

Thanks for your reply. 

The stored procedure doesn't have any return value. Instead it has a Input/Output Parameter (IO type -2). So As you said, I would like to return the OUTPUT value from the stored procedure. Below code is inserting the HL7 message into the DB. But I want to know how do I capture the output value returned by the SPROC.

//Two parameters, one input and one input/output
               set arrParam = 2
               
               //define input parameter
               set arrParam(1)= HL7Message
               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(1,"SqlTypeName") = "TEXT"
               
               //define input/output parameter
               set arrParam(2) = ""
               set params(2, "SqlType") = $$$SqlLongVarchar
         
             //define the query without return value syntax. No return value defined in SP
             Set tQuery="{call dbo.sp_insertMessage(?,?) }"
             
         Set tSC = ..Adapter.ExecuteProcedureParmArray(,.tOutParams,tQuery,"ib",.arrParam)    $$$TRACE(tSC)
   
   //Here I would like to get the Stored procedure's output value - NOT WORKING currently
      set sOut = tOutParams.GetNext(.key)
   $$$TRACE("output " _sOut)
  
   if 'tSC write " failed ",tSC 
   quit tSC

Hi there,

I'm trying to insert HL7 messages into a DB using the Stored Procedure. 

Stored procedure contains two parameter - 1 input (for HL7  raw content) , 1 input/output parameter. 

Execute SPROC as -   EXEC sp_insertMessage @message, @result = @output OUTPUT;

Below code is not inserting the message as required. Not sure how to define the input/output parameter

               set arrParam = 2
               
               //for input and output parameter
               set arrParam(1,"IOType") = 2
               set arrParam(1,"SqlType") = $$$SqlLongVarchar
               
               //for input parameter
               set arrParam(2)= Message
               set arrParam(2,"SqlType") = $$$SqlLongVarchar
               set arrParam(2,"CType") = $$$SqlWChar
               set arrParam(2,"IOType") = 1
               set arrParam(2,"Prec") = 8000
               set arrParam(2,"LOB") = 1
               set arrParam(2,"UseLOBLen")= 1
               set arrParam(2,"SqlTypeName") = "TEXT"    

  Set tQuery="{?=call dbo.sp_insertMessage(?,?) }"
 
   Set tSC = ..Adapter.ExecuteProcedureParmArray(,.tOutParams,tQuery,"bi",.arrParam)

Thank you.

Perfect. Thanks Ralf. it worked. 

I'm trying to unzip a CSV file and save it to a different folder. I utilised above example but unfortunately below command is returning the status code 1. 

set path = "U:\xxx\xxxxx.zip"
set pathtoExt = "U:\uncompress\"
set cmd = """C:\Program Files\7-Zip\7zG.exe"""
set args = 3
set args(1) = "x"
set args(2) = path_ "*.csv"
set args(3) = pathtoExt
set status = $ZF(-100,"/SHELL",cmd,.args)
write status

.