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
Execute the Job, then query the blobdemo table to verify that the pictures were inserted successfully.
