TDASQLWhereBuilder
Overview
TDASQLWhereBuilder adds connection support to TDABaseSQLWhereBuilder
Location
- Unit: uDAQueryBuilder.pas
- Ancestry: TDAWhereBuilder | TDABaseSQLWhereBuilder | TDASQLWhereBuilder
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)
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
andaUpperValue
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)
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
andaUpperValue
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.
- Dynamic Where
- http://en.wikipedia.org/wiki/SQL-92
- TDASQL92WhereBuilder