TDASQLMacroProcessor

Overview

The TDASQLMacroProcessor provides the base for classes that can process macros in SQL queries for different databases. It is part of the Macro Processor conception.

This class allows to use the following macros in SQL queries:

  • Date
  • DateTime
  • AddTime
  • FormatDateTime
  • FormatDate
  • Length
  • LowerCase
  • UpperCase
  • TrimLeft
  • TrimRight
  • Copy
  • NoLock

If you want to process an unknown macro, use the OnUnknownMacroVariable event of the TDAConnectionManager class.

procedure TServerDataModule.ConnectionManagerUnknownMacroVariable(
  Sender: TObject; const Name: string; var Value: string);
var ConnectionType:String;
begin
  ConnectionType := ConnectionManager.Connections.ConnectionByName(ConnectionManager.GetDefaultConnectionName).ConnectionType;
  if (ConnectionType = 'Interbase') then
  begin
    if (Name = 'MyUpperCase') then Value := 'Upper';
  end;
end;

Location


 

constructor Create  overload

Creates a new instance.

constructor Create

constructor Create (string, string, Boolean, string, string)  overload

Creates a new instance with given parameters

constructor Create(const aDateFormat: string; const aDateTimeFormat: string; aDoubleQuoteStrings: Boolean; const aStoredProcParamsPrefix: string; const aModFormat: string)

Parameters:

  • aDateFormat: Date format
  • aDateTimeFormat: Datetime format
  • aDoubleQuoteStrings: Quote string for the date and datetime formats
  • aStoredProcParamsPrefix: Spored procedure param prefix
  • aModFormat: Mod format

AddTime  protected virtual abstract

Returns a new datetime value based on adding an interval to the specified date.

function AddTime(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (date) - Specified input valid datetime value
    [1] (interval) - number of years, weeks, days, hours, minutes, seconds
    [2] (interval_type) - The part of date or time that has to be added: "year", "week", "day", "hour", "min", "sec"

AddVariable    (declared in TROMacroParser)

Adds a variable.

function AddVariable(const Name: string): TROMacroVar

Parameters:

  • Name: variable name.

AllowComplexIdent  protected    (declared in TROMacroParser)

Allows complex identificators, like "table"."name"

property AllowComplexIdent: Boolean read write

AllowDoubleQuotedIdent  protected    (declared in TROMacroParser)

Allows double quoted identificators, like "field"

property AllowDoubleQuotedIdent: Boolean read write

ClearProcs    (declared in TROMacroParser)

Clear all procedures.

procedure ClearProcs

ClearVariables    (declared in TROMacroParser)

Clear all variables.

procedure ClearVariables

ComposeMacroCall

Allows to execute known macro method

function ComposeMacroCall(aMacroKind: MacroKind; Parameters: array of string): string

Parameters:

  • aMacroKind: Idenfificator of macro method
  • Parameters: Parameters

Copy  protected virtual abstract

Returns <count> characters starting from <index> from <value>

function Copy(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (value) - string value or field name
    [1] (index) - position from where to start copying
    [2] (count) - number of characters to copy

Date  protected virtual

Returns the current date from the server.

function Date(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters: none

DateFormat

Gets the date format for the current database.

property DateFormat: string read

DatePart  protected virtual abstract

Returns an integer representing the specified datepart of the provided date.

function DatePart(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (date) - a valid date
    [1] (interval_type) - sec, min, hour, day, week, month, year

DateTime  protected virtual abstract

Returns the current datetime from the server.

function DateTime(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters: none

DateTimeFormat

Gets the datetime format for the current database.

property DateTimeFormat: string read

DeleteVariable (TROMacroVar)  overload    (declared in TROMacroParser)

Deletes variable

procedure DeleteVariable(aVariable: TROMacroVar)

Parameters:

  • aVariable: variable

DeleteVariable (Integer)  overload    (declared in TROMacroParser)

Deletes variable by index

procedure DeleteVariable(I: Integer)

Parameters:

  • I: index

DoubleQuoteStrings

Gets the quote string for the date and datetime formats for the current database. If this property is true, the datetime format is represented as "datetime", otherwise as 'datetime'.

property DoubleQuoteStrings: Boolean read

DoUnknownIdentifier  protected virtual

Fires OnUnknownIdentifier and OnDAServiceUnknownIdentifier events.

function DoUnknownIdentifier(Sender: TObject; const Name: string; const OrgName: string; var Value: string): Boolean

Parameters:

  • Sender: Sender
  • Name: name of variable
  • OrgName: original name of variable
  • Value: value

Eval    (declared in TROMacroParser)

Evaluates the given text.

function Eval(const Text: string; TextDelimiter: Char = ''''): string

Parameters:

  • Text: text
  • TextDelimiter: delimiter

EvalToken    (declared in TROMacroParser)

Evaluates a token.

function EvalToken(const Text: string): string

Parameters:

  • Text: token

FormatDate  protected virtual

Formats the specified date to the date format used by the database.

function FormatDate(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (value) - string representation of a date

FormatDateTime  protected virtual

Formats the specified datetime to the datetime format used by the database.

function FormatDateTime(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (value) - string representation of a date

IndexOfName    (declared in TROMacroParser)

Returns the position of the first name-value pair with the specified name.

function IndexOfName(const aName: string): Integer

Parameters:

  • aName: specified name

Length  protected virtual abstract

Returns the length of the given string.

function Length(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (value) - string value or field name

LowerCase  protected virtual abstract

Converts the given string to lower case.

function LowerCase(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (value) - string value or field name

ModFormat

Format for Modulo statement

can be one from following statements (according to actual DB) - (dividend % divisor) - (dividend MOD divisor) - MOD(dividend, divisor)

property ModFormat: string read

Modulo  protected

Generates modulo statement.

function Modulo(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (dividend)
    [1] (divisor)

NoLock  protected virtual

Returns (NOLOCK) for Microsoft SQL Server and empty string for other databases.

function NoLock(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters: none

OnDAServiceUnknownIdentifier

This event is fired when an unknown variable is detected. TDataAbstractService sets this events.

property OnDAServiceUnknownIdentifier: TOnUnknownIdentifier read write
delegate:  function OnDAServiceUnknownIdentifier(Sender: TObject; const Name: string; const OrgName: string; var Value: string): Boolean

OnUnknownIdentifier

This event is fired when an unknown variable is detected.

property OnUnknownIdentifier: TOnUnknownIdentifier read write
delegate:  function OnUnknownIdentifier(Sender: TObject; const Name: string; const OrgName: string; var Value: string): Boolean

OpenBlockEscape  protected    (declared in TROMacroParser)

Allow to use square brackets for identificators, i.e. [something]

property OpenBlockEscape: Boolean read write

QueryInterface  protected stdcall    (declared in TDAInterfacedObject)

Implements the IInterfaceQueryInterface method.

function QueryInterface(const IID: TGUID; out Obj: ): HResult

Parameters:

  • IID: Identifier of the interface to return
  • Obj: Variable to store the returned interface reference to

RefCount  protected    (declared in TDAInterfacedObject)

Returns 0

property RefCount: Integer read

RegisterMacros  protected virtual

Registers the existing standard macros:

  • Date
  • DateTime
  • AddTime
  • FormatDateTime
  • FormatDate
  • Length
  • LowerCase
  • UpperCase
  • TrimLeft
  • TrimRight
  • Copy
  • NoLock
procedure RegisterMacros

RegisterProc    (declared in TROMacroParser)

Registers a procedure.

procedure RegisterProc(const Name: string; ExProc: TExternalProc; aParamCount: Integer)

Parameters:

  • Name: Name
  • ExProc: Method
  • aParamCount: Parameters count

Session  protected

Provides access to value of session variable

usage:

{SESSION(variablename)}

function Session(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (variable_name) - name of session's variable

StoredProcedurePrefix

Gets the prefix for stored procedures for the current database.

property StoredProcedurePrefix: string read

StoredProcParamPrefix

Gets the prefix for stored procedure params for the current database.

property StoredProcParamPrefix: string read

Time  protected virtual

Returns the current time from the server.

function Time(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters: none

Trim  protected virtual abstract

Removes both leading and trailing spaces from a string

function Trim(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (value) - string value or field name
    [1] (char) - (optional) character to trim. The default value is space character (char(32))

TrimLeft  protected virtual abstract

Removes the leading spaces from a string

function TrimLeft(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (value) - string value or field name
    [1] (char) - (optional) character to trim. The default value is space character (char(32))

TrimRight  protected virtual abstract

Removes the trailing spaces from a string.

function TrimRight(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (value) - string value or field name
    [1] (char) - (optional) character to trim. The default value is space character (char(32))

UpperCase  protected virtual abstract

Converts the given string to upper case.

function UpperCase(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (value) - string value or field name

Variable    (declared in TROMacroParser)

Returns the registered variable.

property Variable[I: Integer]: TROMacroVar read

VariableCount    (declared in TROMacroParser)

Returns the count of variables.

property VariableCount: Integer read

 

AllowComplexIdent  protected    (declared in TROMacroParser)

Allows complex identificators, like "table"."name"

property AllowComplexIdent: Boolean read write

AllowDoubleQuotedIdent  protected    (declared in TROMacroParser)

Allows double quoted identificators, like "field"

property AllowDoubleQuotedIdent: Boolean read write

DateFormat

Gets the date format for the current database.

property DateFormat: string read

DateTimeFormat

Gets the datetime format for the current database.

property DateTimeFormat: string read

DoubleQuoteStrings

Gets the quote string for the date and datetime formats for the current database. If this property is true, the datetime format is represented as "datetime", otherwise as 'datetime'.

property DoubleQuoteStrings: Boolean read

ModFormat

Format for Modulo statement

can be one from following statements (according to actual DB) - (dividend % divisor) - (dividend MOD divisor) - MOD(dividend, divisor)

property ModFormat: string read

OpenBlockEscape  protected    (declared in TROMacroParser)

Allow to use square brackets for identificators, i.e. [something]

property OpenBlockEscape: Boolean read write

RefCount  protected    (declared in TDAInterfacedObject)

Returns 0

property RefCount: Integer read

StoredProcedurePrefix

Gets the prefix for stored procedures for the current database.

property StoredProcedurePrefix: string read

StoredProcParamPrefix

Gets the prefix for stored procedure params for the current database.

property StoredProcParamPrefix: string read

Variable    (declared in TROMacroParser)

Returns the registered variable.

property Variable[I: Integer]: TROMacroVar read

VariableCount    (declared in TROMacroParser)

Returns the count of variables.

property VariableCount: Integer read

 

constructor Create  overload

Creates a new instance.

constructor Create

constructor Create (string, string, Boolean, string, string)  overload

Creates a new instance with given parameters

constructor Create(const aDateFormat: string; const aDateTimeFormat: string; aDoubleQuoteStrings: Boolean; const aStoredProcParamsPrefix: string; const aModFormat: string)

Parameters:

  • aDateFormat: Date format
  • aDateTimeFormat: Datetime format
  • aDoubleQuoteStrings: Quote string for the date and datetime formats
  • aStoredProcParamsPrefix: Spored procedure param prefix
  • aModFormat: Mod format

AddTime  protected virtual abstract

Returns a new datetime value based on adding an interval to the specified date.

function AddTime(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (date) - Specified input valid datetime value
    [1] (interval) - number of years, weeks, days, hours, minutes, seconds
    [2] (interval_type) - The part of date or time that has to be added: "year", "week", "day", "hour", "min", "sec"

AddVariable    (declared in TROMacroParser)

Adds a variable.

function AddVariable(const Name: string): TROMacroVar

Parameters:

  • Name: variable name.

ClearProcs    (declared in TROMacroParser)

Clear all procedures.

procedure ClearProcs

ClearVariables    (declared in TROMacroParser)

Clear all variables.

procedure ClearVariables

ComposeMacroCall

Allows to execute known macro method

function ComposeMacroCall(aMacroKind: MacroKind; Parameters: array of string): string

Parameters:

  • aMacroKind: Idenfificator of macro method
  • Parameters: Parameters

Copy  protected virtual abstract

Returns <count> characters starting from <index> from <value>

function Copy(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (value) - string value or field name
    [1] (index) - position from where to start copying
    [2] (count) - number of characters to copy

Date  protected virtual

Returns the current date from the server.

function Date(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters: none

DatePart  protected virtual abstract

Returns an integer representing the specified datepart of the provided date.

function DatePart(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (date) - a valid date
    [1] (interval_type) - sec, min, hour, day, week, month, year

DateTime  protected virtual abstract

Returns the current datetime from the server.

function DateTime(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters: none

DeleteVariable (TROMacroVar)  overload    (declared in TROMacroParser)

Deletes variable

procedure DeleteVariable(aVariable: TROMacroVar)

Parameters:

  • aVariable: variable

DeleteVariable (Integer)  overload    (declared in TROMacroParser)

Deletes variable by index

procedure DeleteVariable(I: Integer)

Parameters:

  • I: index

DoUnknownIdentifier  protected virtual

Fires OnUnknownIdentifier and OnDAServiceUnknownIdentifier events.

function DoUnknownIdentifier(Sender: TObject; const Name: string; const OrgName: string; var Value: string): Boolean

Parameters:

  • Sender: Sender
  • Name: name of variable
  • OrgName: original name of variable
  • Value: value

Eval    (declared in TROMacroParser)

Evaluates the given text.

function Eval(const Text: string; TextDelimiter: Char = ''''): string

Parameters:

  • Text: text
  • TextDelimiter: delimiter

EvalToken    (declared in TROMacroParser)

Evaluates a token.

function EvalToken(const Text: string): string

Parameters:

  • Text: token

FormatDate  protected virtual

Formats the specified date to the date format used by the database.

function FormatDate(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (value) - string representation of a date

FormatDateTime  protected virtual

Formats the specified datetime to the datetime format used by the database.

function FormatDateTime(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (value) - string representation of a date

IndexOfName    (declared in TROMacroParser)

Returns the position of the first name-value pair with the specified name.

function IndexOfName(const aName: string): Integer

Parameters:

  • aName: specified name

Length  protected virtual abstract

Returns the length of the given string.

function Length(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (value) - string value or field name

LowerCase  protected virtual abstract

Converts the given string to lower case.

function LowerCase(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (value) - string value or field name

Modulo  protected

Generates modulo statement.

function Modulo(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (dividend)
    [1] (divisor)

NoLock  protected virtual

Returns (NOLOCK) for Microsoft SQL Server and empty string for other databases.

function NoLock(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters: none

QueryInterface  protected stdcall    (declared in TDAInterfacedObject)

Implements the IInterfaceQueryInterface method.

function QueryInterface(const IID: TGUID; out Obj: ): HResult

Parameters:

  • IID: Identifier of the interface to return
  • Obj: Variable to store the returned interface reference to

RegisterMacros  protected virtual

Registers the existing standard macros:

  • Date
  • DateTime
  • AddTime
  • FormatDateTime
  • FormatDate
  • Length
  • LowerCase
  • UpperCase
  • TrimLeft
  • TrimRight
  • Copy
  • NoLock
procedure RegisterMacros

RegisterProc    (declared in TROMacroParser)

Registers a procedure.

procedure RegisterProc(const Name: string; ExProc: TExternalProc; aParamCount: Integer)

Parameters:

  • Name: Name
  • ExProc: Method
  • aParamCount: Parameters count

Session  protected

Provides access to value of session variable

usage:

{SESSION(variablename)}

function Session(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (variable_name) - name of session's variable

Time  protected virtual

Returns the current time from the server.

function Time(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters: none

Trim  protected virtual abstract

Removes both leading and trailing spaces from a string

function Trim(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (value) - string value or field name
    [1] (char) - (optional) character to trim. The default value is space character (char(32))

TrimLeft  protected virtual abstract

Removes the leading spaces from a string

function TrimLeft(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (value) - string value or field name
    [1] (char) - (optional) character to trim. The default value is space character (char(32))

TrimRight  protected virtual abstract

Removes the trailing spaces from a string.

function TrimRight(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (value) - string value or field name
    [1] (char) - (optional) character to trim. The default value is space character (char(32))

UpperCase  protected virtual abstract

Converts the given string to upper case.

function UpperCase(Sender: TObject; const Parameters: array of string): string

Parameters:

  • Sender: Sender
  • Parameters:
    [0] (value) - string value or field name

 

OnDAServiceUnknownIdentifier

This event is fired when an unknown variable is detected. TDataAbstractService sets this events.

property OnDAServiceUnknownIdentifier: TOnUnknownIdentifier read write
delegate:  function OnDAServiceUnknownIdentifier(Sender: TObject; const Name: string; const OrgName: string; var Value: string): Boolean

OnUnknownIdentifier

This event is fired when an unknown variable is detected.

property OnUnknownIdentifier: TOnUnknownIdentifier read write
delegate:  function OnUnknownIdentifier(Sender: TObject; const Name: string; const OrgName: string; var Value: string): Boolean