Delphi

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;
TQuery(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);" 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');