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'
Comments
The final text would only be "compiled" on a target system if at all.
Why do you need that?
Is it so. Before the SP is executed on target DB, the statement must be prepared and a exec statement is run in the below form. I am expecting this to capture.
exec pSPName '1', '2', '3'
Not really. The statement is passed as is and three arguments are passed separately.
Why do you need that?
Well, I am looking to capture this for analysis purpose. I need not frame those exec statement manually.
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.
BO:
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
{
<MapItems>
<MapItem MessageType="Ens.Request">
<Method>OnMessage</Method>
</MapItem>
</MapItems>
}
}
SP:
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
}
}