Upload file to DB and download

1. Application scenarios

Hoping that users can directly save small files in the database, and download these files in the report.

2. Realization idea

Upload: Use the "File Widget" to upload the file, set the Data Entry Attributes, and save it to the database field in a binary stream (the field type must be able to store binary data)

The types of fields that can be stored in each database are different. The following lists the field types that commonly used databases store binary data:

Download: Drag the binary data column into the cell, and set the "Other> Display Content" of the Cell Attributes to display the binary content with the download link, and then the download can be realized.

Note: This document is suitable for uploading and downloading small files. If the file is too large, it will take up too much space of database table. It is recommended to use the upload and download file plugin.

Below we illustrate the implementation process by uploading a jpg file to the MySQL database and downloading it as an example.

Note: The FRDemo database that comes with FanRuan is a Sqlite database, and it does not support downloading files.

1. Data preparation

1) Prepare a jpg file, such as 1.jpg;

2) A database table test is created in the database, which contains fields (filename[varchar], file[Blob]). as shown in figure below:

3) After designing the table, establish a connection between the designer and the database. For details, refer to JDBC connection to database.

2. Upload

1) Create a template

Insert the formula filename(B2) into cell A2. Add "File Widget" to cell B2.

If there is a need to insert a row, you need to select cell A2 and set "Cell Attributes> Other> Insert Row Policy" to the Original, otherwise the formula for the inserted row will become invalid.

2) Set Data Entry Attributes

Add built-in SQL to bind columns and values in the Data Entry Attributes, as shown in the following figure:

3) Upload and submit

Save the template and click Data Entry Preview. Use the widget in cell B2 and select a local file to upload, as shown in the figure below:

After uploading successfully, click Submit to see the data in the database. As shown in the figure below:

3. Download

Create a template, add a dataset ds1, the SQL statement is select * from test

Drag the field into the cell, and set the "Other> Display Content" of the cell where the file field is located to "Display the binary content using download link", and set the "File Name for Download" as shown in the figure below:

Save the template and click Data Entry Preview, you will see the file field will be displayed in the form of a hyperlink, click on the hyperlink to download.

Refer to the completed templates:

%FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\DataEntry\DataEntryApplication\Upload_to_database_and_download.cpt

%FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\DataEntry\DataEntryApplication\download.cpt

Click to download:

Upload_to_database_and_download.cpt

download.cpt