Frequently Asked Questions

Modifying application server SQL from the client.

Question:

Is it possible to modify the TQuery.SQL property when it

resides in a remote data module?

Answer:

Yes.  There are three different avenues to accomplish this:



1. If it is a parameterized query, you can use the 

   IProvider.SetParams call.  Assuming you have 2 parameters 

   (Param1 and Param2).  The following code fragment

   demonstrates matching by index, Value1 goes to the first

   parameter, etc...



ClientDataSet1.Provider.SetParams(VarArrayOf(Value1, Value2));



Or matching by param name:





var

  V: Variant;

  ParamCount: Integer; //used for ease of reading

begin

  ClientDataSet1.Close;

  ParamCount := 2;

  V := VarArrayCreate([0, ParamCount - 1], varVariant);

  V[0] := VarArrayOf(['Param1',Value1]);

  V[1] := VarArrayOf(['Param2',Value2]);

  ClientDataSet1.Provider.SetParams(V);

  ClientDataSet1.Open;

end;



For more example code see \demos\midas\setparam.





2. If you want to change the SQL, then you can use the 

   IProvider.DataRequest method.



On the Client;





CDS.Data := CDS.Provider.DataRequest('select * from customer');





On the server, you must use a TProvider object, and assign

the OnDataRequest event;





function TForm1.Provider1DataRequest(Sender: TObject;

  Input: OleVariant): OleVariant;

begin

  //assumes DataSet is a TQuery.

  Provider1.DataSet.Close;

  Provider1.DataSet.SQL.Text := Input;

  Provider1.DataSet.Open;

  Result := Provider1.Data;

end;



For more example code see \demos\midas\adhoc.





3. Create you own interface function that executes the SQL 

   statement.



In the RemoteDataModule:



a. Edit | Add To Interface  "procedure ExecSQL(SQL: 

   WideString);" <OK>  This will add a procedure to your

   interface.



b. On the server, code the new procedure: 





procedure RemoteData.ExecSQL(SQL: WideString);

begin

  { Query1 is kept in the RemoteDatamodule for this purpose}

  Query1.SQL.Text := SQL;

  Query1.ExecSQL;

end;





c. On the client, call the new procedure:





RemoteServer1.AppServer.ExecSQL('Delete from deal details

where dealnumber=1');