Writing data to an XDM File
Overview
This guide explains how to use a hook to retrieve data from a database and write it to a file. It covers both
character-based data types (such as NVARCHAR and CLOB) and BLOB data. Since the implementation differs only in a
few processing steps, both variants are described together.
The guide first explains the common workflow using NVARCHAR as the reference implementation. The BLOB-specific
changes are then described alongside the corresponding code sections, where the retrieved binary data is
Base64-encoded before being written to the output file.
The guide provides the required code examples, parameters, and configuration details for both variants.
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 seven 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 data column. |
dataColumn |
|
The data column storing the required data. |
condition |
|
The SQL condition to select the correct row. |
outputFile |
|
The XDM file where the data will be stored. |
isBlob |
|
Specifies whether the coding mode is BLOB (true) or NVARCHAR (false) |
The connection and the file of type Simple text must exist in XDM, while the display name is specified through the corresponding parameter. The remaining four parameters are used to build the SQL query, defining the database table containing the data column and the row to be selected. The last parameter specifies the processing mode, indicating whether the selected column contains character-based data or BLOB data. The hook is designed so that the query returns exactly one row.
The code of the hook is given as follows:
import de.ubs.xdm3.script.evaluator.ScriptValidationException;
def query = "SELECT " + taskStageHook.dataColumn +
" AS DATAVALUE FROM " + taskStageHook.tableSchema +
"." + taskStageHook.tableName +
" WHERE " + taskStageHook.condition;
print("Query is: " + query);
def preparedStatement = taskStageHook.databaseConnection.jdbcConnection.prepareStatement(query);
def resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
if(taskStageHook.isBlob){
blob = resultSet.getBlob("DATAVALUE");
blobBytes = blob.getBinaryStream().readAllBytes();
base64String = Base64.encodeBase64(blobBytes);
taskStageHook.outputFile.content=base64String;
}else{
text = resultSet.getString("DATAVALUE");
taskStageHook.outputFile.content = text
}
} else {
throw new ScriptValidationException("No data found in query ");
}
resultSet.close();
preparedStatement.close();The data is selected from the database and written as a string into a simple text file, depending on the coding mode.
For character-based data the database field is read using a standard string retrieval method and then directly
transferred to the output file. The resulting file contains the original text content as stored in the database.
The code for the BLOB value is the same as for the NVARCHAR case, with only one change in the write routine: the data
is Base64-encoded before being written to the XDM file.
The isBlob parameter determines how the retrieved value is processed. When set to true, the value is handled as BLOB data; otherwise, it is treated as character-based data.
The complete hook can be found here Writing database data to an XDM File.
2. Execute the Hook in a Workflow
Call the hook using HookRunner() in a workflow:
HookRunner()
.hook('Writing Database Data to an XDM File')
.parameter('databaseConnection', '<Connection in XDM>')
.parameter('tableSchema', '<Table schema>')
.parameter('tableName', '<Table name>')
.parameter('dataColumn', '<Data column>')
.parameter('condition', '<Condition>')
.parameter('outputFile', '<XDM file object>')
.parameter('isBlob', '<true/false>')
.run();Replace the placeholders (<>) with the actual values from your database or with the display names of the XDM objects.
The database connection and output file must already exist in XDM before executing the process. Set isBlob according to
the data type: false for character-based columns (such as NVARCHAR or CLOB) and true for BLOB columns.