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, and required drivers to run scripts in any environment with Java 17 or newer. Run from the command line, Ant, or any other Java environment. Remain on Scriptella 1.3 if you need Java 8.
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>