Inserting BLOB or CLOB data into a database


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.


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.


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,

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{ file=new; fis = new;
int fileLength = (int) file.length();
byte[] incoming_file_data = new byte[fileLength]; // allocate byte array of right size, 0, fileLength ); // read into byte array
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

Execute the Job, then query the blobdemo table to verify that the pictures were inserted successfully.

Version history
Revision #:
4 of 4
Last update:
‎09-29-2018 12:14 AM
Updated by:
Labels (2)