Qlik Talend Data Integration: Inserting BLOB or CLOB data into a database

TalendSolutionExpert — Mon, 22 Dec 2025 08:26:42 GMT

Some projects require a large string or block of binary data to be stored in a database. A BLOB (Binary Large Object) is a data type that can be used to store a large collection of binary data in a database table. A CLOB (Character Large Object) is a data type that can be used to store a large collection of character data in a database table. For example, a digital file containing a picture, video, or a song can be stored in a database using a BLOB, or a plain text file can be stored in a database using a CLOB.

This article explains how to insert images into a MySQL table with a BLOB type. The example can be adapted, with minor changes, to use the CLOB type or another database.

Environment

This procedure was written with:

Talend Open Studio for Data Integration 5.5.0-r117820
JDK version: Sun JDK build 1.6.0_26-b03
Operating system: Windows 7 Professional, 64-bit
Mysql 5.0.67-community-nt

Talend verified this procedure to be compatible with Data Integration releases starting from v4.2.3.

Procedure

This example uses a MySQL table with two fields: ID and picture. The table definition is as follows:

CREATE TABLE blobdemo (
  Id int(11) NOT NULL auto_increment,
  Picture blob,
  PRIMARY KEY (`Id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

A specific folder (for example D:\image\) stores the pictures that will be inserted into the blobdemo table.

Creating a user routine

Create a user routine called MyRoutineDemo. It has a ByteArrayFromFile function that requires a file path as input parameter and is used to read a file and convert it to a byte array. The user routine code for this example follows.

package routines;public class MyRoutineDemo {
public static byte[] ByteArrayFromFile(String filepath) {
try{ 
java.io.File file=new java.io.File(filepath);
java.io.FileInputStream fis = new java.io.FileInputStream(file);
int fileLength = (int) file.length();
byte[] incoming_file_data = new byte[fileLength]; // allocate byte array of right size
fis.read(incoming_file_data, 0, fileLength ); // read into byte array
fis.close();
return incoming_file_data;
}catch(Exception err){
err.printStackTrace();return null;
}
}
}

For details on creating a user routine, see How to create user routines in the Talend Help Center.

Creating demo job

Create a Job to iterate over the picture files in the D:\image\ folder and insert each one into the blobdemo table. The Job design is shown in the following image and is available in the attached Zip file:

tFileList: Iterate all pictures in the D:\image\ folder.

tFixedFlowInput: get the current file path and output it.

tMap: call the routine to read file as byte array.

tMysqlOutput: insert the BLOB data into target table, select BLOB type in the Db type List to map the byte[] type on the schema of tMysqlOutput.

Executing the Job