Saving changes back to your data source with a stored procedure

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.


Tutorial: Saving changes with a NavigatorControl

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,

  1. Select File|Close All. Select File|Open/Create. Open the project file you created for "Tutorial: accessing data through a stored procedure". We will add resolving capability to the existing project.

  2. Select Frame1.java in the Navigation pane. Select the Design tab to activate the UI Designer.

  3. Place a NavigatorControl component from the JBCL tab of the Component Palette on to the UI Designer below the grid. The NavigatorControl has buttons that allow you to move around the grid, and negate or save changes to your data. You may need to resize the grid to make room for the navigator, or enlarge the panel and move components around to make room for it.

  4. Set the dataSet property of the NavigatorControl to procedureDataSet1 by selecting the NavigatorControl, selecting the dataSet property in the Inspector, then selecting procedureDataSet1 from the list. This connects the NavigatorControl to the data set. You can set the buttonType property to TextOnly if you are not familiar with the navigator symbols.

  5. Place a StatusBar control from the JBCL tab of the Component Palette on the UI Designer below the NavigatorControl. This control displays the current record and any other pertinent information related to the record. Set the dataSet property of the StatusBar to procedureDataSet1. This connects the StatusBar to the data set.

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:

  1. Set the rowID property of the key column to True. To do this, click the + sign to the left of procedureDataSet1 in the Component Tree to expose the columns of the data set. Select the key column named COUNTRY. In the Inspector, select the rowID property and select True from the list.

  2. Set the tableName property of procedureDataSet1 to COUNTRY. To do this, click procedureDataSet1 in the Component Tree. In the Inspector, select the tableName property and enter COUNTRY.

  3. Verify that the resolvable property of the procedureDataSet1 is set to to True.

  4. Select Run|Run to run the application.

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.

Coding stored procedures to handle data resolution

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.

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.


Tutorial: Saving changes with a ProcedureResolver

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,

  1. Select File|Close All from the menu. Select File|Open / Create. Open the project file you created for "Tutorial: accessing data through a stored procedure". Resolving capability will be added to the existing project.

  2. Place a NavigatorControl component from the JBCL tab of the Component Palette on to the UI Designer below the grid. The NavigatorControl has buttons that allow you to move around the grid, and negate or save changes to your data. You may need to resize the grid to make room for the navigator, or enlarge the panel and move components around to make room for it.

  3. Set the dataSet property of the NavigatorControl to procedureDataSet1 by selecting the NavigatorControl, selecting the dataSet property in the Inspector, then selecting procedureDataSet1 from the list. This will connect the NavigatorControl to the data set. You can set the buttonType property to TextOnly if you are not familiar with the navigator symbols.

    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).

  4. Place a StatusBar control from the JBCL tab of the Component Palette on the UI Designer below the NavigatorControl. This control will display the current record and any other pertinent information related to the record. Set the dataSet property of the StatusBar to procedureDataSet1. This will bind the StatusBar to the data set.

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.

  1. Place a ProcedureResolver component from the Data Express tab of the Component Palette on the Component tree. Set the database property of the ProcedureResolver to the instantiated database, database1.

  2. Set the deleteProcedure property of the ProcedureResolver to DELETE_COUNTRY. To do this, double-click in the deleteProcedure property to bring up the deleteProcedure dialog.

    1. Set the Database property to database1.

    2. Click Browse Procedures, then double-click the procedure named DELETE_COUNTRY. The following statement is written in the Stored Procedure Escape or SQL Statement field:
      execute procedure DELETE_COUNTRY :OLD_COUNTRY
      

    3. Edit this statement to:
      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.

    4. Click Test Procedure. When the area beneath the button indicates Success, click OK.

  3. Set the insertProcedure property to INSERT_COUNTRY. To do this, double-click in the insertProcedure property of the ProcedureResolver.

    1. Set the Database field to database1.

    2. Click Browse Procedures, then double-click the procedure named INSERT_COUNTRY.

    3. Edit the generated code to read:
      execute procedure INSERT_COUNTRY :COUNTRY, :CURRENCY
      
    4. Click Test Procedure. When the area beneath the button indicates Success, click OK.

  4. Set the updateProcedure property to UPDATE_COUNTRY. To do this, double-click in the updateProcedure property of the ProcedureResolver.

    1. Set the Database property to database1.

    2. Click Browse Procedures, then double-click the procedure named UPDATE_COUNTRY.

    3. Edit the generated code to read:
      execute procedure UPDATE_COUNTRY :ORIGINAL.COUNTRY, :CURRENT.COUNTRY, 
        :CURRENT.CURRENCY
      

      Click Test Procedure. When the area beneath the button indicates Success, click OK.

  5. Select procedureDataSet1 in the Component tree. Set the resolver property to procedureResolver1.

  6. Select Run|Run to run the application.

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.


Example: Using InterBase stored procedures with return parameters

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: