How can I monitor the SQL commands send over my ADO connection?

Question

i need intercept all the SQL commands that pass between an ADO connection component and a database server. something like the TSQLmonitor of dbExpress, but for ADO .

Anybody know any third-party component that implements this functionality?

UPDATE

I want to do is to monitor the SQL statements programmatically (by code) from my application without using an external tool. for any database engine.

Answer 1

If your database is MS-SQL, you can monitor SQL traffic with the SQL Profiler tool. There's quite a few tutorials on how to set this up, including this one.

Answer 2

Because your asking for any database engine, I believe the only reliable approach is to instrument your application to log the SQL prior to executing it. There are several ways to do this, from creating descendant objects from the existing TAdoxxxx ones, to creating simple functions that you must remember to call each time you want something inspected. As far as logging, one of the prefered methods is to use outputdebugstring to send it to the debug console. When delphi is running, this will show up in the messages window. There is a standalone executable DebugView which can be used on remote systems.

If your just wanting access to the SQL while debugging (so you can test and tune external to your application) and your using a tAdoQuery component, you can use the debug evaluator to save this to a file. Just set a break point right before the open, then at the breakpoint run the debug evaluator and enter the name of your query component followed by: .sql.savetofile('c:\sqlfilename.sql') and then press return, it will save the entirety of your existing SQL to the file specified. This can be a lifesaver if your SQL is being generated or is too large to view using the debug inspector.

Answer 3

I found a solution, use the event TAdoConnection.OnWillExecute (Wich occurs after a database server signals acceptance of a command execution. )

More info here

procedure TDataModuleProd.ADOConnection1WillExecute(
  Connection: TADOConnection; var CommandText: WideString;
  var CursorType: TCursorType; var LockType: TADOLockType;
  var CommandType: TCommandType; var ExecuteOptions: TExecuteOptions;
  var EventStatus: TEventStatus; const Command: _Command;
  const Recordset: _Recordset);
begin
   AddLog(CommandText);
end;

I wrote a small article on my blog, for those who want more information.

http://theroadtodelphi.wordpress.com/2010/02/21/build-your-own-profiler-using-ado/

Answer 4

I have posted how to use Windbg as a SQL profiler to get all sql calls from the application.

This can be easily changed to use it for any database. With this I am trying to demonstrate , How we could trace something from bottom of the stack.