TDAQueryBuilder

Overview

The TDAQueryBuilder class contains a set of methods to build complete SQL SELECT statements.

You do not need to use this class directly. It is used as the base class for the TDASQL92QueryBuilder that is utilized to generate auto SQL statements for all database driver types.

Location


 

constructor Create  reintroduce overload virtual

Creates an new instance

constructor Create

AddCrossJoin    (declared in TDASimpleQueryBuilder)

Adds ATable to MainTable.JoinSourceTables (see TDAJoinDataTable).

procedure AddCrossJoin(ATable: string)

Parameters:

  • ATable: table name

AddGroupBy    (declared in TDASimpleQueryBuilder)

Adds an ATable/AField pair as TDATableFieldCollectionItem to the GroupBy collection.

procedure AddGroupBy(ATable: string; AField: string)

Parameters:

  • ATable: table name
  • AField: field name

AddJoin (string, string, string, string, TDAJoinType)  overload    (declared in TDASimpleQueryBuilder)

Adds a new item to MainTable.JoinSourceTables (see TDAJoinDataTable).

procedure AddJoin(AJoinTable: string; AJoinFieldName: string; AJoinToTableName: string; AJoinToFieldName: string; AJoinType: TDAJoinType)

Parameters:

  • AJoinTable: join table
  • AJoinFieldName: join field name
  • AJoinToTableName: join to table name
  • AJoinToFieldName: join to field name
  • AJoinType: type

AddJoin (string, array of string, string, array of string, TDAJoinType)  overload    (declared in TDASimpleQueryBuilder)

Adds a new item for each AJoinToFieldNames to MainTable.JoinSourceTables (see TDAJoinDataTable).

procedure AddJoin(AJoinTable: string; AJoinFieldNames: array of string; AJoinToTableName: string; AJoinToFieldNames: array of string; AJoinType: TDAJoinType)

Parameters:

  • AJoinTable: join table
  • AJoinFieldNames: join field name
  • AJoinToTableName: join to table name
  • AJoinToFieldNames: join to field name
  • AJoinType: type

AddOrderBy

Adds a new OrderBy clause with a specified table name and field name to the OrderBy collection.

procedure AddOrderBy(ATable: string; AField: string; AAscMode: Boolean)

Parameters:

  • ATable: Specified table
  • AField: Specified field in the table
  • AAscMode: mode

AddSelect    (declared in TDASimpleQueryBuilder)

Adds an ATable/AField pair as TDATableFieldCollectionItem to the Select collection.

procedure AddSelect(ATable: string; AField: string)

Parameters:

  • ATable: table name
  • AField: field name

AddUnion

Adds a new union to the Union collection and returns a new TDAQueryBuilder object.

function AddUnion: TDAQueryBuilder

Assign  override

Copies data from a given source.

procedure Assign(Source: TPersistent)

Parameters:

  • Source: Instance whose properties will be copied

Clear  override

Clears all properties.

procedure Clear

ColumnMapping    (declared in TDASimpleQueryBuilder)

Note: TDASimpleQueryBuilder does not modify ColumnMapping.

property ColumnMapping: TDAColumnMappingCollection read write

Connection

The database connection.

property Connection: IDACustomConnection read write

CreateFieldGenerator  protected virtual abstract    (declared in TDASimpleQueryBuilder)

Creates TDABaseFieldGenerator.

function CreateFieldGenerator: TDABaseFieldGenerator

CreateGroupByClause  protected virtual    (declared in TDASimpleQueryBuilder)

Generates the corresponding part of the SQL clause based on the content of GroupBy.

function CreateGroupByClause: string

CreateOrderByClause  protected virtual

Converts the OrderBy collection to SQL format and returns the SQL string in the format
Table1.Field1,...,TableN.FieldN, where the Table and the Field are properties of the TDAOrderByItem (the item of the OrderBy collection).

function CreateOrderByClause: string

CreateOrderByCollection  protected virtual abstract

Creates TDAOrderByCollection

function CreateOrderByCollection: TDAOrderByCollection

CreateSelectClause  protected virtual    (declared in TDASimpleQueryBuilder)

Generates the corresponding part of the SQL clause based on the content of Select and Options.

function CreateSelectClause: string

CreateTableClause  protected virtual abstract    (declared in TDASimpleQueryBuilder)

Builds an SQL statement based on a specified table clause and returns it.

function CreateTableClause: string

CreateUnionClause  protected virtual

Creates a new union clause

function CreateUnionClause(const AUnion: TDAQueryBuilder): string

Parameters:

  • AUnion: union

CreateWhereBuilder  protected virtual abstract    (declared in TDASimpleQueryBuilder)

Returns a new WHERE Builder object that will contain a WHERE statement of one of the following types: TDABinaryExpression, TDAUnaryExpression, TDAConstantExpression, TDAListExpression, TDAParameterExpression, TDAFieldExpression, TDANullExpression, TDAMacroExpression or TDABetweenExpression.

function CreateWhereBuilder: TDABaseSQLWhereBuilder

CreateWhereClause  virtual    (declared in TDASimpleQueryBuilder)

Generates the corresponding part of the SQL clause.

function CreateWhereClause: string

FieldGenerator    (declared in TDASimpleQueryBuilder)

Referense to TDABaseFieldGenerator that contains rules for generating field name

property FieldGenerator: TDABaseFieldGenerator read

GenerateFieldName  protected    (declared in TDASimpleQueryBuilder)

Generates field name according to column mapping and quotation.

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

Parameters:

  • aTablename: table name
  • aFieldName: field name
  • aProcessMapping: Controls if GetMappingTableField is applied to aFieldName. true by default.

GenerateSelectClauseWithFieldNames  protected override

Generates Select part of SQL as a comma-separated list of required fields.

function GenerateSelectClauseWithFieldNames: string

GenerateSelectSQL  override

Generates an SQL SELECT statement based on the Unions and the OrderBy collection and returns an SQL string that represents the complete SELECT statement. The result string is different for different kinds of connections, meaning that this function is based on the specified connection (Connection property of the TDASimpleQueryBuilder ancestor class).

function GenerateSelectSQL: string

GroupBy    (declared in TDASimpleQueryBuilder)

Returns the TCollection of the TDATableFieldCollectionItem. Its value is changed by TDASimpleQueryBuilder and TDASimpleQueryBuilder.

property GroupBy: TDAGroupByCollection read

intCreate  protected override

Performs initial initialization

procedure intCreate

MainTable    (declared in TDASimpleQueryBuilder)

Represents the target data set (see TDAJoinDataTable, TDAJoinDataTable).

property MainTable: TDAQueryBuilderTable read

MaxRecords    (declared in TDASimpleQueryBuilder)

This property allows to limit the count of rows of the request. Put it as -1 to receive all available records.

property MaxRecords: Integer read write

Options    (declared in TDASimpleQueryBuilder)

Holds the set of TDAQueryBuilderOption.

property Options: TDAQueryBuilderOptions read write

OrderBy

Represents the collection of OrderBy statements.

property OrderBy: TDAOrderByCollection read

QuoteFieldNameIfNeeded  protected    (declared in TDASimpleQueryBuilder)

Quotes field name if is required.

function QuoteFieldNameIfNeeded(const aTableName: string; const aFieldName: string): string

Parameters:

  • aTableName: table name.
  • aFieldName: field name.

QuoteIdentifierIfNeeded  protected    (declared in TDASimpleQueryBuilder)

If Connection is assigned, the method delegates the call to it. If IdentifierNeedsQuoting returns true, the call is delegated to QuoteIdentifier, otherwise the unchanged iIdentifier is returned.

function QuoteIdentifierIfNeeded(const iIdentifier: string): string

Parameters:

  • iIdentifier: identifier.

Select    (declared in TDASimpleQueryBuilder)

Returns the TCollection of the TDATableFieldCollectionItem.
Its value is changed by TDASimpleQueryBuilder and TDASimpleQueryBuilder.

property Select: TDASelectCollection read

SelectSkip    (declared in TDASimpleQueryBuilder)

Allows to suppress the given number of rows before starting to return output.

property SelectSkip: Integer read write

Unions

Represents the collection of Union statements.

property Unions: TDAUnionCollections read

Validate  protected virtual    (declared in TDASimpleQueryBuilder)

Raises an exception if MainTable.MasterTable is empty.

procedure Validate

Where    (declared in TDASimpleQueryBuilder)

Returns the TDASQLWhereBuilder that is used to dynamically build the WHERE part of the SQL statement. Calls GetWhereBuilder, which in turn calls the abstract TDASimpleQueryBuilder. The result is cached for subsequent calls.

property Where: TDABaseSQLWhereBuilder read

 

ColumnMapping    (declared in TDASimpleQueryBuilder)

Note: TDASimpleQueryBuilder does not modify ColumnMapping.

property ColumnMapping: TDAColumnMappingCollection read write

Connection

The database connection.

property Connection: IDACustomConnection read write

FieldGenerator    (declared in TDASimpleQueryBuilder)

Referense to TDABaseFieldGenerator that contains rules for generating field name

property FieldGenerator: TDABaseFieldGenerator read

GroupBy    (declared in TDASimpleQueryBuilder)

Returns the TCollection of the TDATableFieldCollectionItem. Its value is changed by TDASimpleQueryBuilder and TDASimpleQueryBuilder.

property GroupBy: TDAGroupByCollection read

MainTable    (declared in TDASimpleQueryBuilder)

Represents the target data set (see TDAJoinDataTable, TDAJoinDataTable).

property MainTable: TDAQueryBuilderTable read

MaxRecords    (declared in TDASimpleQueryBuilder)

This property allows to limit the count of rows of the request. Put it as -1 to receive all available records.

property MaxRecords: Integer read write

Options    (declared in TDASimpleQueryBuilder)

Holds the set of TDAQueryBuilderOption.

property Options: TDAQueryBuilderOptions read write

OrderBy

Represents the collection of OrderBy statements.

property OrderBy: TDAOrderByCollection read

Select    (declared in TDASimpleQueryBuilder)

Returns the TCollection of the TDATableFieldCollectionItem.
Its value is changed by TDASimpleQueryBuilder and TDASimpleQueryBuilder.

property Select: TDASelectCollection read

SelectSkip    (declared in TDASimpleQueryBuilder)

Allows to suppress the given number of rows before starting to return output.

property SelectSkip: Integer read write

Unions

Represents the collection of Union statements.

property Unions: TDAUnionCollections read

Where    (declared in TDASimpleQueryBuilder)

Returns the TDASQLWhereBuilder that is used to dynamically build the WHERE part of the SQL statement. Calls GetWhereBuilder, which in turn calls the abstract TDASimpleQueryBuilder. The result is cached for subsequent calls.

property Where: TDABaseSQLWhereBuilder read

 

constructor Create  reintroduce overload virtual

Creates an new instance

constructor Create

AddCrossJoin    (declared in TDASimpleQueryBuilder)

Adds ATable to MainTable.JoinSourceTables (see TDAJoinDataTable).

procedure AddCrossJoin(ATable: string)

Parameters:

  • ATable: table name

AddGroupBy    (declared in TDASimpleQueryBuilder)

Adds an ATable/AField pair as TDATableFieldCollectionItem to the GroupBy collection.

procedure AddGroupBy(ATable: string; AField: string)

Parameters:

  • ATable: table name
  • AField: field name

AddJoin (string, string, string, string, TDAJoinType)  overload    (declared in TDASimpleQueryBuilder)

Adds a new item to MainTable.JoinSourceTables (see TDAJoinDataTable).

procedure AddJoin(AJoinTable: string; AJoinFieldName: string; AJoinToTableName: string; AJoinToFieldName: string; AJoinType: TDAJoinType)

Parameters:

  • AJoinTable: join table
  • AJoinFieldName: join field name
  • AJoinToTableName: join to table name
  • AJoinToFieldName: join to field name
  • AJoinType: type

AddJoin (string, array of string, string, array of string, TDAJoinType)  overload    (declared in TDASimpleQueryBuilder)

Adds a new item for each AJoinToFieldNames to MainTable.JoinSourceTables (see TDAJoinDataTable).

procedure AddJoin(AJoinTable: string; AJoinFieldNames: array of string; AJoinToTableName: string; AJoinToFieldNames: array of string; AJoinType: TDAJoinType)

Parameters:

  • AJoinTable: join table
  • AJoinFieldNames: join field name
  • AJoinToTableName: join to table name
  • AJoinToFieldNames: join to field name
  • AJoinType: type

AddOrderBy

Adds a new OrderBy clause with a specified table name and field name to the OrderBy collection.

procedure AddOrderBy(ATable: string; AField: string; AAscMode: Boolean)

Parameters:

  • ATable: Specified table
  • AField: Specified field in the table
  • AAscMode: mode

AddSelect    (declared in TDASimpleQueryBuilder)

Adds an ATable/AField pair as TDATableFieldCollectionItem to the Select collection.

procedure AddSelect(ATable: string; AField: string)

Parameters:

  • ATable: table name
  • AField: field name

AddUnion

Adds a new union to the Union collection and returns a new TDAQueryBuilder object.

function AddUnion: TDAQueryBuilder

Assign  override

Copies data from a given source.

procedure Assign(Source: TPersistent)

Parameters:

  • Source: Instance whose properties will be copied

Clear  override

Clears all properties.

procedure Clear

CreateFieldGenerator  protected virtual abstract    (declared in TDASimpleQueryBuilder)

Creates TDABaseFieldGenerator.

function CreateFieldGenerator: TDABaseFieldGenerator

CreateGroupByClause  protected virtual    (declared in TDASimpleQueryBuilder)

Generates the corresponding part of the SQL clause based on the content of GroupBy.

function CreateGroupByClause: string

CreateOrderByClause  protected virtual

Converts the OrderBy collection to SQL format and returns the SQL string in the format
Table1.Field1,...,TableN.FieldN, where the Table and the Field are properties of the TDAOrderByItem (the item of the OrderBy collection).

function CreateOrderByClause: string

CreateOrderByCollection  protected virtual abstract

Creates TDAOrderByCollection

function CreateOrderByCollection: TDAOrderByCollection

CreateSelectClause  protected virtual    (declared in TDASimpleQueryBuilder)

Generates the corresponding part of the SQL clause based on the content of Select and Options.

function CreateSelectClause: string

CreateTableClause  protected virtual abstract    (declared in TDASimpleQueryBuilder)

Builds an SQL statement based on a specified table clause and returns it.

function CreateTableClause: string

CreateUnionClause  protected virtual

Creates a new union clause

function CreateUnionClause(const AUnion: TDAQueryBuilder): string

Parameters:

  • AUnion: union

CreateWhereBuilder  protected virtual abstract    (declared in TDASimpleQueryBuilder)

Returns a new WHERE Builder object that will contain a WHERE statement of one of the following types: TDABinaryExpression, TDAUnaryExpression, TDAConstantExpression, TDAListExpression, TDAParameterExpression, TDAFieldExpression, TDANullExpression, TDAMacroExpression or TDABetweenExpression.

function CreateWhereBuilder: TDABaseSQLWhereBuilder

CreateWhereClause  virtual    (declared in TDASimpleQueryBuilder)

Generates the corresponding part of the SQL clause.

function CreateWhereClause: string

GenerateFieldName  protected    (declared in TDASimpleQueryBuilder)

Generates field name according to column mapping and quotation.

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

Parameters:

  • aTablename: table name
  • aFieldName: field name
  • aProcessMapping: Controls if GetMappingTableField is applied to aFieldName. true by default.

GenerateSelectClauseWithFieldNames  protected override

Generates Select part of SQL as a comma-separated list of required fields.

function GenerateSelectClauseWithFieldNames: string

GenerateSelectSQL  override

Generates an SQL SELECT statement based on the Unions and the OrderBy collection and returns an SQL string that represents the complete SELECT statement. The result string is different for different kinds of connections, meaning that this function is based on the specified connection (Connection property of the TDASimpleQueryBuilder ancestor class).

function GenerateSelectSQL: string

intCreate  protected override

Performs initial initialization

procedure intCreate

QuoteFieldNameIfNeeded  protected    (declared in TDASimpleQueryBuilder)

Quotes field name if is required.

function QuoteFieldNameIfNeeded(const aTableName: string; const aFieldName: string): string

Parameters:

  • aTableName: table name.
  • aFieldName: field name.

QuoteIdentifierIfNeeded  protected    (declared in TDASimpleQueryBuilder)

If Connection is assigned, the method delegates the call to it. If IdentifierNeedsQuoting returns true, the call is delegated to QuoteIdentifier, otherwise the unchanged iIdentifier is returned.

function QuoteIdentifierIfNeeded(const iIdentifier: string): string

Parameters:

  • iIdentifier: identifier.

Validate  protected virtual    (declared in TDASimpleQueryBuilder)

Raises an exception if MainTable.MasterTable is empty.

procedure Validate