Tracing and Monitoring (FireDAC)

Go Up to Debugging and Support (FireDAC)

This topic shows how to use FireDAC tracing and monitoring capabilities, which make up the main FireDac application debugging tool and which show how an application is communicating with a database.

Overview

The FireDAC trace output is the detailed log of the communication between the FireDAC 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 Database Management System (DBMS) environment report.

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

Component	MonitorBy value	Description
TFDMoniFlatFileClientLink	FlatFile	Outputs the trace to a flat text file. When the application finishes, it shows the list of produced trace files.
TFDMoniRemoteClientLink	Remote	Outputs the trace to the FDMonitor utility and allows to monitor the application. FDMonitor must be running before activating a trace output.
TFDMoniCustomClientLink	Custom	Outputs the trace to a custom event handler. Applications should use the OnOutput event handler to produce custom trace output.

Monitor components are singletons that refer to a single instance of a corresponding tracer implementation. All connections with enabled trace output use the same tracer, and a single output is produced for all connections.

Controlling Tracing

To enable trace output for a connection:

1. Drop a TFDMoniXxxxClientLink component to a form.
2. Set its Tracing property to True.
3. Add the MonitorBy=Xxx connection definition parameter.

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

Set the TFDMoniXxxxClientLink.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 the Tracing property of the TFDMoniXxxxClientLink component.

Note: TFDMoniXxxxClientLink should come before TFDConnection 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 the TFDConnection.ConnectionIntf.Tracing property. Note that the ConnectionIntf is accessible only after the connection is established.

For example:

 
FDMoniFlatFileClientLink1.Tracing := True;

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

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

The Trace Content

To control the trace content, use the TFDMoniXxxxClientLink.EventKinds property. The following is a sample trace output:

     1385 15:47:14.093     >> Fetch [ATable="FDQA_FK_tab", Command="SELECT AF.fk_id, AF.id, AT1.f1, AT1.f2
 FROM FDQA_FK_tab AF LEFT JOIN FDQA_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 FDQA_FK_tab AF LEFT JOIN FDQA_tabwithpk AT1 ON AF.fk_id=AT1.f1", time=0]
     1402 15:47:14.093     << Fetch [ATable="FDQA_FK_tab", Command="SELECT AF.fk_id, AF.id, AT1.f1, AT1.f2
 FROM FDQA_FK_tab AF LEFT JOIN FDQA_tabwithpk AT1 ON AF.fk_id=AT1.f1", RowsAffected=2]

Here:

• The first number (1385-1402) is the line number, which can be used to reference the exact line.
• Timestamp is the local time when the line was generated, which can be used to measure performance.
• These parameters shows the command SQL text and other information:
  • >> Xxxx--a FireDAC operation start
  • << Xxxx--a FireDAC operation finish
• sqlite3_xxx--a DBMS API call (SQLite), which was performed to execute a FireDAC operation.

Controling Output

There are several components that control the output.

Flat File Output

The TFDMoniFlatFileClientLink 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, a FireDAC application shows the list of generated trace files.

To control the output, use the following properties:

• FileName--the trace file name. Environment variables--$(name)--can be used. The default name is $(TEMP)\traceN.txt.
• FileEncoding--the trace file encoding. The default encoding is ANSI.
• FileAppend--when True, the existing trace file is appended. Otherwise, it is overwritten on each new run.
• FileColumns--the columns to include into the text file.

Remote Output

The TFDMoniRemoteClientLink component controls the remote trace output. Use the FDMonitor utility to see the trace output. FireDAC uses TCP/IP as trace transport, which means that FDMonitor and the application can run on different systems in a network. This also applies for OS X development, where the application can run on the Mac while the FDMonitor runs on a Windows box.

To control the output, use the following properties:

• Host--the IP address of the FDMonitor box. Default is localhost.
• Port--the IP port that FDMonitor is listening. Default is 8050.
• Timeout --the maximum number of ms allowed to establish a connection to FDMonitor.

Custom Output

The TFDMoniCustomClientLink component can be used to produce a custom trace output. For that, the application should define an OnOutput event handler.

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

Monitoring

The FDMonitor utility also supports the monitoring of the FireDAC objects' state. The developers can use this functionality to enable monitoring for their own objects. See the FireDAC\Samples\Moni Layer\Main demo for details.

Checking the SQL Command Text

Use the following tracing capabilities to see the SQL command text as it will be sent to a database:

• TFDQuery.Text--returns the SQL command text as it will be sent to a database.
• EFDDBEngineException.SQL, Params--returns the SQL command text that raised an exception.

Checking the Dataset Rows

You can dump the dataset rows using one of the methods:

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

See Also

• DBMS Environment Reports

Samples

• FireDAC MoniLayerClients sample