Package scriptella.jdbc

Scriptella JDBC Bridge.

See: Description

Interface Summary

Interface	Description
SqlTokenizer	This interface provides a contract to iterate SQL statements.

Class Summary

Class	Description
GenericDriver	Generic adapter for JDBC drivers.
JdbcConnection	Represents a JDBC connection.
JdbcUtils	Utility class JDBC related operations.
ParametersParser	Parses parameter expressions in SQL statements.
ResultSetAdapter	Represents SQL query result set as ParametersCallback.
SqlParserBase	Customizable SQL parser.
SqlReaderTokenizer	Reader based SQL tokenizer.

Exception Summary

Exception	Description
JdbcException	Unchecked wrapper for SQL exceptions or other SQL related errors.

Package scriptella.jdbc Description

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.hsqldb.jdbcDriver" url="jdbc:hsqldb:file:tmp" user="sa" classpath="hsqldb.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>