Escaping SQL in Jitterbit App Builder

mvSQL allows for the escaping of statements that can be passed directly to the database. This allows you to call functions supported by the database that are not supported by mvSQL in general, including calling database user defined functions (Note that App Builder will not query or manage these user defined functions). SQL is escaped by enclosing it in ${ sqlHere }

Note

If you need to refer to a column in your raw database expression, it is recommended that you use mvSQL column substitution so that changes to how mvSQL writes statements internally do no affect your rule.

Example 1

select
    ${ HASHBYTES('SHA1', 'Hello world') } as hashout
from Customers

Example 2 with Column Substitution

select
    Name as Name,
    ${ HASHBYTES('SHA1', {{ Name }} ) } as hashout
from Customers

Resources

See this Microsoft article for a list of Configuration Functions (Transact-SQL): https://docs.microsoft.com/en-us/sql/t-sql/functions/configuration-functions-transact-sql?view=sql-server-2017

Note

Not all of the functions listed in the Microsoft article are supported by App Builder.