有两种方法:
一、首先要设置服务端的服务AllowWhereSQL为True;
客户端
with ClientDataModule.dtCustomers do begin Close; // Prepares the custom WHERE clause Where.Clear; Where.AddConditions(['CustomerIdx','FirstName','LastName','City'], [cEqual,cLike,cLike,cLike], [eCode.Text,eFirstName.Text,eLastName.Text, eCity.Text], opAnd); Close; Memo1.Text:=Where.Clause; if Where.Empty then stWhereClause.Caption := msg_NoCondition else stWhereClause.Caption := Where.Clause; MaxRecords := seMaxCustomers.Value; Open; end;
说明:
//一 复合条件
// Where.AddConditions(['CustomerID'],
// [cEqual],
// ['ALFKI'], opAnd);
//二 增加一段条件
// Where.AddText('CustomerID = ''ALFKI''');
//三 增加一个in条件
Where.AddValueGroup('CustomerID',['ALFKI']);
// OpenBraket 增加一个左括号,CloseBraket增加一个右括号
// Where.OpenBraket;
// Where.CloseBraket;
二、首先要设置服务端的服务AllowDynamicWhere为True;
Operations between atomic expressions can be:
- comparison (less, less or equal, equal, greater or equal, greater, not equal).
- likewise (like) for string types.
- arithmetic (addition, substraction, multiplication, division).
- occurrences (in) for list expressions.
The value of the expression can be changed by unary operators (minus, not).
Examples WHERE LastName LIKE '%in%' begin with ClientDataModule.tbl_Employees do begin Close; DynamicWhere.Clear; DynamicWhere.Expression := DynamicWhere.NewBinaryExpression (DynamicWhere.NewField('Employees', 'LastName'), DynamicWhere.NewConstant('%in%', datString), dboLike); Open(); end; end; WHERE(LastName LIKE '%in%') OR (Not(EmployeeID = 5)) begin with ClientDataModule.tbl_Employees do begin Close; DynamicWhere.Clear; DynamicWhere.Expression := DynamicWhere.NewBinaryExpression (DynamicWhere.NewBinaryExpression(DynamicWhere.NewField('Employees', 'LastName'), DynamicWhere.NewConstant('%in%', datString), dboLike), DynamicWhere.NewUnaryExpression(DynamicWhere.NewBinaryExpression (DynamicWhere.NewField('Employees', 'EmployeeID'), DynamicWhere.NewConstant(5, datInteger), dboEqual), duoNot), dboOr); Open(); end; end; WHERE((LastName LIKE '%in%') OR (Not(EmployeeID = 5))) AND (FirstName IN ('Nancy', 'Robert')) var lWhereExpression: TDAWhereExpression; begin with ClientDataModule.tbl_Employees do begin Close; DynamicWhere.Clear; lWhereExpression := DynamicWhere.NewBinaryExpression (DynamicWhere.NewBinaryExpression(DynamicWhere.NewBinaryExpression (DynamicWhere.NewField('Employees', 'LastName'), DynamicWhere.NewConstant('%in%', datString), dboLike), DynamicWhere.NewUnaryExpression(DynamicWhere.NewBinaryExpression (DynamicWhere.NewField('Employees', 'EmployeeID'), DynamicWhere.NewConstant(5, datInteger), dboEqual), duoNot), dboOr), DynamicWhere.NewBinaryExpression(DynamicWhere.NewField('Employees', 'FirstName'), DynamicWhere.NewList([DynamicWhere.NewConstant('Robert', datString), DynamicWhere.NewConstant('Nancy', datString)]), dboIn), dboAnd); DynamicWhere.Expression := lWhereExpression; Open(); end; end; WHERE(Upper(LastName)LIKE '%IN%') begin with ClientDataModule.tbl_Employees do begin Close; DynamicWhere.Clear; DynamicWhere.Expression := DynamicWhere.NewBinaryExpression (DynamicWhere.NewMacro('UpperCase(LastName)'), DynamicWhere.NewConstant('%IN%', datString), dboLike); Open(); end; end; end;