Package scriptella.jdbc

package scriptella.jdbc
Scriptella JDBC Bridge.

As specified in Service Provider API all JDBC drivers are registered via this bridge. This procedure is transparent to end user, he only have to specify jdbc driver class name or an alias.

General information

Driver class:JDBC driver class name
URL:JDBC driver URL, e.g. jdbc:mydb:...
Runtime dependencies:Set of libraries required by the JDBC driver.

JDBC Bridge Properties

JDBC bridge makes the following configuration properties available in configuration element:

Name Description Required
statement.cache Size of prepared statements cache or 0 to disable statement caching. No, the default value is 64.
statement.separator SQL statements separator string. Similar to Ant delimiter property. No, the default value is ; (semicolon).
statement.batchSize Activates batching with specified batch size. See Batching section for more details. No, the default value is 0 (batching is disabled). Since version 1.1.
statement.fetchSize Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet. See setFetchSize JavaDoc for additional details. Since version 1.1. No, the default value is 0.
flushBeforeQuery True if flush of current connection need to be performed before query execution. Flushing might be necessary in batch mode, in order to send pending batches before executing a query. See Batching section for more details. No, the default value is false. Since version 1.1.
statement.separator.singleline True if the delimiter should only be recognized on a line by itself. Leading and trailing whitespaces are ignored when searching for a separator in statement.separator.singleline=true mode. Similar to Ant delimitertype property. No, the default value is false.
keepformat True if the original SQL formatting should be preserved.

This property is similar to Ant keepformat property except that Oracle-style hints (?*+ hint */) are always preserved in Scriptella.

 No, the default value is false, i.e. extra whitespaces and comments removed.
transaction.isolation Transaction isolation level name or an integer value according to java.sql.Connection javadoc. The valid level names are: READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ and SERIALIZABLE. No, the default value is driver specific.
autocommit True if connection is in auto-commit mode. If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual transactions.See also autocommit.size.

Note: In general avoid setting autocommit to true, because in this case an ETL process cannot be rolled back correctly. Use this parameter only for performance critical operations (bulk inserts etc.).

 No, the default value is false.
autocommit.size If positive, specifies the number of statements to execute before producing implicit commit, i.e. controls how much data is committed in its batches.

Notes:

  • In general avoid using autocommit.size, because in this case an ETL process cannot be rolled back correctly. Use this parameter only for performance critical operations (bulk inserts etc.).
  • If the autocommit is true, then setting autocommit.size has no effect

 No, the default value is false.

Properties Substitution

The bridge supports standard ${} variables expansion and allows to set prepared statement parameters via ?{} syntax.

Example: 


var=_name
id=11
--------------------------------------
select * FROM table${var} where id=?id
-- is transformed to a JDBC prepared statement---
select * FROM table_name where id=?
-- where parameter id=11

Notes:

  • $ prefixed expressions are substituted in all parts except comments.
  • ? prefixed expressions are not substituted inside quotes and comments.

    • Example: 
    --only ${prop} and ?surname are handled
     SELECT * FROM "Table" WHERE NAME="?John${prop}" and SURNAME=?surname;

Batching

JDBC batching is a very important feature which allows sending multiple commands to the database in one call. Scriptella batching was introduced in version 1.1 and controlled by statement.batchSize parameter. The value of this parameter specifies number of statements to be combined in a batch before sending it to the database.

Please note that behavior for batching depends on the type of statements processed, as the result non-prepared SQL statements (statements without ? parameters) are processed in a single batch group different from parameterized prepared SQL statements. The grouping rules are the following:

  • Normal(non-prepared) statements are always grouped in a single batch per ETL script element.
  • Parameterized prepared statement use SQL text as a group key, i.e. different statements go into different groups what may introduce not always desired behavior.
Notes:
  • As the result, mixing parameterized (prepared statements) with normal statements in a single ETL element is NOT RECOMMENDED for batch-mode, because statements are processed in different batch groups and results might be unpredictable. The recommended approach is to create 2 connections: one for non-batched operations like DLL scripts, or non-parameterized statement and one for bulk load.
  • If data is updated in a batch and then selected using the same connection, flushBeforeQuery connection parameter must be enabled in order to flush pending batch updates before querying the database.
  • When querying large tables, consider using statement.fetchSize to increase number of records fetched per each request to the database.

The following 2 examples show a RECOMMENDED way of batching. Example 1. Extract->Load using PreparedStatement.setParameters/addBatch : 


<connection id="in" url="$db_url" user="$db_user" password="$db_password">
    #Fetch larger number of rows per DB request
    statement.fetchSize=1000
</connection>
<connection id="out" url="$db_url" user="$db_user" password="$db_password">
    statement.batchSize=5
</connection>
<query connection-id="in">
    SELECT * from Bug
    <script connection-id="out">
        INSERT INTO Bug VALUES (?ID, ?priority, ?summary, ?status);
    </script>
</query>
Example 2. Bulk load of non-prepared statements (without ?, but $ are allowed) 
<script connection-id="out">
    INSERT INTO Bug VALUES (1, 'One');
    INSERT INTO Bug VALUES (2, 'Two');
    INSERT INTO Bug VALUES (3, '$Value');
    ....
</script>

Examples

    <connection id="in" driver="org.h2.Driver" url="jdbc:h2:tmp" user="sa" classpath="h2.jar">
        #Disable cache - just for example
        statement.cache=0
        #Set SQL statements separator
        statement.separator=;
    </connection>

    <connection id="out" driver="h2" url="jdbc:h2:file:out" user="sa"/>
    <query connection-id="in">
        SELECT * from Bug
        <script connection-id="out">
            INSERT INTO Bug VALUES (?ID, ?priority, ?summary, ?status);
        </script>
    </query>