TDASQLWhereBuilder

Overview

TDASQLWhereBuilder adds connection support to TDABaseSQLWhereBuilder

Location


 

constructor Create    (declared in TDAWhereBuilder)

Creates a new instance.

constructor Create

constructor Create (IDACustomConnection)  overload

Creates a new instance with a specified database connection.

constructor Create(AConnection: IDACustomConnection)

Parameters:

  • AConnection: specified database connection.

constructor Create (TDASimpleQueryBuilder)  overload    (declared in TDABaseSQLWhereBuilder)

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

Connection

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

property Connection: IDACustomConnection read

CreateWhereClause    (declared in TDABaseSQLWhereBuilder)

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    (declared in TDABaseSQLWhereBuilder)

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    (declared in TDABaseSQLWhereBuilder)

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

function ProcessBetweenExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessBinaryExpression  protected virtual abstract    (declared in TDABaseSQLWhereBuilder)

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

function ProcessBinaryExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessConstantExpression  protected virtual    (declared in TDABaseSQLWhereBuilder)

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

function ProcessConstantExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessDatePartExpression  protected virtual    (declared in TDABaseSQLWhereBuilder)

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

function ProcessDatePartExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessDateTimeNowExpression  protected virtual    (declared in TDABaseSQLWhereBuilder)

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

function ProcessDateTimeNowExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessDateTimeTodayExpression  protected virtual    (declared in TDABaseSQLWhereBuilder)

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

function ProcessDateTimeTodayExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessExpression  protected    (declared in TDABaseSQLWhereBuilder)

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

function ProcessExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessFieldExpression  protected virtual    (declared in TDABaseSQLWhereBuilder)

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

function ProcessFieldExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessFunctionCallExpression  protected virtual    (declared in TDABaseSQLWhereBuilder)

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

function ProcessFunctionCallExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessListExpression  protected virtual    (declared in TDABaseSQLWhereBuilder)

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

function ProcessListExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessMacroExpression  protected virtual    (declared in TDABaseSQLWhereBuilder)

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

function ProcessMacroExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessNullExpression  protected virtual abstract    (declared in TDABaseSQLWhereBuilder)

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

function ProcessNullExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessParameterExpression  protected virtual    (declared in TDABaseSQLWhereBuilder)

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

function ProcessParameterExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessUnaryExpression  protected virtual abstract    (declared in TDABaseSQLWhereBuilder)

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

function ProcessUnaryExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

QueryBuilder    (declared in TDABaseSQLWhereBuilder)

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 override

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

Connection

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

property Connection: IDACustomConnection read

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    (declared in TDABaseSQLWhereBuilder)

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 (IDACustomConnection)  overload

Creates a new instance with a specified database connection.

constructor Create(AConnection: IDACustomConnection)

Parameters:

  • AConnection: specified database connection.

constructor Create (TDASimpleQueryBuilder)  overload    (declared in TDABaseSQLWhereBuilder)

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    (declared in TDABaseSQLWhereBuilder)

Generates Where clause based on Expression

function CreateWhereClause: string

GenerateFieldName  protected virtual    (declared in TDABaseSQLWhereBuilder)

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    (declared in TDABaseSQLWhereBuilder)

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

function ProcessBetweenExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessBinaryExpression  protected virtual abstract    (declared in TDABaseSQLWhereBuilder)

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

function ProcessBinaryExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessConstantExpression  protected virtual    (declared in TDABaseSQLWhereBuilder)

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

function ProcessConstantExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessDatePartExpression  protected virtual    (declared in TDABaseSQLWhereBuilder)

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

function ProcessDatePartExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessDateTimeNowExpression  protected virtual    (declared in TDABaseSQLWhereBuilder)

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

function ProcessDateTimeNowExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessDateTimeTodayExpression  protected virtual    (declared in TDABaseSQLWhereBuilder)

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

function ProcessDateTimeTodayExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessExpression  protected    (declared in TDABaseSQLWhereBuilder)

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

function ProcessExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessFieldExpression  protected virtual    (declared in TDABaseSQLWhereBuilder)

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

function ProcessFieldExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessFunctionCallExpression  protected virtual    (declared in TDABaseSQLWhereBuilder)

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

function ProcessFunctionCallExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessListExpression  protected virtual    (declared in TDABaseSQLWhereBuilder)

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

function ProcessListExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessMacroExpression  protected virtual    (declared in TDABaseSQLWhereBuilder)

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

function ProcessMacroExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessNullExpression  protected virtual abstract    (declared in TDABaseSQLWhereBuilder)

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

function ProcessNullExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessParameterExpression  protected virtual    (declared in TDABaseSQLWhereBuilder)

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

function ProcessParameterExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

ProcessUnaryExpression  protected virtual abstract    (declared in TDABaseSQLWhereBuilder)

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

function ProcessUnaryExpression(AExpression: TDAWhereExpression): string

Parameters:

  • AExpression: expression

QuoteFieldName  protected override

Quotes field name

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

Parameters:

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