Saving changes back to your data source with a stored procedure

Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.

You can use different Resolver implementations to save changes back to your data source. QueryDataSets use a QueryResolver to save changes by default. The default resolver can be overridden by setting the StorageDataSet.resolver property.

This topic explores the basic resolver functionality provided by the DataExpress package for ProcedureDataSet components. It extends the concepts explored in "Obtaining data through a stored procedure" by exploring the different methods for saving data changes back to a data source.

The topic "Tutorial: retrieving data using stored procedures" explores how to run a stored procedure in order to retrieve data. The tutorial creates a table on the server, and then creates insert, update, and delete procedures that will provide information on how to resolve changes back to the source. Using JBuilder's IDE, the tutorial instantiates a ProcedureDataSet component and associated UI components, and displays the data returned from the JDataStore in a table. The Save button on the JdbNavToolBar can be used to save data changes back to the employee file when certain properties have been set.

In this topic, the retrieving tutorial is expanded 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 using a QueryResolver

If the resolver property of a ProcedureDataSet is not set, the default resolver is a QueryResolver that will generate INSERT, UPDATE, and DELETE queries to save the changes. The QueryResolver requires tableName and rowID properties to be set. This tutorial shows how.

The finished example for this tutorial may be available as a completed project in the /samples/DataExpress/SimpleStoredProcedure/ directory of your JBuilder installation. Other sample applications referencing stored procedures on a variety of servers are available in the /samples/DataExpress/ServerSpecificProcedures/ directory.

To complete the application and save changes back to the COUNTRY table,

  1. Select File|Close if you have any open projects. Select File|Open. Open the project file you created for "Tutorial: retrieving data using stored procedures". We will add resolving capability to the existing project.

    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:

  2. Select Frame1.java in the project pane. Select the Design tab to activate the UI designer.

  3. Select procedureDataSet1 in the component tree.

  4. Set the tableName property of procedureDataSet1 to "COUNTRY" in the Inspector.

  5. Verify that the resolvable property of the procedureDataSet1 is set to to True.
  6. Click the expand icon to the left of procedureDataSet1 in the project pane to expose the columns of the data set.

  7. Select the key column, which is named COUNTRY.

  8. Set the rowID property of the COUNTRY column to True.

  9. Select Run|Run Project to run the application.

The application compiles and displays in a separate window. Data is displayed in a table with the toolbar and a status label 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 JButton coded to handle saving changes in place of the JdbNavToolBar. For more detailed information about how to do this, see "Tutorial: adding a button to save changes from a QueryDataSet". 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 JdbStatusLabel if
// the application includes one,
// or displays an error dialog if there isn't 
    DBExceptionHandler.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

To use a ProcedureResolver, you need to implement three stored procedures on the database, and specify them as properties of the ProcedureResolver. The three procedures are:

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/DataExpress/ServerSpecificProcedures directory.

To complete the application and save changes back to the COUNTRY table with custom defined insert, update, and delete procedures, first, open the project file you created for "Tutorial: retrieving data using stored procedures". Resolving capability will be added to the existing project.

The current project contains a JdbNavToolBar component. In addition to enabling you to move around the table, a toolbar provides a Save Changes button. At this point, this button will use a QueryResolver. Once we provide a custom resolver via a ProcedureResolver, the Save Changes button will call the insert, update, and delete procedures specified there instead

At this point in the application, 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. With the project open,

  1. Select the Frame file in the content pane, then select the Design tab to activate the UI designer.
  2. Select a ProcedureResolver component from the DataExpress tab of the component palette on the content pane. Click in the content pane to add the component to the application.

  3. Set the database property of the ProcedureResolver to the instantiated database, database1 in the Inspector.

  4. Set the deleteProcedure property to DELETE_COUNTRY as follows:

    1. Select procedureResolver1 in the component tree and click its deleteProcedure property in the Inspector.
    2. Double-click in the deleteProcedure property value field to bring up the DeleteProcedure dialog.

    3. Set the Database property to database1.

    4. 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
      
    5. Edit this statement to be:
      execute procedure DELETE_COUNTRY :COUNTRY
      

      See the text of the procedure in ""Creating tables and procedures for the tutorial manually" or by using the JDBC Explorer (Tools|JDBC Explorer).

      Note: Don't click Test Procedure because this procedure does not return a result.

  5. Set the insertProcedure property to INSERT_COUNTRY as follows:
    1. Select, then double-click the insertProcedure property of the ProcedureResolver to open the insertProcedure dialog.
    2. Set the Database field to database1.

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

    4. Edit the generated code to read:

      execute procedure INSERT_COUNTRY :COUNTRY, :CURRENCY
      

      Note: Don't click Test Procedure because this procedure does not return a result.

  6. Set the updateProcedure property to UPDATE_COUNTRY as follows:

    1. Select, then double-click the updateProcedure property of the ProcedureResolver to open the updateProcedure dialog.
    2. Set the Database property to database1.

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

    4. Edit the generated code to read:

      execute procedure UPDATE_COUNTRY :ORIGINAL.COUNTRY, :CURRENT.COUNTRY, 
        :CURRENT.CURRENCY
      

      Note: Don't click Test Procedure because this procedure does not return a result.

  7. Select procedureDataSet1 in the project pane. Set the resolver property to procedureResolver1.
  8. Select procedureDataSet1. Set its metaDataUpdate property to None.

  9. Select Run|Run Project to run the application.

When you run the application, you can browse, edit, insert, and delete data in the table. Save any change you make with the Save Changes button on the toolbar. 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 the following function :

CREATE PROCEDURE fct (x SMALLINT)
RETURNS (y SMALLINT)
AS
BEGIN
  y=2*x;
END

Calling fct procedure from different drivers: