Support

Recovering corrupt or lost data

One of the nagging potential problems Visual FoxPro developers face is the possibility of running into a corrupt table (DBF) or memo file (FPT). This typically happens when a user is in the middle of updating records and they experience a power outage or determine the process has locked up their computer and decide to reboot. The scenario is not what is important, rather the results. A corrupt table is not a pleasant experience for any customer, but fortunately there are tools to repair a corrupt table most of the time.

The best defense against corrupt tables is good backups . The reason for this is there are no guarantees a corrupt table can be repaired. This author has been fortunate and only needed to repair a production table six times in my entire career. Each time it was fixed with ease because of the tools used to repair tables (more on this later). The majority of the time what happens is Visual FoxPro attempts to open the table and observes the record count in the header does not match the physical record count in the table. This triggers error 2091, ‚“Table " name " has become corrupted. The table will need to be repaired before using again. ‚½

If a table cannot be repaired or the state of the data is unknown, restore all the tables and the associated database container. The reason for not restoring one table is the possibility of primary keys and foreign keys being out of sync. You do not want to run into the situation where child records are orphaned or parent records lose their children. Debugging alleged problems like this takes a significant amount of time and depending on the timing of the backups, more time than repairing the data via the normal data entry or batch processes. If you determine restoring one or two tables does not have a negative impact, you can save the customer from entering the data again. Each situation is different. We recommend our customers do regular full backups simply because it is easier to restore everything all at once. If there is an incremental scheme in place, you need to determine how far back to go and how many incremental backups need to be restored after the initial full backup is restored. Naturally, many different factors impact the backup scheme. Make sure the customers are aware of the trade-offs and the impact lost or corrupt data can have on their business.

Recovering corrupt data from a DBF and FPT can be challenging unless you have the right tools. As we noted earlier in this section, we have not run into too many corruptions in our career. We hear about them all the time and frankly feel very lucky we have not had some of the experiences other developers have in this arena.

Based on Steve Sawyer ‚ s articles and recommendation, we purchased Recover (see Figure 1 and 2 ) from Abri Technologies ( www.abri.com ) back in 1997. Recently we upgraded to version 4.0 of this product because it works with the new Visual FoxPro 8.0 table changes to accommodate the new Integer (AutoInc) data type.

Figure 1. This is Recover from Abri Technologies, the most recommended product to repair corrupted tables.

Figure 2. The configuration options available from Recover are extensive and allow you to repair various types of corruption.

Note ‚  

Visual FoxPro guru Steve Sawyer wrote a pair of in-depth articles on how tables and memo files can become corrupt ( ‚“Corrupt Your .DBF Files on Purpose (Really!) ‚½) and a comprehensive review on repair tools ( ‚“Database Repair Tools to the Rescue ‚½), both in the September 1997 issue of FoxPro Advisor. A lot of time has passed since Steve wrote these articles, but the information is still worthwhile if you can dig up the old issue or have the magazine PDF available from Advisor.

The one feature we really like about Recover is the ability to generate definition files (DEF extension) in advance. These definition files are used during the repair process. Instead of guessing what the structures are, the definition files are used to recover the data. A definition file is generated for each Visual FoxPro table you deploy and should be deployed in the same folder as the tables for easier recovery in the future. If you want to retrofit a deployed set of tables with the definition files, just make sure to generate them from tables with the same structures as you have in production. If you alter the structures on your development machine and generate the definition files, they will not help in the recovery because they will not match.

There are other commonly recommended products to consider with respect to repairing corrupt tables. The Stonefield Database ToolKit ( www.stonefield.com ) repairs common header problems and FoxFix from Xitech ( www.xitech-europe.co.uk ) is a product much like Recover, with extensive options to repair tables.

The other type of corruption might be easier to fix depending on the type of bug or errant process that physically corrupts the data. This corruption is typically caused by a logic error in your code. One example would be accidentally replacing data in every record because the SQL-Update incorrectly selected the wrong set of data to update. Audit logs might allow you to reverse out each transaction. Architecting solutions with a ‚“data reversal ‚½ feature can save you pain in supporting your own bugs .

Recovering backend databases depends on the database because backups are a process within the database. If we use SQL Server as an example, you can perform complete or partial backups. Restoration of the backups depends on the backup scheme you deploy for the database.

Visual FoxPro database structures and maintenance

Visual FoxPro tables require special handling with respect to reindexing and implementing structure changes

Index changes

Corrupt indexes can trigger strange behavior with sorts and ordering, query results in orders contrary to the SQL Select ORDER BY clause, and cause your application to trigger the ever popular C5 error. This is why developers create reindexing routines to delete the index files (structural CDXs, compound CDXs, or individual IDX files) using DELETE TAG ALL , and recreate them from scratch. Using the REINDEX command in a production environment is a well documented problem because it uses the index definitions found in the index file. If the index file is corrupt, REINDEX might not be able to recreate the index, or it might possibly reproduce the exact same corruption during the reindexing. Obviously, REINDEX cannot be used if the CDX file is deleted.

There are two approaches typically found in a Visual FoxPro solution when it comes to reindexing and packing. The first and less common of the two is to write a hard-coded program line-by-line that has the required INDEX ON commands for each index of each table in the application and ALTER TABLE for primary keys. Each time a new index is added to a table, the developer needs to remember to update the program with the code to add the index. The second approach for reindexing is to use an active data dictionary. This is the more common approach for FoxPro 2.x developers and is found in plenty of Visual FoxPro applications. A table of indexes is maintained and a generic program is written that reads this table and indexes along with a table of tables. Once the generic program is written all a developer has to do to create a new index is add a record to the index table and run the program. Writing a generic program can take quite a bit of time, but can be reused for all applications; the hard- coded method can be faster to write, but requires a new program for each application. Packing tables can be approached using the exact same method.

Both the hard-coded program and active data dictionary approach to reindex and pack tables have a similar drawback: maintenance. All it takes is one developer, one forgetful moment, and the scheme loses one index. In production, the application attempts to SET ORDER TO for the new tag, and in kicks a test of the application error handler. This is why we recommend purchasing a subscription to the Stonefield Database Toolkit (SDT). SDT keeps track of all your changes to indexes, structure changes to columns , and relationships in the underlying DataBase Container Extensions (DBCX) metadata. SDT updates the metadata directly if you make the changes to the index or tables in SDT. If you make the changes in the native Visual FoxPro Database Designer or Table Designer, you can open up SDT and run the validation process. This process updates the metadata and ensures it is current to the actual structures.

Deployment of the new indexes is a snap with the DBCX metadata. You distribute the database container files (DBC/DCT/DCX) and the DBCX metadata (see Table 1 ) into the production environment. Once the files are in place you run some code calling the SDT NeedReindex method to check if the reindexing process should be run. The SDT Reindex method is called (example code is provided with the product) to delete and recreate the indexes. The great thing about SDT is you never have to track which index changes you made in development. SDT does this for you.

SDT/DBCX metadata files

DBCXREG.CDX

DBCXREG.DBF

DBCXREG.FPT

COREMETA.CDX

COREMETA.DBF

COREMETA.FPT

SDTMETA.CDX

SDTMETA.DBF

SDTUSER.CDX

SDTUSER.DBF

Alternatively, you can pass a parameter to the Reindex method to PACK the table. The Stonefield pack takes a safer approach to packing tables and copies the records in the order of the primary index. This improves performance in your applications when you process records in the primary index order.

Structure changes

Structure changes are very common for applications already in production. We are referring to adding columns, deleting columns, renaming columns, changing the data types, altering memo file block sizes, and changing the table code page. Exclusive use of the table is required to make these kinds of changes.

Just like reindexing, developers can manually write the conversion code to handle each of these situations. Doing so can be tedious and prone to mistakes, though. Each time a change is required the developer needs to maintain the conversion program. Forgetting to add code to the script for a change ensures a problem during the deployment phase. A support person or end user runs the script manually or it is called from the application as part of the start up process.

Deploying structure changes is easy with the DBCX metadata and SDT from Stonefield. You distribute the database container files (DBC/DCT/DCX) and the DBCX metadata (see Table 1) into the production environment. Once the files are in place you run code calling the SDT NeedUpdate method to check if the update process should be run. If changes need to be implemented, the Update method (example code is provided with the product) is called to perform the necessary changes. SDT does all the heavy lifting : it checks each table to see if the structures are current and it performs ALTER TABLE commands to synchronize your changes. If the table does not exist in production, it creates the table and indexes. Update also verifies each tag and persistent relation is up to date. The great thing about SDT is you never have to track the structural changes you made in development. SDT does this for you as long as you validate your database before deploying the metadata.

SQL database structures and maintenance

Maintaining and implementing changes to a SQL backend database requires more of an ‚“old fashion ‚½ approach. There is no Stonefield Database Toolkit for SQL databases so you have to maintain scripts to run on the production database. There are several approaches to take depending on the backend database, the customer ‚ s environment, and the sophistication of the deployment tools.

There is no difference in deploying an update to an application compared to deploying the original version if you deploy databases by running a script. If you originally deployed the database by restoring a backup of a database from your development machine, the difference is significant because you did not run a script originally.

The difference is determining how you are going to generate the script to implement the changes and how to implement this script. We know developers have created different techniques over the years and our process might be different from the one you might have created. We have experienced three approaches.

The first approach is to maintain a file or set of files with scripts for each change you make to the database. This means when you change a table structure, alter the attributes of a column in a table, add a new table or view, add/change a rule, add/change stored procedures, etc., you need to script the change. You need to run each of these scripts in the same order as you made the alterations in development. If you add each script to a master script file and check this into a source code control system, it allows teams of developers to work together and not stomp over each other ‚ s changes. It also makes the implementation during the deployment easier because it is one additional file to deploy with the rest of the application. You can run this script in the script execution tool built into the database server. In the case of SQL Server, you open up the script using Query Analyzer and run it to update the database with the latest changes.

The second approach is to store the individual scripts in a DBF file and write a generic script-running program to execute each of the scripts by SQL PassThrough (SPT). The advantage of this approach is the scripts can be sorted by a column in the DBF so they are run in a specific order, or processed in physical record order, or tracked by version. In the backend database, a configuration table can store the script version so only the new scripts from the last update execute on the backend database server. The difficulty with this approach is the DBF is a binary file and it is difficult to perform difference comparisons integrated with source controls tools. The advantage of this approach is more apparent if you have a vertical market application and different customers are running different versions of the product.

The third approach is to use a tool to compare two instances of the same database. In this situation, one of the databases is a copy of the current production data, and the second is the current development data. A tool like Red Gate ‚ s SQL Compare scripts the two databases, performs a comparison (see Figure 3 ), and then generates the script to update the production database to match the development database (see Figure 4 ). The script generated is not always optimized when you read it, but it works well. You can always edit the final script to make certain optimizations if necessary.

Figure 3. SQL Compare evaluates the difference between two databases and visually represents if the objects match or if they are different. After you select the objects to script, SQL Compare generates the change script.

Figure 4. SQL Compare generates script to update each of the databases. You can save this script to a file and include the file as part of the deployment package.

We have used a product called xCase from Resolution ( www.xcase.com ) to deploy changes to a production database. This tool is a data-modeling tool. One of many the features of this tool is the ability to synchronize a database with the xCase data model. xCase can synchronize the data model with the database two different ways. The first way is to apply the changes directly; the other is by generating a script you can run later. First you select the database entities you want synchronized (you can select individual entities or all entities for the comparison). xCase goes out to the database you selected to synchronize the model to, analyzes what has changed, and displays the list (see Figure 5 ). Select the script file and the type of script you want generated (create, drop, alter, copy, import, ignore). The script is generated either way. Once the script is generated, it prompts you to submit the script. If you are deploying the changes remotely, you can send this script to run on the production server. If you are a corporate or on-site developer, you can submit the script right away if you see fit.

Figure 5. xCase database synchronizer shows the differences between the data model and the database, and synchronizes the changes directly or creates a script to run at a later time.

Once the scripts ship to the client site, you need a method to execute them. If you are using SQL Server, the database administrator can open up the script in SQL Server Query Analyzer and run it (see Figure 6 ). If your customers are running the Microsoft SQL Server Desktop Engine (MSDE), you need an alternative method because SQL Server ‚ s Query Analyzer is not part of the ‚“lite ‚½ package. We discuss tools in Chapter 7, ‚“Client/Server Applications Step-By-Step ‚½ including MSDE Query (see Figure 7 ).

Figure 6. SQL Server ‚ s Query Analyzer allows the database administrator to run scripts to update the production database with changes from development.

If you are making the deployments on-site and the SQL backend is MSDE, you can connect to the MSDE database with your SQL Server Query Analyzer (provided you have security clearance to do so) and run the scripts. If you are remote, you can connect through a Virtual Private Network (VPN) via the Internet or dial-up.

You can use the OSQL.EXE command line utility that ships with SQL Server and MSDE. This interface is by far the most cumbersome, but it can be set up to run from a batch file, which allows you to ‚“batch script ‚½ your SQL scripts. For more information on OSQL, check out the Microsoft MSDN Web site: http://msdn.microsoft.com , and search for ‚“osql. ‚½ There are several Web pages documenting the use of this powerful tool.

The last option to run scripts is to purchase an installer package to run SQL scripts on the database directly. This approach eliminates the need to have the users or database administrators run the scripts. We have not used an installer to do this, but the Wise for Windows Installer Professional and Enterprise packages have the ability to move SQL code to the SQL Server and verify end user permissions on the SQL Server.

Figure 7. MSDE Query allows database administrators, developers, and sophisticated end users to run scripts on MSDE, which does not have tools built in like the full license of SQL Server.

Runtime command window

If you have supported an application on-site or remotely through Remote Desktop, Terminal Services, or pcAnywhere, you may find yourself looking for a command window to perform some of the support tasks. These tasks might include browsing a table to look for corrupted data, creating an index on the fly to test some performance issue, running a SQL Select to verify query results, executing a stored procedure, modifying a report the customer created on their own, or writing a quick and dirty program to perform cleanup of the data or run a task.

If your customer purchased and loaded a copy of Visual FoxPro, you have the full power of FoxPro at your disposal. If not, which is more often the case, you need to either build into your application the capability to run commands, or search on the Internet for a tool providing a command window capability for you to support your customers.

FoxBox is an indispensable tool (see Figure 8 ). FoxBox is a Command Window (and much more) without having a full copy of VFP loaded at the customer site. It does require the same runtimes installed for your application. This tool allows you to run any command in Visual FoxPro that does not fire the ‚“Feature not available ‚½ error. Therefore, you can open up a table and BROWSE it, you can perform SQL-Selects to inspect data, you can fire up a REPORT FORM , recreate an index, or you can open text files. All the things you want to perform with Visual FoxPro that you do not want to build into your executable can be accomplished via FoxBox. More information can be found at www.rickschummer.com and www.kirtlandsys.com , The tool can be downloaded from these sites and is available for free.

Figure 8. FoxBox is a very versatile tool for supporting customers who do not have Visual FoxPro loaded on their computer.

There are other runtime command windows available on the Internet. One product is called Fox6Command (see Figure 9 ), created by Walter Meester. It is available on the Universal Thread ( www. universalthread .com ) in the Visual FoxPro download section (search for ‚“runtime command window ‚½). The source code is included in the download and it is free. You can see by the commands entered in the command window, you can open reports , browse cursors , and, we verified this, it handles ‚“Feature not available ‚½ errors gracefully.

Figure 9. The Fox6Command runtime command window looks and feels just like Visual FoxPro 6.0.

We found a product called SS Command created by Matt Weinbender from SoftServ, Inc. ( www.softserv-inc.com/sscommand.html ). This product is shareware and has an inexpensive licensing cost (US$5 for single site, US$13 for unlimited sites). The source code is available if you purchase the unlimited license and a Help file is included in the shareware and production versions. We suggest you try out the shareware to see if it meets your needs. There is a nag screen until you register the product and get a license key.