Change a FireDAC query SQL string from DataSnap client

0
votes

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?

3
delphidatasnapfiredac
I have tried Ulwe's suggestion of closing and reopening the ClientDataSet like this: ClientDataSet1.Close; ClientModule1.ServerMethods1Client.RunSQL(memo1.Text); //Send SQL to server ClientDataSet1.Open; ClientDataSet1.Active:=True; ClientDataSet1.Refresh; but it still shows the records from the original SQL, not the new SQL. – Nick Alexander
I have used this With Delphi 10.1 Berlin and there is no problem in that version. The FDQuery.SQL.Text is updated by this operation too (unlike with DBX equivalents), so the SQL text can be shown on the form or added to the Datasnap server log for easy debugging. – nolaspeaker

3 Answers

0
votes

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

0
votes

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.

0
votes

Check TDataSetProvider->Options->poAllowCommandText is checked