This topic explores the basic resolver functionality provided by the JBCL for ProcedureDataSet components. It extends the concepts explored in Obtaining data through a stored procedure, and adds a resolving phase where you save your changes back to the data source to the "Tutorial: accessing data through a stored procedure" topic.
The "Tutorial: accessing data through a stored procedure" explored the providing phase where data is obtained from a data source with a stored procedure. The tutorial instantiates a table and insert, update, and delete procedures on the server. Then, it instantiates a ProcedureDataSet component and associated components, and displays the data returned from the Local InterBase Server procedure in a grid. The following tutorial expands the providing phase by adding basic resolving capability. With a ProcedureDataSet component, this can be accomplished in two ways. The following sections discuss each option in more detail.
The following tutorial shows how to save changes to your database using JBuilder's UI Designer, a NavigatorControl, and a ProcedureDataSet component.
The finished example for this tutorial may be available as a completed project in the samples\borland\samples\tutorial\dataset\StorProc directory of your JBuilder installation under the file name StorProc.jpr. Other sample applications referencing stored procedures on a variety of servers are available in the samples\borland\samples\tutorial\dataset\StoredProcedure directory.
To complete the application and save changes back to the COUNTRY table,
At this point in the tutorial, you can run the application and view and navigate data. In order to successfully insert, delete, or update records, however, you need to provide more information to the QueryResolver, as follows. The QueryResolver is invoked by default unless a ProcedureResolver is defined (see Tutorial: saving changes with a ProcedureResolver). Then proceed with the following steps:
The application compiles and displays in a separate window. Data is displayed in a grid with the navigator and a status bar that reports the current row position and row count. You can now insert, update, or delete records and save the changes back to your database.
When you run the application, notice the following behavior:
In the above example, you could add a ButtonControl coded to handle saving changes in place of the NavigatorControl. With the button control selected in the Component tree, select the Event tab of the Inspector, select the actionPerformed() method, double-click its value field, and add the following code in the Source window:
try { database1.saveChanges(procedureDataSet1); System.out.println("Save changes succeeded"); } catch (Exception ex) { // displays the exception on the StatusBar if the application includes one, // or displays an error dialog if there isn't DataSetException.handleException(ex); }If you've used different names for the instances of the objects, for example, database1, replace them accordingly.
The ProcedureResolver component extends SQLResolver. The ProcedureResolver requires special coding of stored procedures for the database on which the data should be resolved. The following procedures that are properties of ProcedureResolver should be designed.
The stored procedure should be designed to insert a record in the appropriate table(s) given the data of that row. The ParameterRow may be used for output summaries or for optional input parameters.
The stored procedure should be designed to update a record in the appropriate table(s) given the original data and the modified data. Since the original row and the modified row have the same column names, the named parameter syntax has been expanded with a way to indicate the designated data row. The named parameter ":ORIGINAL.CUST_ID" thus indicates the CUST_ID of the original data row, where ":CURRENT.CUST_ID" indicates the CUST_ID of the modified data row. Similarly, a ":parameter.CUST_ID" parameter would indicate the CUST_ID field in a ParameterRow.
The stored procedure should be designed to delete a record in the appropriate table(s) given the original data of that row.
A example of code that uses this method of resolving data to a database follows in Tutorial: Saving changes with a ProcedureResolver. In the case of InterBase, also see Example: Using InterBase stored procedures with return parameters.
The following tutorial shows how to save changes to your database using JBuilder's UI Designer, a ProcedureDataSet component, and a ProcedureResolver. Some sample applications referencing stored procedures on a variety of servers are available in the samples\borland\samples\tutorial\dataset\StoredProcedure directory.
To complete the application and save changes back to the COUNTRY table with custom defined insert, update, and delete procedures,
In addition to moving around the grid, a navigator provides a Save Changes button. At this point, this button will not do anything. Once we provide a custom resolver via a ProcedureResolver, the Save Changes button will call the insert, update, and delete procedures. You could instead use a ButtonControl as indicated in the Tutorial: saving changes with a NavigatorControl (at the end).
At this point in the tutorial, you can run the application and have the ability to view and navigate data. In order to successfully insert, delete, or update records, however, you need to provide the following information on how to handle these processes.
execute procedure DELETE_COUNTRY :OLD_COUNTRY
execute procedure DELETE_COUNTRY :COUNTRY
See the text of the procedure in Creating tables and procedures for the tutorial manually or through the SQL Explorer.
execute procedure INSERT_COUNTRY :COUNTRY, :CURRENCY
execute procedure UPDATE_COUNTRY :ORIGINAL.COUNTRY, :CURRENT.COUNTRY, :CURRENT.CURRENCY
Click Test Procedure. When the area beneath the button indicates Success, click OK.
When you run the application, you can browse, edit, insert, and delete data in the grid. Save any change you make with the Save Changes button on the navigator. Note that in this particular example, you cannot delete an existing value in the COUNTRY column because referential integrity has been established. To test the DELETE procedure, add a new value to the COUNTRY column and then delete it.
An InterBase stored procedure that returns values is called differently by different drivers. The list below shows the syntax for different drivers for following function :
CREATE PROCEDURE fct (x SMALLINT) RETURNS (y SMALLINT) AS BEGIN y=2*x; END
Drivers:
execute procedure fct ?
If the procedure is called through a straight JDBC driver, the output is captured in a result set with one row. JBuilder allows the following syntax to handle output values:
execute procedure fct ? returning_values ?
JBuilder will then capture the result set and set the value into the parameter supplied for the second parameter marker.
{call fct(?,?)}
where the parameter markers should be placed at the end of the input parameters.