Stored Procedure

A stored procedure is a set of SQL statements stored in the database. FineReport allows you to call stored procedures directly to obtain the table generated after the SQL statements are executed.

The following section takes an MySQL database as an example to demonstrate how to create a template dataset from the stored procedure.

Note: 

1. When executing a stored procedure to obtain the result dataset, the system only supports statements starting with {CALL and {?=CALL; statements starting with EXEC are not supported.

2. If you want to apply the dataset function tablename.select() to the dataset obtained from the stored procedure, you should write the function in the format of Stored procedure name_Result dataset name.select(), for example, Datas_Table.select(Tm_QY_Ic_Sum). Otherwise, the call will not succeed. When a stored procedure contains multiple result sets, you need to check which result set contains the required field and use the name of that result set in the statement.

1. Click the icon and Stored Procedure to create a dataset, as shown in the following figure.

2. Select a database. The stored procedures in the database are displayed in the left box. Double-click the stored procedure, as shown in the following figure.

Note: 

MySQL, IBM Db2, Oracle, and SQL Server databases can automatically retrieve parameters and their default values, while Microsoft Access, HSQL, and Sybase databases require you to manually add parameters and their values.

In MySQL, query results of stored procedures do not use column aliases by default. That is, in statements like SELECT XXX AS Alias FROM ..., the original column name XXX is used by default in the result set, rather than the alias specified after AS.

To display the column aliases, you need to suffix ?useOldAliasMetadataBehavior=true to the database connection URL. For example, jdbc:mysql://env.finedevelop.com:55703/testskp?useOldAliasMetadataBehavior=true.

3. Click the icon to preview the result dataset of the stored procedure.

4. Click OK, and the dataset obtained from the stored procedure is added successfully, as shown in the following figure.

Note: 

FineReport can only return result sets from stored procedures, not individual parameter values.

If a stored procedure returns multiple result sets, these result sets will be returned in FineReport at the same time, as shown in the following figure.

Click the icon. You can view the multiple result sets, as shown in the following figure.

You can also view these result sets in the created template dataset, as shown in the following figure.