Packing tables with FinalBuilder
2011-02Feb-12
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:
Each action will connect to the database, execute the statement, do something with the results and disconnect. The code I want to execute is
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:
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.
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
PACK
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.