Tracing and Monitoring

This article shows how to use AnyDAC tracing and monitoring capabilities, which allows to see how an application is communicating with a database and is main AnyDAC application debugging tool.

Group

Debugging and Support

Links

Debugging and Support, See Also

Description

General

AnyDAC trace output is the detailed log of the communication between AnyDAC application and the database software. It includes reference numbers, times, API calls, SQL sent to a database, data exchange with parameter and fields values, errors and warnings, and the DBMS environment report.

AnyDAC offers 3 methods to output the trace, which are controlled by the different components and different MonitorBy values:

Component	MonitorBy value	Description
TADMoniFlatFileClientLink	FlatFile	Outputs trace to a flat text file. When application finishes, it will show the list of produced trace files.
TADMoniRemoteClientLink	Remote	Outputs trace to ADMonitor utility. Also allows to monitor the application. ADMonitor utility must be running before activating a trace output.
TADMoniCustomClientLink	Custom	Output trace to a custom event handler. Application should use OnOutput event handler to produce custom trace output.

The Moni components are singletons referring to a single instance of a corresponding tracer implementation. All connections with enabled trace output will use the same tracer and a single output for all connections will be produced.

Controlling tracing

To enable trace output for a connection:

Drop TADMoniXxxxClientLink component to a form.
Set it Tracing property to True.
Add MonitorBy=Xxx connection definition parameter.

The MonitorBy parameter associates the definition with a specific tracing method and becomes read-only after first connection is created for this definition. It should be set permanently.

TADMoniXxxxClientLink.Tracing property must be True before opening a first connection with MonitorBy=Xxx. Later to temporarily disable or enable tracing output for all connections, use TADMoniXxxxClientLink component Tracing property. Note, TADMoniXxxxClientLink should come before TADConnection in the data module or form creation order.

To initially disable trace output for a specific connection, use MonitorBy=Xxx-. To temporarily disable or enable tracing output for a specific connection, set ADConnection.ConnectionIntf.Tracing property. Note, that the ConnectionIntf is accessible only after connection is established.

For example:

ADMoniFlatFileClientLink.Tracing := True;

with ADConnection1.Params do begin
  Clear;
  Add('DriverID=SQLite');
  Add('Database=c:\test.sdb');
  Add('MonitorBy=FlatFile');
end;

ADConnection1.Connected := True;
...
// disable trace output for connection
ADConnection1.ConnectionIntf.Tracing := False;
...
// enable trace output for connection
ADConnection1.ConnectionIntf.Tracing := True;

The trace content

To control the trace content, use TADMoniXxxxClientLink.EventKinds property. The sample trace output:

    1385 15:47:14.093     >> Fetch [ATable="ADQA_FK_tab", Command="SELECT AF.fk_id, AF.id, AT1.f1, AT1.f2
FROM ADQA_FK_tab AF LEFT JOIN ADQA_tabwithpk AT1 ON AF.fk_id=AT1.f1"]
    1386 15:47:14.093          . sqlite3_column_type [stmt=$0F16E6B8, iCol=0, Result=SQLITE_NULL]
    1387 15:47:14.093          . sqlite3_column_type [stmt=$0F16E6B8, iCol=1, Result=SQLITE_INTEGER]
    1388 15:47:14.093          . sqlite3_column_int64 [stmt=$0F16E6B8, iCol=1, AValue^=2]
    1389 15:47:14.093          . sqlite3_column_type [stmt=$0F16E6B8, iCol=2, Result=SQLITE_NULL]
    1390 15:47:14.093          . sqlite3_column_type [stmt=$0F16E6B8, iCol=3, Result=SQLITE_NULL]
    1391 15:47:14.093          . sqlite3_step [stmt=$0F16E6B8]
    1392 15:47:14.093          . sqlite3_column_type [stmt=$0F16E6B8, iCol=0, Result=SQLITE_INTEGER]
    1393 15:47:14.093          . sqlite3_column_int64 [stmt=$0F16E6B8, iCol=0, AValue^=2]
    1394 15:47:14.093          . sqlite3_column_type [stmt=$0F16E6B8, iCol=1, Result=SQLITE_INTEGER]
    1395 15:47:14.093          . sqlite3_column_int64 [stmt=$0F16E6B8, iCol=1, AValue^=3]
    1396 15:47:14.093          . sqlite3_column_type [stmt=$0F16E6B8, iCol=2, Result=SQLITE_INTEGER]
    1397 15:47:14.093          . sqlite3_column_int64 [stmt=$0F16E6B8, iCol=2, AValue^=2]
    1398 15:47:14.093          . sqlite3_column_type [stmt=$0F16E6B8, iCol=3, Result=SQLITE_TEXT]
    1399 15:47:14.093          . sqlite3_column_text [stmt=$0F16E6B8, iCol=3, bytes=4]
    1400 15:47:14.093          . sqlite3_step [stmt=$0F16E6B8]
    1401 15:47:14.093          . profile [SQL="SELECT AF.fk_id, AF.id, AT1.f1, AT1.f2
FROM ADQA_FK_tab AF LEFT JOIN ADQA_tabwithpk AT1 ON AF.fk_id=AT1.f1", time=0]
    1402 15:47:14.093     << Fetch [ATable="ADQA_FK_tab", Command="SELECT AF.fk_id, AF.id, AT1.f1, AT1.f2
FROM ADQA_FK_tab AF LEFT JOIN ADQA_tabwithpk AT1 ON AF.fk_id=AT1.f1", RowsAffected=2]

There:

first number (1385-1402) is the line number, which may be used to reference the exact line;
timestamp is the local time, when the line was generated, which may be used to measure performance;
>> Xxxx - an AnyDAC operation start. There parameters shows the command SQL text and other information;
<< Xxxx - an AnyDAC operation finish;
sqlite3_xxx - a DBMS API call (SQLite), which was performed to execute an AnyDAC operation.

Flat file output

The TADMoniFlatFileClientLink component controls the flat text file output. By default the trace file name is automatically generated in the TEMP folder of your system. On termination an AnyDAC application will show the list of the produced trace files.

To control output use properties:

FileName - the trace file name. There may be used environment variables - $(name). Default - $(TEMP)\traceN.txt.
FileEncoding - the trace file encoding. Default - ANSI.
FileAppend - when True, then existing trace file will be appended. Otherwise will be overwritten on each new run.
FileColumns - the columns to include into text file.

Remote output

The TADMoniRemoteClientLink component controls the remote trace output. Use ADMonitor utility to see the trace output. AnyDAC uses TCP/IP as trace transport. That means that ADMonitor and application may run on different systems in a network. That may be actual for cross-platform development, where application may run on a Linux box and the ADMonitor must run on a Windows box.

To control output use properties:

Host - the IP address of ADMonitor box. Default is localhost.
Port - the IP port which ADMonitor is listening. Default is 8050.
Timeout - the maximum number of ms allowed to establish connection to ADMonitor.

Custom output

The TADMoniCustomClientLink component may be used to produce a custom trace output. For that application should define OnOutput event handler. It is up to the application what it will do there.

Note, avoid Application.ProcessMessages call in the OnOutput event handler, as it seriously reduces performance and may lead to weird issues (due to possible AnyDAC reentering, which is not supported).

Monitoring

ADMonitor utility also supports the monitoring of the AnyDAC objects state. The developers may use this functionality to enable monitoring for their own objects. See AnyDAC\Samples\Moni Layer\Main demo for details.

Checking the SQL command text

There are few methods to see the SQL command text, as it will be sent to a database:

TADQuery.Text - returns SQL command text, as it will be sent to a database;
EADDBEngineException.SQL, Params - returns SQL command text, which leaded to an exception;
Use above tracing capabilities to see the SQL commands.

Checking the dataset rows

The dataset rows may be dumped using one of the methods:

DataSet.GetRow.DumpRow(True) - returns text representing current dataset row, including all field values and all field names;
DataSet.GetRow.DumpRow() - returns text representing current dataset row, including all field values;
DataSet.Table.Rows[i].DumpRow() - returns text representing dataset random row.