Files
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), you can 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 to BLOB elements and back.
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 each park's image as a BLOB column. We will use the AttributeFileReader to read each image file and store it in a database field.
We will then use the AttributeFileWriter to perform the reverse operation and write the BLOB back as a JPEG.
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, retrieve JPEG images corresponding to each feature in the dataset, and write the data to a SpatiaLite database that contains a binary "photo" field.
1. Open Workbench
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 an 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 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 @Value() function to dynamically retrieve 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, since SpatiaLite automatically reconfigures attribute names to 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 its Type to "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, each with a "photo" field containing a BLOB.
Note that only the features with photos are output in this case. If we wanted to output all features from the input .tab dataset, including those without associated photos, we could also connect the AttributeFileReader's <Rejected> port to the output feature type. For the purposes of this demo, we only care about the rows with JPEGs attached.
Part 2: Writing BLOB fields in their original form using the AttributeFileWriter
We will now read the SpatiaLite database using the binary "photo" field and output the BLOB values in their original JPEG format. 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 written to 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. The workspace successfully converted these files from BLOB fields to JPEG files.
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.