5

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.

Mark Elder
  • 3,987
  • 1
  • 31
  • 47
  • 1
    Just a wild guess (since I don't have FireDAC by hand at this time), but don't you need to call the [`Prepare`](http://docs.embarcadero.com/products/rad_studio/firedac/frames.html?frmname=topic&frmfile=uADCompClient_TADRdbmsDataSet_Prepare.html) method to trigger the preprocessing ? The result of query prepare should be stored in the [`SQLText`](http://docs.embarcadero.com/products/rad_studio/firedac/frames.html?frmname=topic&frmfile=uADCompClient_TADCustomCommand_SQLText.html) property, but here is the help quite confusing and I guess this property is hidden to `TADSQLQuery`. – TLama Feb 20 '14 at 19:18
  • Well, I've roughly checked what's happening there and I'm still not sure if calling `Prepare` (which is useless for you as I get) is the minimal requirement to trigger that preprocessing. Though, the preprocessed SQL, the one which is sent to the DBMS you can access through the [`Text`](http://docs.embarcadero.com/products/rad_studio/firedac/frames.html?frmname=topic&frmfile=uADCompClient_TADCustomQuery_Text.html) property (quite uncommon name for such property). So, I'd rather wait for some credible answer. Good question! – TLama Feb 21 '14 at 08:18
  • 1
    @TLama - Thanks! ADQuery.Text seems to be what I need. I passed right over that property without giving it a thought. From your comments I did find another short reference to that in the help file. The [Tracing and Monitoring](http://docs.embarcadero.com/products/rad_studio/firedac/Tracing_and_Monitoring.html) section has short Paragraph towards the bottom titled "Checking the SQL command text". It does not look like I can get to .Text property on the error object, but it will still be a big help to be able to see it in the debugger. – Mark Elder Feb 21 '14 at 16:09

2 Answers2

2

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)

Reversed Engineer
  • 1,095
  • 13
  • 26
1

(Just to remove this question from list of unanswered questions)

From comments:

Well, I've roughly checked what's happening there and I'm still not sure if calling Prepare (which is useless for you as I get) is the minimal requirement to trigger that preprocessing. Though, the preprocessed SQL, the one which is sent to the DBMS you can access through the Text property (quite uncommon name for such property). – TLama Feb 21 '14 at 8:18

Nashev
  • 490
  • 4
  • 10
  • 1
    But surely a comment which says " So, I'd rather wait for some credible answer." is hardly an ideal candidate to post as an answer? – MartynA Feb 16 '16 at 16:12
  • In context of "ADQuery.Text seems to be what I need." answer - yes. There are really no other way in FireDAC to view final text, transferred to DBMS server as command. – Nashev Feb 28 '16 at 09:04
  • In our apps we have own copy of FireDAC library with some fixes, some of them transfer this Text through Exception objects to exception handlers, which allow users see more informative exception description with final text and all parameters and most information of call context. – Nashev Feb 28 '16 at 09:07