How to insert images into a MySQL table with BLOB type

This article explains how to insert images into a MySQL table with BLOB type.

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.

Prerequisites

This example uses a MySQL table, named blobdemo, with two fields: Id and Picture. The table definition is as follows:
A specific folder, in this example "C:/Talend/images", stores the pictures that will be inserted into the blobdemo table.

Creating a user routine

Procedure

In the Repository tree view, expand Code to display the Routines folder.
Right-click Routines and select Create routine.
The New routine dialog box opens. Enter its name, in this example MyRoutineDemo, a purpose and a description.
Click Finish to close the dialog box.
Note: The newly created routine appears in the Repository tree view, directly below the Routines node.
The routine editor opens to reveal a model routine which contains a simple example, by default, comprising descriptive text in blue, followed by the corresponding code.

Replace the model with the following code and press Ctrl+S to save the routine.

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.

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 more information on how to create user routines, see the Talend User Guide on Talend Help Center.

Creating a Job to insert BLOB data into a MySQL table

Create a Job to iterate over the picture files in the "C:/Talend/images" folder and insert each one into the blobdemo table.

The tFileList iterates all pictures in the "C:/Talend/images" folder.
The tFixedFlowInput gets the current file path and outputs it.
The tMap calls the routine to read the files as byte array.
The tMysqlOutput inserts the BLOB data into the target table.

Set up the Job

Procedure

Create a Standard Job and enter its name, in this example Insertblobdatatodatabase.
Drag and drop the following components to the design workspace: tFileList, tFixedFlowInput, tMap and tMysqlOutput.
Connect the tFileList and the tFixedFlowInput using an Iterate link.
Connect the tFixedFlowInput to the tMap and the tMap to the tMysqlOutput using Row > Main links.

Configure the data input

Procedure

In the Basic settings view of the tFileList, in the Directory field, click the [...] button to browse to the directory, in this example "C:/Talend/images".
In the FileList Type list, select Files.
Click the [+] button to add a row in the Files area and define a filter to match files. In this example, type in "*.png" to retrieve all picture files.
In the Basic settings view of the tFixedFlowInput, click the Edit Schema button and add a column, filepath of type String.

The defined column displays in the Values panel.
Click in the Value cell of the filepath column and press Ctrl+Space to access the global variable list.
From the global variable list, select tFileList_1_CURRENT_FILEPATH.

Configure the tMap component to call the user routine

Before you begin

You have created a user routine.

Procedure

Double-click the tMap to open the map editor.
Click the [+] button to add an output table named out1.
At the bottom right corner (out1) of the Schema editor view, click the [+] button to add a row, picture of type byte.
In the Expression field of the picture column on the output side, type in MyRoutineDemo.ByteArrayFromFile(row1.filepath).
Click OK to validate these changes and accept the propagation prompted by the dialog box that pops up.

Insert the BLOB data into the target table

Before you begin

You have created a MySQL table.

Procedure

In the design workspace, double-click the tMysqlOutput component to display its Basic settings view and set its parameters.
Set Property Type to Repository and then click the three-dot button to select the correct DB connection.

The connection details display automatically in the corresponding fields.
Click the [...] button next to the Table field and select the target table, blobdemo in this example.

Executing the Job

Procedure

Press Ctrl+S to save your Job.
Press F6 to execute the Job.
Query the blobdemo table to verify that the pictures were inserted successfully.