Delphi XE and ServerMethods

Question

With Delphi XE I create a client-server application with "DataSnap Server" Wizard.

In the ServerMethodUnit I Define a TSQLQuery with sql property like this "Select * from TABLE"

I define a function called ChangeSQL in ServerMethodUnit too, what change the sql property by example SELECT * from TABLE where ID = 5

When i Call ChangeSQL form Client Application through TSQLServerMethod, the ChangeSQL function change SQL property in the ServerMethodUnit, but when it leaves the function ChangeSQL, the TSQLQuery not containing the new sql command but has the original sql.

EDIT: Addition of code sample

I create a new a client-server application with "DataSnap Server" Wizard.

In ServerMethodsUnit:

I put a TSQLConnection to my database. I Put a TSQLQuery call MyQuery, with SQL property = 'Select * from client' I put a TDataSetProvider to TSQLQuery

And define a function like this:

function TServerMethods1.ClientFiltrer (ID:integer):integer;
begin

// ------------------------------------------------ // At this point, MyQuery.RecordCount = all records (both the first time and the following) // ------------------------------------------------

if MyQuery.Active then
   MyQuery.close;

MyQuery.SQL.Clear;
MyQuery.SQL.Add(' SELECT * from CLIENT where ID = '+StrToInt (ID));
MyQuery.Open;
result := MyQuery.RecordCount; 
// ------------------------------------------------ 
// At this point, MyQuery.RecordCount = ONE record
// ------------------------------------------------ 

end;

AND THEN I create a new application with "DataSnap CLient"

In the ClientModuleUni: I put a ClientDataSet, called MyClientDS with RemoteServer and ProviderName to my MyQuery provider. I put a TSQLServerMethod called MyMethod pointing to ClientFilter in ServerMethodsUnit

In a form of the client application Then I put A DataSource and a DBGrid to MyClientDs and open it. I put a Tbutton with code OnClick:

begin
  MyClientDs .Close;
  MyMethod .ParamByName('ID').Value := 5;
  MyMethod .ExecuteMethod;
  showmessage (MyMethod .ParamByName('ID').AsString);
  // AT THIS POINT, returned value is ONE.
  // I supposed that this Query had been filtered on 
  // the server and the client then show the filtered 
  // data, but it always shows the original query

  MyClientDs .Close;
end;

In the DBGrid The I see all records of Client Table, ALWAYS.

I try filter ClientTable clicking in the button, but in the DBGrid always appears all the record.

Welcome to StackOverflow. You didn't post any code, so answering your question is pretty much impossible. You need to edit your question to provide more information (including source code) in order for us to be able to try and help you. — Ken White

Ken White Ken White · Accepted Answer · 2011-09-06T13:44:13

The problem is here:

MyQuery.SQL.Clear;
MyQuery.SQL.Add(' SELECT * from CLIENT where ID = '+StrToInt (ID));
MyQuery.Open;
result := MyQuery.RecordCount; 
// ------------------------------------------------ 
// At this point, MyQuery.RecordCount = ONE record
// ------------------------------------------------

When you assign ID StrToInt(ID), you are not creating a parameter. Therefore, later when you do the `ParamByName' to change it, there's no parameter there to change.

Change the lines above to

MyQuery.SQL.Text := `SELECT * FROM client WHERE ID  = :ID`;
MyQuery.ParamByName('ID').AsInteger := ID;
MyQuery.Open;

This should fix the problem.

Delphi XE and ServerMethods

2 Answers