TDASQL92QueryBuilder

Overview

The TDASQL92QueryBuilder class builds an SQL statement based on the information in the properties of the current class. Built SQL statements can contain a SELECT part and a WHERE part (with help of the TDASQLWhereBuilder class).

The TDASQL92QueryBuilder class and TDASQL92WhereBuilder class are used to build auto-SQL statements for all database driver types. You do not need to use them directly.

The TDASQL92QueryBuilder class builds SQL statements according to the SQL92 standard (SQL92 standard).

Location


 

constructor Create  override

Creates an new instance

constructor Create

constructor Create (TCollection)  override    (declared in TDASimpleQueryBuilder)

Creates a new instance.

constructor Create(Collection: TCollection)

Parameters:

  • Collection: owner

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

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

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

function AddUnion: TDAQueryBuilder

Assign  override    (declared in TDAQueryBuilder)

Copies data from a given source.

procedure Assign(Source: TPersistent)

Parameters:

  • Source: Instance whose properties will be copied

Clear  override    (declared in TDAQueryBuilder)

Clears all properties.

procedure Clear

ColumnMapping    (declared in TDASimpleQueryBuilder)

Note: TDASimpleQueryBuilder does not modify ColumnMapping.

property ColumnMapping: TDAColumnMappingCollection read write

Connection    (declared in TDAQueryBuilder)

The database connection.

property Connection: IDACustomConnection read write

CreateFieldGenerator  protected override

Creates TDASQL92FieldGenerator

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

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 override

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 override

Builds an SQL statement based on a specified table clause and returns it. The returned SQL string represents the SELECT statement based on the following properties of the TDASQL92QueryBuilder object: master table, join tables and join types.

For example, if the MainTable.MasterTable property of the current TDASQL92QueryBuilder object has the value MasterTable, and this master table has the joined table JoinedTable on the fields MasterField (master table) and JoinedField (joined table) and the JoinType property is jtLeftOuter, the result string will be:

MasterTable LEFT OUTER JOIN JoinedTable
ON MasterTable.MasterField = JoinedTable.JoinedField

function CreateTableClause: string

CreateUnionClause  protected virtual    (declared in TDAQueryBuilder)

Creates a new union clause

function CreateUnionClause(const AUnion: TDAQueryBuilder): string

Parameters:

  • AUnion: union

CreateWhereBuilder  protected override

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

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

function GenerateSelectClauseWithFieldNames: string

GenerateSelectSQL  override    (declared in TDAQueryBuilder)

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

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

ModFormat

property ModFormat: string read write

Options    (declared in TDASimpleQueryBuilder)

Holds the set of TDAQueryBuilderOption.

property Options: TDAQueryBuilderOptions read write

OrderBy    (declared in TDAQueryBuilder)

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.

QuoteStringId  protected

Returns the quoted version of the input value.

function QuoteStringId(aValue: string): string

Parameters:

  • aValue: Input value

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

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

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

ModFormat

property ModFormat: string read write

Options    (declared in TDASimpleQueryBuilder)

Holds the set of TDAQueryBuilderOption.

property Options: TDAQueryBuilderOptions read write

OrderBy    (declared in TDAQueryBuilder)

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

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  override

Creates an new instance

constructor Create

constructor Create (TCollection)  override    (declared in TDASimpleQueryBuilder)

Creates a new instance.

constructor Create(Collection: TCollection)

Parameters:

  • Collection: owner

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

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

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

function AddUnion: TDAQueryBuilder

Assign  override    (declared in TDAQueryBuilder)

Copies data from a given source.

procedure Assign(Source: TPersistent)

Parameters:

  • Source: Instance whose properties will be copied

Clear  override    (declared in TDAQueryBuilder)

Clears all properties.

procedure Clear

CreateFieldGenerator  protected override

Creates TDASQL92FieldGenerator

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

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 override

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 override

Builds an SQL statement based on a specified table clause and returns it. The returned SQL string represents the SELECT statement based on the following properties of the TDASQL92QueryBuilder object: master table, join tables and join types.

For example, if the MainTable.MasterTable property of the current TDASQL92QueryBuilder object has the value MasterTable, and this master table has the joined table JoinedTable on the fields MasterField (master table) and JoinedField (joined table) and the JoinType property is jtLeftOuter, the result string will be:

MasterTable LEFT OUTER JOIN JoinedTable
ON MasterTable.MasterField = JoinedTable.JoinedField

function CreateTableClause: string

CreateUnionClause  protected virtual    (declared in TDAQueryBuilder)

Creates a new union clause

function CreateUnionClause(const AUnion: TDAQueryBuilder): string

Parameters:

  • AUnion: union

CreateWhereBuilder  protected override

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

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

function GenerateSelectClauseWithFieldNames: string

GenerateSelectSQL  override    (declared in TDAQueryBuilder)

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

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.

QuoteStringId  protected

Returns the quoted version of the input value.

function QuoteStringId(aValue: string): string

Parameters:

  • aValue: Input value

Validate  protected virtual    (declared in TDASimpleQueryBuilder)

Raises an exception if MainTable.MasterTable is empty.

procedure Validate