How to Read the Contents of a File into a Database BLOB Attribute

Introduction

Sometimes it is necessary to store a file in binary form in a database. By writing the file as a binary large object (BLOB), it is possible to maintain the object's integrity without modifying it for the destination format. For example, images, multimedia, PDFs, or spatial information can be stored in a database that doesn't inherently store such data types.

In FME, the AttributeFileReader transformer reads the contents of a file and stores it in an attribute. Writing it out involves storing it as a binary or blob attribute on the destination feature type. The AttributeFileWriter transformer can be used to perform the reverse operation, taking a binary value and writing it out in its original form.

The RasterExtractor and RasterReplacer transformers can also be used to convert raster images into BLOB elements and back again.

Step-by-Step Instructions

In this example, we have a MapInfo TAB (MITAB) dataset representing Parks in Vancouver, along with JPEG photos of the parks. We want to load the data into a SpatiaLite database and store the images of each park as a BLOB element. We will use the AttributeFileReader to read each image file and store it in a field in the database.

We will then use the AttributeFileWriter to perform the reverse operation and write the BLOB as a JPEG again.

Download the attached zip file to follow along.

Part 1: Writing images to a BLOB attribute using the AttributeFileReader

We will create a workspace to read data from a MapInfo TAB file, get JPEG images that correspond with each feature in the dataset, and write the data to a SpatiaLite database that contains a binary "photo" field.

1. Open a workspace

Open a new FME Workspace to follow this tutorial step-by-step, or open Parks.fmw to follow along in the finished workspace.

2. Add a MITAB reader for the Parks.tab dataset

Click Add Reader and select the Parks.tab file from the tutorial downloads. The format should automatically set to "MapInfo TAB (MITAB)".


Click OK to add it to the workspace.

3. Add an AttributeFileReader

Click anywhere on the canvas and begin typing "AttributeFileReader". Add this to the workspace and connect it to the reader feature type.



Double-click the AttributeFileReader to open the parameters. Here we can specify the name of the new BLOB attribute, the name of the file to be read (we'll use an expression), and the source file's character encoding. Set the parameters as follows:

• Destination Attribute: photo
• Source Filename: <Tutorial Download Location>/@Value(ParkName).jpg
• Source File Character Encoding: Binary (fme-binary)

See the AttributeFileReader documentation for more information on configuring the parameters.


The expression uses the function @Value() to dynamically get the park name for each feature. This transformer will output features with JPEG files stored as BLOBs in the "photo" attribute.

3. Add a SpatiaLite writer

Click Add Writer and set the parameters to:

• Format: SpatiaLite
• Dataset: <Tutorial Download>/output/Parks.sqlite
• Table Definition: Copy from Reader...

Click OK to add it to the canvas, and connect it to the AttributeFileReader.

You may need to expand the feature type and manually connect the attributes, as SpatiaLite automatically reconfigures the attribute names to be lowercase.



Next we'll tell the writer what to do with the new "photo" BLOB attribute.

4. Configure the writer attributes

Double-click the writer feature type to open the properties.

In the User Attributes tab, ensure "Manual" is selected, then add the "photo" attribute and set it to the Type "binary".


Click OK.

Connect the "photo" attribute between the transformer and the writer feature type.



The workspace is now configured to read in Parks.tab, get the associated JPEGs that match the park names, and write the JPEGs as BLOBs in the destination SpatiaLite database.

5. Run the workspace

Run the workspace and inspect the output. The SpatiaLite database contains a few rows of park information with a "photo" field that contains a BLOB.



Note that only the features with photos are output in this case. If we wanted to output all features that were in the input .tab dataset, even the features without associated photos, we could also connect the AttributeFileReader's <Rejected> port to the output feature type. For the purposes of this demo, all we care about are the rows with JPEGs attached.

Part 2: Writing BLOB fields in their original form using the AttributeFileWriter

We will now read back the SpatiaLite database with the binary "photo" field and output the BLOB values in their original JPEG form. We will use the AttributeFileWriter to transform the binary values back into JPEG files.

1. Open a workspace

Open a new FME Workspace to follow this tutorial step-by-step, or open AttributeFileWriter_Demo.fmw to follow along in the finished workspace.

2. Add a SpatiaLite reader for the Parks.sqlite dataset

Click Add Reader and set the format to SpatiaLite. Select the Parks.sqlite file from Part 1 (or use the one in the tutorial downloads).



Click OK to add the reader to the workspace.

3. Add an AttributeFileWriter

Click the canvas and begin typing "AttributeFileWriter". Add it to the workspace and connect it to the parks feature type.

Double-click the transformer to open its parameters. Here we can specify which attribute we want to write out as a file. We will use the @Value() expression to set the JPEG filename based on the park name.

Set the parameters as follows:

• Source Attribute: photo
• Target Filename: <Output folder>/@Value(parkname).jpg
• If Target File Exists: Overwrite file
• Target File Encoding: Binary (fme-binary)

See the AttributeFileWriter documentation for more information on configuring the parameters.

Click OK.

The workspace should look as follows:



4. Run the workspace

Run the workspace. Three .jpg files will be written to the path specified in the transformer. These files were successfully converted from BLOB fields to JPEG files by the workspace.

Data Attribution

The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver. The JPEGs are in the public domain and were obtained from Pixabay.