Browse DevX
Sign up for e-mail newsletters from DevX


What's New with Data in Visual FoxPro 9? : Page 5

The Microsoft Visual FoxPro team has a great reputation for responding to community requests and version 9 reflects many such changes, most notably changes to the data engine that enhance data access and database application development.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

New Datatypes
In an effort to provide better compatibility with back-ends such as SQL Server, Visual FoxPro 9 has added three new data types, VarChar, VarBinary, and BLOB. These data types can be used as part of a local table, which is created by using the CAST() function or mapped to when retrieving results from remote data.

The VarChar is a character data type that is not padded with spaces to the length of the field. This provides similar functionality as SET ANSI_PADDING ON in SQL Server.

Many of the changes made to the CursorAdapter were done to bring behavior in line with remote views.
In addition, if spaces are included with the original value, they are not trimmed. It's important to note that when using expressions combining VarChar with Character data types, the result is always of the type VarChar.

Because a VarChar is a fixed length field in Visual FoxPro 9, the maximum field length is 255 characters. In SQL Server, it is possible for a single VarChar field to have a length <= 8060 bytes.

The VarBinary data type is similar to VarChar in that values assigned to VarBinary fields are not padded. The only real difference between the two is that FoxPro does not perform any code page translation for VarBinary types. (See Figure 1 for a comparison of VarChar and VarBinary datatypes.)

Figure 1. VarChar vs. VarBinary: The figure shows a comparison between Character and VarBinary data types.
The BLOB (Binary Large OBject) data type is not a fixed length type, like a Memo. Its information is stored in an .FPT file that is external to but referenced by the .DBF file. BLOB fields have the same limitations and issues as Memo fields and do not support indexes.

Like the VarBinary type, Visual FoxPro 9 does not perform any code page translation and treats the fields as binary content.

The BLOB datatype is an ideal candidate to replace the legacy General field. Pictures and other media can be stored in a BLOB and then rendered using the Image control PictureVal property.

MODIFY MEMO of a BLOB displays a hex dump of the binary data.

SET EXACT & Binary Comparison
With Binary data types comes a difference in behavior with SET EXACT and SET COLLATE.

SET EXACT ON specifies that expressions must match exactly to be equal. However, with VarBinary types whose values are padded with CHR(0), the trailing bytes are ignored for the comparison. The shorter of the two expressions is padded on the right with CHR(0) bytes to match the length of the longer expression.

SET EXACT OFF specifies that expressions must match up exactly to the length of the expression on the right side of the comparison.

The == operator requires that both sides of the expression contain exactly the same number of bytes, including CHR(0) bytes.

Further, because Binary data is case-sensitive, comparisons are always case-sensitive regardless of SET COLLATE. This is different than a comparison with a Character type, which is case-insensitive if SET COLLATE is set to GENERAL.

Learn to Type
Prior to version 9, Visual FoxPro allowed a SQL CREATE TABLE statement to include a long typename, although only the first letter of the typename was respected. This resulted in issues with data types such as Character and Currency. There is now full support for long typenames with both CREATE and ALTER TABLE/CURSOR as well as the new CAST() function. Table 1 provides a list of FoxPro data types with their long name, single letter, and, in some cases, abbreviations, supported.

Table 1: Here are some FoxPro data types with their supported long names and initials.

Data Type

Long Name



Char, Character









Num, Number






Int, Integer





























Just like with the new Binary index datatype, using these new datatypes requires that all clients accessing the data be upgraded to Visual FoxPro 9. Tables that include these new types cannot be read by prior versions.

Remote Data
Visual FoxPro has always had a strong remote data story, and Visual FoxPro 9 adds even more control over how remote data is manipulated and retrieved.

Transactions and Connections
A new property, DisconnectRollBack, has been added to control whether pending transactions should be rolled back when the connection is disconnected. This property is available through the SQLSetProp, SQLGetProp, DBSetProp, and DBGetProp functions.

SQLIdleDisconnect() is a new function that can be used to temporarily disconnect a connection. In most cases, this is controlled through the IdleTimeOut property of a connection. But in multithreaded run-time (MTDLL), idle tasks are disabled. Calling this function releases the connection until the next command requiring a connection is issued.

Fetching and Processing Information
CursorGetProp() receives two new properties, RecordsFetched and FetchIsComplete. These properties allow you to determine the number of records fetched and when a fetch is completed during the execution of SQL Pass Through statements.

The RecordsFetched value might not reflect the number of records in the cursor in the event that some records in the cursor were locally appended or deleted. The number of records reported does not respect filters that may be on the cursor.

The number of records affected by the SQL Pass Through can be determined using the additional parameter aCountInfo available with the SQLEXEC() and SQLMORERESULTS() functions. ACountInfo is a two column array containing the alias and count.

Rowsets from the Provider
Three functions have been added to support returning result sets from stored procedures when accessed via the provider.

SetResultSet() is used to specify which work area in the current DataSession is to be used as the result set. Only one cursor in a DataSession can be marked.

GetResultSet() returns the work area for the cursor marked by SetResultSet.

ClearResultSet() sets the marked result set to 0 and returns the work area for the previously marked cursor.

Within a stored procedure, you create a cursor with the results you want returned and use SetResultSet() to identify the work area for the cursor. When executed by the provider, return values are not respected, but a rowset is created based on the cursor you provided.

CursorAdapter and XMLAdapter Enhancements
Changes to the CursorAdapter and XMLAdapter classes are worthy of another multi-page article. But for the purpose of this overview, it's important to briefly note some of the more significant enhancements.

Many of the changes made to the CursorAdapter bring behavior in line with remote views. These enhancements include:

  • Support for Timestamp fields. This allows UPDATE/DELETE commands to use Timestamp fields as part of the WhereType method.
  • Auto-Refresh support. Several new properties have been added so that remote data managed by the CursorAdapter is automatically refreshed after an INSERT or UPDATE operation. This is valuable for retrieving auto-incremented or default field values and timestamps.
  • On Demand Record Refresh support. More properties and events have been added to CursorAdapter to support the same kind of functionality provided by the REFRESH() function for local and remote views.
In addition, properties have been added to support DEFAULT and CHECK constraints and for mapping remote data to the new VarChar and VarBinary data types.

XMLAdapter changes include support for:

  • Hierarchical XML
  • XPath expressions
  • XML encoding and decoding enhancements
Visual FoxPro 8 included changes to FoxPro's SQL commands, and brought it into greater compliance with ANSI SQL standards. These changes may have discouraged you from upgrading to version 8 because of the impact on existing code. If you've been putting off fixing some of those ambiguous queries or at least bracketing them with SET ENGINEBEHAVIOR, Visual FoxPro 9 provides many compelling reasons to make the investment to upgrade.

Fortunately, the kinds of changes made in Visual FoxPro 9 won't require the kind of recoding you may have found necessary for 8.

As in prior versions, using the SET ENGINEBEHAVIOR command allows you to isolate legacy code that may be problematic.

As in prior versions, using the SET ENGINEBEHAVIOR command allows you to isolate legacy code that may be problematic.
SET ENGINEBEHAVIOR 90 impacts the behavior of TOP N and the behavior of aggregate functions without a GROUP BY clause. In versions prior to 9, if such a statement resulted in no matching criteria, no records were returned. To be more ANSI compliant in version 9, FoxPro returns a single record resultset with NULL value(s) for aggregate columns.

Final Thoughts
In this article, you've seen that the changes to the data engine in this release are substantial. A commitment to backward compatibility and an easy upgrade path has made these changes nearly transparent when moving from version 8 to version 9. Once you've made the move, you can start taking advantage of these great enhancements—some without changing a single line of code. Other enhancements ensure that code based on the new features is more compatible, powerful and maintainable than ever before.

David T. Anderson is a member of Alden Anderson, LLC. He recently completed a contract tester assignment with the Visual FoxPro team at Microsoft testing the data enhancements made to Visual FoxPro 9 and implementing performance benchmarks such as the Transaction Processing Council's TPC-H. With 22 years of application development experience in the PC industry for government, military, educational institutions, and private industry, David uses his experience relating Enterprise Architecture and Organizational Maturity to assist organizations in determining the most appropriate software processes for their needs.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date