Using Velocity

What and why

Velocity is a Template engine internally used by DATPROF Runtime to parse code.

Velocity is an apache project: http://velocity.apache.org/engine/1.7/

Runtime Projects generated by DATPROF Privacy and DATPROF Subset consist of Velocity templates using the Velocity syntax. 

They are parsed using Velocity and the plain SQL output is executed immediately.

Not only the internal templates are parsed, also all SQL/OS scripts are parsed using Velocity prior to execution.

Scripts without any Velocity elements remain unchanged.

You can refer to several properties by using this syntax: ${identifier.property} 

For DATPROF Runtime all DATPROF Runtime Parameters are available using the the dpf.parameters identifier.

Example:  ${dpf.parameters.DPF_RUN_DATE}

Any SQL script or OS script is parsed using this Engine.^* 

In DATPROF Subset  this means the parsing of the additional OS scripts and SQL scripts but also the startfilter and additional filters.

In DATPROF Privacy  this means the parsing of the additional OS scripts and SQL scripts but also the expressions  and conditions.

In DATPROF Integrate this means the parsing of the Os Calls and Sql actions, but also the coding in any other action.

To avoid parts of your code from Velocity parsing check the Velocity Comments chapter below about Unparsed Content.

Using Velocity properties can be very usefull in your reports.

For example to write to a logfile to show up as downloadable artifact in your Run History: 

echo Run started on ${dpf.parameters.DPF_RUN_DATE} > ${dpf.parameters.DPF_ARTIFACTS_DIR}\MyLogfile-${dpf.parameters.DPF_RUN_ID}.log

You can acces all runtime parameters , visible and set in the parameters section of your environment , via this method. 

There are also some general Parameters available as Velocity properties which are not visible in the parameter section of your environment.

* As of Privacy 3.9.0 and Subset 3.4 you can select whether a script is parsed using Velocity or not.

DATPROF Parameters

In your scripts you can refer to the internally used DATPROF parameters by using this Velocity property syntax. They are all strings.

General available properties

In DATPROF Integrate defined parameters become Velocity properties

You can refer to your own defined DATPROF Integrate parameters by using the dpf.parameters identifier.

Example: ${dpf.parameters.DATA_INPUT_DIR}

DATPROF Runtime Velocity methods

There are 4 Velocity methods available you can use in your scripts/code. They enable you to run OS specific code in your application,

Velocity comments

Velocity uses ## to identify Single line comments.

This might interfere with the naming of Microsoft Sql Server Global Temporary Tables . 

They are referenced by ## . For example: create table ##test (X int, Y int) ;

If you specify this in your code and use DATPROF Runtime this will fail.

Also using ## in comparissons may fail, 

To enable the use of ## in your code without parsing it as Velocity comments you should tell Velocity to NOT parse this content.

Velocity allows for specifying a block code as being Unparsed Content.

For this enclose code with #[[ and ]]# .

Example to create test table

Original:
Create table ##test (X int, Y int) ;

Use Unparsed content:
#[[
   create table ##test (X int, Y int) ;
]]#

Example to check on three empty fields.

Original:
select a.*
from customers a
where concat( a.x , '#'
, a.y , '#'
, a.z , '#'
) <> '###'

Use Unparsed content:
#[[
select a.*
from customers a
where concat( a.x , '#'
, a.y , '#'
, a.z , '#'
) <> '###'
]]#


Or avoid using them:
select a.*
from customers a
where concat( a.x , '~'
, a.y , '~'
, a.z , '~'
) <> '~~~'