Posted By

ejboy on 02/23/07

Tagged

database
xml

Versions (?)

Last Edited at 02/23/07 01:46pm

Statistics

Viewed 1267 times

Favorited by 1 user(s)

Related snippets

ETL Script to Insert BLOB from File into a Database

/ Published in: XML

Scriptella ETL allows inserting files into a database. This is achieved by a simple bind variables extension syntax ?{file ...}.
The following sample initializes table of music tracks. Each track has a DATA field containing a file loaded from an external location. File song1.mp3 is stored in the same directory as etl.xml and song2.mp3 is loaded from the web:

Expand | Embed | Plain Text

Copy this code and paste it in your HTML

<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
    <etl>
        <connection driver="hsqldb" url="jdbc:hsqldb:file:tracks" user="sa" classpath="hsqldb.jar"/>
        <script>
            CREATE TABLE Track (
              ID INT,
              ALBUM_ID INT,
              NAME VARCHAR(100),
              DATA LONGVARBINARY
            );
            <!-- Inserts file with path relative to ETL script location -->
            INSERT INTO Track(id, album_id, name, data) VALUES
                   (1, 1, 'Song1.mp3', ?{file 'song1.mp3'});
            <!-- Inserts file from an external URL-->
            INSERT INTO Track(id, album_id, name, data) VALUES
                   (2, 2, 'Song2.mp3', ?{file 'http://musicstoresample.com/song2.mp3'});
        </script>
    </etl>