Visual FoxPro

From TheAlmightyGuru
Revision as of 20:12, 16 July 2019 by TheAlmightyGuru (talk | contribs) (File Path Commands Aren't Quoted)
Jump to: navigation, search
Visual FoxPro 9.

Visual FoxPro, or VFP, is a programming language and IDE from Microsoft. The primary use of the language is for rapid database application development. The programming syntax is similar to that of Visual BASIC (not VB.NET). The language uses dynamic inferred structural typing. It compiles to pseudo-code which is interpreted by the runtimes which must be distributed along with the program.

Visual FoxPro is based off of FoxPro, which was the result of Microsoft buying FoxBase from Fox Software. FoxBase was derived from dBase III.

The last official release of Visual FoxPro was version 9, which was released in 2004 with a final service pack released in 2007. Microsoft officially discontinued support in 2015.

Though I had seen FoxPro icons since my first days with Windows 3, I never knew what FoxPro was. I got my first taste of Visual FoxPro in 1999 because it was the main programming language used by the company I started working for. I was familiar with Visual BASIC, so it was pretty easy for me to pickup the syntax, and I had just developed an Access application for my high school, so I knew enough about databases to secure my job. I've used VFP from version 6 until its final release, version 9.0 SP2.



  • The language has a built-in database creator, editor, and viewer. Though it's not without its problems, it's the best I've ever worked with.
  • It has a full SQL interpreter as well as many additional database features not found even in modern databases, and they're very easy to use.
  • The SQL speed is lightning fast, even to the point of outpacing SQL Server at times since it doesn't have built-in transaction logging.
  • The IDE uses a wonderful multi-window system so you can easily open and view several different code blocks at the same time, even from the same object, something I wish Visual Studio would adopt.
  • Some of the table field types are quite useful like the currency with 4-digit decimal precision, and the numeric which is based on length of digits rather than bytes.
  • Unlike most earlier versions of Visual BASIC, VFP had several built-in variable types like datetime and logical.


  • The database format is way out of date and doesn't support any modern formats like Unicode.
  • Numeric variables (and likewise, table fields) are divorced from most other languages. There is no support for unsigned values at all, and there are no primitive types like byte, int, long, etc. Instead, all numbers are treated as either signed double integers or floats, and even then, their type is implied.
  • Functions that affect file names like Copy File don't preserve text case.
  • The UI tries to remember the position of windows and the code block you last viewed, but usually fails.
  • Although VFP is mostly object oriented, it has a lot of legacy commands and functions that are procedural. This lack of standards creates confusion.
  • While text fields longer than 254 characters are possible, they require the use of "memos" which are especially cumbersome to work with.
  • VFP's help has a poor index which is missing dozens of commands. For example, Type "ALEN" in the Index tab and you get a page for "_ALen() API library routine," but there isn't an entry for the ALEN() Function, even though a page exists and can be found in the Search tab.
  • VFP's help is seriously lacking on SQL examples and only shows you the most basic of queries.


  • VFP has really poor ActiveX and OLE support, often to the point of crashing the UI.
  • VFP has really poor array support and only supports 2D arrays (to resemble tables). Single dimensional arrays and arrays with three or more dimensions are not possible.


This is a list quirks found in VFP and, in some cases, how to bypass them.

Dynamic Inferred Structural Typing

It's refreshing to have a language intelligent enough to determine what type of variable you need based on how you assign it and not need to cast types every time it changes. For example, the following code will yield "1.1," but in a strong-typed language, you would need to declare the type of i as an int, f as a float, Sum as a float, cast Integer into a float to add them, and then convert to a string to display them with MessageBox.

i = 1
f = 0.1
Sum = i + f

However, this make it very east to shoot yourself in the foot with sloppy code like this which will run without error:

s = "test"
s = 1
s = .T.
s = DateTime()

Not to mention, this prevents the compiler from identifying type-based errors at design time, so you get a lot of "Operator/operand type mismatch" errors during runtime. For example, the following code compiles just fine, but crashed when you run it:

Integer = 1
String = "This is not a number!"
Sum = Integer + String

Uncommon Logical Representation

Most languages use the words "true" and "false" to represent their values, but VFP uses the first letter surrounded by periods; so, .T. is true and .F. is false. There is also .NULL. for null.

Non-Exact Comparison With Equals

By default in VFP, when you compare two strings with an equal sign, VFP only compares to the length of the second string. This yields results different from most programming languages. For example, the following code will return true:

MessageBox("Testing" = "Test")

To force VFP to behave like other languages and compare the strings to their full length, use a double equals sign. Thus, the following code will return false:

MessageBox("Testing" == "Test")

There is another option in VFP with the command, Set Exact On, but this is misleading, because it doesn't actually use exact matching. Instead, VFP pads the length of each string with spaces until they are of equal length, but this can yield undesired results as well. For example, the first comparison will be false, but the second will be true:

Set Exact On
MessageBox("Testing" = "Test")
MessageBox("Testing" = "Testing ")

VFP Processes Fields Before Variables

When reading or writing data, VFP tries to access table fields before memory variables. This can create unexpected results when you have a memory variable with the same name as field in the currently selected table. For example, in the following code, the MessageBox will read "Table Field" not "Memory Variable":

Create Cursor Test (MyVariable C(11))
Insert Into Test (MyVariable) Values ("Table Field")
MyVariable = "Memory Variable"

To force VFP to read and write to a memory variable instead of a field, add "m." to the beginning of the variable. For example, in this code, the MessageBox will read "Memory Variable":

Create Cursor Test (MyVariable C(11))
Insert Into Test (MyVariable) Values ("Table Field")
m.MyVariable = "Memory Variable"

Personally, I find it to be best practice to never give my variables names that might be used by fields. To guarantee this, I use Hungarian notation for memory variables, but not for fields.

Can't Force Field Types Using Select

Because VFP uses inferred variable typing, you cannot specify the type of a variable you want when you define a variable. While this generally doesn't cause a problem, because most variable types are properly inferred, it does create a problem when using Select to create new fields. The following code:

Select *, Space(50) As NewCharField From table name Into Cursor Test

Will create a cursor of the specified table with a new 50-character-long field called NewCharField. This works as expected because VFP properly infers character variables. However, this code:

Select *, 0 As NewIntField From table name Into Cursor Test

Will create a cursor, not with a new integer field as desired, but with a numeric field with a length of 1. Although you can increase the length of the numeric field by substituting 00, 000, 0000, etc. for the field value, VFP has no way to force the query to use a cursor type of integer.

There are a couple ways you can get around this. Obviously, you can use Alter Table to add an integer field, issue the Select statement, and then Alter Table to drop the field, however this requires exclusive access to the table and is very resource heavy.

A simpler method is to use an existing field from the table that is the type you desire, and then wiping out the values afterward. For example:

Select *, Existing Integer Field As NewIntField From table name Into Cursor Test
Update Test Set NewIntField = 0

However, if the table you're querying from doesn't have an integer type field, you can use a Create Cursor command to make a cursor with all the desired fields, including the new integer field, run the query into a second cursor, and then append the results into the created cursor:

Create Cursor Test (Existing fields, NewIntField I)
Select * From table name Into Cursor Test2
Select Test
Append From DBF("Test2")

Brackets Are Used For Both Strings and Arrays

Like Visual BASIC, VFP uses parentheses to call an array element, for example:

Local Array aTest(10, 2)

However, VFP also lets you use brackets like a C-based language:

Local Array aTest[10, 2]

But, to really complicate things, VFP also lets you use brackets for strings:

sString = [This is the contents of a string]

Using brackets for array dimensions confuses the interpreter's color syntaxing, so, when you use brackets to denote an element in an array, it is colored as though you're setting a value of a string. Since VFP is more BASIC-like than C-like, I prefer to use parentheses for my arrays which yields the correct color syntax.

Weak Cursor Support

VFP uses temporary tables called cursors which, despite the ambiguous name, are extremely useful. However, several commands do not work on them. For example:

  • You cannot Pack a cursor.
  • You cannot Alter Table a cursor if the cursor uses long field names. VFP will give a cryptic error about trying to Pack a cursor, but this is wrong. Although, strangely, you can still use Modify Structure to make changes.
  • Append From only works if you first wrap the cursor's alias with DBF().

VarType Can't Identify Non-Existent Properties On Non-Existent Objects

Because VFP uses the period operator for both table fields and object properties, it prevents VarType from being able to properly distinguish a table from non-existent properties on non-existent objects. For example, in the following code, NotAnObject will properly be described with a "U" for undefined, the oForm.Visible property will be described with an "L" for logical, the oForm.NotAProperty non-existent property will be described with a "U," but the non-existent property on the non-existent object will throw an error of "Alias 'NOTANOBJECT' is not found," because, since it's not an object, VFP will assume it's a table before running VarType.

oForm = CreateObject("Form")

You can get around this problem by using the Type command when you're not sure if the object will exist:


File Path Commands Aren't Quoted

In VFP you can execute file path commands like CD and Copy File as though you were at a command line. For example:

CD C:\Temp
Copy File Test.txt To C:\Windows

Because file paths do not have to be quoted or be forced into a variable, the compiler can't predetermine if the value is legit, so clearly incorrect statements will go unnoticed. For example, the following code which is clearly missing the last closing parenthesis will try to copy C:\Temp\Test.txt into a directory called "(cPath":

cPath = "C:\Temp"
CD (cPath)
Copy File (cPath + "\Test.txt") To (cPath

To avoid accidents like this, I always use string variables or constants when writing a path.

Can't Close Database When Its In the Open Project

The command Close Database will properly close a database during run time and in the Command Window during design time. However, if you have a project open that has a database included, running Close Database during design time will not close the database (even if you do not have the project's Data tab selected), and will not throw an error of its failure to close the database. To close the database when it's included in a project, you must first close the project then issue Close Database, or just use Close All, which will close both.

VFP Doesn't Escape Quotes In CSV Format

If you use Copy To or Append From to export or import a table into CSV format, and a character field contains a double quote, VFP will not escape the quote. For example, the following code will generate invalid CSV output:

Create Cursor Outbound (Description C(20))
Insert Into Outbound (Description) Values ('Lt. Blue "Cyan"')
Copy To ("C:\Temp\Description.csv") CSV

The CSV file will have the following contents:

"Lt. Blue "Cyan""

While this looks acceptable, it will cause most CSV importers to give unwanted results because the quotes inside the quotes are not escaped. Even VFP, the creator of the CSV file, cannot import it correctly. VFP sees the second double-quote as the end of the field prematurely ending the field and throwing off the remainder of the line. When you run the following code:

Create Cursor Inbound (Description C(20), ShouldBeBlank C(20))
Append From ("C:\Temp\Description.csv") CSV

You will get a cursor with the following values:

Description          ShouldBeBlank
Lt. Blue             Cyan""

Some importers (like LibreOffice) are smart enough to import the file properly because its program will only end the field when it sees a double-quote followed immediately by a comma. However, VFP's export can create truly unreadable output when you include a comma in the field as well:

Create Cursor Outbound (Description C(20))
Insert Into Outbound (Description) Values ('Broken",Field')
Copy To ("C:\Temp\Description.csv") CSV

The contents of the file will look like this:


And, importing the file with the following code:

Create Cursor Inbound (Description C(20), ShouldBeBlank C(20))
Append From ("C:\Temp\Description.csv") CSV

The contents of the new cursor will look like this:

Description          ShouldBeBlank
Broken               Field"

VFP also doesn't properly handle the two most common methods of escaping a double-quote. If you create a CSV file with the following contents:

"Lt. Blue ""Cyan"""
"Lt. Blue \"Cyan\""

Most CSV importers will import the data correctly. However, if you use the following VFP code to import it:

Create Cursor Inbound (Description C(20), ShouldBeBlank C(20))
Append From ("C:\Temp\Description.csv") CSV

Your cursor will have the following data in it:

Description          ShouldBeBlank
Lt. Blue ""Cyan""    
Lt. Blue \           Cyan\""   

Since VFP can't handle either exporting or importing CSV files with character fields that have double-quotes, you must handle this process manually. The easiest way to do this that I'm aware of is to replace double-quotes with a "safe" character, that is, a character that is unlikely to ever occur in normal data and can't easily be typed in on a keyboard. For example, a pilcrow (¶) or a division symbol (÷). The following code will make a duplicate of a table an replace all instances of double-quotes in character fields with the pilcrow symbol:

Select * From YourTableNameHere Into Cursor CSVCursor ReadWrite
nColumns = AFields(aColumns, "CSVCursor")
For nColumn = 1 To nColumns
	If aColumns(nColumn, 2) = "C"
		cCommand = "Update CSVCursor Set " + aColumns(nColumn, 1) + " = StrTran(" + aColumns(nColumn, 1) + [, '"', "¶")]
Copy To "C:\Temp\YourTableNameHere.csv" CSV

When you import the file, run the following code to put the double-quotes back:

Create Cursor YourTableNameHere (Field1 C(20), Field2 C(20), Field3 C(20))
Append From "C:\Temp\YourTableNameHere.csv" CSV
nColumns = AFields(aColumns, "YourTableNameHere")
For nColumn = 1 To nColumns
	If aColumns(nColumn, 2) = "C"
		cCommand = "Update YourTableNameHere Set " + aColumns(nColumn, 1) + " = StrTran(" + aColumns(nColumn, 1) + [, "¶", '"')]

Of course, this only works when working from VFP to VFP, which you wouldn't normally do. If you're exporting or importing data in VFP, it usually means you're working with another program, so, you'll have to figure out how to replace or encode the double quotes in that program as well.

Sporadic Failures On One-Record Tables

My company found an extremely rare bug that occurs when you run a Locate command on a table with only one record. Even if Found() returns .T. and RecNo() returns 1 indicating it is on the first record, VFP is actually on End of File and will return an empty value for any requested field. Our test code was something to this effect:

Create Table "Settings.dbf" (SettingNo I, SettingName C(4))
Insert Into Settings (SettingNo, SettingName) Values ("Test", 1)
Locate For Settings.SettingNo = 1
If Found() = .T.

For the majority of the time, the MessageBoxes would read "1" then "Test", but every so often the second MessageBox would be blank. This can be resolved by adding several blank records to the table.


Importing From a Table With an Auto-Incrementing Unique Key

Needing to import the data from one table to another is quite common, and can be done with the command:

Append From table name 

However, if the table from which you're importing uses an auto-incrementing unique key, the above command will fail, because you can't insert a record with a specific value in an auto-incrementing unique key. A quick way to get around this problem is to eliminate the unique key from the table being imported using Modify Structure. However, if you don't have the ability to modify the structure, you can use a cursor to do it:

Select * From table name Into Cursor ImportCursor ReadWrite

At this point, you might think to use an Alter Table SQL statement to eliminate the unique key, but VFP can only run Alter Table on cursors when all of the field names are 10 characters long or fewer. If the cursor does feature a long field name, you can strangely still use Modify Structure on the cursor and manually remove the column. Once the column is removed you can safely append the data with the following line:

Append From DBF("ImportCursor")