A few days ago I decided to refactor a 6-year old data integration program I wrote in VFP7 (or was it VFP6?) that moves data from a legacy tax system to an operational data warehouse for reporting (SQL Server). The program was self-contained (could be run as a compiled exe from the command line or through the IDE). To make a long story short, I decided to up the code to VFP9 and add some TRY/CATCH statements and additional parameter checking.

The code in this program makes heavy use of TEXTMERGE, utilizing the “” and “\” commands.

I wrote a few test cases to exercise my new error handling. What I discovered was that some of my logging stopped working after I added a few TRY…CATCH blocks. Hugh? Here’s a sample:

SET TEXTMERGE ON
SET TEXTMERGE TO test ADDITIVE NOSHOW 
hello
TRY
    USE xxx
CATCH TO oErr
    Error: <<oErr.Message>>
ENDTRY
world
SET TEXTMERGE TO
MODIFY FILE test.txt

If you run the above, you only see “hello” printed in the file test.txt:

hello

I went back to my original code without any TRY/CATCH blocks and to my surprise, got the same result. Only ‘hello’ printed. But WAIT… I’m running in VFP9 SP1.

When I run the original code in VFP7:

ON ERROR Error: <<MESSAGE()>>
SET TEXTMERGE ON
SET TEXTMERGE TO test ADDITIVE NOSHOW 
hello
USE xxx
world
SET TEXTMERGE TO
ON ERROR
MODIFY FILE test.txt

I get the expected output:

hello
Error: 'xxx.dbf' does not exist.
world

It appears as if program errors are killing my TEXTMERGE in VFP9 SP1. I’ll have to try this out on my laptop with VFP9 SP2 to see if it magically got fixed.

To get around the problem in VFP9, you can put the line “SET TEXTMERGE TO test ADDITIVE NOSHOW” in the CATCH block above just before the “” command. If an error occurs, you remind TEXTMERGE that it needs to keep working. Perhaps it figures it needs some time off.

Recommendations

Unless someone has some insight or additional thoughts, I would suggest searching through your source code for cases where you are using TEXTMERGE with TRY/CATCH/FINALLY blocks. If so, insert the SET TEXTMERGE TO … line in your CATCH block. Alternately, you could use STRTOFILE() to append logging information to a file. This is the final approach I took in this program.

STRTOFILE("hello"+CHR(13),"test.txt",1)
TRY
    USE xxx.dbf
CATCH TO oErr
    STRTOFILE("Error: " + oErr.Message+CHR(13),"test.txt",1)
ENDTRY
STRTOFILE("world"+CHR(13),"test.txt",1)
MODIFY FILE test.txt

I hope this helps someone, or maybe someone who knows more about this particular issue can help me!

Tags: Debugging, VFP

Update: FYI, this is a known “issue” and works this way by default. See comments for more details.

Maybe I’m missing something (and I hope I am). The following condition exists in VFP7 through to VFP9SP1 (and maybe earlier than 7). I don’t remember ever reading anything about it either.

This one is hard to explain (and believe), so you may need to run the example code to see what I mean. And right off the bat: This issue has nothing to do with the RECCOUNT/_TALLY results we all know about regarding deleted records (RECCOUNT always returns the number of rows in the table regardless of the SET DELETED setting). No, this issue has something to do with an index and the current setting of DELETED. When an index is set on a column involved in a WHERE clause of an SQL statement, RECCOUNT will return the total number of records from the ‘FROM’ table instead of the results from the cursor when SET DELETED is OFF! It seems as though the resulting cursor has a bunch of deleted records (the scrollbar in the browse window only goes half-way up (if you know what I mean)), but they can’t be accessed regardless of the SET DELETED setting.

The problem is with code like this:

SET DELETED OFF
SELECT sometable
SET INDEX ON somefield   && can exist in a CDX too
SELECT * FROM sometable WHERE somefield = somevalue INTO CURSOR crTemp
? RECCOUNT('crTemp')   && wrong! RECCOUNT returns the wrong reccount!
? _TALLY               && correct results

Here’s a complete example, using a table called ‘name_list’, which has a column for name c(30) and some deleted records. In the first example, the table name_list does NOT have an index on ‘name’. RECCOUNT and _TALLY return expected results.

USE name_list IN 0 EXCL 
 
SET DELETED OFF
 
SELECT name FROM name_list WHERE name>='H' INTO CURSOR crName
? "WITH DELE OFF:  "
?? _TALLY              && 81,019
?? RECCOUNT('crName')  && 81,019
 
SET DELETED ON
 
SELECT name FROM name_list WHERE name>='H' INTO CURSOR crName
? "WITH DELE ON: "
?? _TALLY              && 80,951
?? RECCOUNT('crName')  && 80,951

Now, add an index to the name column and be amazed:

USE name_list IN 0 EXCL
INDEX ON name TAG name
 
SET DELETED OFF
 
SELECT name FROM name_list WHERE name>='H' INTO CURSOR crName
? "WITH DELE OFF:  "
?? _TALLY              && 81,019
?? RECCOUNT('crName')  && 126,607 -- the actual number of records in name_list!?!
 
SET DELETED ON
 
SELECT name FROM name_list WHERE name>='H' INTO CURSOR crName
? "WITH DELE ON: "
?? _TALLY              && 80,951
?? RECCOUNT('crName')  && 80,951

In the above example, it doesn’t matter if the index is set to name (SET ORDER TO name) or not! Just the fact that the index exists throws off RECCOUNT(). If you do a count on crName with DELETED OFF, you get the expected results:

USE name_list IN 0 EXCL
INDEX ON name TAG name
 
SET DELETED OFF
 
SELECT name FROM name_list WHERE name>='H' INTO CURSOR crName
? "WITH DELE OFF:  "
?? _TALLY              && 81,019
?? RECCOUNT('crName')  && 126,607 -- the actual number of records in name_list!?!
COUNT TO ln
?? ln                  && 81,019

The moral is not to use RECCOUNT() to get the total number of records from
an SQL statement. Always use _TALLY. But unfortunately, this undocumented behavior has certainly crept into someone’s code somewhere. Consider a situation where you want to test to see if the SQL returns any records. You might do something like this:

SELECT * FROM sometable WHERE somefield = somevalue INTO CURSOR crTemp
IF RECCOUNT() > 0
  ...
ENDIF

This code is fatal if in fact there happens to be an index on somefield and SET DELETED is OFF! If anyone has some insight, I’d love to hear it…

Tags: Debugging, VFP

There is no question that having a custom error handler working behind the scenes in your Visual FoxPro Application is not only a good thing, but essential for good, stable production systems. Oftentimes, these custom error handlers are nothing more than a generic message alerting the user that a problem has occurred. Good designs will then dump the error details to a file or table for later analysis. While this method is a good first step, so much more can be done. Consider the following:

1. Instead of a generic message, write code to work with certain predictable errors. In my code, I like to trap errors such as “E1, File Does not exist”, “E6, Too many files open”, “E1001, Feature is not available”, “E2012 Cannot call SetFocus error”, and “E2028 API call caused an exception”. In these cases, special messages alert the user to what is going on, while code behind the scenes tries to handle the problem. For some errors, a simple RETRY or RETURN works well (like for error code 1001). For others, more work (error code 1) needs to be done.

2. Dumping the error contents to a file or table is a nice idea, but what if the user never tells you they received an error? Without you knowing, you can’t fix it. In a perfect environment, you could email a copy of the error details to the development staff automatically through FoxPro. Messages can also be posted (via XML and web services) to an Internet support site. Simply including tech support’s phone and fax number on the error message may give the operator a little more incentive as well. In the end, no method is foolproof. The important thing to remember is that no one likes an error, and more importantly, no one likes to get the same error over and over.

3. Intermingle all available error handling techniques in the application. This includes TRY…CATCH…FINALLY blocks where appropriate, using form and class level error methods, and even writing bullet-proof code (checking to make sure a table isn’t already open before you USE it, for example). Spending time on this endeavors will pay dividends in the end. Good error handling doesn’t go away, once it is in place it will continue to do its work throughout the life of the application.

4. Leave ON ERROR alone! There is sometimes a need to handle certain potential error cases differently. To achieve this, developers often swap ON ERROR routines. Take this typical case:
The global ON ERROR points to some code that displays a generic message when an error occurs. In a procedure, the developer needs to try to open a table for EXCLUSIVE use. So, the developer wraps the USE…EXCLUSIVE command around a customized ON ERROR that handles the error case when a file is in use. Next, the developer sets the ON ERROR command back to the global handler.

*-- Set up global error handler
ON ERROR oApp.gobal_err_hand(ERROR()    ,;
                             MESSAGE()  ,;
                             MESSAGE(1) ,;
                             LINENO(1)  ,;
                             SYS(16)    ,;
                             SYS(2018)  ) 
 
*-- Start event processing (run the app!)
READ EVENTS                       
 
*-- function to open a table exclusively
FUNCTION open_data_exclusive()
 
    LOCAL lcOldError
    lcOldError = ON("ERROR")
 
    ON ERROR MESSAGEBOX("Can't open the file!")
    USE sometable IN 0 EXCLUSIVE
    ON ERROR &lcOldError
 
RETURN USED('sometable')

This method has many flaws, but certainly gets the developer where he or she needs to be. But what if the developer forgets to switch the error handler back? What about SET COMPATIBLE? What if a different error occurs?). This method also begs the question: Why not build a single a method with a large CASE structure to handle the specific error conditions? Of course, this is the best approach. Use a single ON ERROR command at the start of the application, and then work with that handler. No switching allowed! Things are easier now in newer versions of FoxPro. TRY…CATCH…FINALLY essentially renders the ON ERROR swap totally and utterly archaic.

Another side-effect of using ON ERROR in this way is that if you’re like me, then you have a debug menu and a debug environment that will treat errors differently. When an error occurs I don’t want to get a generic message. I want the ability to suspend the application and start a debugging session. See my blog entry on the debug menu for more details!

Tags: Debugging, VFP