FireDAC TFDQuery - table names as parameters

[Roger Cigol 103]

I have several tables that I want to completely empty. My plan was to use a single TFDQuery and to set (at design time) the SQL to       DELETE FROM :tablename       and then to set the :tablename parameter to type ptInput and datatype to ftString. Then I planned to call this query for each table, having set the parameter to the table name. But it doesn't work, each call to Execute() (following the setting of the parameter to the required table name) gives an exception.  I am using a 64bit PostgreSQL database so I can't test the parameter query at design time (can't connect from a 32 bit app). If I query my database directly using a query tool I can enter DELETE FROM MyTable and all the contents of MyTable disappear as required. I also find that the following code works (I am using C++ clang 64 in Alexandria 11.0):

 

      auto SQL(std::make_unique<TStringList>());
      SQL->Clear();
      SQL->Add(String("DELETE FROM ") + DestinationTableName);
      DestinationDeleteAllFDQuery->Close();
      DestinationDeleteAllFDQuery->SQL->Text = SQL->Text;
      DestinationDeleteAllFDQuery->OpenOrExecute();
 

My question is : Is this expected behaviour? ie Is it known that parameters in FireDAC queries can not used to pass table names ?

[Uwe Raabe 2057]

A table name cannot be treated as a parameter. Instead you can use a macro like this: DELETE FROM &TableName and then set the actual table name with MacroByName.AsIdentifier := DestinationTableName;

 

[Martin Sedgewick 30]

In this case, I use a bog standard %s in the string and Format before passing to FireDAC. I like the macro idea and did have a look but might be worth trying it out.

[Uwe Raabe 2057]

The advantage of TFDMacro.AsIdentifier is, that it automatically quotes it according to the current DBMS. 

[Roger Cigol 103]

This "generalisation" of FireDAC to suit the very many supported databases is one of it's great features. It is worth using approaches that continue/extend this generalisation primarily because it allows re-use of your code in other applications (that could be using different databases). As it happens the job that led to my question is actually migrating a working-well-for-many-years system that uses MS Access to a 64 bit application using PostgreSQL. The fact that the MS Access system uses FireDAC makes this task so much more straightforward.