We Recommend

Beginning XML Beginning XML
The perfect resource for beginning XML programmers, this guidebook shows you what XML is, how to use it, and what technologies surround it. The authors build on the strengths of previous editions while covering the latest changes in the XML landscape such as XQuery, RSS and Atom, and Ajax.


Posted By

ejboy on 02/23/07


Tagged

database xml jdbc etl scriptella blob


Versions (?)


Who likes this?

1 person has 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.