Scriptella ETL Frequently Asked Questions

An up to date drivers documentation is available in the Scriptella Javadoc. Reference Documentation also covers drivers usage and scripts syntax.

Microsoft Access and other databases with ODBC interface are supported via ODBC-JDBC bridge driver which comes with Sun's JRE. Download ODBC example for more details. Connection declaration example:

                <connection url="jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=Northwind.mdb"/>
                

Binding variables syntax varies between drivers. JDBC drivers use the following rules for properties substitution:

• $variable - inserts a value of the variable as text content.
• ${expression} - braces are used for JEXL expressions. For example ${column1+column2} inserts a sum of 2 columns.
• ?variable, ?{expression} - syntax is the same as in 2 previous examples, but the result of evaluation is set as a prepared statement parameter, thus increasing the performance and eliminating the need to escape values.
  Please note that currently ?{} syntax is only supported by the JDBC drivers.

See Reference Manual for additional details.

In general such practice in not recommended because often it's a sign of a bad design. It's like using GOTOs or global variables in programming languages. But in several cases using a global variable may help to achieve required goal. We propose 2 approaches:

1. Use System.setProperty and System.getProperty to share variable between scripts. This is definitely a hack, but very easy to implement in JavaScript for example
2. Another approach is similar to the technqiue utilized in anonymous inner classes to modify a single-element array declared as a final variable.

The following example demonstrates both approaches:

<etl>
    <connection driver="script"/>
    <script> java.lang.System.setProperty('globalVar', 'Message from sript 1') </script>
    <script> java.lang.System.out.println('globalVar='+java.lang.System.getProperty('globalVar')) </script>

    <query>
        var globalVar = []; //Declare global variable
        query.next(); //Executes child scripts
        <script> //Updates global variable
           globalVar[0] = 1;
        </script>
        <script> //Outputs updated value of global variable
             java.lang.System.out.println('globalVar[0]='+globalVar[0]);
        </script>
    </query>
</etl>
         

Scriptella does not support whitespaces in field names, nevertheless several drivers including CSV and JDBC allow referencing columns by an index, i.e. $1, $2 ... $n.
Example:

<query connection-id="csv"> <!-- Read CSV file content -->
    <script connection-id="text">
        $1,$2,$3,$4 <!-- Print columns 1-4 -->
    </script>
</query>
                

To recognize Oracle PL/SQL statement blocks you'd have to specify plsql=true connection property (supported only by Scriptella Adapter for Oracle). In this case a slash(/) on a single line is used as a statement separator:

<connection driver="oracle" ...>
    plsql=true
</connection>
<script>
    CREATE OR REPLACE TRIGGER secure_del_trigger
       BEFORE DELETE
       ON emp
       FOR EACH ROW
    DECLARE
       unauthorized_deletion   EXCEPTION;
    BEGIN
       IF <your business rule is violated> THEN
         RAISE unauthorized_deletion;
       END IF;
    EXCEPTION
       WHEN unauthorized_deletion
       THEN
          raise_application_error (-20500,
            'This record cannot be deleted');
    END;
    /

    -- Other statements separated with a slash on a single line
</script>

            

If you are using Oracle JDBC driver directly then set the following configuration properties:

<connection driver="oracle.jdbc.driver.OracleDriver" ...>
    statement.separator=/
    statement.separator.singleline=true
</connection>
                

This idea is similar to Ant solution.

The following connection parameters are supported by the JDBC bridge:

• transaction.isolation - Transaction isolation level name
• autocommit - Enables/disables auto-commit mode.
• autocommit.size - Enables/disables auto-commit mode.

Example:

<connection driver="auto" url="jdbc:hsqldb:mem:test">
    autocommit.size=4 <!-- Automatically commit after every 4th statement -->
    transaction.isolation=SERIALIZABLE <!-- Sets TX level to Serializable -->
</connection>

Suppose we have 3 variables - a='valueA';b=null and c='valueC'.

Here is a CSV script which outputs them in a single line:

                    $a,$b,$c
                

The output for this script:

        valueA,$b,valueC
                

By default NULL variables are not substituted, because Scriptella substitution engine cannot distinguish null value from undeclared variable. This behaviour is not always desired. As a workaround you can specify a null_string connection property as follows:

<connection driver="text">
    null_string=<!-- Expand nulls to empty string -->
</connection>
                

And the output is:

valueA,,valueC