Where builder with Connection.NewDataset

Data Abstract
1

I need to select all fields from a table in the database with a user defined where clause. I was going to do . . .

Connection.newDataSet(Format('SELECT * FROM %s WHERE %s',
                      ['TableNameHERE',wherebuilder.AsString]);

Obviously this won’t work but how can i accomplish this same idea easily??

Thanks

2

Hi,
You can use the Connection.GetQueryBuilder for this purpose.

  FQueryBuilder := aConnection.GetQueryBuilder;

  FQueryBuilder.MainTable.MasterTable := aTable;
  FQueryBuilder.Options := FQueryBuilder.Options + [qboGenerateSimpleSelect]; // allow to generate `select * from table`
  FQueryBuilder.Where.Xml := aWhereXML;
  lDataset := aConnection.NewDataset(FQueryBuilder.GenerateSelectSQL);
  lDataset.Open;
  FQueryBuilder.Free;
3

Awesome! Can I also do

Connection.GetQueryBuilder.Where.Params.Add

To support parameters and the where clause at the same time?

4

Yes, you can.

for generating statements like

CustomerID = :id

you should use

with Connection.GetQueryBuilder.Where do
   Expression :=  NewBinaryExpression(NewField('','CustomerID'),NewParameter('ID'),dboEqual);
5

One follow up question. I see the SQL generated correctly with parameters and where builder. I am doing the following but need to refresh the params in the IDADataset. How do I do that?

 ds := Connection.NewDataset(qb.GenerateSelectSQL);

When I go to do ad ds.ParambyName it is nil.

6

Hi,

you can call ds.RefreshParams;