All
Accepted answers
Nirshanthini Kugappiriyan · Apr 6, 2022 go to post

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

Nirshanthini Kugappiriyan · Mar 31, 2022 go to post

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)

Nirshanthini Kugappiriyan · Mar 18, 2022 go to post

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)

Nirshanthini Kugappiriyan · Mar 18, 2022 go to post

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.

Nirshanthini Kugappiriyan · Mar 17, 2022 go to post

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

Nirshanthini Kugappiriyan · Mar 17, 2022 go to post

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.

Nirshanthini Kugappiriyan · Nov 16, 2021 go to post

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