Jump to content
Roger Cigol

FireDAC TFDQuery - table names as parameters

Recommended Posts

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 ?

Share this post


Link to post

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;

 

  • Like 1
  • Thanks 3

Share this post


Link to post

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.

  • Thanks 1

Share this post


Link to post

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

  • Like 1

Share this post


Link to post

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.

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×