Inserting BLOB or CLOB data into a database

 Overview

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.

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

Requirement

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

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 DemoJob.zip Jul 27, 2014 byShicong Hong
Version History
Revision #:
1 of 1
Last update:
‎04-17-2017 08:30 PM
Updated by:
 
Labels (1)
Contributors