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


Where - 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()}
        


        • Time - Returns the current time from the server.

          • Syntax:

            {Time()}
            

          • Example:

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


            • DateTime - Returns the current datetime from the server.

              • Syntax:

                {DateTime()}
                

              • Example:

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


                • 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


                    • 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


                        • 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


                                        • 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


                                            • Trim - Removes both leading and trailing spaces from a string

                                              • Syntax:

                                                {Trim(value)}
                                                

                                              • Example:

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

                                              • Parameters:

                                                • value - string value or field name


                                                • TrimLeft - Removes the leading spaces from a string

                                                  • Syntax:

                                                    {TrimLeft(value)}
                                                    

                                                  • Example:

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

                                                  • Parameters:

                                                    • value - string value or field name


                                                    • TrimRight - Removes the trailing spaces from a string.

                                                      • Syntax:

                                                        {TrimRight(value)}
                                                        

                                                      • Example:

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

                                                      • Parameters:

                                                        • value - string value or field name


                                                        • 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


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

                                                              • Syntax:

                                                                {NoLockHint}
                                                                

                                                              • Example:

                                                                SELECT * FROM Table {NoLockHint}