Previous Page Next Page
Stored Procedures
Adding stored procedures to a catalog
Editing parameter values
Updating stored procedures
A stored procedure is a compiled program, consisting of one or more statements and is saved into a database. The statement in a stored procedure can be an SQL statement or a control flow statement, such as an If-else statement or a Loop statement. A stored procedure is stored in the DBMS, so that it can be called locally or remotely. In addition, a stored procedure can return a value, single result set or multiple result sets. Currently, JReport supports stored procedures that return a single result set. If a stored procedure returns more than one result set, the first one will be used by JReport.
As a program, a stored procedure can take parameters. There are three types of parameters: IN, OUT and INOUT. The IN parameter lets you pass values to the procedure. The OUT parameter returns values to the caller. The INOUT parameter enables you to pass initial values to the procedure, and then returns the updated value to the caller.
Queries and business views can be built on stored procedures and a report is developed from a query (or something else which is functionally similar) or from a business view.
A stored procedure itself can be used to create page reports directly, and in this sense it functions the same as a query. Therefore, you can use the Data Manager to control the data retrieval of stored procedures, including the number of rows to be displayed and the duration allowed for the retrieval, and use the manager to keep access information from previous runs of a stored procedure. You can also create cached result files for stored procedures and save the result files somewhere in your machine, then when you view reports based on the stored procedures, you can choose to use the data from the cached query result files as opposed to the database.
Adding stored procedures to a catalog
To add procedures stored in the database to a catalog via a JDBC conn ection, take the following steps:
- In the Catalog Manager resource tree, right-click the JDBC connection node and click Add Stored Procedure on the shortcut menu. The Add Stored Procedures dialog appears.
- The dialog lists all the stored procedures in the Stored Procedures box in a three-level tree. The top level is SQL-catalog, the second is SQL-schema and the last level is stored procedures. Select the required stored procedures, and then click Add. However, not all DBMSs support database stored procedures. In this case, you may see nothing in the box.
- If any of the selected procedures contain parameters, the Stored Procedure Parameters dialog appears for you to input the required values. These values will be saved inside the stored procedure object, and will be used as the default value when executing this procedure. You can edit the stored procedure parameters at any time. For details, see Editing parameter values.
- When finished, click the Done button to close the dialog.
When a stored procedure has been added into a catalog, JReport will in turn do the following:
- Retrieve the stored procedure's information from DBMS via JDBC.
- Prompt you to provide the values of IN and INOUT parameters if the stored procedure has them.
- Execute the stored procedure once in order to get the result set.
- Create the field objects of the procedure object according to the result set returned.
You will then be able to use the field objects to design your reports.
Notes:
- Not all stored procedures will return data usable by JReport. The stored procedure needs to return a result set.
- When adding a stored procedure, you may get the following error:
[DBS-B]Could not find stored procedure 'test1;1'.
This is because test;1 becomes quoted when JReport Designer finds the semicolon in it. To remove the quotation marks, you will have to modify your connection information. To do this:
- Right-click your JDBC connection and select Edit Connection from the shortcut menu to bring out the Get JDBC Connection Information dialog.
- Select the Qualified tab, then in the Quote Qualifier box, check the User Defined radio button and remove all the characters from the Extra Characters and Quote Character text boxes.
- Click OK to confirm the settings.
- Due to the unique nature of Oracle stored procedures and EnterpriseDB stored procedures, you are unable to add them directly into a JReport Designer connection. As an alternative, JReport has developed the user data source API which can use a stored procedure in Oracle or EnterpriseDB. For details, see Oracle Stored Procedure UDS and EnterpriseDB Stored Procedure UDS. These user data source classes are included with JReport so you do not need to write any Java code yourself to use them.
Editing parameter values
When you add a stored procedure that contains parameters, you will be asked to input values for its parameters. These values will then be saved inside the stored procedure object in the catalog, and will be used as the default values when executing the stored procedure. You can edit stored procedure parameters at any time. Also, the IN type parameter is available for use with a stored procedure in a report the same as any other JReport parameter.
To edit the parameter values of a stored procedure:
- Right-click the stored procedure and click Parameters on the shortcut menu. The Stored Procedure Parameters dialog appears.
- This dialog lists all the IN and INOUT parameters in the stored procedures. Double-click the Value cell to edit the value of each parameter.
- Double-click the Bind Parameter Name cell to bind the IN and INOUT parameters in the stored procedure to an existing parameter or constant level formula of the same type predefined in the catalog data source in which the JDBC connection is created, or to the special field User Name. You can input the parameter/formula name as @parametername/@formulaname, or the special field User Name as username. By default, the bound parameter will be the one with the same name of the IN and INOUT parameter which is automatically created when adding the stored procedure. If the special field User Name is bound, to specify the user name, go to File > Options, on the menu bar in the General category of the Options dialog, set the User Name option. This username only applies to running the report in JReport Designer, when running the report on the server the real user's login ID will be used.
- Click OK to apply the changes to the parameters.
Updating stored procedures
If you make any changes to stored procedures in the database, you will need to update them in the connection so that the reports built on them will work properly. To do this:
- Select any stored procedure, right-click it, and click Update on the shortcut menu. The Update Procedures dialog appears.
- Select the stored procedures you want to update, and then click the Update button.
- When finished, click the Done button to close the dialog.
Previous Page Next Page