How exactly do I run SQL statements directly on the database?

Data Abstract
1

Can someone enlighten me as to how exactly I should go about sending SQL commands directly to the database?

I know I have to enable the AllowExecuteSQL property on my service but don’t know what else I need to do.

The TDataAbstractService class has an SQLExecuteCommand function but it’s protected. Should I simply make this public in my own derived service and call that or is there a different way I should be doing it?

Thanks

2

Hello,
It’s better to forbid to call SQL directly because an user can run a harmful code.
Allow only SQL which stored in your schema commands.

To run command use TDARemoteCommand component.

ClientDataModule.DARemoteCommand1.Execute('S_Test_Cmd', ['EmpId'], ['000001']);

If you wand to run SQL directly:

=== On the server side ===

  1. Set AllowExecuteSQL service property to True

  2. Add own function to the service which executes commans.

    function TNewService.MySQLExecuteCommand(const aSQLText: Utf8String; const aParameters: DataParameterArray):Integer;
    var
    lSQLText: string;
    j:Integer;
    ds:IDADataset;
    begin
    if not AllowExecuteSQL then
    raise Exception.Create(‘Execution of SQL has been disabled (SQLExecuteCommand)’);

    lSQLText := UTF8ToString(aSQLText);

    ds := Connection.NewDataset(lSQLText);
    ds.RefreshParams();
    for j := 0 to aParameters.Count - 1 do
    ds.ParamByName(UTF8ToString(aParameters[j].Name)).Value := aParameters[j].Value;
    ds.Execute();
    end;

    === On the client side ===

    1. Set a new method name
      ClientDataModule.DARemoteCommand1.ExecuteCall.MethodName := ‘MySQLExecuteCommand’;

    2. Call SQL

    begin
    ClientDataModule.DARemoteCommand1.Execute(‘exec S_Test :EmpId’, [‘EmpId’], [‘000001’]);
    end;

    or

    begin
    pa:=DataParameterArray.Create();
    with pa.Add do
    begin
    Name := ‘EmpId’;
    Value := ‘000001’;
    end;
    ClientDataModule.DARemoteCommand1.Execute(‘exec S_Test :EmpId’, pa);
    pa.Free();
    end;