VFP2Text v3

by Frank Perez May 26, 2014

Back in 2008 the first version of VFP2Text was initially released. I was immediately surprised by how many people liked it. Sometimes an idea seems really cool to you, but it's hard to gauge what others will think.

For the sake of those who are new, Beyond Compare is a tool for comparing folders and files. VFP2Text is an add-on utility for Beyond Compare that allows Beyond Compare to perform comparisons on Microsoft FoxPro binary files (DBC, DBF, FRX, SCX, and VCX).

Three years later, VFP2Text v2 was released which added support for Beyond Compare v3 and a couple of minor adjustments. At that time I attempted to add Merge capabilities, but I was never able to get it to work well. Which explains why I never blogged about VFP2Text v2.

After six years of near silence, I'm proud to release the next major version of VFP2Text with some very cool features.

  • Installation Program
  • Update Beyond Compare Settings
  • Human Readable
  • Deleted Records
  • Caching
  • Digitally Signed

Installation Program

VFP2Text now includes a real installation program. The installation program will automatically locate the folder where Beyond Compare is installed and then install VFP2Text into a sub-folder.

Update Beyond Compare Settings

VFP2Text can now automatically update the Beyond Compare File Formats. This simplifies the setup process and makes getting started with VFP2Text easier.

Human Readable

The previous versions of VFP2Text used the CURSORTOXML() function to convert FoxPro binaries to XML. XML was chosen because it is very easy and fast to create, and XML automatically handled MEMO fields.

One of the problems with the XML format is that Table Structure was very verbose and difficult to read.

Another problem with the XML format is that it converted special characters, such as the lessor than and greater than signs.

VFP2Text v3 manually converts every field to plain text in a custom format that is easier to read.

VFP2Text v3 includes the entire table structure (code page, fields, indexes, database name, and etc).

VFP2Text v3 automatically handles special characters.

Deleted Records

The previous versions of VFP2Text ignored deleted records. This made sense for FoxPro source code (e.g. FRX, SCX, and VCX) files, but was not always the desired behaviour for DBF files.

VFP2Text v3 includes deleted records when DBF files are processed.


FoxPro binary files mostly come in pairs. For example, a Report consists of a FRX and a FRT file, whereas a Form consists of a SCX file and a SCT file. Because there is no way for VFP2Text to know which file in a pair will get passed to it (sometimes both, sometimes just one), VFP2Text has to perform the conversion process for either file in a set. That is why the File Formats has multiple extensions.

VFP2Text v3 saves time by caching the last 30 seconds of conversions. For example, once the CDX has been converted to plain text, VFP2Text uses the cache for the corresponding DBF and FPT files.

Digitally Signed

The VFP2Text.EXE and Setup*.EXE files are now digitally signed.

Download VFP2Text http://pfsolutions-mi.com/Product/VFP2Text
Scooter Software Beyond Compare http://www.scootersoftware.com/


Filed Under: VFP | VFP2Text

Never Use an Alias in an Index Expression

by Frank Perez November 16, 2010

Part of the work I do involves supporting existing Visual FoxPro applications. In some cases, the original developers are no longer available and I take over maintaining the code. Some may not like this kind of work, but I enjoy the variety. I also like it when I get the opportunity to track down mysterious bugs like the one I'm about to describe.

The problem occurred in a portion of the code that called the VALIDATE DATABASE command. I don't recall why the code need to perform the command, but when it executed a "Cannot open table 'C:\FolderName\TableName.DBF'" message was displayed on the main Visual FoxPro window.

My first thought was that the table might be corrupt. So I tried viewing the contents of the table with the USE and BROWSE commands as follows. No errors and everything worked as expected.

USE IN SELECT("TableName")
USE "C:\FolderName\TableName.DBF" IN 0 AGAIN ALIAS "TableName" NOUPDATE

My second thought was that maybe the VALIDATE DATABASE command needed exclusive use of the table and something else had a shared lock on the table. So I tried viewing the contents of the table with the following commands. Once again, no errors and everything worked as expected.

USE IN SELECT("TableName")
USE "C:\FolderName\TableName.DBF" IN 0 ALIAS "TableName" EXCLUSIVE

My next thought was that maybe the table or database were corrupt in some unusual way. I could try recreating the database and table, and then copy the data from the bad table into the good table. One of the easiest ways to accomplish this is to use GenDBC. GenDBC is a utility program that is included in the Visual FoxPro Tools folder. It generates a script of native Visual FoxPro commands that can recreate a database along with every connection, table, view, relation, and referential integrity constraint.

I ran GenDBC and executed the script to recreate the database and tables. Everything worked as expected. I was about to start copying the data from the bad table into the good table when it occurred to me that I should try validating the database first. So I performed the VALIDATE DATABASE command on the freshly created database...BAM! I got the "Cannot open table 'C:\FolderName\TableName.DBF'" error message.

This made me wonder if maybe GenDBC failed when it generated the script and just did not display the error message. So I opened the script and scrolled down to the section with the table create code. And that is where I noticed something strange. The CREATE TABLE command looked okay, but the INDEX ON command had the table name included in the expression. It looked something like this.

CREATE TABLE "TableName.DBF" (pk I, description C(20))
INDEX ON TableName.description TAG "DESCRIPTIO"

Including the table name in the index expression is not something I would ever do. It seemed redundant. So I removed the table name from the index expression, re-ran GenDBC, and then executed the script to recreate the database and tables. This time when I performed the VALIDATE DATABASE command, everything worked as expected. Cool!

However, finding a solution isn't always enough for me. I needed to know more. For example, is this the problem in the INDEX command because it allows the table name to be included in the index expression or is the problem the VALIDATE DATABASE command? I did a little research and I found a MSDN article called "Considerations for Creating Index Expressions". In this article Microsoft states "If you include a field prefaced by a table alias or work area letter in the index expression, Visual FoxPro generates an error message." The article isn't very clear about the error, but at least I had documented proof. Just because Visual FoxPro allows a table name to be included in the index expression, you shouldn't.

On a side note, an interesting thing happened when I got ready to write this blog entry. I wanted to have some code that could reproduce the error. So I wrote a program that would create a database with a both a good table (a table with a valid index expression), and a bad table (a table with an invalid index expression). When I ran the VALIDATE DATABASE command I did not get an error like I expected. It took me a little while to figure out that the error would not occur unless I closed and re-opened the database. The following code reproduces the behaviour.

* create a database
* add a table with an index and then some records
CREATE TABLE "GoodTable.DBF" (pk I, description C(20))
INSERT INTO "GoodTable" (pk, description) VALUES(1, "Description 1")
INSERT INTO "GoodTable" (pk, description) VALUES(2, "Description 2")
INSERT INTO "GoodTable" (pk, description) VALUES(3, "Description 3")
* validate the database, expectation is no error
* add a 2nd table, but this time put the alias in the index expression
CREATE TABLE "BadTable.DBF" (pk I, description C(20))
INDEX ON BadTable.description TAG "DESCRIPTIO"
INSERT INTO "BadTable" (pk, description) VALUES(1, "Description 1")
INSERT INTO "BadTable" (pk, description) VALUES(2, "Description 2")
INSERT INTO "BadTable" (pk, description) VALUES(3, "Description 3")
* validate the database, expectation is an error, but VFP does not display one
? "VALIDATE DATABASE (GoodTable + BadTable)"
* close and re-open the database
* validate the database, now VFP displays the error
? "VALIDATE DATABASE (GoodTable + BadTable)"

Considerations for Creating Index Expressions http://msdn.microsoft.com/en-us/library/5bxf1b0a(VS.80).aspx


Filed Under: VFP

Always Select or Specify a Work Area

by Frank Perez December 14, 2008

Just the other day I got burned by what I consider a rookie mistake. I was working with a Visual FoxPro application (compiled as a multi-threaded DLL) that records application events to a Visual FoxPro table. To limit the size of the log table, the application re-used records after 500,000 events by updating the oldest record instead of adding new records.

As a general rule I know that I should always use a SELECT command before performing any command or function that processes a work area. For example, in the following sample code the SCATTER command is used to copy data from the current record to an array. Because this command processes the current work area only, I should always select the desired work area first.

* create a cursor with a single record
CREATE CURSOR "CURSOR01" (character1 C(10))
* create another cursor with a single record
CREATE CURSOR "CURSOR02" (character1 C(10))
* before calling the SCATTER command, select the work area
* this should display "VALUE01"
WAIT WINDOW loValues.character1

The exception to this rule is any command that has an IN clause or alias parameter. For example, in the following sample code the REPLACE command can safely be used without selecting the desired work area first.

* create a cursor with a single record
CREATE CURSOR "CURSOR01" (character1 C(10))
* create another cursor with a single record
CREATE CURSOR "CURSOR02" (character1 C(10))
* this should display "CURSOR02"
* change the value in CURSOR01 cursor
* this should display "NEWVALUE"

In this particular situation, I was using a LOCATE command without specifically selecting the work area first. In an application compiled into an EXE, executing the LOCATE command when the current work area is blank would cause an Open File Dialog similar to the one below to appear.

However, in an application compiled into a DLL, the LOCATE command appears to be ignored if the current work area is blank. An error does not get thrown and the program simply executes the next line of code. :(

Fortunately, I was able to track down and fix the problem quickly. I'm sure that this is one of those mistakes that we all make at one time or another. I'm just hoping that by blogging about it, I will be less likely to make it again {g}.


Filed Under: VFP

Southwest Fox 2008 - Day 4

by Frank Perez October 19, 2008
Southwest Fox 2008 - Day 4

My last day started with Christof Wollenhaupt's "Optimizing and Debugging" session. In this presentation Christof demonstrated many good debugging and optimizing tips. Some of my favorites are:

  • When building a new application, work on the data migration before the forms and reports. This way you will have good sample data to test the forms and reports with.
  • Design to match complexity. For example, N-Tier is a good idea for enterprise systems, but probably overkill for a hobby application.
  • Avoid defensive programming such as parameter checking. It can promote bad coding practices.
  • Forms make horrible progress bars because they change focus (Activate and Deactivate events), which can interfere with debugging. Use a toolbar instead. This also has the benefit of always being on top.
  • Use a hotkey to turn the Coverage Profiler on and off.
  • When using Coverage Profiler, keep in mind that the time it takes to log an event can make a line of code that normally executes quickly appear much slower due to the overhead.
  • Use SCAN FOR instead of REPLACE FOR whenever possible because the REPLACE FOR command has to do a table lock, versus a record lock.

My last session of the conference was Craig Boyd's "VFP Fundamentals: Creating a Professional VFP Application from Start to Finish". Craig began with how he likes to setup his project folders, how he uses Subversion for version control, and sub-classing the base classes. He also discussed user interface best practices, such as using one font, using colors sparingly, avoid creating a battleship (black, white, and gray only), and being consistent in how you layout and size things.

My favorite part of his demonstration was an awesome grid that he created. It had really cool features such as column sorting, incremental search and filtering (very similar to Microsoft Excel), export capabilities, and the ability to save and restore how a user customizes the grid (i.e. column re-ordering and re-sizing).

Subversion http://subversion.tigris.org/


Filed Under: VFP

Southwest Fox 2008 - Day 3

by Frank Perez October 18, 2008

Saturday began with Doug Hennig’s “Creating Explorer Interfaces in Visual FoxPro” session. This presentation was about creating an explorer like interface similar to Windows Explorer using a TreeView control and the VFPX OutlookNavBar component.

Besides being an excellent speaker, Doug’s sample code and white papers are alone worth the price of admission. For example, Doug does not just use the standard TreeView control. He extends it by automatically linking an ImageList control, proving node load control, adding double click and right click functionality, adding “go back” support, adding support for saving and restoring the state on close/open, and more. Did I mention that it is also data-driven? That’s just awesome.

The second session of the day was Andrew MacNeill’s “Working with CodePlex and VFPX”. In this presentation Andrew discussed the basics of VFPX, how to use the CodePlex site, and how to access the source code. I’ve always felt like the VFPX site was a little confusing to navigate so it was nice to get a demonstration.

The third session was Alan Stevens’ “Manage Complexity with Agility”. This was my first time hearing Alan speak. I had heard good things about his presentation last year, so I was eager to experience one this time. I have to say, Alan exceeded my expectations. His presentation contained a lot of good ideas about the software development. For example, on the topic of software schedules, Alan suggests delivering something every 1 to 2 weeks. It is better to find out what you’ve done is not what the customer wanted, even though it may be what they asked for, sooner than later. He discussed that most customers are not technical and therefore cannot provide formal requirements. What you can do is get the user to explain what they need in short descriptions, a.k.a. user stories. A user story consists of a role, a need, and a reason.

Alan also demonstrated unit testing with FoxUnit, and automating builds using VFP MSBuild Target. FoxUnit is an open source unit testing framework from VisionPace. VFP MSBuild Target is a VFPX project for performing Visual FoxPro builds.

After lunch I attended Doug Hennig’s “Advantage Database Server for Visual FoxPro Developers” session. I was really interested in attending this session so that I could get some first-hand knowledge about this product from a trusted Visual FoxPro developer.

Doug did a really thorough job of explaining what Advantage Database Server (ADS) is, how to install it, and how to use it with Visual FoxPro. This session covered a lot of material, so I’m going to limit my comments to my favorite three things.

First, if you are still using the Visual FoxPro ODBC driver, which has not been updated since Visual FoxPro 6, you may want to consider replacing it with the ADS ODBC for the following reasons. One, it supports Visual FoxPro 9 fields types, such as auto incrementing. Two, it’s free!

Second, ADS supports accessing Visual FoxPro data both as native tables and through ADS. This means that you can slowly convert an existing system to a client server model one module at a time. For example, you could change one form to access the data using ADS, while the rest of the system continued to access the tables directly. The end goal would be to change all the forms to use ADS, but this one module at a time approach allows you to first concentrate on the portions of the system that need the conversion to client server the most. How cool is that?

And finally, the ADS full text search capability. This feature alone could be enough reason to use this product. Doug started by creating a table with 80,000+ records (60MB DBF) that had a 500MB FPT file. He then performed a search with both Visual FoxPro and ADS. The results speak for themselves.

  • Visual FoxPro Case Insensitive: 305 seconds
  • Visual FoxPro Case Sensitive: 60 seconds
  • ADS: 0.07 seconds

My last session of the day was Alan Stevens’ “Ignorance is Bliss: Why You Don't Need to Know Where or How Your Data is Stored?”. Alan discussed the idea that an application should not tightly coupled to the data. This makes it possible to have a more flexible system that can support different data sources.

Unlike his other session, Alan walked through quite a bit of sample code in this session to show this could be implemented using XML to transport the data. Alan warned that the Visual FoxPro XMLTOCURSOR() function tends to hang when the size of XML reaches about 11MB. His solution, was to use the XML DOM object in a loop to process the XML in batches of 500. I’ll have to keep that in mind if I ever run into that problem in the future.


Filed Under: VFP

About Frank

Frank lives in West Bloomfield, Michigan with his wife and three children.  When he is not writing code, he enjoys long distance running and riding his motorcycle.

Month List

Tag Cloud