scriptella.jdbc.package.html Maven / Gradle / Ivy

Go to download

Show more of this group Show more artifacts with this name
Show all versions of scriptella-core Show documentation

The newest version!


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>

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

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`.