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

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.

-------------Problems Reply------------

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/

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.

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.

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.

Category:delphi Views:1 Time:2010-01-14
Tags: delphi trace ado

Related post

  • Run a SQL command in the event my connection is broken? (SQL Server) 2011-03-10

    Here's the sequence of events my hypothetical program makes... Open a connection to server. Run an UPDATE command. Go off and do something that might take a significant amount of time. Run another UPDATE that reverses the change in step 2. Close conn

  • Change Excel SQL Command with macros 2012-02-10

    I need to create a Excel 2010 macro, which changes the SQL Command text of a Excel Connection. I'm working with a SQL Server, if it matters. How could I do this? I've found several links online, but none of them have worked. The most recent code I us

  • How can I keep Oracle SQL Developer from closing the DB connection? 2009-08-20

    Is there any way to keep Oracle SQL Developer from closing my DB connections, or to increase the timeout? Sometimes during a long-running query SQL Dev will just close the connection, leaving my query running on the server but me with no results. If

  • Is it possible to execute SQL commands and send MSMQ messages in a TransactionScope? 2008-10-30

    I'm investigating using MSMQ for my team's new project but I need to know if I can send MSMQ messages and execute SQL commands within a System.Transactions.TransactionScope and have them commit or rollback together. I can't find a reliable source onl

  • how to send user input to php, excute sql command and display output from php 2011-10-17

    my program works like this. there are three buttons on html page which are linking to three different php files which result in xml, result are based on fixed sql command in oracle. when user click on one of the button eg. <p><input class="f

  • SQL commands to get performance statistics 2009-02-17

    Are there SQL commands that I could use to extract performance monitoring data from MS SQL 2005, such as: transactions per second page reads/writes connections (@@CONNECTIONS gives the total, but what about current) physical reads locks and blocks ot

  • retrieve most recently executed SQL command (T-SQL) 2009-07-28

    One of my developers working on a trigger-based logging facility in SQL Server 2008 asked me if there was a command to retrieve the most recently executed SQL command within T-SQL. I thought there was a system stored procedure for just such a functio

  • Is it possible to output the results of an sql command to a text file, when sent via SMO in C#? 2010-05-25

    I am using SMO in C# to run an SQL script. I need to output the results of the file into a text file. When using a command line to run the query I can get the desired result using the "-o [output file]" argument. Is there a way to carry out the same

  • Which SQL command can I use to see the structure of a table on SQL Server? 2010-07-01

    I have a table on a SQL Server and I would like to export its structure to send to a colleague. Which SQL command should I issue to get the structure? I don't have access to the SQL Server Management Studio. Thanks --------------Solutions------------

  • SQL Command cannot be converted to string 2010-10-28

    Here's my code I seem to be getting an error SQL command cannot be converted to string with this code Dim LogData2 As sterm.markdata = New sterm.markdata() Dim query As New SqlCommand("Select * from openquery (db, 'SELECT * FROM table WHERE [email protected]

  • I could use some help with my SQL command 2011-01-15

    I've got a database table called 'mesg' with the following structure: receiver_id | sender_id | message | timestamp | read Example: 2 *(«me)* | 6 *(«nice girl)* | 'I like you, more than ghoti' | yearsago | 1 *(«seen it)* 2 *(«me)* | 6 *(«nice girl)*

  • What is the SQL command to return the field names of a table? 2008-09-19

    Say I have a table called myTable. What is the SQL command to return all of the field names of this table? If the answer is database specific then I need SQL Server right now but would be interested in seeing the solution for other database systems a

  • SQL Command for copying table 2008-09-25

    What is the SQL command to copy a table from one database to another database? I am using MySQL and I have two databases x and y. Suppose I have a table in x called a and I need to copy that table to y database. Sorry if the question is too novice. T

  • Improving code readability for SQL commands 2008-10-03

    In one of my classes for a web application I am developing, I have some reasonably long SQL queries. When developing a three-tier application, what are the best practices to make this kind of code neater? Dim dc As New SqlCommand("INSERT INTO Choices

  • Is there a way to make Crystal Reports include a constant in a join condition, without using a SQL command object? 2008-10-22

    What I want to do is an outer join to a table, where I exclude records from the joined table based on matching a constant, however keep records from the main table. For example: SELECT a.id, a.other, b.baz FROM a LEFT OUTER JOIN b ON a.id = b.id AND

  • What SQL command grants a securityadmin access to a database? 2008-10-28

    My account is in the securityadmin role and I cannot grant myself sysadmin permission. I wish to gain access to a database so I can add my account to a particular role within it. As I don't yet have access to the database I can't use the UI. Does any

  • How do I get the SQL command text when I insert a new record with Linq? 2009-02-11

    var newUser = new tblUser() { Email = strEmail, Password = strPassword, DateBirth = DateTime.Parse(strDateBirth), }; db.tblUsers.InsertOnSubmit(newUser); db.SubmitChanges(); I want to get the actual sql command text that linq generated. -------------

  • Using the SQL Command object, how can you check to see if the result set is empty? 2009-04-06

    Using the ExecuteScalar method in the SQL Command object, how can you check to see if the result set is empty? I am using ASP.net, C#, and MS SQL 2008. Right now when I run the following code the Response.Write returns a 0 when the resultset is empty

  • Why does my query produce error "ORA-00933: SQL command not properly ended"? 2009-04-18

    My query: CREATE VIEW cambiodatos AS SELECT a.last_name||','||a.first_name AS "Nombre", a.salary AS "Salario", b.name AS "Nombre Departamento", c.name AS "Nombre de Region" FROM s_emp a, s_dept b, s_region c WHERE a.dept_id = b.id AND b.region_id = c

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.134 (s). 11 q(s)