Dynamic SQL Query in DataAbstract

Data Abstract
1

I am evaluating DataAbstract at the moment but due to time constraints I am running way behind. I have looked at this type of topic on the site but cannot find the answer I am hoping for. To summarise, lets say I have a dynamic sql TQuery statement as:-1:

procedure xxx: TDataSource;
begin
    Qry.SQL.Text := 'c.Code, g.Name, g.Code, p.Code as ProductCode, p.GroupCode, p.Name as ProductName from ProductGroups g left outer join Products p on p.GroupCode = g.Code where g.Code = :GCODE';
    Qry.ParamByName('GCODE').AsString := '1'; 
    Result := Qry;
end;

Can I do exactly the same type of thing using DataAbstract and return a TDataSet object to the calling code based on a dynamic query. I am hoping that we can convert the operation of the Query objects we use to DataAbstract objects? This could cut down our development effort 10 fold if we convert our application.

Thanks.
Robert.

2

Hi, Can I just add to the previous posting that I do not have a problem in building any amount of code to make the above work as I intend on adding the code to the native Query objects that we use. As standard, we always subclass all components so we have complete control over the ‘Active’ property. It would be my intention that when Active=True to call the code to invoke DataAbstract and return a dynamically created dataset.

3

DataAbstract doesn’t support returning TDataSet objects.
you can return usual record set as if this sql was executed

select 
    c.Code, g.Name, g.Code, p.Code as ProductCode, p.GroupCode, p.Name as ProductName 
from 
    ProductGroups g left outer join Products p on p.GroupCode = g.Code
where 
    g.Code = :GCODE
4

Hi EvengyK,
Thanks. I will try that out.

5

Hi Again, Normally my code uses the following syntax:-1:

Qry.Sql.Text := Select * from Customer where Code=:CODE
Qry.ParamByName(‘CODE’).AsString := ‘xxx’;

Is it possible to generate the parameters for the associated TDaMemDataTable based on the SQL Text that will be used in the call to RemoteDataAdapter.FillWithDASql ?

Modifying the ParamByName method of TDAMemTable to add the parameter dynamically if it does not exist is simple enough but I would prefer to stay away from modifying any of your code.

6

DA SQL feature is supported only by DA/.NET server.
I can recommend to use TDataAbstractService.SQLGetDataEx method instead of FillWithDASql.
it allows to specify custom sql and specify parameters via Dynamic Where.

I’ve attached Dynamic SQL sample: Dynamic SQL.zip (42.2 KB).

Note: it’s from DA7 and may require some upgrading to DA9