In an effort to provide better compatibility with back-ends such as SQL Server, Visual FoxPro 9 has added three new data types, VarChar
, 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.
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
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.
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
|Figure 1. VarChar vs. VarBinary: The figure shows a comparison between Character and VarBinary data types.|
(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
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.
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
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.
operator requires that both sides of the expression contain exactly the same number of bytes, including CHR(0)
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
. 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.
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.
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
, and DBGetProp
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
receives two new properties, RecordsFetched
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.
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()
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.
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.
returns the work area for the cursor marked by SetResultSet
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
constraints and for mapping remote data to the new VarChar
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.
SET ENGINEBEHAVIOR 90
|As in prior versions, using the SET ENGINEBEHAVIOR command allows you to isolate legacy code that may be problematic.|
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.
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 enhancementssome 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.