TDABaseSQLWhereBuilder

Overview

The TDABaseSQLWhereBuilder class represents a rich set of methods to build SQL WHERE statements from Dynamic Where. The TDABaseSQLWhereBuilder class is used as part of the full query builder infrastructure that automatically builds select statements for stAutoSQL.

The TDABaseSQLWhereBuilder class is the ancestor class for the TDASQL92WhereBuilder class that is used to generate auto SQL statements for all database drivers types. The descendant TDASQL92WhereBuilder class overrides virtual methods for processing between, binary, null and unary expressions to build SQL WHERE statements according to the SQL92 standart.

You do not need to use this class directly; this class and its descendants are used to build complete SQL statements.

Location


 

constructor Create    (declared in TDAWhereBuilder)

Creates a new instance.

constructor Create

constructor Create (TDASimpleQueryBuilder)  overload

Creates a new instance with a specified query builder object.

constructor Create(AQueryBuilder: TDASimpleQueryBuilder)

Parameters:

  • AQueryBuilder: query builder

AsJson    (declared in TDAWhereBuilder)

Returns object as JSON string

property AsJson: UnicodeString read write

AsString    (declared in TDAWhereBuilder)

Returns object as string according to Format

property AsString: UnicodeString read write

AsXml    (declared in TDAWhereBuilder)

Returns object as XML string

property AsXml: UnicodeString read write

Clear    (declared in TDAWhereBuilder)

Clears all properties.

procedure Clear

ColumnMapping    (declared in TDAWhereBuilder)

Column mapping.

property ColumnMapping: TDAColumnMappingCollection read write

CreateWhereClause

Generates Where clause based on Expression

function CreateWhereClause: string

Expression    (declared in TDAWhereBuilder)

Represents Where expressions.

property Expression: TDAWhereExpression read write

ExpressionToJson    (declared in TDAWhereBuilder)

Converts aExpression to JSON string

class function ExpressionToJson(const aExpression: TDAWhereExpression): UnicodeString

Parameters:

  • aExpression: Expression

ExpressionToString    (declared in TDAWhereBuilder)

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

ExpressionToXml    (declared in TDAWhereBuilder)

Converts aExpression to XML string

class function ExpressionToXml(const aExpression: TDAWhereExpression): UnicodeString

Parameters:

  • aExpression: Expression

ExpressionToXmlNode    (declared in TDAWhereBuilder)

Converts TDAWhereExpression to XML node

class function ExpressionToXmlNode(const aExpression: TDAWhereExpression): IXMLNode

Parameters:

  • aExpression: expression

Format    (declared in TDAWhereBuilder)

Format of serialization of Dynamic Where

property Format: TDynamicWhereFormat read write

GenerateFieldName  protected virtual

Generates field name according to column mapping and quotation.

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

Parameters:

  • aTablename: table name
  • aFieldName: field name

IsEmpty    (declared in TDAWhereBuilder)

Returns True is Expression is empty.

function IsEmpty: Boolean

JsonToExpression    (declared in TDAWhereBuilder)

Reads Where expression from JSON

class function JsonToExpression(const aJson: UnicodeString): TDAWhereExpression

Parameters:

  • aJson: given JSON

NewBetweenExpression (TDAWhereExpression, TDAWhereExpression, TDAWhereExpression): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewBetweenExpression (string, string, TDAWhereExpression, TDAWhereExpression): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewBetweenExpression (string, string, Variant, Variant, TDADataType): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewBinaryExpression (TDAWhereExpression, TDABinaryOperator, Variant): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewBinaryExpression (TDAWhereExpression, TDABinaryOperator, Variant, TDADataType): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewBinaryExpression (TDAWhereExpression, TDAWhereExpression, TDABinaryOperator): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewBinaryExpression (string, string, TDABinaryOperator, Variant): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewBinaryExpression (string, string, TDABinaryOperator, Variant, TDADataType): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewBinaryExpression (string, string, string, TDADataType, TDABinaryOperator): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewBinaryExpressionList    (declared in TDAWhereBuilder)

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

NewConstant (Variant): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewConstant (Variant, TDADataType): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewDateTimePart    (declared in TDAWhereBuilder)

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

NewField    (declared in TDAWhereBuilder)

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

NewFunctionCall    (declared in TDAWhereBuilder)

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

NewIsNotNull  overload    (declared in TDAWhereBuilder)

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

NewIsNotNull (string, string): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewList    (declared in TDAWhereBuilder)

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

NewMacro (string): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewMacro (string, array of TDAWhereExpression): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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.

NewNow    (declared in TDAWhereBuilder)

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

NewNull    (declared in TDAWhereBuilder)

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

NewParameter    (declared in TDAWhereBuilder)

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

NewToday    (declared in TDAWhereBuilder)

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

NewUnaryExpression    (declared in TDAWhereBuilder)

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

Params    (declared in TDAWhereBuilder)

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

property Params: TDAParamCollection read write

ProcessBetweenExpression  protected virtual

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

function ProcessBetweenExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessBinaryExpression  protected virtual abstract

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

function ProcessBinaryExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessConstantExpression  protected virtual

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

function ProcessConstantExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessDatePartExpression  protected virtual

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

function ProcessDatePartExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessDateTimeNowExpression  protected virtual

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

function ProcessDateTimeNowExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessDateTimeTodayExpression  protected virtual

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

function ProcessDateTimeTodayExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessExpression  protected

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

function ProcessExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessFieldExpression  protected virtual

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

function ProcessFieldExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessFunctionCallExpression  protected virtual

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

function ProcessFunctionCallExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessListExpression  protected virtual

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

function ProcessListExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessMacroExpression  protected virtual

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

function ProcessMacroExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessNullExpression  protected virtual abstract

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

function ProcessNullExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessParameterExpression  protected virtual

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

function ProcessParameterExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessUnaryExpression  protected virtual abstract

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

function ProcessUnaryExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

QueryBuilder

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

property QueryBuilder: TDASimpleQueryBuilder read

QuoteFieldName  protected virtual

Quotes field name

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

Parameters:

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

ReadFromJson  protected virtual    (declared in TDAWhereBuilder)

Reads where expression from the Json.

class function ReadFromJson(aItem: TROJSONObject): TDAWhereExpression

Parameters:

  • aItem: Json object

ReadFromXml  protected virtual    (declared in TDAWhereBuilder)

Reads Where expression from XML node

class function ReadFromXml(xr: IXmlNode): TDAWhereExpression

Parameters:

  • xr: node

StringToExpression    (declared in TDAWhereBuilder)

Reads Where expression from string

class function StringToExpression(const Value: UnicodeString): TDAWhereExpression

Parameters:

  • Value: given string

WriteToJson  protected virtual    (declared in TDAWhereBuilder)

Writes where expression to the Json object.

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

Parameters:

  • aItem: destination object
  • aExpression: expression

WriteToXml  protected virtual    (declared in TDAWhereBuilder)

Writes Where expression to XML node

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

Parameters:

  • sw: node
  • aExpression: expression

Xml    (declared in TDAWhereBuilder)

Represent Where expressions as XML node.

property Xml: UnicodeString read write

XMLToExpression    (declared in TDAWhereBuilder)

Reads Where expression from XML

class function XMLToExpression(const aXML: UnicodeString): TDAWhereExpression

Parameters:

  • aXML: given XML

 

AsJson    (declared in TDAWhereBuilder)

Returns object as JSON string

property AsJson: UnicodeString read write

AsString    (declared in TDAWhereBuilder)

Returns object as string according to Format

property AsString: UnicodeString read write

AsXml    (declared in TDAWhereBuilder)

Returns object as XML string

property AsXml: UnicodeString read write

ColumnMapping    (declared in TDAWhereBuilder)

Column mapping.

property ColumnMapping: TDAColumnMappingCollection read write

Expression    (declared in TDAWhereBuilder)

Represents Where expressions.

property Expression: TDAWhereExpression read write

Format    (declared in TDAWhereBuilder)

Format of serialization of Dynamic Where

property Format: TDynamicWhereFormat read write

Params    (declared in TDAWhereBuilder)

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

property Params: TDAParamCollection read write

QueryBuilder

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

property QueryBuilder: TDASimpleQueryBuilder read

Xml    (declared in TDAWhereBuilder)

Represent Where expressions as XML node.

property Xml: UnicodeString read write

 

ExpressionToJson    (declared in TDAWhereBuilder)

Converts aExpression to JSON string

class function ExpressionToJson(const aExpression: TDAWhereExpression): UnicodeString

Parameters:

  • aExpression: Expression

ExpressionToString    (declared in TDAWhereBuilder)

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

ExpressionToXml    (declared in TDAWhereBuilder)

Converts aExpression to XML string

class function ExpressionToXml(const aExpression: TDAWhereExpression): UnicodeString

Parameters:

  • aExpression: Expression

ExpressionToXmlNode    (declared in TDAWhereBuilder)

Converts TDAWhereExpression to XML node

class function ExpressionToXmlNode(const aExpression: TDAWhereExpression): IXMLNode

Parameters:

  • aExpression: expression

JsonToExpression    (declared in TDAWhereBuilder)

Reads Where expression from JSON

class function JsonToExpression(const aJson: UnicodeString): TDAWhereExpression

Parameters:

  • aJson: given JSON

NewBetweenExpression (TDAWhereExpression, TDAWhereExpression, TDAWhereExpression): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewBetweenExpression (string, string, TDAWhereExpression, TDAWhereExpression): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewBetweenExpression (string, string, Variant, Variant, TDADataType): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewBinaryExpression (TDAWhereExpression, TDABinaryOperator, Variant): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewBinaryExpression (TDAWhereExpression, TDABinaryOperator, Variant, TDADataType): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewBinaryExpression (TDAWhereExpression, TDAWhereExpression, TDABinaryOperator): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewBinaryExpression (string, string, TDABinaryOperator, Variant): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewBinaryExpression (string, string, TDABinaryOperator, Variant, TDADataType): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewBinaryExpression (string, string, string, TDADataType, TDABinaryOperator): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewBinaryExpressionList    (declared in TDAWhereBuilder)

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

NewConstant (Variant): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewConstant (Variant, TDADataType): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewDateTimePart    (declared in TDAWhereBuilder)

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

NewField    (declared in TDAWhereBuilder)

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

NewFunctionCall    (declared in TDAWhereBuilder)

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

NewIsNotNull  overload    (declared in TDAWhereBuilder)

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

NewIsNotNull (string, string): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewList    (declared in TDAWhereBuilder)

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

NewMacro (string): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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

NewMacro (string, array of TDAWhereExpression): TDAWhereExpression  overload    (declared in TDAWhereBuilder)

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.

NewNow    (declared in TDAWhereBuilder)

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

NewNull    (declared in TDAWhereBuilder)

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

NewParameter    (declared in TDAWhereBuilder)

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

NewToday    (declared in TDAWhereBuilder)

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

NewUnaryExpression    (declared in TDAWhereBuilder)

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

ReadFromJson  protected virtual    (declared in TDAWhereBuilder)

Reads where expression from the Json.

class function ReadFromJson(aItem: TROJSONObject): TDAWhereExpression

Parameters:

  • aItem: Json object

ReadFromXml  protected virtual    (declared in TDAWhereBuilder)

Reads Where expression from XML node

class function ReadFromXml(xr: IXmlNode): TDAWhereExpression

Parameters:

  • xr: node

StringToExpression    (declared in TDAWhereBuilder)

Reads Where expression from string

class function StringToExpression(const Value: UnicodeString): TDAWhereExpression

Parameters:

  • Value: given string

WriteToJson  protected virtual    (declared in TDAWhereBuilder)

Writes where expression to the Json object.

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

Parameters:

  • aItem: destination object
  • aExpression: expression

WriteToXml  protected virtual    (declared in TDAWhereBuilder)

Writes Where expression to XML node

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

Parameters:

  • sw: node
  • aExpression: expression

XMLToExpression    (declared in TDAWhereBuilder)

Reads Where expression from XML

class function XMLToExpression(const aXML: UnicodeString): TDAWhereExpression

Parameters:

  • aXML: given XML

 

constructor Create    (declared in TDAWhereBuilder)

Creates a new instance.

constructor Create

constructor Create (TDASimpleQueryBuilder)  overload

Creates a new instance with a specified query builder object.

constructor Create(AQueryBuilder: TDASimpleQueryBuilder)

Parameters:

  • AQueryBuilder: query builder

Clear    (declared in TDAWhereBuilder)

Clears all properties.

procedure Clear

CreateWhereClause

Generates Where clause based on Expression

function CreateWhereClause: string

GenerateFieldName  protected virtual

Generates field name according to column mapping and quotation.

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

Parameters:

  • aTablename: table name
  • aFieldName: field name

IsEmpty    (declared in TDAWhereBuilder)

Returns True is Expression is empty.

function IsEmpty: Boolean

ProcessBetweenExpression  protected virtual

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

function ProcessBetweenExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessBinaryExpression  protected virtual abstract

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

function ProcessBinaryExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessConstantExpression  protected virtual

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

function ProcessConstantExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessDatePartExpression  protected virtual

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

function ProcessDatePartExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessDateTimeNowExpression  protected virtual

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

function ProcessDateTimeNowExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessDateTimeTodayExpression  protected virtual

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

function ProcessDateTimeTodayExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessExpression  protected

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

function ProcessExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessFieldExpression  protected virtual

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

function ProcessFieldExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessFunctionCallExpression  protected virtual

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

function ProcessFunctionCallExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessListExpression  protected virtual

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

function ProcessListExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessMacroExpression  protected virtual

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

function ProcessMacroExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessNullExpression  protected virtual abstract

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

function ProcessNullExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessParameterExpression  protected virtual

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

function ProcessParameterExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessUnaryExpression  protected virtual abstract

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

function ProcessUnaryExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

QuoteFieldName  protected virtual

Quotes field name

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

Parameters:

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