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.

To Check Or Not To Check (Error Codes)

I have this fear that someday, somewhere, I'll find myself working on a life-critical computer system. That's a system where someone gets hurt if your code is wrong: a car crashes, a building collapses, a patient dies.

So far I've been lucky. When an airliner almost ran out of runway because the flaps were set incorrectly due to an error in the "load balancing" program, it wasn't my fault. I wasn't even working on the same system. But I was there when the call came, I saw the look of terror on the programmer's face.

Now I work on systems that do not kill when errors occur. They merely annoy, frustrate or infuriate, as when the data is displayed in the wrong order, or doesn't appear at all, or contains invalid information that leads to a bad business decision. The users might want to kill someone, but they rarely follow through.

Nevertheless, I worry a lot about errors: how to avoid making them, how to remove them, and how to detect and handle the errors that can still happen in spite of my best efforts.

A Recipe For Disaster

Avoiding errors is a matter of good programming practice, the proper use of the tools at hand. Thorough testing will remove most of the errors that do get inserted in your code. But some errors are guaranteed to remain, and that is the subject of this article: the detection, diagnosis and handling of runtime errors in PowerBuilder programs.

Runtime errors can be broken down into three classes:

• Most errors are caused by incorrect user input. Because they are expected, your program should be designed to handle them gracefully and thoroughly, and they're beyond the scope of this article.

• Some unexpected errors are handled by the PowerBuilder runtime environment, such as undefined objects. Since there's not much your program can do about these errors, they too are being ignored here.

• The third class is comprised of unexpected errors that are diagnosed by a return code that can be either ignored or handled by your program code. These are the ones that worry me.

The PowerScript language is less than ideal when it comes to error handling. Most functions can diagnose errors, but with very few exceptions the default action is "ignore error and proceed".

There are no debugging switches or compiler directives to change this behavior to "trap all errors and halt". With the exception of the global SystemError event, there is little language support for automatic error detection or handling, let alone any kind of "on error then" construction that applies to a particular event or object.

This default action encourages to unsafe programming practices, a recipe for disaster:

• "Be selective: check for likely errors, let the others slide by." Never mind that it's impossible to predict which ones are "likely" and which ones will "never happen". This is really a variation of "Wait until something bad happens, then add code to handle it," usually just before your annual performance review.

• "Avoid overprogramming: concise code is always best." If that were true, of course, we'd all be writing in APL.

• "Avoid overkill: error codes are just debugging aids." And once debugging is done, all the errors are gone, right?

• "Worry about performance: extra logic slows everything down." And of course, if it doesn't have to work properly, the code can be written to run like a rocket.

• "Be thorough: get rid of error checking when testing is over." After all, maintenance is for other people.

• "Have faith in the beta testers: they'll catch anything you miss." And if they don't, there's always "User Beta."

• "Above all, have faith in yourself: your code is perfect."

Well, my code sure isn't perfect. Murphy's Law, "Whatever can go wrong will go wrong," always seems to find the error I forgot, the one I didn't think could happen. To deal with Murphy, I build firewalls into my code. This recent CompuServe message expressed quite well how this should be done in PowerBuilder:

"In order to build industrial strength applications with PB, one must check the return code on EVERY PB function that can return an error code. That means on all of them. This means the majority of PB functions (100s). This means that your lines of code will double up:

   return_code = PB_Function()
   if return_code = -1 then ErrorFunction()

I would be very interested to hear from anyone who uses this approach.

Salah"

Here's my answer, Salah: I agree with you one hundred percent! The nature of PowerScript requires extra logic to handle errors.

A Recipe For Success

After every operation that can detect errors, check to see if in fact an error did occur. Here's the pseudocode:

   As execution proceeds, gather helpful diagnostic information.
   Perform the operation, saving the return code if applicable.
   If an error occurred then
      Display, print and/or log all available diagnostic information.
      Take appropriate action (e.g., terminate the application.)
   end if

The first step, "gathering helpful diagnostic information", makes debugging a breeze. For example, different scripts could initialize a "Where Am I?" string to include in error messages:

   ls_where_am_I = parent.ClassName() + '.' + this.ClassName()

A lot of diagnostic information is available in a DBError script; the following logic saves it in global variables for later display:

   gb_DBError_happened = true
   gl_DBError_count  += 1
   gdt_DBError_when   = DateTime ( today(), now() )
   gl_DBError_code    = this.DBErrorCode()
   gs_DBError_message = this.DBErrorMessage()
   gs_SQL_preview     = this.dwGetSQLPreview()
   gs_table_select    = this.dwDescribe ( 'datawindow.table.select' )
   if gb_suppress_message then
      this.SetActionCode ( 1 )    // suppress PowerBuilder message
      return
   end if

When checking for errors, it's important to test the right thing. For example, DataWindow functions provide a return code, while embedded SQL statements put the error code in the transaction object:

   li_RC = this.update()
   if li_RC <> 1 then error...

   connect using SQLCA;
   if SQLCA.SQLCode <> 0 then error...

When an error is detected, the program should gather whatever other diagnostic data is available and display and/or log everything. In the following, a global function handles all the duties of displaying diagnostic data, but doesn't take any remedial action; that's up to the caller (halt close, return, etc.):

   connect using SQLCA;
   if SQLCA.SQLCode <> 0 then
      f_data_error ( 0, SQLCA, 'Application open', 'Connect failed' )
      halt close
   end if

   li_RC = this.update()
   if li_RC <> 1 then
     f_data_error ( li_RC, SQLCA, ls_where_am_I, &
        'Update failed' )
     rollback using SQLCA;
     if SQLCA.SQLCode <> 0 then
        f_data_error ( SQLCA.SQLCode, SQLCA, ls_where_am_I, &
           'Rollback failed')
     end if
     return -1
   end if

   ll_row_count = this.ImportFile ( ls_file_spec )
   if ll_row_count < 1 then
      f_data_error ( ll_row_count, SQLCA, ls_where_am_I, &
         'ImportFile failed' )
      return -1
   end if

Error Messages Are Your Friends

F_data_error simply displays the diagnostic information gathered earlier, plus four items known at the point of call:

1. Numeric error code, or just 0 if it's not applicable.

2. Database transaction, or just SQLCA if none is involved.

3. The current "Where Am I?" string, or just "".

4. An error message, perhaps including local variables, etc.

The code for this function is pretty straightforward, and too long to include here. You could write several versions to handle different situations (with or without transaction objects, etc.) but it really isn't necessary. In fact, an argument can be made for displaying all information all the time in case the cause isn't obvious.

Here's the pseudocode for f_data_error:

   Display a simple Error window (I like 'Splat!' as a title.)

   Show detailed diagnostic information if the user asks for it.

   Provide print, log to file, and copy to clipboard functions.

   Let the user decide between 'Halt now' and 'Proceed'.

To every rule, of course, there are exceptions. Two come to mind immediately: First, a function like f_data_error should not 'diagnose' any errors that occur within itself. That doesn't mean it should fail catastrophically, just that it should step gracefully over any of its own errors.

Second, application shutdown logic should Rollback and Disconnect without displaying any error messages. At any point in an application it should be safe to shut it down immediately; this means all pending updates should be rolled back because the default for Disconnect is Commit. No further error messages should be produced because, presumably, they already have been, and it's too late now to do anything except prevent database corruption:.

   rollback using SQLCA;    // Disconnect issues an implicit Commit,
                            // and there might be a partially
                            // complete update in progress.

   disconnect using SQLCA;

   // Don't bother checking for errors, there ain't nothing that can
   // be done, and there's a good chance 'error handling' will cause
   // a loop!  After all, an error handler might do a halt close.

Most of the time, however, errors should be handled explicitly by extra code in your program. Error messages are your friends, not your enemies... when you see one, it means you're well on your way to solving another problem and making your program that much better.