LIST STRUCTURE | Hackers Guide to Visual FoxPro 7.0

Sys(3055)

This function, new in VFP 6, lets you decide how much of your resources (okay, FoxPro's resources) should be available for parsing complex FOR and WHERE clauses. You'll rarely need it, but when you do, you'll sure be glad they added it.

Usage

nPreviousAllocation = SYS( 3055 [, nNewAllocation ] )

One of the cooler things about VFP is the ability to work against views and not have to think about whether data is local or remote. In previous versions of VFP, this ability was severely limited. If a local view was updateable and set for the WHERE clause to check "key and modified fields" (or "key and updateable fields"), you were limited to changing 23 or fewer fields at a time. If you changed more than that, you'd get error 1812—SQL Statement Too Long. (And when it happened to you, you felt a lot like Napoleon on the road to Moscow.)

It turns out that this was a trade-off made by the VFP team. (We think it's a memory issue, though we couldn't detect memory consequences.) As of VFP 6, they've put the trade-off in our hands instead. First, they raised the initial limit. The new default, and the lower limit the function will accept, is 40 changed fields. If you know you need to handle more than that, you can call SYS(3055) to up the limit. Pass it eight times the number of fields you want to handle. The maximum is 2040, which is conveniently 255*8.

We've also heard occasionally of people getting the same or similar error messages in other situations. This function should help there, too. You may need to experiment to find the right setting in that case.

Our friends at Microsoft tell us there can be performance consequences from raising this value. In our tests here at Hacker Labs, we found that the time to update from a view to a table did increase a little with higher values of SYS(3055), but the increases were so tiny you won't see them unless you're doing a ton of updates. (With 1000 updates of a record with 48 fields, the total time increased by about three seconds when we went from the minimum SYS(3055) setting that would do the job to the maximum setting. Interestingly, the increase wasn't a straight line—some lower SYS(3055) values resulted in more time than higher ones—but that could reflect other things going on with the test machine. Also interestingly, we saw no difference in memory usage with the different settings.) Bottom line: Raise this one if you need it, but don't bother if it's not relevant to your situation.

We also note that the term "complexity," used to describe this function in the docs, is something of a misnomer in this context. The WHERE clause generated by a TableUpdate() is never complex, just long.

Example

* Handle TableUpdate() for a query with 100 modifiable fields. SYS(3055, 800)