How do I do simple transformations in Scriptella

[Dan Galewsky]

I am spending a couple of days evaluating Scriptella - to see if it would work better than just writing a Java (or Python - or Groovy) app.

I have been successful with Scriptella scripts to read from one db and just write to another. 

What I have not had any luck doing is transforming values from the input query - and then putting them into the output query.

For example here - I really want to see if a value on input is a negative one (-1) and if so set it's value to null ( or maybe the empty string).

I don't know where to put the Jexl expression to do this transformation. I may have a bunch of transformations I want to do on the input values before writing them to the output table. 

I don't have much more time to evaluate Scriptella so any help would be appreciated.

-------------------------

For example -- I need to see if 'assigned_to' is -1 if so - set it to null.

<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">

<etl>

    <description>Scriptella ETL File Template.</description>

    <properties>

        <include href="etl.properties"/> 

    </properties>

    <!-- Connection declarations -->

    <connection id="con1" driver="$driver" url="$url" user="$user" password="$password" classpath="$classpath"/>

    <connection id="new" driver="$driver" url="$new_url" user="$new_user" password="$new_password" classpath="$classpath"/>

    <connection id="out" driver="csv" url="report.csv" />

    <connection id="log" driver="script" url="log.txt"/>

    <connection id="jexl" driver="jexl"/>

    <!--ETL Queries and Scripts -->

    <query connection-id="con1">

        select submission_id, applicant_id, assigned_to, umi, approval_date, college, committee_email_address, email_hash,  degree_type from vireosubmission

        <script connection-id="new">

        insert into submission (id, submitter_id, assignee_id, umirelease, committeeapprovaldate, college, committeecontactemail, committeeemailhash, degree) values

        (?submission_id, ?applicant_id, ?assigned_to, ?umi, ?approval_date, ?college,  ?committee_email_address, ?email_hash, ?degree_type)

        </script>

    </query>

</etl>

[Ivan Vasiliev]

You can use nested scripts:

<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">

<etl>

    <description>Scriptella ETL File Template.</description>

    <properties>

        <include href="etl.properties"/> 

    </properties>

    <!-- Connection declarations -->

    <connection id="con1" driver="$driver" url="$url" user="$user" password="$password" classpath="$classpath"/>

    <connection id="new" driver="$driver" url="$new_url" user="$new_user" password="$new_password" classpath="$classpath"/>

    <connection id="out" driver="csv" url="report.csv" />

    <connection id="log" driver="script" url="log.txt"/>

    <connection id="jexl" driver="jexl"/>

    <!--ETL Queries and Scripts -->

    <query connection-id="con1">

        select submission_id, applicant_id, assigned_to, umi, approval_date, college, committee_email_address, email_hash,  degree_type from vireosubmission

        <script connection-id="grrovy">

             ----check and set new values here   ----

            <script connection-id="new">

               insert into submission (id, submitter_id, assignee_id, umirelease, committeeapprovaldate, college, committeecontactemail, committeeemailhash, degree) values

              (?submission_id, ?applicant_id, ?assigned_to, ?umi, ?approval_date, ?college,  ?committee_email_address, ?email_hash, ?degree_type)

            </script>

       </script>

    </query>

С уважением,
Васильев Иван

2012/8/9 Dan Galewsky <dgal...@gmail.com>

[Dan Galewsky]

Ivan -- 
 
Thanks for your reply. 
 
I have now gotten that far. 
 
Another problem I have run across is that it seems like I can't set one of the input variables to a new value. 
 
Is this possible? 
 
I used jexl as the scripting language. If I said: 
 
if (assigned_to == -1) assigned_to = null; 
            query.next() ; 
 
then referenced assigned_to in the insert statement - it still had it's old value. 
 
If I assigned to a new variable and referenced the new variable then I could see the new value. 
 
Is this possible? Or am I just not understanding something. 
 
Thanks for your help 
 
--Dan 

[Ivan Vasiliev]

> query.next() ; 

This is not required if you use <script>

>If I assigned to a new variable and referenced the new variable then I could see the new value.

Use new var or try to add  put(String,Object)

С уважением,
Васильев Иван

[Fyodor Kupolov]

Hi guys,

Dan, what version of Scriptella are you using. In 1.1 ternary expressions are available, so you can avoid an extra query block by using a jexl expression in the output script:

    <query connection-id="con1">

        select * from .....

        <script connection-id="new">

            INSERT .............. ?{assigned_to == -1 ? null : assigned_to}

        </script>

    </query>

Unfortunately Scriptella parameters model does not distinguish between nulls  and undefined values. As a result, after null is assigned to the variable, the next time it is read, the original value is returned from the parent context. I'll try to fix this issue in ParametersCallbackMap, but this change breaks backwards compatibility, so I have to evaluate the impact as well. As pointed out by Ivan, for now I would suggest to use a separate variable to avoid this limitation:

       <query connection-id="jexl">

            _assigned_to = assigned_to;

            if (assigned_to == -1) {

                _assigned_to = null;

            }

            query.next();

            <script connection-id="new">

                INSERT .............. ?_assigned_to

            </script>

        </query>

Regards,

Fyodor

[Fyodor Kupolov]

Two tickets were created:

• ParametersCallbackMap ignores a null value if a variable was already defined
  
• Its not clear from documentation what is available for jexl driver
  

Thanks,

Fyodor