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 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 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 as 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;

Refer to this page for detailed information on creating a user routine. 

 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:

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 Job

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

Related Files


  File Modified
  ZIP Archive Jul 27, 2014 byShicong Hong
Version History
Revision #:
1 of 1
Last update:
‎04-17-2017 08:30 PM
Updated by:
Labels (1)