Writing BLOB data in an XDM File
Overview
This guide explains how to use a hook to extract a BLOB value from a database, encode it as Base64, and store it in an XDM file. This method replaces the local file approach by dynamically retrieving data from a database. The guide provides the necessary code, parameters, and workflow to execute this process efficiently.
Prerequisites
Before proceeding, ensure the following:
-
A valid database connection in XDM as source.
-
A file object with type Simple text in XDM as target.
Steps
1. Define the Hook
Create a task stage hook. The hook contains six parameters:
Display Name |
Data Type |
Description |
databaseConnection |
|
The name of the database connection in XDM. |
tableSchema |
|
The schema where the table is located. |
tableName |
|
The name of the table containing the BLOB column. |
blobColumn |
|
The BLOB column storing the required data. |
condition |
|
The SQL condition to select the correct row. |
outputFile |
|
The XDM file where the Base64 data will be stored. |
The connection and the file of type Simple text must exist in XDM; the display name is specified in the parameter. The other four parameters are used in the SQL query; they define the database table in which the BLOB column is located and which row should then be selected. The hook is structured in such a way that the query should only select one row.
The code of the hook is given as follows:
import org.apache.commons.codec.binary.Base64;
import de.ubs.xdm3.script.evaluator.ScriptValidationException;
def query = "SELECT " + taskStageHook.blobColumn +
" AS BLOBVALUE FROM " + taskStageHook.tableSchema +
"." + taskStageHook.tableName +
" WHERE " + taskStageHook.condition;
print("Query for BLOB is: " + query);
def preparedStatement = taskStageHook.databaseConnection.jdbcConnection.prepareStatement(query);
def resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
blob = resultSet.getBlob("BLOBVALUE");
blobBytes = blob.getBinaryStream().readAllBytes();
base64String = Base64.encodeBase64(blobBytes);
taskStageHook.outputFile.content=base64String;
} else {
throw new ScriptValidationException("No data found in query " + query);
}
resultSet.close();
preparedStatement.close();The hook selects the BLOB from the database in the first step, converts it to a Base64 string in the second step and then writes this as content to the XDM file.
The complete hook can be found here.
2. Execute the Hook in a Workflow
Call the hook using HookRunner() in a workflow:
HookRunner()
.hook('BLOB example')
.parameter('databaseConnection', '<Connection in XDM>')
.parameter('tableSchema', '<Table schema>')
.parameter('tableName', '<Table name>')
.parameter('blobColumn', '<BLOB column>')
.parameter('condition', '<Condition>')
.parameter('outputFile', '<XDM file object>')
.run();Replace the placeholders (<>) with actual values from your database or with the display names of the XDM objects. The database connection and output file must exist in XDM.