Recently I blogged about data overflow not always happening with numeric data in Visual FoxPro. This was a frustrating problem to track down and yesterday I ran into another side effect of this problem when using TRANSFORM().
If you execute the following in the Command Window:
?TRANSFORM(6500.0000/0.00015)
You get 43333333.333333340 dumped on the VFP desktop.
If you try to store the result of 6500.0000/0.00015 into a N(12,5) column in a table you get 43333333.333 even though one could expect an overflow condition to occur. But as I relearned a couple of weeks ago, this is not the case.
I was debugging this same conversion I originally discovered this behavior with last time for a different overflow problem. In the text output file I was seeing the asterisks for the column storing the large number. In the intermediate and temporary DBF of the output I was seeing 43333333.333. This particular field is numeric in nature, but I pass it along to the receiving system as a character field per the spec. To convert the number I elected to use TRANSFORM() because they did not want any leading spaces or trailing zeros. The STR() function returns asterisks by default on the large number and requires me to specify the number of digits and the decimals to avoid the overflow representation. TRANSFORM() was not only easier and required less code, but also met the exact requirements for this situation.
But yesterday I was seeing stars. Stepping through the code I was observing the values without overflow until I hit the code to TRANSFORM() the column to a character representation. The result was the overflow asterisks. Argh.
The VFP Help file indicates their can be problems with large negative numbers not getting correctly transformed, but says nothing about large positive numbers. What is happening here is that VFP realizes the overflow condition in the table column and transforms it correctly to asterisk.
The problem was tricky to figure out and time consuming to track down, but thanks to the new CAST() function the solution was easy. I casted the field early in the conversion process to ensure the column is wide enough. The real kicker is the data was bad data (not that I am being judgemental {g}).
Yesterday was a pretty frustrating day all the way around, and this was just the frosting on the cake.
