Macro Processor

From RemObjects Software

Jump to: navigation, search

This is a Data Abstract Architecture entry
Feel free to add your notes to this topic below.



Macro Processor Overview

Data Abstract provides extensible macro support which allows you to create universal queries for different databases.

For example: 

SELECT * FROM Orders WHERE OrderDate > {Date()}
SELECT * FROM Orders WHERE OrderDate > {AddTime(Date(), 2, day)}

The two statements above will be converted to the right SQL dialect at runtime. 

SELECT * FROM Orders WHERE OrderDate > GetDate()
SELECT * FROM Orders WHERE OrderDate > dateadd(GetDate(), 2, day)


SELECT * FROM Orders WHERE OrderDate > SYSDATE
SELECT * FROM Orders WHERE OrderDate > SYSDATE + 2


SELECT * FROM Orders WHERE OrderDate > (select current_timestamp from rdb$database)


SELECT * FROM Orders WHERE OrderDate > CURRENT_TIMESTAMP
SELECT * FROM Orders WHERE OrderDate > TIMESTAMP + 2

All Macros should be surrounded with {}

Macros Available

Where - Serves as a placeholder for the injection of the Dynamic Where statement.

  • Syntax: {WHERE}
  • Example: SELECT * FROM Table WHERE Status = 3 AND {WHERE}


Date - Returns the current date from the server

  • Syntax: {Date()}
  • Example: SELECT * FROM Table WHERE OrderDate>{Date()}
  • Parameters: this macro has no parameters


Time - Returns the current time from the server

  • Syntax: {Time()}
  • Example: SELECT * FROM Table WHERE OrderDate>{Time()}
  • Parameters: this macro has no parameters


DateTime - Returns the current datetime from the server

  • Syntax: {DateTime()}
  • Example: SELECT * FROM Table WHERE OrderDate>{DateTime()}
  • Parameters: this macro has no parameters


AddTime - Returns a new datetime value based on adding an interval to the specified date. Notice that not all databases support this macro.

  • Syntax: {AddTime(<date>, <interval>, <interval_type>)}
  • Example: SELECT * FROM Table WHERE OrderDate>{AddTime(Date(), 2, day)}
  • Parameters:

<date>: a valid date <interval>: number of days, weeks, etc as per next parameter <interval_type>: day, week, year, hour, min, sec


FormatDateTime - Formats the specified datetime to the datetime format used by the database

  • Syntax: {FormatDateTime(<date>)}
  • Example: SELECT * FROM Table WHERE OrderDate>{FormatDateTime('12/22/2003 15:22:34.123')}
  • Parameters: <date>: a string expression representing a data in format MM/DD/YYYY HH24:MM:SS.MS


FormatDate - Formats the specified date to the date format used by the database

  • Syntax: {FormatDateTime(<date>)}
  • Example: SELECT * FROM Table WHERE OrderDate>{FormatDate('12/22/2003')}
  • Parameters: <date>: a string expression representing a data in format MM/DD/YYYY


Length - Returns the length of the given string

  • Syntax: {Length(<string>)}
  • Example: SELECT * FROM Table WHERE {Length(CustomerID)}>3
  • Parameters: <string>: a text string

LowerCase - Converts the given string to lower case

  • Syntax: {LowerCase(<string>)}
  • Example: SELECT * FROM Table WHERE {LowerCase(CustomerID)}='alfki'
  • Parameters: <string>: a text string


UpperCase - Converts the given string to upper case

  • Syntax: {UpperCase(<string>)}
  • Example: SELECT * FROM Table WHERE {UpperCase(CustomerID)}='ALFKI'
  • Parameters: <string>: a text string


TrimLeft - Removes the leading spaces from a string

  • Syntax: {TrimLeft(<string>)}
  • Example: SELECT * FROM Table WHERE {TrimLeft(CustomerID)}='ALFKI'
  • Parameters: <string>: a text string


TrimRight - Removes the trailing spaces from a string

  • Syntax: {TrimRight(<string>)}
  • Example: SELECT * FROM Table WHERE {TrimRight(CustomerID)}='ALFKI'
  • Parameters: <string>: a text string


Copy - Returns <count> characters starting from <index> from <string>

  • Syntax: {Copy(<string>, <index>, <count>)}
  • Example: SELECT * FROM Table WHERE OrderDate>{Date()}

Parameters: <string>: a field name or a string <index>: the position from where to start copying <count>: the number of characters to copy


NoLockHint - Returns (NOLOCK) for MSSQL server and empty string for other databases

  • Syntax: {NoLockHint}
  • Example: SELECT * FROM Table {NoLockHint}


Product: RemObjects Data Abstract
Current version: Data Abstract 'Vinci' (5.0)

Lists — Glossary — Features — How To — Drivers — Components — Tools — Samples — Articles — Architecture — Issues

Retrieved from "http://wiki.remobjects.com/wiki/Macro_Processor"
Personal tools