Posted By

ejboy on 02/23/07


Tagged

database xml jdbc etl scriptella blob


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

ejboy


ETL Script to Insert BLOB from File into a Database


 / Published in: XML
 

URL: http://scriptella.javaforge.com

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:

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

Report this snippet  

Comments

RSS Icon Subscribe to comments
Posted By: ejboy on May 13, 2007

?{textfile 'url'} can be used for CLOBs

You need to login to post a comment.