Built-in SQL Submission

Version

Function Description

After designing a table and adding data entry widgets, you may need to bind cells (where data to be entered is located) to table fields in the database so that the entered data can be recorded into the corresponding table field in the database, as shown in the following figure.

Function Entrance

Choose Template (on the designer menu bar) > Data Entry Attribute, click the Submit tab, click the + icon, and select Built-in SQL from the drop-down list, as shown in the following figure.

Note:

Multiple built-in SQL submissions are executed in series (from the first one to the last one). If these built-in SQL submissions belong to the same transaction and one of them fails to be executed, all built-in SQL submissions will be rolled back.

The following figure shows how to set a built-in SQL submission. You can refer to the following sections for specific steps.

Selecting the Submission Type

Submission Type Selection (namely how to store data into a database) can be set to Smart Submission, Deletion Submission, Insert Submission, or Update Submission.

Selecting a Database and a Table

Note:

You need to set Mode for some databases (such as Oracle databases).

FineReport in 11.0.2 and later versions supports fuzzy search during data table selection. After you enter texts in the search box, the system can accurately search the tables corresponding to the relevant database and mode. The search results are displayed in the drop-down list for you to select, as shown in the following figure.

Adding Fields

You can add fields through Smart Add Field or Add Field. The following sections introduce differences between the two methods.

Smart Add Field is suitable for situations where you need to add multiple fields at the same time. After you click Smart Add Field, all fields in the table are selected by default. You can deselect unneeded fields.

Add Field is suitable for situations where you only need to add several fields. After clicking Add Field, you can add the needed fields one by one.

Adding Cells

Click Smart Add Cell to bind data entry cells to the table fields in the database. You need to manually select data entry cells, as shown in the following figure.

Note:

After adding cells, you'd better check whether the data entry cells of all fields are selected and whether the fields match the cells accurately.

Setting the Key

Key allows you to determine one or more fields based on which you can perform update or deletion. Key (not the same thing as the key in the database) is similar to the effect of where in SQL. If you need to modify or delete fields, you must select one or multiple keys, as shown in the following figure.

Smart-Adding a Cell Group

If you need to bind multiple cells to table fields in a database, you can use the Smart Add Cell Group function. For details, see Smart Add Cell Group.

Batch Modifying Cells

You can modify cells through the location offset (including Row Offset and Column Offset) of the Batch Modify Cells function.

Row Offset: If the offset value is n, cells are moved down by n rows. If the offset value is -n, cells are moved up by n rows.

Column Offset: If the offset value is n, cells are moved right by n rows. If the offset value is -n, cells are moved left by n rows.

Removing Fields

You can delete set fields through the Remove Field function, as shown in the following figure.

No Modification, No Update

After No Modification, No Update is selected, if the cell where the record is located has not been edited and modified, the record will not be updated.

Using this setting in scenarios with large-data-volume query and data entry can improve the data entry submission performance.

Submission Condition

Submission Condition allows you to filter to-be-stored data without interrupting the submission process. Only data that meets the conditions can be stored into the database.

Submission Event

You can set submission events separately for each record of data influenced by the outer built-in SQL submission. Each event is executed for one time after the outer built-in SQL is executed.