Migrate from the Ant SQL task

Convert Ant-based database creation scripts to Scriptella ETL files and gain multi-source ETL, binary uploads, and simple redistribution.

Intended audience

Developers and database administrators.

Purpose

This how-to simplifies migration from Ant-based database creation scripts to Scriptella ETL files. Benefits include:

Uploading binary files from any URL. Example: INSERT INTO Data_Table(ID, File) VALUES(1, ?{file 'pagefile.sys'})
Built-in support for non-SQL data sources such as CSV or LDAP directories
Interoperability between several databases (or other data sources) in a single file — for example, query one database and insert the results into another, or export a result set to a CSV or HTML report
Easy redistribution: package the ETL file, scriptella.jar (about 580 KB in 1.3), and required drivers to run scripts in any environment with a Java 8 JRE (the tested release baseline). Run from the command line, Ant, or any other Java environment
User-friendly GUI wizards for interactive mode — not supported yet

Prerequisites

Read the tutorial.
Download the Scriptella binary distribution.

Steps

The simplest case

A simple Ant SQL task:

<sql driver="org.database.jdbcDriver" url="jdbc:database-url"
    userid="sa"  password="pass" classpath="lib/driver.jar">
	INSERT INTO Table VALUES (1,2,3,4);
	INSERT INTO Table VALUES (2,3,4,5);
</sql>

The Scriptella equivalent:

<!DOCTYPE etl SYSTEM "http://scriptella.org/dtd/etl.dtd">
<etl>
    <connection driver="org.database.jdbcDriver" url="jdbc:database-url"
    user="sa" password="pass" classpath="lib/driver.jar"/>
    <script>
	INSERT INTO Table VALUES (1,2,3,4);
	INSERT INTO Table VALUES (2,3,4,5);
    </script>
</etl>