Understanding Resolvers in JBuilder

John B. Moore

Micro-Phyla Systems

Overview

The Java JDBC architecture is fundamentally a "Provider/Resolver" model. The process of providing data is handled by the StorageDataSet classes. For basic two tier configurations the built-in resolving capabilities of the StorageDataSet classes will handle resolving (posting) data back to the database. For those cases where the potential for being disconnected is likely or intentional (briefcase model for example) posting (resolving) data back to the server is not guaranteed. The developer needs tools to handle the resolution conflicts and server errors that might be encountered. The DataExpress components provide just such tools in the Resolver classes: QueryResolver and ProcedureResolver.

Basic or Default Resolving

Using the StorageDataSet classes (QueryDataSet for example) does not require any specific action on the developer's part to provide simple resolution back to the JDBC data source. The saveChanges() method will handle all posting of inserts/deletes/updates made to the DataSet. By default these changes are resolved back as a single transaction. If control of transactions is required, the Database.saveChanges() method provides a boolean "doTransactions" that can be set to false. Setting this parameter to false, turns off the default transaction.

Multiple dependent datasets, as defined in the DataSet.MasterLink ,are handled with special logic that insures that the correct order of insert/delete/update occurs. Deletes are handled first with updates, then inserts. For deletes and updates, detail relationships are handled first. For inserts, master DataSets are handled first.

Basic resolving of the data is handled by simple code:

try {
        database1.saveChanges(queryDataSet1);
}
catch (Exception ex) {
        ex.printStackTrace();
}

The basic architecture of the DataExpress includes both default resolving functionality and custom resolving. Figure 01 illustrates how these components relate to an entire Jbuilder/DataExpress database application.

Figure 01. Relationship of Resolvers in the DataExpress Architecture.

Data Integrity and Validation

Before focusing on the specifics of handling more complex errors returned from the server, a few words should be added on proactively avoiding as many data integrity errors right up front. At first glance, it would seem wise to place a lot of this code in the client. Unfortunately this creates an administrative and maintenance nightmare if you have multiple clients from multiple applications (possibly written in different languages or from different platforms). Despite this, there is some very basic validation that can be done at various points in the interaction with the user doing the data entry.

At Data Entry

This is the traditional desktop location for data validation. Depending on this location for data validation creates a fat client with the business and data validation residing on each client. Even in a thin client there is cause for placing a limited amount of validation. Obvious validation fall under the heading of:

• insuring a valid data type (e.g. dates, integers, currency)
• insuring very broad reasonable data ranges (e.g one date larger than another, no negative numbers, etc.)
• Alphanumeric case (e.g. enforcing proper case or all caps)

At Posting to local DataSet

When the user moves off a record or posts the record, the changes only occur on the local dataset This is another opportunity to validate data, and it is important to reemphasize that this posting is local only. Again, decisions about placing data validation, business and integrity rules on the client should be considered. Any of the previously listed validation items would be still appropriate. They are basic validation rules that would be of value regardless of the ultimate business rules that reside on the server. (Note that all of these basic rules would also be implemented on the server.) Another possibility is enforcing a required field. The importance of validation up to this point is the immediacy of the reenforcement to the user.

Custom Resolving

There are times when special handling or intervention must occur before sending data back to the server. This can go beyond the basic validation discussed above. Up to this point, basic validation has relied on events triggered by the user's interaction with the application. In addition, as part of the resolution system found in both the QueryResolver and the ProcedureResolver, every record that is to be resolved is available for inspection before it is sent to the server. The events provided in the ResolverListener interface are: deletingRow(), insertingRow(), and updatingRow(). Most of the previous basic validation can also be handled here as well.

The importance of these events is not so much validation opportunities, as it is a perfect location to customize the entire resolution process. Here, special issues can be handled that are unique to that JDBC data source. These datasources can be proprietary databases that are not handled in the standard default mechanisms. Examples include EJB, application servers, SAP, BAAN, IMS, CICS, etc. In addition, with the ProcedureResolver, custom procedures can be called from a SQL database, each for inserting, updating, and deleting; thereby keeping the logic of resolution on the server and not on the client. Resolvers can be shared between different DataSets, therefore, providing a central store for the resolving logic.

Responding to Server Errors

Having received a validation error from the server that goes beyond the basic validation discussed above, the only way to resolve these errors is either by interaction with the user or just denying or skipping the posting of the record entirely. Any other solution begins to place too many business rules on the client and defeats the basic concept of a thinner client. The complexity of resolving comes with the possibility of errors occurring during the resolving process. There are two basic sources of errors: a violation of server integrity, and resolution conflicts.

Violation of Server Integrity

This is an error directly from the server that some internal validity check has failed. On a SQL type database this can be a very complex validation that was handled by triggers and procedures within the database. The default handling of these types of errors is to place the user on the offending row (if it is not deleted) and display an error with a message dialog. This type of error handling can be too simplistic, as the user lacks enough information to make a decision on how to handle the problem, and therefore DataExpress provides access to the original record, the current record on the server, and the modified record from the current user.

Resolution Errors

Resolution errors become a problem with a client that is not in some way holding a record (via a lock) that the client may edit or change and can be assured access to that record for update. Without this assurance another user could alter or delete this record before the current user can update the record and therefore the update would fail. This means that an error may be returned that the record in question was altered by another user or has been deleted.

Resolving Event Architecture

Whether the problem is a violation of server integrity or a resolution error, the programmer can control the response to these errors via the ResolverListner interface and three events provided: deleteError(), insertError(), and updateError(). Each of these methods provides a number of valuable information about the error via the following parameters:

• DataSet - This is a handle to the original dataset passed to the resolver to be resolved.
• Row - This is the offending row. This row can be modified and then passed back for retry.
• ex - The exception that caused the error. This information can help the user determine what changes need to be made.
• OldRow - In an update this would be the original record. (updateError only)
• UpdRow - The modified row that triggered the error in an update operation. If this is passed to the Dataset.fetchRow() the current record on the server can be retrieved in cases that the error indicates the record has been changed by another user. This allows the user to merge the old, update, and current row and allow posting to continue. (updateError only)
• Response - There are three responses: ABORT, IGNORE or RETRY. ABORT causes all insert, update or delete operations to be rolled back in that transaction.

A basic overview of the interaction is illustrated by the Figure02, once the user initiates the saveChanges() method.

Figure02 Overview of Resolver events

Of particular interest is the interaction that occurs that allows the user to: fix these errors and retry, skip the resolution of that record, or ignore the error, thereby throwing the changes away entirely. The important issue for this paper is to outline techniques for displaying these records to the user in a fashion that will allow the user to make these decisions. The flow of interaction and points of intervention are illustrated in Figure03.

Figure03 Key Events in intervention of Resolving Errors

The remainder of this paper will deal with this customization mechanism and provide examples that illustrate techniques and basic code that will handle these errors and display the problem records.

Investigating Pending Records

The BasicResolve.jpr project is created using a basic GridControl, Tabsetcontrol, and NavigatorControl. This first demo is not in fact going to demonstrate any resolving, but will illustrate how the information can be collected that will be needed later in more complex demonstrations. The idea at this point is to work into the necessary ideas a bit slowly.

Place five tab buttons on the TabsetControl. They will be labeled : "FullDB", "Inserts", "Deletes", "Modified", and "AllPending". Figure04 illustrates the layout expected.

<Figure04 Screen shot showing the UI needed for the Viewing Basic demonstration>

The bulk of the code for this demonstration is found in the SelectionChanged event of the TabsetControl. A switch/case construct is used to respond to the correct tab press. Following is a discussion of the code in each of these case statements.

AllPending - Viewing Pending Records.

Place the following code in this case statement:

   //show all pending.. (except deletes are hidden)
      case 4: {
      try {
        if (qdsCustomers.changesPending()) {
            // use DSD to extract changes from dataset
            qdsToResolve.emptyAllRows();
            DataSetData changes = 
DataSetData.extractDataSetChanges(qdsCustomers);
            if (changes != null) {
               changes.loadDataSet(qdsToResolve);
               //set grid to DSView
               gridControl1.setDataSet(qdsToResolve);
               statusBar.setText("Viewing Pending Count = 
"+ qdsToResolve.getRowCount()+
" Deleted: "+qdsToResolve.getDeletedRowCount());
             } else
               statusBar.setText("Extracting Pending failed");

        } else {
            statusBar.setText("No Pending Records to view");
            gridControl1.setDataSet(dsvFullDB);
            }
      }
      catch (DataSetException ex) {
         System.out.println("Error in case 4");
         ex.printStackTrace();
      }
      break;

The method "changesPending" returns a boolean indicating that there are changes pending in this dataset. A new class, DataSetData, is used that has two static methods that will allow us to extract the changed data from the dataset. The method DataSetData.extractDataSetChanges(..) extracts all inserts, updates, and deletes. I find it unfortunate that the deleted records do not show (not visible) in the dataset with this method, but these records can be viewed using qds.getDeletedRows(..). The reasoning is that you can load these changes into a different QueryDataSet and issue a SaveChanges(), and all records are properly flagged for resolution. This is a handy feature. (This technique will be illustrated on the next demo)

Tabbed Control code for Displaying Inserted, Deleted, or Modified Records

Each of these case statements will first create a DataSetView (DSV) object, which will be used in the appropriated method based on the index of the tab control. The methods getInsertedRows(), getDeletedRows(), or getUpdatedRows() are called. These method will load the rows that have been modified into this DSV. Then it is a simple matter of attaching the gridcontrol to this view. The following code illustrates the basic steps for the insert tab event.

    //intialize a DSV object
    dsv = new DataSetView();
    //get index of tab selected
    int tabVar = tabsetControl1.getSelectedIndex();
    //query tab index and set grid control to view that subset
    switch (tabVar){
      //show all records
      case 0: {
        gridControl1.setDataSet(dsvFullDB);
        statusBar.setText("Viewing all records.");
        break;
      }
      //show inserted records
      case 1: {
      try {
         if (qdsCustomers.getInsertedRowCount() > 0) {
            //load inserted records in to DSView
            qdsCustomers.getInsertedRows(dsv);
            //attach grid to DSView
            gridControl1.setDataSet(dsv);
            statusBar.setText("Viewing Inserted Count = 
"+ qdsCustomers.getUpdatedRowCount());
         } else  {
           statusBar.setText("No Inserted Records to view");
           gridControl1.setDataSet(dsvFullDB);
           }
      }
         catch (DataSetException ex) {
           ex.printStackTrace();
      }
      break;
      }

The statusBar is updated with the appropriate message to remind the user what records are on display.

The question is of course, why bother with showing the user the changed, deleted or inserted records. The answer is that any information that is provided to the user that will minimize errors and allow the user to correct errors before the resolution process begins will minimize the work required by the resolver. The ability to review the status of pending records is an important part of resolving data back to the server.

QueryDataSet Resolver

Our first true resolving demo will use the QueryDataSet (QDS) to illustrate the basic techniques required for resolution. As outlined above, there are two points of entry for dealing with a problem record: before the record is sent to the server, and in response to an error from the server. In the first case our response will be to tell the user the problem with a message dialog, and then place the user on the problem record. The second case will be much more complex, with a special dialog to display the errors for the user to correct.

Displaying Errors to the User

Unlike the default behavior of an insert, delete, or update error, which places the cursor at the offending record with an error message, we want to take this a bit farther. Making it easier for the user to make a decision on what needs correction or whether to skip the record entirely, a dialog will be presented with the current changed record and the unchanged record. Another feature in this demo is to gather all problem records at one time. Figure05 illustrates the basic layout of this error Dialog.

<Figure05 Error Dialog layout for resolving records>

At this point the user has the ability to make changes to the record that will allow the resolution to continue with three choices: Cancel, Skip, or RePost. This dialog can be made even more complex by presenting the user with three versions of the record (the altered record, the original record, and the current record on the server) and the options to retry, merge, or skip the resolution of this record. The code required to retrieve the error record and insert it into the error dialog is illustrated below, using the insertError event as an example.

void qrCustomer_insertError(DataSet dataSet, 
ReadWriteRow row, DataSetException ex, ErrorResponse response)
 throws DataSetException {
     System.out.println("InsertError event");
     if (!insertError_l) {
        //populate the error message box
        errorfrm.InsertLabel.setText("InsertError:  " +
 ex.getMessage());

        //match the columns - without this line there will be an 
        //error thrown that it could not find the column.
        errorfrm.tdsInserts.close();
        errorfrm.tdsInserts.setColumns(((
QueryDataSet)dataSet).cloneColumns());
        //open the tabledataset
        errorfrm.tdsInserts.open();
     }
     //Create a new data row object
     DataRow dataRow = new DataRow(dataSet);
     //copy our readWrite row to the data row
     row.copyTo(dataRow);
     //insert new row
     errorfrm.tdsInserts.insertRow(false);
     // populate Tabledataset one column at a time..
     // use Variant with getColumnCount and getCaption to 
     // insert values
     for (int i=0;i< dataRow.getColumnCount();i++) {
        Variant varValue = new Variant();
        dataRow.getVariant(i,varValue);
        //set column value based on column name
        errorfrm.tdsInserts.setVariant(
dataRow.getColumn(i).getCaption(),varValue);
     }
     //ignore error for now
     response.ignore();
     showError_l = true;
     insertError_l = true;
  }

In addition the error messages are displayed on the title of each grid. In a real application some effort should be made to provide a more user friendly translation to the possible error conditions. No attempt was made to do this for this demo.

Saving Changes

Saving changes can be as simple as a single line: DataSet.SaveChanges(). But for this demonstration we will explore other things that might be done before, during and after making this call. The first step is to insure that the user is not on an unposted record. To do this, a call is made the post() method of the QDS.

qdsCustomer.post();

Since any possible errors will be inserted into one of three different TableDataSets for display, those tables need to be emptied.

errorfrm.tdsDeletes.emptyAllRows();
errorfrm.tdsInserts.emptyAllRows();   
errorfrm.tdsUpdates.emptyAllRows();

And lastly, the save changes is called on the database instead of the QDS. In this particular case it does not make any difference which SaveChanges you use, but when saving multiple datasets the database version will honor any relations and post the changes in the correct order. Because of this, it is a good habit to always use the database version.

theDatabase.saveChanges(qdsCustomer) ;

Resolving Event Sequence

After "saveChanges" is called, a specific sequence of events are triggered. An example of the insert event sequence when saving two inserted records.

• InsertingRow event
• InsertError event
• InsertingRow event
• InsertError event
• SettingResult = 64
• theResult = 64
• Retry
• Retrying Resolve
• InsertingRow event
• InsertingRow event
• errorfrm close
• EndPosting

Events 1 and 3 occur before the data is sent to the server where our code could check the record before the data actually is posted. This code does the validation and can stop the resolving process before ever being submitted to the server.

void qrCustomer_insertingRow(ReadWriteRow row, 
ResolverResponse response) throws DataSetException {
    // Get the contact value from the row to be inserted
    String  contactVar = row.getString("CONTACT_FIRST");

    // Make sure that the contact field is entered
    if (contactVar.equals("")){
      //alert User to error
      GeneralMsg.setMessage(
           "Contact required, Correct record and retry\n" +
           "  Correct Customer No.: "+ row.getInt("CUST_NO"));
      GeneralMsg.show();
      //cancel inserting
      response.skip();

      //locate customer number
      //first create a scoped data row on the customer number
      DataRow dr = new DataRow(qdsCustomer,"CUST_NO");
      //next, load the datarow with the customer number
      dr.setInt("CUST_NO",row.getInt("CUST_NO"));
      //locate problem record
      qdsCustomer.locate(dr,Locate.FIRST);
      statusBar.setText("Value for CONTACT_FIRST is required.");

    }
    System.out.println("InsertingRow event");
  }

Events 2 and 4 are were both errors from the incomplete records are returned from the server. (Code sample already provided earlier under "Displaying Errors to User")

The TableDataSet (TDS) in the error dialog is first emptied on the first triggering of an error event. The columns are set to match the QDS, and the TDS is opened. The logical "insertError_l" is then set to prevent the initialization code from repeating if there is another insert error. The rest of the code deals with moving the record into the TDS.

After event 4, the ErrorDialog is displayed (Figure06) for the user to correct the cause of the error.

<Figure06 ErrorDialog showing insert error>

The user then selects "RePost" and events 5,6 and 7 indicate that the "retry" button was selected and the result property of the buttonDialog was set. The next task is to re-resolve the corrected data. To do this, the data is moved into an empty QDS in which a basic "select" with a maxRows set to zero. This has the effect of initializing the metaData for the QDS and preparing it for the records to be resolved. Using the DataSetData.extractDataSet() static method to pull all data out of the TDS, and then loadDataSet() loads the data into the newly initialized QDS. The method, saveChanges(), is again called (events 9-10) and if there are still errors, the whole sequence could repeat itself.

It is important to note that this is not the only way to handle errors but is meant to demonstrate the possibilities available and the code that would be needed.

ProcedureDataSet Resolver

For the ProcedureDataSet (PDS), the same basic code structure is used. This was intentionally done to illustrate how exchangeable the QDS is with the PDS in application code. The key difference lies in the wiring of the beans and the additional work needed on the back end of the server.

Setting the Stage - Stored Procedures

For those experienced in creating stored procedures, this section can be bypassed or very quickly scanned. For those less familiar with the steps, the following will outline the setup in more detail. In this demonstration, Local Interbase Server (LIBS) will be used. This local SQL database has become the standard database installed with most Borland products. To illustrate creating new stored procedures the file procsql.sql file is included with this demo that can be run in Interbase's Windows ISQL tool as shown in Figure07.

<Figure07 Interbase ISQL Tool>

Be sure and make a backup of the employee.gdb before doing any testing. After running this ISQL file the following new procedures will be installed to your employee.gdb database.

SHOW PROCEDURE     
Procedure Name Dependency, Type    
=========================================================    
DELETE_CUST CUSTOMER, Table    
GET_CUST_NO    
INSERT_CUST CUSTOMER, Table   
LOAD_CUST CUSTOMER, Table    
UPDATE_CUST CUSTOMER, Table

The listing of procedures can be viewed by selecting "MetaData|Show" from the menu as illustrated above. Leave the "Object Name" blank and select "Procedure" from the "View Information On" dropdown field. You can also review the code in a Stored Procedure by selecting the same Show dialog box, but instead of leaving the "Object Name" blank, fill in the procedure you wish to view.

In addition, the trigger SET_CUST_NO must be dropped so as to not cause problems when inserting new customer records in the supplied demos. (Don't forget to do that backup!). Use the SQL statement:

DROP TRIGGER SET_CUST_NO;

at the ISQL (Interactive SQL) tool provided with LIBS.

The Wiring

As mentioned earlier, the key differences between using a PDS and the QDS is mostly in the wiring of the beans and the previously discussed back end stored procedures. A PDS was dropped into the project and the "procedure" property editor was activated as illustrated in Figure08.

<Figure08 Procedure Property Editor>

Once the database has been selected (Don't forget to add and wire the database the same way it was added for any QDS examples) the "Browse Procedures" button is activated (Figure09).

<Figure09 Browse Procedures>

The nice feature added with this tool, is that it attempts to intelligently determine how to call your procedure. If it sees needed parameters, it will add them to the call. Be sure and add a ParameterRow if you have any required parameters (You can also use a QDS if this procedure is to respond to, or be fired after a QDS that provides the needed information.) Be forwarned that the needed parameters may be spelled differently in the stored procedure, and take caution to resolve any differences. This can be the source of frustrating and time consuming errors.

Another issue that can come up is that the JDBC driver may not, under some circumstances, provide sufficient metadata to determine what the keyed fields are in that view or table. If you get an error that it could not find the proper row to update, you will have to set the proper column "rowID" to true.

The next step is to drop in a PDS resolver. The resolver has three important properties you need to fill in: the update, insert, and delete procedures. All three work very much like the procedure property on the PDS. A property editor pops up and you select the database, and browse the procedures. Again Jbuilder attempts to create a call to that procedure that is appropriate to that database. For the Insert and Update procedures you can often use the PDS for the parameter row. An insert procedure call might look like:

execute procedure INSERT_CUST :ACUSTNO, :CUST, :FIRST, :LAST, :PHONE, :ADD1,

:ADD2, :ACITY, :STATE, :CTRY, :ZIP, :HOLD

Again, the field variable names used in your procedure may not be the same as the ones in your PDS, so if you get the error "column <acolumn> is not...", check your spelling first.

Wrap Up

Once the wiring is finished, the rest of the code is virtually identical to the QDS version. You will find that the SQLException is now in java.sql.* and not in the PDS classes, so you need to add another import statement:

import java.sql.*;

Also you must deal with the correct functioning of your stored procedures. The big advantage and reward for your extra work is that your system is probably much more portable because the SQL is now on the database and not on the client. I say "probably" because unfortunately there does not seem to be a truly standard syntax for calling procedures for all drivers. As they say "..the nice thing about standards is there are so many to choose from..."

More Notes on CustomResolvers

Designing a custom resolver can range from writing an entire resolve class from scratch by extending the Resolver abstract class, (Similar to the sample found under \samples\com\borland\samples\dx\datasetdata\ ) or as simple as extending what was demonstrated in the previous examples. These examples are all degrees on customizing the resolving process beyond what is available by default within the QueryDataSet and the ProcedureDataSet.

Rolling Your Own

If you are inclined to completely handle the entire resolving process because the interaction with the datasource is either outside the normal Query or Procedure component area or it's an RMI project, there are a number of steps that should be followed. Note that CORBA often handles resolving a bit differently via the IIOP communication with the remote object that is then responsible for the resolving. It should also be mentioned that a custom Provider would generally have to be designed as well.

The first step is to extend the abstract Resolver class (com.borland.dx.dataset.Resolver) and implement the needed classes. At a minimum, the method "resolveData(DataSet) should be overridden with our custom behavior. (See code below copied from the sample project \samples\com\borland\samples\dx\datasetdata\)

 public void resolveData(DataSet dataSet) throws DataSetException {
    try {
      String serverName = "//" + hostName + "/DataServerApp";
      EmployeeApi server  = (EmployeeApi)Naming.lookup(serverName);
      // This blocks the dataset for invalid operations while resolving.
      ProviderHelp.startResolution(dataSet.getStorageDataSet(), true);   
      DataSetData changes = DataSetData.extractDataSetChanges(dataSet);
      DataSetData errors  = server.resolveEmployeeChanges(changes);
      if (errors != null)
        handleErrors(dataSet,errors);
      dataSet.resetPendingStatus(true);
    }
    catch (DataSetException ex) {
      // reset status bits to: nothing is resolved !
      dataSet.resetPendingStatus(false); 
      throw ex;
    }
    catch (NotBoundException ex) {
      // reset status bits to: nothing is resolved !
      dataSet.resetPendingStatus(false); 
      throw new DataSetException(res.getString("RS_NoServer"));
    }
    catch (Exception ex) {
      // reset status bits to: nothing is resolved !
      dataSet.resetPendingStatus(false); 
      String error = ex.getMessage();
      if (error == null)
        error = res.getString("RS_NoConnect")+hostName;
      throw new DataSetException(error);
    }
    finally {
      // This unblocks the dataSet after resolving.
      ProviderHelp.endResolution(dataSet.getStorageDataSet());  
    }
  }

Two other methods can be customized as well. The method "checkIfBusy(..)" allows the developer to allow asynchronous resolving. The default behavior is no asynchronous resolving. The last method available in this class is "close(..)". This method should be overridden if your implementation caches information during the resolving process. The "close" method would then release these resources for the garbage collector, thereby maintaining a more efficient memory footprint.

Summary Notes

The Java JDBC architecture is fundamentally a "Provider/Resolver" model and DataExpress takes much of the pain out of implementing this model. Intelligent assistance in writing the procedure calls and acquiring metadata, helps to insure that all the bases are covered. Sometimes not all the metadata information needed is available, and those are the unavoidable headaches the developer must work around..

The design of DataExpress with the StorageDataSet (SDS) classes provides a uniform API in which switching from a GUI front end using QDS to one using PDS requires little more than rewiring the DataSet Beans and perform a "search and replace" for any changes in field or object names. Resolving becomes a critical function for databases that have special needs or are subject to disconnection, which increases the likelihood that resolving problems in posting the data back to the server will occur. Lastly, in JBuilder3, the DataSetData classes were added to provide a more convenient way of moving data between various SDS objects. This alone, is worth much of the price of admission.