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.

For Microsoft SQL Server connections:

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

For Oracle connections:

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

For Firebird connections:

SELECT * FROM Orders WHERE OrderDate > (select current_timestamp from rdb$database)
SELECT * FROM Orders WHERE OrderDate > dateadd(day, 2, (select current_timestamp from rdb$database))

For DBISAM Driver

SELECT * FROM Orders WHERE OrderDate > CURRENT_TIMESTAMP
SELECT * FROM Orders WHERE OrderDate > CURRENT_TIMESTAMP + 172800000

For SQLite Driver

SELECT * FROM Orders WHERE OrderDate > date('now')
SELECT * FROM Orders WHERE OrderDate > datetime(date('now'),'+2 days')

All Macros should be surrounded with {}

Macros Available

AddTime	Copy	Date	DatePart
DateTime	FormatDateTime	FormatDate	Length
LowerCase	NoLockHint	Time	Trim
TrimLeft	TrimRight	UpperCase	Where

---

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 - sec, min, hour, day, week, month, year

---

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

• Syntax:

  {Copy(value, index, count)}
  

• Example:

  SELECT * FROM Table WHERE {Copy(CustomerID, 1, 10)} = 'ALFKI'
  

• Parameters:

  • value - string value or field name
  • index - position from where to start copying
  • count - number of characters to copy

---

Date - Returns the current date from the server.

• Syntax:

  {Date()}
  

• Example:

  SELECT * FROM Table WHERE OrderDate > {Date()}
  

---

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

• Syntax:

  {DatePart(date, interval_type)}
  

• Example:

  SELECT * FROM Orders o WHERE DatePart(o.OrderDate, year) >= 2018
  

• Parameters:

  • date - a valid date
  • interval_type - sec, min, hour, day, week, month, year

---

DateTime - Returns the current datetime from the server.

• Syntax:

  {DateTime()}
  

• Example:

  SELECT * FROM Table WHERE OrderDate > {DateTime()}
  

---

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

• Syntax:

  {FormatDateTime(value)}
  

• Example:

  SELECT * FROM Table WHERE OrderDate > {FormatDateTime('12/22/2003 15:22:34.123')}
  

• Parameters:

  • value - string representation of a date

---

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

• Syntax:

  {FormatDate(value)}
  

• Example:

  SELECT * FROM Table WHERE OrderDate > {FormatDate('12/22/2003')}
  

• Parameters:

  • value - string representation of a date

---

Length - Returns the length of the given string.

• Syntax:

  {Length(value)}
  

• Example:

  SELECT * FROM Table WHERE {Length(CustomerID)} = 3
  

• Parameters:

  • value - string value or field name

---

LowerCase - Converts the given string to lower case.

• Syntax:

  {LowerCase(value)}
  

• Example:

  SELECT * FROM Table WHERE {LowerCase(CustomerID)} = 'alfki'
  

• Parameters:

  • value - string value or field name

---

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

• Syntax:

  {NoLockHint}
  

• Example:

  SELECT * FROM Table {NoLockHint}
  

---

Time - Returns the current time from the server.

• Syntax:

  {Time()}
  

• Example:

  SELECT * FROM Table WHERE OrderDate > {Time()}
  

---

Trim - Removes both leading and trailing spaces from a string

• Syntax:

  {Trim(value)}
  {Trim(value, char)}
  

• SELECT * FROM Table WHERE {Trim(CustomerID)} = 'ALFKI'
  

  SELECT * FROM Table WHERE {Trim(CustomerID),'.'} = 'ALFKI'
  

• Parameters:

  • value - string value or field name
  • char - (optional) character to trim. The default value is space character (char(32))

---

TrimLeft - Removes the leading spaces from a string

• Syntax:

  {TrimLeft(value)}
  {TrimLeft(value, char)}
  

• Example:

  SELECT * FROM Table WHERE {TrimLeft(CustomerID)} = 'ALFKI'
  

  SELECT * FROM Table WHERE {TrimLeft(CustomerID,'.')} = 'ALFKI'
  

• Parameters:

  • value - string value or field name
  • char - (optional) character to trim. The default value is space character (char(32))

---

TrimRight - Removes the trailing spaces from a string.

• Syntax:

  {TrimRight(value)}
  {TrimRight(value,char)}
  

• Example:

  SELECT * FROM Table WHERE {TrimRight(CustomerID)} = 'ALFKI'
  

  SELECT * FROM Table WHERE {TrimRight(CustomerID,'.')} = 'ALFKI'
  

• Parameters:

  • value - string value or field name
  • char - (optional) character to trim. The default value is space character (char(32))

---

UpperCase - Converts the given string to upper case.

• Syntax:

  {UpperCase(value)}
  

• Example:

  SELECT * FROM Table WHERE {UpperCase(CustomerID)} = 'ALFKI'
  

• Parameters:

  • value - string value or field name

---

Where - Placeholder for the injection of the Dynamic Where statement.

• Syntax:

  {WHERE}
  

• Example:

  SELECT * FROM Table WHERE Status = 3 AND {WHERE}