FireDAC - Show SQL after Macro Expantion

Question

I am trying to use Macros in FireDAC to Preprocess my SQL Queries. I have a TADQuery object on a Data Module with the SQL set to something like:

Select * from MyTable
  join OtherTable on MyTable.Key = OtherTable.Key
&Where

Then in my code I do this:

WhereClause = 'stuff based on my form';
Query.MacroByName('Where').AsRaw := WhereClause;
Query.Open;

This has worked great for complicated queries because it lets me make sure my fields and join conditions are correct using the SQL Property editor.

My problem is when the SQL statements ends up invalid because of my where clause. Is there any way to see the SQL after pre-processing that is going to be executed? Right now I am catching the FireDac errors and showing the SQL that is on EADDBEngineException object. However that is still showing my original SQL with the macros. If I can't get to it after the error happens is there anyway to force the Macro replacement to take place so I can look at the SQL in the debugger to help me see what is wrong.

If it matters I am connecting to a MS Access database with the goal of moving to SQL Server in the near future.

Answer 1

Apart from using Text property, to monitor what SQL is actually going to the database engine, consider using the "FDMonitor" FireDAC utility. According to the DokWiki pages (below):

• drop a TFDMoniRemoteClientLink component on your form,
• Set its Tracing property to True,
• Add the MonitorBy=Xxx connection definition parameter to your existing FDConnection component. You can do this in the IDE object inspector, by selecting your FDConnection component, expanding the Params property, and setting MonitorBy to mbRemote.

Note that the TFDMoniXxxxClientLink should come before TFDConnection in the data module or form creation order, so adjust this by right clicking on the form or data module, then Creation Order, and moving the TFDMoni.. component above the FDConnection.

Also, it's helpful in the options of the TFDMoniXxxxClientLink, to disable most of the events being recorded, otherwise all the data returned is also shown in the FireDAC monitor. Expand the EventKinds property, and turn all the event kinds off, except for perhaps ekConnConnect, ekConnPrepare, and ekCmdExecute.

Then open the FireDAC Monitor from the IDE, (Tools > FireDAC Monitor). Start your app only once the monitor is running. Double click on a trace event (in the Trace Output tab), and you will see the actual SQL sent to the database in the bottom pane.

It also seems likely that adding the EventType of ekConnPrepare as mentioned above, would show you when the query's Prepare is called, but I haven't played enough with it say for sure.

Please see the following pages on the DocWiki for more information:

Overview: FDMonitor

How to: Tracing and Monitoring (FireDAC)

Other FireDAC utilities: Utilities (FireDAC)