Monday, March 2, 2015

Using AppleScript with PDI SuperScript

In a previous blog post, I announced my SuperScript step for PDI, which adds and enhances some capabilities of the built-in Script step.  One notable addition is the ability to use AppleScript on a Mac, as the AppleScript script engine comes with the Mac JDK.

However the implementation of the script engine is a bit different than most other script engines, especially in terms of getting data into the script as variables, arguments, etc. If you just want to call a script for every row (without using incoming fields), you can just write straight AppleScript. However if you want to use incoming field(s), you have to do a little magic to get it all working.

First, the AppleScript script engine in Java will not pass bindings to the script as variables. Instead they use a combination of bindings to achieve this:

javax_script_function: This variable is set to the name of a function to be invoked in AppleScript
javax.script.argv: This variable is set to the value to be passed to the function. Since PDI doesn't have a List type, you can only pass one argument into your function in SuperScript. If you need multiple values, you'll have to concatenate them in PDI and pass them in as a single field value.

To make matters worse, SuperScript only passes in "used" fields to the script. To determine used fields, it (like the Script step) simply looks for the field name in the script. In this case, the actual field name used in the function invocation is likely neither the above properties. To get around this, simply put both of the above variable names in a comment:

(* uses javax_script_function and javax.script.argv *)

Then wrap your logic inside the function call:

on testFunc(testVal)
  return testVal & " World!"
end testFunc

In this example I used a Generate Rows step to set javax_script_function to "testFunc" and javax.script.argv to "Hello".  Then I ran the following sample transformation:

The final script for this example looks like:

The above picture doesn't show it but for the "result" output field I set "Script result?" to "Y". This way I can use "result" with "Hello World!" later on in the transformation.

Running the trans gives:

Hopefully this helps demystify the use of AppleScript with SuperScript in PDI, and allows you to leverage powerful Apple capabilities alongside the powerful capabilities of PDI.  Cheers!