DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Fyodor has posted 14 posts at DZone. View Full User Profile

Script To Insert BLOB From File Into A Database

  • submit to reddit
        <a href="">Scriptella ETL</a> 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:
    <!DOCTYPE etl SYSTEM "">
        <connection driver="hsqldb" url="jdbc:hsqldb:file:tracks" user="sa" classpath="hsqldb.jar"/>
            CREATE TABLE Track (
              ID INT,
              ALBUM_ID INT,
              NAME VARCHAR(100),
            <!-- 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 ''});


Fyodor Kupolov replied on Wed, 2006/12/20 - 11:28am

Use ?{textfile 'path/to/file'} to insert CLOBs