TDASQLWhereBuilder

 

Creates a new instance.

constructor Create

---

Creates a new instance with a specified database connection.

constructor Create(AConnection: IDACustomConnection)

Parameters:

• AConnection: specified database connection.

---

Creates a new instance with a specified query builder object.

constructor Create(AQueryBuilder: TDASimpleQueryBuilder)

Parameters:

• AQueryBuilder: query builder

---

Returns object as JSON string

property AsJson: UnicodeString read write

---

Returns object as string according to Format

property AsString: UnicodeString read write

---

Returns object as XML string

property AsXml: UnicodeString read write

---

Clears all properties.

procedure Clear

---

Column mapping.

property ColumnMapping: TDAColumnMappingCollection read write

---

Represents the database connection that relates to the current TDASQLWhereBuilder object. This property is read-only.

property Connection: IDACustomConnection read

---

Generates Where clause based on Expression

function CreateWhereClause: string

---

Represents Where expressions.

property Expression: TDAWhereExpression read write

---

Converts aExpression to JSON string

class function ExpressionToJson(const aExpression: TDAWhereExpression): UnicodeString

Parameters:

• aExpression: Expression

---

Converts aExpression to XML or JSON string.

Default format can be changed via defining/undefining DYNAMIC_WHERE_JSON_FORMAT definition in DataAbstract_user.inc file.

class function ExpressionToString(const aExpression: TDAWhereExpression): UnicodeString

Parameters:

• aExpression: Expression

---

Converts aExpression to XML string

class function ExpressionToXml(const aExpression: TDAWhereExpression): UnicodeString

Parameters:

• aExpression: Expression

---

Converts TDAWhereExpression to XML node

class function ExpressionToXmlNode(const aExpression: TDAWhereExpression): IXMLNode

Parameters:

• aExpression: expression

---

Format of serialization of Dynamic Where

property Format: TDynamicWhereFormat read write

---

Generates field name according to column mapping and quotation.

function GenerateFieldName(aTablename: string; aFieldName: string): string

Parameters:

• aTablename: table name
• aFieldName: field name

---

Returns True is Expression is empty.

function IsEmpty: Boolean

---

Reads Where expression from JSON

class function JsonToExpression(const aJson: UnicodeString): TDAWhereExpression

Parameters:

• aJson: given JSON

---

Creates a new BetweenExpression.

• SQL:

Price BETWEEN 20 AND 50

• code:

with ClientDataModule.tbl_OrderDetails, DynamicWhere do begin
  Expression:=NewBetweenExpression(
                NewField(LogicalName, 'Price'),
                NewConstant(20),
                NewConstant(50));
end;

class function NewBetweenExpression(aExpression: TDAWhereExpression; aLower: TDAWhereExpression; aUpper: TDAWhereExpression): TDAWhereExpression

Parameters:

• aExpression: expression
• aLower: The minimal value
• aUpper: The maximum value

---

Creates a new BetweenExpression.

• SQL:

Price BETWEEN 20 AND 50

• code:

with ClientDataModule.tbl_OrderDetails, DynamicWhere do begin
  Expression:=NewBetweenExpression(
                LogicalName, 
                'Price',
                NewConstant(20),
                NewConstant(50));
end;

class function NewBetweenExpression(const aExprTableName: string; const aExprFieldName: string; aLower: TDAWhereExpression; aUpper: TDAWhereExpression): TDAWhereExpression

Parameters:

• aExprTableName: table name
• aExprFieldName: field name
• aLower: The minimal value
• aUpper: The maximum value

---

Creates a new BetweenExpression.

• SQL:

Price BETWEEN 20 AND 50

• code:

with ClientDataModule.tbl_OrderDetails, DynamicWhere do begin
  Expression:=NewBetweenExpression(
                LogicalName, 
                'Price',
                20,
                50,
                datInteger);
end;

class function NewBetweenExpression(const aExprTableName: string; const aExprFieldName: string; aLowerValue: Variant; aUpperValue: Variant; aValuesDataType: TDADataType): TDAWhereExpression

Parameters:

• aExprTableName: table name
• aExprFieldName: field name
• aLowerValue: The minimal value
• aUpperValue: The maximum value
• aValuesDataType: data type of aLowerValue and aUpperValue

---

Creates a new BinaryExpression.

• SQL:

price > 20

• code:

with tbl_OrderDetails, DynamicWhere do
  Expression := NewBinaryExpression(
                  NewField(LogicalName,'price'),
                  dboGreater,
                  20);

class function NewBinaryExpression(aLeft: TDAWhereExpression; anOp: TDABinaryOperator; const aValue: Variant): TDAWhereExpression

Parameters:

• aLeft: Left expression
• anOp: Operator
• aValue: Value

---

Creates a new BinaryExpression.

• SQL:

price > 20

• code:

with tbl_OrderDetails, DynamicWhere do
  Expression := NewBinaryExpression(
                  NewField(LogicalName,'price'),
                  dboGreater,
                  20);

class function NewBinaryExpression(aLeft: TDAWhereExpression; anOp: TDABinaryOperator; const aValue: Variant; aType: TDADataType): TDAWhereExpression

Parameters:

• aLeft: Left expression
• anOp: Operator
• aValue: Value
• aType: Value type

---

Creates a new BinaryExpression.

• SQL:

price > 20

• code:

with tbl_OrderDetails, DynamicWhere do
  Expression := NewBinaryExpression(
                  NewField(LogicalName,'price'),
                  NewConstant(20),
                  dboGreater);

class function NewBinaryExpression(aLeft: TDAWhereExpression; aRight: TDAWhereExpression; anOp: TDABinaryOperator): TDAWhereExpression

Parameters:

• aLeft: Left expression
• aRight: Right expression
• anOp: Operator

---

Creates a new BinaryExpression.

• SQL:

price > 20

• code:

with tbl_OrderDetails, DynamicWhere do
  Expression := NewBinaryExpression(LogicalName,'price',dboGreater,20);

class function NewBinaryExpression(const aTableName: string; const aFieldName: string; anOp: TDABinaryOperator; const aValue: Variant): TDAWhereExpression

Parameters:

• aTableName: Table name
• aFieldName: Field name
• anOp: Operator
• aValue: Value

---

Creates a new BinaryExpression.

• SQL:

price > 20

• code:

with tbl_OrderDetails, DynamicWhere do
  Expression := NewBinaryExpression(LogicalName,'price',dboGreater,20,datInteger);

class function NewBinaryExpression(const aTableName: string; const aFieldName: string; anOp: TDABinaryOperator; const aValue: Variant; aType: TDADataType): TDAWhereExpression

Parameters:

• aTableName: Table name
• aFieldName: Field name
• anOp: Operator
• aValue: Value
• aType: Value type

---

Creates a new BinaryExpression.

• SQL:

price > :p_price

• code:

with tbl_OrderDetails, DynamicWhere do
  Expression := NewBinaryExpression(LogicalName,'price','p_price',datInteger,dboGreater);

class function NewBinaryExpression(const aTableName: string; const aFieldName: string; const aParameterName: string; aParameterType: TDADataType; anOp: TDABinaryOperator): TDAWhereExpression

Parameters:

• aTableName: Table name
• aFieldName: Field name
• aParameterName: Parameter name
• aParameterType: Parameter type
• anOp: Operator

---

Creates a new BinaryExpression.

• SQL:

price > 20 AND Amount <= 5

• code:

with tbl_OrderDetails, DynamicWhere do begin
    Expression:=NewBinaryExpressionList(
      [
        NewBinaryExpression(NewField(LogicalName,'price'),NewConstant(20),dboGreater),
        NewBinaryExpression(NewField(LogicalName,'Amount'),NewConstant(5),dboLessOrEqual)
      ],
      dboAnd);
end;

class function NewBinaryExpressionList(const aExpressions: array of TDAWhereExpression; anOp: TDABinaryOperator): TDAWhereExpression

Parameters:

• aExpressions: list of expressions
• anOp: operator

---

Creates a new ConstantExpression.

• SQL:

LastName = 'Frank'

• code:

with ClientDataModule.tbl_SALESMEN, DynamicWhere do begin
  Expression:=NewBinaryExpression(
                NewField(LogicalName,'LastName'),
                NewConstant('Frank'),
                dboEqual);
end;

class function NewConstant(const aValue: Variant): TDAWhereExpression

Parameters:

• aValue: value

---

Creates a new ConstantExpression.

• SQL:

LastName = 'Frank'

• code:

with ClientDataModule.tbl_SALESMEN, DynamicWhere do begin
  Expression:=NewBinaryExpression(
                NewField(LogicalName,'LastName'),
                NewConstant('Frank', datString),
                dboEqual);
end;

class function NewConstant(const aValue: Variant; aType: TDADataType): TDAWhereExpression

Parameters:

• aValue: value
• aType: value type

---

Creates a new DateTimePartExpression.

• SQL:

DATEPART(order_date, YEAR) = 2020

• code:

with ClientDataModule.tbl_ORDERS, DynamicWhere do begin
  Expression := NewBinaryExpression(
          NewDateTimePart(
            NewField(LogicalName, 'order_date'),
            ddpYear),
          NewConstant(2020, datInteger),
          dboEqual);
end;

class function NewDateTimePart(anExpression: TDAWhereExpression; aPart: TDADateTimePart): TDAWhereExpression

Parameters:

• anExpression: Expression
• aPart: DateTime part

---

Creates a new FieldExpression.

• SQL:

LastName = 'Frank'

• code: with ClientDataModule.tbl_SALESMEN, DynamicWhere do begin Expression:=NewBinaryExpression( NewField(LogicalName,'LastName'), NewConstant('Frank'), dboEqual); end;

class function NewField(const aTableName: string; const aFieldName: string): TDAWhereExpression

Parameters:

• aTableName: table name
• aFieldName: field name

---

Creates a new FunctionCallExpression.

• SQL:

LTRIM(customer_name) = 'TEST'

• code:

with ClientDataModule.tbl_ORDERS, DynamicWhere do begin
  Expression := NewBinaryExpression(
          NewFunctionCall(
            NewField('', 'customer_name'),
            dfkTrimLeft),
          NewConstant('TEST',datWideString),
          dboEqual);
end;

class function NewFunctionCall(anExpression: TDAWhereExpression; aFunctionKind: TDAFunctionKind): TDAWhereExpression

Parameters:

• anExpression: Expression
• aFunctionKind: Function kind

---

Creates a new NullExpression.

• SQL:

client_address IS NOT NULL

• code:

with ClientDataModule.tbl_CLIENTS,DynamicWhere do begin
  Expression:=NewBinaryExpression(
                 NewField(LogicalName,'Client_Address'),
                 NewNotNull(),
                 dboEqual);
end;

class function NewIsNotNull: TDAWhereExpression

---

Creates a new NullExpression.

• SQL:

client_address IS NOT NULL

• code:

with ClientDataModule.tbl_CLIENTS,DynamicWhere do
  Expression:=NewIsNotNull(LogicalName,'client_address');

class function NewIsNotNull(const aTableName: string; const aFieldName: string): TDAWhereExpression

Parameters:

• aTableName: table name
• aFieldName: field name

---

Creates a new ListExpression.

• SQL:

City IN ('Chicago IL','Seattle WA', 'Portland OR')

• code:

with ClientDataModule.tbl_SALESMEN,DynamicWhere do begin
  Expression:=NewBinaryExpression(
                NewField(LogicalName,'City'), 
                NewList([
                     NewConstant('Chicago IL',datString),
                     NewConstant('Seattle WA',datString),
                     NewConstant('Portland OR',datString)
                ]),
                dboIn);
end;

class function NewList(const aValues: array of TDAWhereExpression): TDAWhereExpression

Parameters:

• aValues: list of expressions

---

Creates a new MacroExpression.

• SQL:

'myValue' = :p

• code:

we := TDAWhereBuilder.NewBinaryExpression(
                          TDAWhereBuilder.NewMacro('SESSION_myvalue',
                          TDAWhereBuilder.NewParameter('p', datString),
                          dboEqual);

class function NewMacro(const aName: string): TDAWhereExpression

Parameters:

• aName: macro name

---

Creates a new MacroExpression.

• SQL:

SUBSTRING(lastname FROM 1 FOR 3) = 'Col'

• code:

we := TDAWhereBuilder.NewBinaryExpression(
                          TDAWhereBuilder.NewMacro('Copy',
                            [TDAWhereBuilder.NewField('','lastname'),
                             TDAWhereBuilder.NewConstant('1', datByte),
                             TDAWhereBuilder.NewConstant('3', datByte)]),
                          TDAWhereBuilder.NewConstant('Col'),
                          dboEqual);

class function NewMacro(const aName: string; const aValues: array of TDAWhereExpression): TDAWhereExpression

Parameters:

• aName: macro name
• aValues: array of expressions.

---

Creates a new DateTimeNowExpression.

• SQL:

order_date = GETDATE()

• code:

with ClientDataModule.tbl_ORDERS, DynamicWhere do begin
  Expression := NewBinaryExpression(
          NewField(LogicalName, 'order_date'),
          NewNow(),
          dboEqual);
end;

class function NewNow: TDAWhereExpression

---

Creates a new NullExpression.

• SQL:

client_address IS NULL

• code:

with ClientDataModule.tbl_CLIENTS,DynamicWhere do begin
  Expression:=NewBinaryExpression(
                 NewField(LogicalName,'Client_Address'),
                 NewNull(),
                 dboEqual);
end;

class function NewNull: TDAWhereExpression

---

Creates a new ParameterExpression.

• SQL:

lastname LIKE :p

• code:

with ClientDataModule.tbl_SALESMEN,DynamicWhere do begin
  Expression:=NewBinaryExpression(
                NewField(LogicalName,'lastname'),
                NewParameter('p', datString),
                dboLike);
end;

class function NewParameter(const aParameterName: string; aParameterType: TDADataType = datUnknown): TDAWhereExpression

Parameters:

• aParameterName: parameter name
• aParameterType: parameter type

---

Creates a new DateTimeTodayExpression.

• SQL:

order_date = CAST(GETDATE() AS DATE)

• code:

with ClientDataModule.tbl_ORDERS, DynamicWhere do begin
  Expression := NewBinaryExpression(
          NewField(LogicalName, 'order_date'),
          NewToday(),
          dboEquals);
end;

class function NewToday: TDAWhereExpression

---

Creates a new UnaryExpression.

• SQL:

lastname NOT LIKE 'D%'

• code:

with ClientDataModule.tbl_SALESMEN, DynamicWhere do begin
  Expression:=NewUnaryExpression(
                  NewBinaryExpression(
                    NewField(LogicalName,'lastname'),
                    NewConstant('D%', datString),
                    dboLike),
                  duoNot);
end;

class function NewUnaryExpression(anExpression: TDAWhereExpression; anOp: TDAUnaryOperator): TDAWhereExpression

Parameters:

• anExpression: Expression
• anOp: Operator

---

Provides access to the list of parameters defined for the command.

property Params: TDAParamCollection read write

---

Converts the specified BetweenExpression to SQL format and returns the SQL string.

function ProcessBetweenExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified BinaryExpression to SQL format and returns the SQL string.

function ProcessBinaryExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified ConstantExpression to SQL format and returns the SQL string.

function ProcessConstantExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified DateTimePartExpression to SQL format and returns the SQL string.

function ProcessDatePartExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified DateTimeNowExpression to SQL format and returns the SQL string.

function ProcessDateTimeNowExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified DateTimeTodayExpression to SQL format and returns the SQL string.

function ProcessDateTimeTodayExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified expression to SQL format and returns the SQL string.

function ProcessExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified FieldExpression to SQL format and returns the SQL string.

function ProcessFieldExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified FunctionCallExpression to SQL format and returns the SQL string.

function ProcessFunctionCallExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified ListExpression to SQL format and returns the SQL string.

function ProcessListExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified MacroExpression to SQL format and returns the SQL string.

function ProcessMacroExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified NullExpression to SQL format and returns the SQL string.

function ProcessNullExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified ParameterExpression to SQL format and returns the SQL string.

function ProcessParameterExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified UnaryExpression to SQL format and returns the SQL string.

function ProcessUnaryExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Represents the query builder object (TDASimpleQueryBuilder object) that is related to the current TDABaseSQLWhereBuilder object. This property is read-only.

property QueryBuilder: TDASimpleQueryBuilder read

---

Quotes field name

function QuoteFieldName(aTablename: string; aFieldName: string): string

Parameters:

• aTablename: table name.
• aFieldName: field name.

---

Reads where expression from the Json.

class function ReadFromJson(aItem: TROJSONObject): TDAWhereExpression

Parameters:

• aItem: Json object

---

Reads Where expression from XML node

class function ReadFromXml(xr: IXmlNode): TDAWhereExpression

Parameters:

• xr: node

---

Reads Where expression from string

class function StringToExpression(const Value: UnicodeString): TDAWhereExpression

Parameters:

• Value: given string

---

Writes where expression to the Json object.

class procedure WriteToJson(aItem: TROJSONObject; const aExpression: TDAWhereExpression)

Parameters:

• aItem: destination object
• aExpression: expression

---

Writes Where expression to XML node

class procedure WriteToXml(sw: IXmlNode; const aExpression: TDAWhereExpression)

Parameters:

• sw: node
• aExpression: expression

---

Represent Where expressions as XML node.

property Xml: UnicodeString read write

---

Reads Where expression from XML

class function XMLToExpression(const aXML: UnicodeString): TDAWhereExpression

Parameters:

• aXML: given XML

 

Returns object as JSON string

property AsJson: UnicodeString read write

---

Returns object as string according to Format

property AsString: UnicodeString read write

---

Returns object as XML string

property AsXml: UnicodeString read write

---

Column mapping.

property ColumnMapping: TDAColumnMappingCollection read write

---

Represents the database connection that relates to the current TDASQLWhereBuilder object. This property is read-only.

property Connection: IDACustomConnection read

---

Represents Where expressions.

property Expression: TDAWhereExpression read write

---

Format of serialization of Dynamic Where

property Format: TDynamicWhereFormat read write

---

Provides access to the list of parameters defined for the command.

property Params: TDAParamCollection read write

---

Represents the query builder object (TDASimpleQueryBuilder object) that is related to the current TDABaseSQLWhereBuilder object. This property is read-only.

property QueryBuilder: TDASimpleQueryBuilder read

---

Represent Where expressions as XML node.

property Xml: UnicodeString read write

 

Converts aExpression to JSON string

class function ExpressionToJson(const aExpression: TDAWhereExpression): UnicodeString

Parameters:

• aExpression: Expression

---

Converts aExpression to XML or JSON string.

Default format can be changed via defining/undefining DYNAMIC_WHERE_JSON_FORMAT definition in DataAbstract_user.inc file.

class function ExpressionToString(const aExpression: TDAWhereExpression): UnicodeString

Parameters:

• aExpression: Expression

---

Converts aExpression to XML string

class function ExpressionToXml(const aExpression: TDAWhereExpression): UnicodeString

Parameters:

• aExpression: Expression

---

Converts TDAWhereExpression to XML node

class function ExpressionToXmlNode(const aExpression: TDAWhereExpression): IXMLNode

Parameters:

• aExpression: expression

---

Reads Where expression from JSON

class function JsonToExpression(const aJson: UnicodeString): TDAWhereExpression

Parameters:

• aJson: given JSON

---

Creates a new BetweenExpression.

• SQL:

Price BETWEEN 20 AND 50

• code:

with ClientDataModule.tbl_OrderDetails, DynamicWhere do begin
  Expression:=NewBetweenExpression(
                NewField(LogicalName, 'Price'),
                NewConstant(20),
                NewConstant(50));
end;

class function NewBetweenExpression(aExpression: TDAWhereExpression; aLower: TDAWhereExpression; aUpper: TDAWhereExpression): TDAWhereExpression

Parameters:

• aExpression: expression
• aLower: The minimal value
• aUpper: The maximum value

---

Creates a new BetweenExpression.

• SQL:

Price BETWEEN 20 AND 50

• code:

with ClientDataModule.tbl_OrderDetails, DynamicWhere do begin
  Expression:=NewBetweenExpression(
                LogicalName, 
                'Price',
                NewConstant(20),
                NewConstant(50));
end;

class function NewBetweenExpression(const aExprTableName: string; const aExprFieldName: string; aLower: TDAWhereExpression; aUpper: TDAWhereExpression): TDAWhereExpression

Parameters:

• aExprTableName: table name
• aExprFieldName: field name
• aLower: The minimal value
• aUpper: The maximum value

---

Creates a new BetweenExpression.

• SQL:

Price BETWEEN 20 AND 50

• code:

with ClientDataModule.tbl_OrderDetails, DynamicWhere do begin
  Expression:=NewBetweenExpression(
                LogicalName, 
                'Price',
                20,
                50,
                datInteger);
end;

class function NewBetweenExpression(const aExprTableName: string; const aExprFieldName: string; aLowerValue: Variant; aUpperValue: Variant; aValuesDataType: TDADataType): TDAWhereExpression

Parameters:

• aExprTableName: table name
• aExprFieldName: field name
• aLowerValue: The minimal value
• aUpperValue: The maximum value
• aValuesDataType: data type of aLowerValue and aUpperValue

---

Creates a new BinaryExpression.

• SQL:

price > 20

• code:

with tbl_OrderDetails, DynamicWhere do
  Expression := NewBinaryExpression(
                  NewField(LogicalName,'price'),
                  dboGreater,
                  20);

class function NewBinaryExpression(aLeft: TDAWhereExpression; anOp: TDABinaryOperator; const aValue: Variant): TDAWhereExpression

Parameters:

• aLeft: Left expression
• anOp: Operator
• aValue: Value

---

Creates a new BinaryExpression.

• SQL:

price > 20

• code:

with tbl_OrderDetails, DynamicWhere do
  Expression := NewBinaryExpression(
                  NewField(LogicalName,'price'),
                  dboGreater,
                  20);

class function NewBinaryExpression(aLeft: TDAWhereExpression; anOp: TDABinaryOperator; const aValue: Variant; aType: TDADataType): TDAWhereExpression

Parameters:

• aLeft: Left expression
• anOp: Operator
• aValue: Value
• aType: Value type

---

Creates a new BinaryExpression.

• SQL:

price > 20

• code:

with tbl_OrderDetails, DynamicWhere do
  Expression := NewBinaryExpression(
                  NewField(LogicalName,'price'),
                  NewConstant(20),
                  dboGreater);

class function NewBinaryExpression(aLeft: TDAWhereExpression; aRight: TDAWhereExpression; anOp: TDABinaryOperator): TDAWhereExpression

Parameters:

• aLeft: Left expression
• aRight: Right expression
• anOp: Operator

---

Creates a new BinaryExpression.

• SQL:

price > 20

• code:

with tbl_OrderDetails, DynamicWhere do
  Expression := NewBinaryExpression(LogicalName,'price',dboGreater,20);

class function NewBinaryExpression(const aTableName: string; const aFieldName: string; anOp: TDABinaryOperator; const aValue: Variant): TDAWhereExpression

Parameters:

• aTableName: Table name
• aFieldName: Field name
• anOp: Operator
• aValue: Value

---

Creates a new BinaryExpression.

• SQL:

price > 20

• code:

with tbl_OrderDetails, DynamicWhere do
  Expression := NewBinaryExpression(LogicalName,'price',dboGreater,20,datInteger);

class function NewBinaryExpression(const aTableName: string; const aFieldName: string; anOp: TDABinaryOperator; const aValue: Variant; aType: TDADataType): TDAWhereExpression

Parameters:

• aTableName: Table name
• aFieldName: Field name
• anOp: Operator
• aValue: Value
• aType: Value type

---

Creates a new BinaryExpression.

• SQL:

price > :p_price

• code:

with tbl_OrderDetails, DynamicWhere do
  Expression := NewBinaryExpression(LogicalName,'price','p_price',datInteger,dboGreater);

class function NewBinaryExpression(const aTableName: string; const aFieldName: string; const aParameterName: string; aParameterType: TDADataType; anOp: TDABinaryOperator): TDAWhereExpression

Parameters:

• aTableName: Table name
• aFieldName: Field name
• aParameterName: Parameter name
• aParameterType: Parameter type
• anOp: Operator

---

Creates a new BinaryExpression.

• SQL:

price > 20 AND Amount <= 5

• code:

with tbl_OrderDetails, DynamicWhere do begin
    Expression:=NewBinaryExpressionList(
      [
        NewBinaryExpression(NewField(LogicalName,'price'),NewConstant(20),dboGreater),
        NewBinaryExpression(NewField(LogicalName,'Amount'),NewConstant(5),dboLessOrEqual)
      ],
      dboAnd);
end;

class function NewBinaryExpressionList(const aExpressions: array of TDAWhereExpression; anOp: TDABinaryOperator): TDAWhereExpression

Parameters:

• aExpressions: list of expressions
• anOp: operator

---

Creates a new ConstantExpression.

• SQL:

LastName = 'Frank'

• code:

with ClientDataModule.tbl_SALESMEN, DynamicWhere do begin
  Expression:=NewBinaryExpression(
                NewField(LogicalName,'LastName'),
                NewConstant('Frank'),
                dboEqual);
end;

class function NewConstant(const aValue: Variant): TDAWhereExpression

Parameters:

• aValue: value

---

Creates a new ConstantExpression.

• SQL:

LastName = 'Frank'

• code:

with ClientDataModule.tbl_SALESMEN, DynamicWhere do begin
  Expression:=NewBinaryExpression(
                NewField(LogicalName,'LastName'),
                NewConstant('Frank', datString),
                dboEqual);
end;

class function NewConstant(const aValue: Variant; aType: TDADataType): TDAWhereExpression

Parameters:

• aValue: value
• aType: value type

---

Creates a new DateTimePartExpression.

• SQL:

DATEPART(order_date, YEAR) = 2020

• code:

with ClientDataModule.tbl_ORDERS, DynamicWhere do begin
  Expression := NewBinaryExpression(
          NewDateTimePart(
            NewField(LogicalName, 'order_date'),
            ddpYear),
          NewConstant(2020, datInteger),
          dboEqual);
end;

class function NewDateTimePart(anExpression: TDAWhereExpression; aPart: TDADateTimePart): TDAWhereExpression

Parameters:

• anExpression: Expression
• aPart: DateTime part

---

Creates a new FieldExpression.

• SQL:

LastName = 'Frank'

• code: with ClientDataModule.tbl_SALESMEN, DynamicWhere do begin Expression:=NewBinaryExpression( NewField(LogicalName,'LastName'), NewConstant('Frank'), dboEqual); end;

class function NewField(const aTableName: string; const aFieldName: string): TDAWhereExpression

Parameters:

• aTableName: table name
• aFieldName: field name

---

Creates a new FunctionCallExpression.

• SQL:

LTRIM(customer_name) = 'TEST'

• code:

with ClientDataModule.tbl_ORDERS, DynamicWhere do begin
  Expression := NewBinaryExpression(
          NewFunctionCall(
            NewField('', 'customer_name'),
            dfkTrimLeft),
          NewConstant('TEST',datWideString),
          dboEqual);
end;

class function NewFunctionCall(anExpression: TDAWhereExpression; aFunctionKind: TDAFunctionKind): TDAWhereExpression

Parameters:

• anExpression: Expression
• aFunctionKind: Function kind

---

Creates a new NullExpression.

• SQL:

client_address IS NOT NULL

• code:

with ClientDataModule.tbl_CLIENTS,DynamicWhere do begin
  Expression:=NewBinaryExpression(
                 NewField(LogicalName,'Client_Address'),
                 NewNotNull(),
                 dboEqual);
end;

class function NewIsNotNull: TDAWhereExpression

---

Creates a new NullExpression.

• SQL:

client_address IS NOT NULL

• code:

with ClientDataModule.tbl_CLIENTS,DynamicWhere do
  Expression:=NewIsNotNull(LogicalName,'client_address');

class function NewIsNotNull(const aTableName: string; const aFieldName: string): TDAWhereExpression

Parameters:

• aTableName: table name
• aFieldName: field name

---

Creates a new ListExpression.

• SQL:

City IN ('Chicago IL','Seattle WA', 'Portland OR')

• code:

with ClientDataModule.tbl_SALESMEN,DynamicWhere do begin
  Expression:=NewBinaryExpression(
                NewField(LogicalName,'City'), 
                NewList([
                     NewConstant('Chicago IL',datString),
                     NewConstant('Seattle WA',datString),
                     NewConstant('Portland OR',datString)
                ]),
                dboIn);
end;

class function NewList(const aValues: array of TDAWhereExpression): TDAWhereExpression

Parameters:

• aValues: list of expressions

---

Creates a new MacroExpression.

• SQL:

'myValue' = :p

• code:

we := TDAWhereBuilder.NewBinaryExpression(
                          TDAWhereBuilder.NewMacro('SESSION_myvalue',
                          TDAWhereBuilder.NewParameter('p', datString),
                          dboEqual);

class function NewMacro(const aName: string): TDAWhereExpression

Parameters:

• aName: macro name

---

Creates a new MacroExpression.

• SQL:

SUBSTRING(lastname FROM 1 FOR 3) = 'Col'

• code:

we := TDAWhereBuilder.NewBinaryExpression(
                          TDAWhereBuilder.NewMacro('Copy',
                            [TDAWhereBuilder.NewField('','lastname'),
                             TDAWhereBuilder.NewConstant('1', datByte),
                             TDAWhereBuilder.NewConstant('3', datByte)]),
                          TDAWhereBuilder.NewConstant('Col'),
                          dboEqual);

class function NewMacro(const aName: string; const aValues: array of TDAWhereExpression): TDAWhereExpression

Parameters:

• aName: macro name
• aValues: array of expressions.

---

Creates a new DateTimeNowExpression.

• SQL:

order_date = GETDATE()

• code:

with ClientDataModule.tbl_ORDERS, DynamicWhere do begin
  Expression := NewBinaryExpression(
          NewField(LogicalName, 'order_date'),
          NewNow(),
          dboEqual);
end;

class function NewNow: TDAWhereExpression

---

Creates a new NullExpression.

• SQL:

client_address IS NULL

• code:

with ClientDataModule.tbl_CLIENTS,DynamicWhere do begin
  Expression:=NewBinaryExpression(
                 NewField(LogicalName,'Client_Address'),
                 NewNull(),
                 dboEqual);
end;

class function NewNull: TDAWhereExpression

---

Creates a new ParameterExpression.

• SQL:

lastname LIKE :p

• code:

with ClientDataModule.tbl_SALESMEN,DynamicWhere do begin
  Expression:=NewBinaryExpression(
                NewField(LogicalName,'lastname'),
                NewParameter('p', datString),
                dboLike);
end;

class function NewParameter(const aParameterName: string; aParameterType: TDADataType = datUnknown): TDAWhereExpression

Parameters:

• aParameterName: parameter name
• aParameterType: parameter type

---

Creates a new DateTimeTodayExpression.

• SQL:

order_date = CAST(GETDATE() AS DATE)

• code:

with ClientDataModule.tbl_ORDERS, DynamicWhere do begin
  Expression := NewBinaryExpression(
          NewField(LogicalName, 'order_date'),
          NewToday(),
          dboEquals);
end;

class function NewToday: TDAWhereExpression

---

Creates a new UnaryExpression.

• SQL:

lastname NOT LIKE 'D%'

• code:

with ClientDataModule.tbl_SALESMEN, DynamicWhere do begin
  Expression:=NewUnaryExpression(
                  NewBinaryExpression(
                    NewField(LogicalName,'lastname'),
                    NewConstant('D%', datString),
                    dboLike),
                  duoNot);
end;

class function NewUnaryExpression(anExpression: TDAWhereExpression; anOp: TDAUnaryOperator): TDAWhereExpression

Parameters:

• anExpression: Expression
• anOp: Operator

---

Reads where expression from the Json.

class function ReadFromJson(aItem: TROJSONObject): TDAWhereExpression

Parameters:

• aItem: Json object

---

Reads Where expression from XML node

class function ReadFromXml(xr: IXmlNode): TDAWhereExpression

Parameters:

• xr: node

---

Reads Where expression from string

class function StringToExpression(const Value: UnicodeString): TDAWhereExpression

Parameters:

• Value: given string

---

Writes where expression to the Json object.

class procedure WriteToJson(aItem: TROJSONObject; const aExpression: TDAWhereExpression)

Parameters:

• aItem: destination object
• aExpression: expression

---

Writes Where expression to XML node

class procedure WriteToXml(sw: IXmlNode; const aExpression: TDAWhereExpression)

Parameters:

• sw: node
• aExpression: expression

---

Reads Where expression from XML

class function XMLToExpression(const aXML: UnicodeString): TDAWhereExpression

Parameters:

• aXML: given XML

 

Creates a new instance.

constructor Create

---

Creates a new instance with a specified database connection.

constructor Create(AConnection: IDACustomConnection)

Parameters:

• AConnection: specified database connection.

---

Creates a new instance with a specified query builder object.

constructor Create(AQueryBuilder: TDASimpleQueryBuilder)

Parameters:

• AQueryBuilder: query builder

---

Clears all properties.

procedure Clear

---

Generates Where clause based on Expression

function CreateWhereClause: string

---

Generates field name according to column mapping and quotation.

function GenerateFieldName(aTablename: string; aFieldName: string): string

Parameters:

• aTablename: table name
• aFieldName: field name

---

Returns True is Expression is empty.

function IsEmpty: Boolean

---

Converts the specified BetweenExpression to SQL format and returns the SQL string.

function ProcessBetweenExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified BinaryExpression to SQL format and returns the SQL string.

function ProcessBinaryExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified ConstantExpression to SQL format and returns the SQL string.

function ProcessConstantExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified DateTimePartExpression to SQL format and returns the SQL string.

function ProcessDatePartExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified DateTimeNowExpression to SQL format and returns the SQL string.

function ProcessDateTimeNowExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified DateTimeTodayExpression to SQL format and returns the SQL string.

function ProcessDateTimeTodayExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified expression to SQL format and returns the SQL string.

function ProcessExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified FieldExpression to SQL format and returns the SQL string.

function ProcessFieldExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified FunctionCallExpression to SQL format and returns the SQL string.

function ProcessFunctionCallExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified ListExpression to SQL format and returns the SQL string.

function ProcessListExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified MacroExpression to SQL format and returns the SQL string.

function ProcessMacroExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified NullExpression to SQL format and returns the SQL string.

function ProcessNullExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified ParameterExpression to SQL format and returns the SQL string.

function ProcessParameterExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Converts the specified UnaryExpression to SQL format and returns the SQL string.

function ProcessUnaryExpression(AExpression: TDAWhereExpression): string

Parameters:

• AExpression: expression

---

Quotes field name

function QuoteFieldName(aTablename: string; aFieldName: string): string

Parameters:

• aTablename: table name.
• aFieldName: field name.