Change a FireDAC query SQL string from DataSnap client

Question

I have set up a client connecting to a DataSnap Server in Delphi XE7. I need to send a SQL string created on the client to the server to be executed against a Firebird DB. I am using FireDAC, but I get similar results if I use DB Express.

I have TFDPhysFBDriverLink -> TFDConnection -> TFDQuery -> TDataSetProvider on the server.

I have TSQLConnection -> TDSProviderConnection -> TClientDataSet -> TDataSource -> DBGrid on the client

The TFDQuery seems to require a SQL.Text value at design time. (e.g. select * from Cust_Master) I can send the SQL string (e.g. select * from Proj_Master) back to the server fine and load it into the TFDQuery, and if I check the rows affected before and after I change the SQL.Text, I get the right number of rows returned for the customers and the projects queries. The problem is that on the client side, I only get the results of the design time SQL i.e. customers, not the SQL I sent to the server i.e. projects being displayed in the grid. I do call ClientDataSet.Refresh after sending the SQL to the server.

I need to be able to send various SQL queries back to the server, I can't have them all defined at design time. Am I using the right components to achieve this?

Answer 1

You have to close and reopen the ClientDataSet. A simple Refresh won't do.

Answer 2

You need to set DSServer's lifecycle to Invocation or create the FBQuery directly in the ServerMethods' class function. I recommend the second one though.

Answer 3

Check TDataSetProvider->Options->poAllowCommandText is checked