Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.


Breck Carter
Last modified: May 17, 1996
mail to: bcarter@bcarter.com



Wait_For_Commit

Referential integrity is a wonderful thing but sometimes it gets in the way. Here's an example:

You have several related tables and you want to let the user add, change and delete rows in all these tables as part of one "business transaction". That means the user can choose to save or cancel all the changes. If "save" is chosen then all the tables are updated as one database transaction with a single commit (or rollback if there is an error).

The new tab folder control in PowerBuilder 5 makes it easy to include several DataWindows on one window and issue several update() calls when the user asks to save all the changes.

Now, let's say two of these tables are called parent and child and there is a foreign key relationship between them. The user inserts related rows in parent and child, and also deletes related rows in both tables, and then clicks on Save.

If your program calls dw_parent.update() before dw_child.update() the first call will fail. That's because dw_parent.update() issues all the SQL commands having to do with the parent table before your program has a chance to call dw_child.update(). It works for the SQL insert but not for the delete because that would create an orphan, a child row with no corresponding parent. Figure 1 shows the error message produced by SQL Anywhere 5.0.

Figure 1: Update Parent Before Child

   SQLSTATE = 23000
   [WATCOM][ODBC Driver]Integrity constraint violation:
       primary key for row in table 'parent' is
       referenced in another table
   No changes made to database.
   DELETE FROM "parent"
    WHERE "parent_key" = '1'
      AND "column1" = '1'
      AND "column1" = '1'


Catch 22

It doesn't help to put the call to dw_child.update() before dw_parent.update(). This time it would be a SQL insert that failed because the new row in the child table has no parent yet. It's a Catch 22 situation as shown in Figure 2.

Figure 2: Update Child Before Parent

   SQLSTATE = 23000
   [WATCOM][ODBC Driver]Integrity constraint violation:
      no primary key value for foreign key 'fk_parent'
      in table 'child'
   No changes made to database.
   INSERT INTO "child"
      ( "parent_key", "child_key", "column1" )
      VALUES ( '4', '1', '1' )
It's tempting to change the foreign key relationship to on delete cascade to solve the problem in Figure 1 but that creates two new problems. First, many database designers avoid cascading deletes because they are quite dangerous: "I thought this parent had no children and now the whole family is gone!"

Second, if the dw_parent.update() call causes cascaded deletes the user must be very careful when deleting rows in dw_child. It's OK to delete a child row when its parent row is not being deleted, but if the call to dw_parent.update() has already caused a cascaded delete then dw_child.update() will fail because the row is already gone.


Wait_For_Commit = On

SQL Anywhere 5.0 offers a better solution in the wait_for_commit option. By default this is set to off to cause referential integrity checking as each SQL insert, update and delete command is executed. If you set it to on then all the checks are deferred until the SQL commit is issued.

Figure 3 shows how to change the wait_for_commit setting for the current connection to allow the separate dw_parent.update() and dw_child.update() calls to work.

Figure 3: Defer Referential Integrity Checking

   string ls_sql
   ls_sql = "set temporary option wait_for_commit = on"
   execute immediate :ls_sql using SQLCA;
   if SQLCA.SQLCode <> 0 then
      MessageBox ( "Error", "Set option failed." )
      return
   end if
There is a Dark Side to wait_for_commit: If you actually do have a real referential integrity error the message produced by the failed commit won't be nearly as informative as the ones shown in Figures 1 and 2. In particular, the error message will not show the row in error.


Breck Carter can be reached by phone at (416) 763-5200 or via email at bcarter@bcarter.com.