Stored Procedure

1) FineReport can call the stored procedures in mainstream databases, and the result of execution can become a dataset to be used in report design.

2) This article use MYSQL 5.6 as an example to show how to create datasets from stored procedures.

1. Select Stored Procedure

Click + and select Stored Procedure

2. Select the data connection corresponding to a MYSQL database

All the stored procedures in the database are displayed on the left of the panel.

3. Call a stored procedure

1) Double click proc_1. The SQL statement defining proc_1 is displayed on the right.

2) Parameters of proc_1 are automatically displayed below, including parameter name, type, IN/OUT model and default value.

3) Note: FineReport only supports automatically reading the parameters of stored procedures with respect to Oracle, MYSQL, DB2 and SQL Server, and parameters of other databases have to be added manually.

4. Input the default value of the parameter

1)Input the default value of the parameter [area]:East China

2)Preview the dataset.

5. Generate the dataset

1)Click theOK to generate the detaset.

2)The dataset has three returned sets, corresponding respectively to the three output parameters [avg_sales], [min_sales] and [max_sales].

6. Create a new stored procedure

1) Double click proc_2.

2) The parameter area is displayed below.

7. Preview theDataset

1）Click Preview.

2）Although the SQL statement defining [proc_2] defines the field [Salesperson] as [SP], in the preview of the dataset, [Salesperson] is still displayed.

8. Edit the data connection

Add the following parameter after the URL: ?useOldAliasMetadataBehavior=true. Click OK.

9. Preview it again

[Salesperson] is displayed as [SP].

10.Save the editing of the dataset

1)ClickOKto save the editing of the dataset.

2)The generated dataset has a returned set, which contains three data columns corresponding respectively to the three selected fields in the SQL statement.