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 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 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.
Refer to this page for detailed information on creating a user routine.
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.