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'

  • 0
  • 0
  • 59
  • 4
  • 1

Answers

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.