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.

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

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

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