Packing tables with FinalBuilder
We use FinalBuilder to package our bi-monthly updates of our FoxPro DOS pharmacy application. Itís a set of multiple FinalBuilder files with hundreds of individual actions. One of the tasks involves creating a clean set of DBF files for specific modules that we ship to our clients when their version got corrupt.
Every two weeks we get updates for these tables. The import, as likely most imports do, involves updating, adding and deleting records. What we ship to our clients should not contain deleted records, so we need to pack them first. Thatís a simple job in a FoxPro application. For my FinalBuilder scripts I prefer to use built-in solutions whenever possible.
External programs arenít totally banned here, but they add subtle problems. Some of my external programs are Visual FoxPro code. FinalBuilder (which runs on a different machine than my development environment is, I must add) calls a compiled Visual FoxPro EXE. More than once I made changes to a program, tested the EXE on my development machine and commited the changes to the SubVersion repository... but forgot to compile an EXE on the build server and place it into the bin directory for the build project.
Hence, whenever I can find a built-in solution with reasonable performance, Iíll use that. FinalBuilder supports ADO to access tables, which means the OLEDB provider for Visual FoxPro gets used. FinalBuilder isnít a full blown programming languages that would let you create a connection, use multiple command objects, and the like. Instead you have three actions:
- ADO Execute SQL
- ADO Execute Stored Procedure
- ADO Dataset Iterator
Each action will connect to the database, execute the statement, do something with the results and disconnect. The code I want to execute is
USE %table% EXCLUSIVE
As you notice these are two code lines. In a program you could connect, send two commands separately and disconnect. Since the VFP OLEDB provider has an instance of VFP running it will just keep tables open in between commands. This doesnít work with FinalBuilder, though, because FinalBuilder closes the connection.
Fortunately, EXECSCRIPT() is among the supported functions in the OLEDB provider. Also fortunately, the old code to handle non-VFP types of DBF files is still part of the product. Although packing a table creates a new file without deleted records, the resulting table is still a FoxPro 2.6 DOS compatible DBF file.
The - for now - final solution is to use the ADO Execute SQL action, uncheck the ďFail if 0 rows are affectedĒ option and use the following SQL statement:
ExecScript("USE %table% EXCLUSIVE"+chr(13)+chr(10)+"PACK")
The final, final solution will likely involve a loop that builds up a number of PACK commands from a FileSet which then serves as the input into the Create ZIP File action. But thatís for another day.
Significant digits in Visual FoxPro
Steve Bodnar asked an interesting question on Twitter yesterday. Why does he get different results for the following two lines?
? Round(512.9250000000,2) && 512.92
? Round(512.925000000,2) && 512.93
Our mathematical education tells us that it shouldn't matter how many zeros the number is padded with. The result has to be the same. Computers commonly don't share the view of their users, nor do they use the same foundation as humans do. We live in a decimal world (except for a few non-metric measurements some countries just refuse to give up), computers live in a binary world. What is an almost perfect number to use, 512.925, is in fact 512.924999999999 to the computer.
Visual FoxPro, like most other languages, uses floating point numbers to express fractions. Those number do not have a fixed precision unlike decimal numbers we use. Working with 15-digits floating point numbers would quickly be cumbersome, nor do they match what we use Visual FoxPro for in reality. Nobody wants an an invoice with the amount spelled out down to the millionths cent.
Fox Software found several solutions to what is basically a presentation issue. On the one side we have obscure commands that hardly anyone can explain properly such as SET DECIMALS and SET FIXED.
To simplify things - down to the point that we are not seeing any longer what's going on behind the scene - Visual FoxPro keeps track of the number of digits every floating value has. This is an extra piece of information that is not part of the numeric value itself.
512.9250000000 and 512.925000000 aren't the same values. Both have the same floating point value 512.924999999999. The former having a precision of 13, the second a precision of 12 attached to it. With a number like this it's quite easy to figure out how many significant places the number has. Just count its digits. But what about using this number in any kind of calculation? How does Visual FoxPro know how many digital places the result shall have? When you run the following code:
? 512.925000000 font "Courier new"
? 512.925000000+0 font "Courier new"
? 512.925000000*1 font "Courier new"
you'll notice that the number remains the same (fortunately, otherwise we would more serious things to worry about), however it keeps shifting to the right. Why's that?
When Visual FoxPro prints a value onto the screen, it does so according to the precision stored with the value. There's no way to directly ask VFP for those values.
Someone - probably a poor trainee 25 years ago - specified for every single operation by how many digits the result could vary from the values that participate in the operation. Just printing the number has no effect, therefore it's printed aligned to the left border. When you add two numbers, you can never ever add more than one digit to the left. 9+9 is 18. Hence, the add operation makes room for a potential overflow and adds one digit. Visual FoxPro looks at both sides of the decimal point separately. Adding a 4.1 and a 1.4 number results in a 5.4 number - that is the maximum number of digits on either side plus one for the overflow. Multiplications combine the number of digits:
? 1.11*1.1 font "Courier new"
? 1.11*1.10 font "Courier new"
? 01.11*01.10 font "Courier new"
Multiplications also make space for the sign character. In the first line we multiply a 1.2 and a 1.1 value. The result has a total of 6 significant digits: One for the sign, two for the whole numbers and three for the fraction. By adding zeros you change the precision of the expression. Adding zero to the fraction results in four decimal places (2+2), but doesn't move the number to the right. Adding zeros to the whole number though results in the whole number part now being four instead of two digits. The whole number shifts two characters to the right.
With this in mind it becomes easy to understand the rounding issue. The last piece you need to know - as Jody Meyer kindly pointed out on Twitter - is that VFP uses an internal precision of 15 digits. ROUND() - just like any other operation - changes the number of significant places of its result. If you pass in an integer value, you get back the same value plus two decimal places.
Let's start with the second value of Steve's example to show how Visual FoxPro works. The operand has 12 decimal places. ROUND() will add another 2. The result will be a floating point value with 14 significant places. Every value participating in the operation is rounded to that precision. 512.924999999999 - the stored value with the maximum of 15 significant places - is rounded to 512.92500000000, a value with 14 digits. The result of the ROUND() operation is therefore 512.93.
The first line, though has 13 digits. Adding the two digits from the ROUND() operations results in a whopping 15 digit number. This maxes out the number of digits that Visual FoxPro supported. Following the same pattern, the value of 512.924999999999 is rounded to 15 digits. In other words, it remains unchanged. The third decimal place is now a four instead of a five. As a result, Visual FoxPro rounds down to 512.92.