Working with FoxPro developers with different degrees of knowledge, it's interesting to see where the general knowledge line is. What do I mean by that?
If you put a bunch of FoxPro developers into a room and talk about standards, you get one indication of where their technical level is at. Yet when you look at the code behind the scenes of individual applications, you can get an entirely different impression.
I've had two (well more actually but two will illustrate it) situations where this came up. Someone was running a query where the results had more records than were expected. When I looked at the query statement itself, the WHERE statement was TRIM(tablea.field1) = TRIM(table2.field2). So that was the problem but why was that in there? As it turns out, it comes back to misunderstanding one of the basics of FoxPro comparisons:
x = "John"
y = "Johnson"
? x=y && Returns False
? y=x && Returns True
Why? Because when FoxPro compares two values, it essentially forces the text of the first value to match the length of the second. In short,
x = y is really does "John " (3 spaces) = "Johnson"
while
y = x becomes "John"(son) = "John" - so only the first four characters apply.
This is without SET EXACT ON , or x == y which does the implicit forcing the length of each string to be the maximum.
Update: As noted in the comments by Sergey Berezniker, the problem in the Query above had more to do with the SET ANSI statements than with the exact (he has a great post about this here).
The lack of understanding of how information is compared is what I was trying to get at and Sergey, your post gives a great set of examples on this. Thanks!
Another scenario was when a client was troubled over problems with their primary keys in their system and getting errors. Upon looking at the tables, I saw that their tables had a number of indices and they were using a flag to identify "deleted" or "archived" records. But more troubling was that what they considered to be their primary key was:
INDEX ON clientID TAG MAINKEY FOR myFlag="A" CANDIDATE
Then when their application made a particular update (like changing the myFlag field), the system would bomb out with a Violating Index error.
Even more so, there were indices based on
INDEX ON TRIM(custname) TAG custname
Similar to the above discussion of comparisons, it shows a basic misunderstanding of how items are put together. (I've seen FoxPro developers creating TRIM indices and then wondering why their code still runs slow with huge tables).
Certainly the Using Rushmore Query Optimization topic looks helpful or even Craig's short version but it still doesn't get away from the problem that likely exists in lots of older code.
So what's the answer? Basic Training obviously - but where do you get it? I had started a while back with a site calling Learning Visual FoxPro where the goal was to point people to great starting resources. But the problem with a lot of training is that when you discuss features and then the ideal way to do things, users can still go out and make bad choices (like creating filters on indices for primary keys).
On the Wiki, there's a topic of Learn VFP in 21 Days which has a great outline for getting started but what I think is missing is a "What NOT to do" or maybe it's more of a "what not to do unless you absolutely know what you're talking about" <g>
As it turns out, there is such a page on the Visual FoxPro Wiki but it's VFP Rookie Mistakes (does that make anyone feel smug when they find code that is in there?). To be fair, there is VFP Misused and Abused but once again, it's hard to find it. It seems like some refactoring of those Wiki pages may be in order.
If anyone has found any other "things not to do", be sure to add them to the site or let's discuss them here.
If you put a bunch of FoxPro developers into a room and talk about standards, you get one indication of where their technical level is at. Yet when you look at the code behind the scenes of individual applications, you can get an entirely different impression.
I've had two (well more actually but two will illustrate it) situations where this came up. Someone was running a query where the results had more records than were expected. When I looked at the query statement itself, the WHERE statement was TRIM(tablea.field1) = TRIM(table2.field2). So that was the problem but why was that in there? As it turns out, it comes back to misunderstanding one of the basics of FoxPro comparisons:
x = "John"
y = "Johnson"
? x=y && Returns False
? y=x && Returns True
Why? Because when FoxPro compares two values, it essentially forces the text of the first value to match the length of the second. In short,
x = y is really does "John " (3 spaces) = "Johnson"
while
y = x becomes "John"(son) = "John" - so only the first four characters apply.
This is without SET EXACT ON , or x == y which does the implicit forcing the length of each string to be the maximum.
Update: As noted in the comments by Sergey Berezniker, the problem in the Query above had more to do with the SET ANSI statements than with the exact (he has a great post about this here).
The lack of understanding of how information is compared is what I was trying to get at and Sergey, your post gives a great set of examples on this. Thanks!
Another scenario was when a client was troubled over problems with their primary keys in their system and getting errors. Upon looking at the tables, I saw that their tables had a number of indices and they were using a flag to identify "deleted" or "archived" records. But more troubling was that what they considered to be their primary key was:
INDEX ON clientID TAG MAINKEY FOR myFlag="A" CANDIDATE
Then when their application made a particular update (like changing the myFlag field), the system would bomb out with a Violating Index error.
Even more so, there were indices based on
INDEX ON TRIM(custname) TAG custname
Similar to the above discussion of comparisons, it shows a basic misunderstanding of how items are put together. (I've seen FoxPro developers creating TRIM indices and then wondering why their code still runs slow with huge tables).
Certainly the Using Rushmore Query Optimization topic looks helpful or even Craig's short version but it still doesn't get away from the problem that likely exists in lots of older code.
So what's the answer? Basic Training obviously - but where do you get it? I had started a while back with a site calling Learning Visual FoxPro where the goal was to point people to great starting resources. But the problem with a lot of training is that when you discuss features and then the ideal way to do things, users can still go out and make bad choices (like creating filters on indices for primary keys).
On the Wiki, there's a topic of Learn VFP in 21 Days which has a great outline for getting started but what I think is missing is a "What NOT to do" or maybe it's more of a "what not to do unless you absolutely know what you're talking about" <g>
As it turns out, there is such a page on the Visual FoxPro Wiki but it's VFP Rookie Mistakes (does that make anyone feel smug when they find code that is in there?). To be fair, there is VFP Misused and Abused but once again, it's hard to find it. It seems like some refactoring of those Wiki pages may be in order.
If anyone has found any other "things not to do", be sure to add them to the site or let's discuss them here.
Comments
Your explanation for the first scenario is totally of mark.
Comparison in a query is controlled by SET ANSI setting, not SET EXACT. Also, in a query it doesn't matter if shorter string is on the right or on the left.
Check http://berezniker.com/content/pages/visual-foxpro/string-comparison-vfp
Sergey
Good point. I wasn't referring in my first scenario however to the ANSI or SET EXACT statement. I was referring more to the poor use of TRIM() in a query.
But thanks for the note - good to know.
Thanks
The first article in the series is about the JustX() and ForceX() functions for handling files and paths. The second covers the different looping constructs in VFP.
Tamar