Tod means Fox

Business Intelligence, Data Warehousing, SQL, Visual FoxPro.

Archive for May, 2007


Published May 4th, 2007

Not So Big Anymore

Now that we have our new car (a VW Rabbit, no less), I’m starting to notice something: People don’t seem to respect me on the road as much as they did when I had the Jeep. I get cut off more, produce less of an impact when I tail-gate the slower folk, and have also noticed that I get less ‘looks’ — almost like I’m not even there.

My wife thinks I’m an aggressive driver (and based on my tail-gate comment, I bet you think that now too). I’ve always maintained that I am a defensive driver who has a little offense in him as well. Apparently those days are over, though. Looks like I have to shape up, calm down, and stay in my lane. I can’t even see over the feller in front of me anymore.

VW Rabbit

On the bright side, this car is FAST and fun to drive. It has this “S” mode (which stands for Sport) and can accelerate much faster than the Jeep. Plus it feels good not to worry about tipping over so much. I still need to play with the tiptronic. Lastly, this car is much, much more fuel efficient. Which is good for everyone except Mobile and Shell.

Published May 4th, 2007

Take That, Microsoft!

We just picked up 5 new licenses for VFP9, with the more on the way. I am very happy about this decision (it was in management’s hands, certainly not mine). We are in the process of migrating a VFP7 application, which has a few hundred installations around the country, to VFP9. The move was almost squashed thanks to MS’s latest announcement. VFP9 SP1 will make our application run better, give the engineers here more features and tools, open more doors into the XML world, and allow us to bother MS for support a little while longer.

Published May 3rd, 2007

FoxPro: A tool for developing countries

Wilson Ng, of Wired Desktop has made an interesting observation about VFP. He was reviewing the MasFoxPro site (which is what the meat of his article is about) and noticed that most people who have signed the petition are from ‘developing’ countries. (As a side note: I don’t like the term ‘developing’, but anyway.)

The people who signed come from about 80different countries worldwide. It is especially noticeable that the tool is really popular in developing countries. If you note, there were only 341 programmers from the United States, 90 from Germany and 42 from United Kingdom who signed. In contrast, 479 developers from Russia, 280 from China, 294 from Argentina, 150 from Brazil, 156 from Peru and 109 from Ukraine signed petition.

I’ve always known VFP to have a strong international presence. But where are all the US developers? Where are their signatures?

Published May 1st, 2007

VFP gotcha with _TALLY, RECCOUNT, DELETED, and an INDEX

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…