Title: 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;
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');