SQL Scripting

Certain configurations allow direct SQL statements to be entered, most commonly within:

• SQL Lists
• SQL Sheets
• Job steps

Currently only Job step SQL statements allow scripting, but this may be extended to other SQL statements in the future.

Job SQL statement scripting

Each Jobs step that contains SQL supports scripting. This is using the same Freemarker template scripting language as for Email bodies and CMS bodies (see above). This allows the SQL statements to be built up based on if-statements, loops etc, and it gives the SQL statements access to the NEON data model such as Property Sheets.

There is one major difference between SQL Scripts and the other forms of scripting and expressions mentioned above. SQL Scripts don't run within a context and not for a specific Member. This means that the Script Model is reduced and don't give access to a current Member, current User, Dimensions, Lists or Member Sheets.

Job Step SQL statements are parsed each time the Job step is executed.

Here is an example of an SQL Job Step which makes use of scripting to resolve the underlying Entity Id and table name:

sq

INSERT INTO ${_ent.MyDataListName.table} (member_id, some_column)
SELECT member_id, '${MyPropertySheetName.someProperty}'
FROM ng_tag_list_tb WHERE list_id = '${_ent.MyTagListName.id}'

❗️

Use SQL Scripting with caution

As the example above illustrates, the SQL scripting simply generates a text SQL statement. Relying on property values and not enclosing in quotes etc can easily break the SQL statement. It also opens up to the risk of SQL injection.

If a job script references an Entity's cms content (as in _cms. or _ent.cms.) the content value will be parsed before processed, but this parsing will not have access to dimensions or Members, and will therefore not take any localisations into consideration.

Job Parametes

Any defined Job parameters are available to the SQL script body using the _in model map. This is very helpful for incremental processing. Let's say that a Job step processes any new entries to the Activity table. We first define a Job parameter called last_processed_attribute_id. We can then pass it in to the statement before it's executed.

SQL

INSERT INTO some_table (activity_id, member_id, activity)
SELECT @last_processed_attribute_id = activity_id, member_id, activity
FROM ng_activity_tb
WHERE activity_id > IFNULL(${_in.last_processed_attribute_id}, 0)
ORDER BY activity_id

Note that the _out Script Model attribute is not directly supported, but this can be solved using SQL variables, as per the example above. See Jobs for a more detailed explanation.