· May 16, 2018

Get the complete exec statement for store procedure executed with SQL.OutboundAdapter

I am looking to get the exec statement when we call the SP with  ..Adapter.ExecuteProcedure.

Set tSQLQuery="exec pSPName ? ?  ?"

Set Status = ..Adapter.ExecuteProcedure(.tResultList,.tOutParms,tSQLQuery,"iii*, 1,2,3)

Get the exec string:  exec pSPName '1', '2', '3'

Discussion (6)0
Log in or sign up to continue

You can create a separate query table with fields: query, user, time, arg1. arg2. ar3, ..., argN.

And log information about the queries as is. This way you would be able to:

  • group by query - to get how often some specific procedure is called regardless of the argument values
  • filter by argument values

Additionally there's ODBC logging and JDBC logging might be available depending on the database vendor.

Maybe you can try this Business opertion in Ensemble/HealthShare to a stored procedure in Cache.

Class User.TestSQLOperation Extends Ens.BusinessOperation

Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";

Property Adapter As EnsLib.SQL.OutboundAdapter;

Parameter INVOCATION = "Queue";

Method OnMessage(pRequest As Ens.Request, Output pResponse As Ens.Response) As %Status
    set tSC = $$$OK
    SET sql="{?=call api.SimpleSP(?,?)}"
    set tSC=..Adapter.ExecuteProcedure(.rs,.out,sql,"oii",2,"Hello1")
    quit tSC

XData MessageMap
                <MapItem MessageType="Ens.Request">



Class api.TestSQL [ Abstract ]

ClassMethod SimpleMethod(Id As %String, Name As %String) As %Status [ SqlName = SimpleSP, SqlProc ]
                Set ^localData("Test")=$G(Id)_" "_$G(Name)
                If $L(Id) {
                                Set ^localData(Id)=$G(Name)
                Quit $$$OK