LDSTechForumProjects

Loading binary data with DB Migrator

Overview

As of version 3.4 of the LDS Java Stack, DB Migrator is capable of loading binary data.

You may have data needs where you need to load images, files or other binary content into your database either as bootstrapped data needed for you application or as versioned binary content.

Steps to make binary data work in a migration script

DB Migrator was updated to handle binary content but there are some rules and restrictions when using this feature.

  • NOTE/WARNING: Binary data can only be loaded through the DB Migrator Java/Maven plugin. SQL scripts will not run natively in Oracle. Because we are working with files, we have to utilize Java to open and stream them to the Oracle server for inserts. Oracle does have native ways of working with files and loading them with procedures but that requires the file to reside on the oracle server. Since migrations are typically ran from a remote computer, Java is needed to make the file push happen.
  • Create a new database migration fwd.sql file.
  • Add a migration annotation called -- @binary_data at the top of your migration script.
  • Create a sub-folder in your schema and give it a name either the same as your migration script or at a minimum the timestamp. Your binary files will be placed in here. Versioning the folder name as the same name as your scripts allows you to load the same or altered binary files multiple times as you enhance your data and project over time.
  • Copy all of your images, files or whatever binary content you want into this new sub-folder. Give the file names something meaningful to you or your project.
  • Back in the migration script add insert SQL statements configured for your table where you want to load binary data.
  • In the SQL insert for the binary blob column utilize the bin_insert{<filename>} code block to reference the binary file you want inserted into that column. This new keyword block will be recognized by DB Migrator, resolve the referenced file and insert the binary stream in your final SQL insert that gets sent to the database.

Sample Script and Setup

File and folder setup

-schema-versions
|
--SCHEMA
  |
  --201309051625-blob_insert.fwd.sql
  --201309051625-blob_test
    |
    --Picture.jpg
    --Report.pdf

Migration Script

-- @binary_data

INSERT INTO PICTURE (ID, CONTENTTYPE, IMAGE) VALUES(PICTURE_ID_SEQ.NEXTVAL,'image/jpeg', bin_insert{201309051625-blob_test/Picture.jpg});
INSERT INTO PICTURE (ID, CONTENTTYPE, IMAGE) VALUES(PICTURE_ID_SEQ.NEXTVAL,'application/pdf', bin_insert{201309051625-blob_test/Report.pdf});
This page was last modified on 11 September 2013, at 10:30.

Note: Content found in this wiki may not always reflect official Church information. See Terms of Use.