The Microsoft Visual FoxPro team has a great reputation for responding to community requests and the next version of is no exception.

Most evident in several changes to the data engine, Visual FoxPro 9 includes many enhancements for data access and database application development.

Visual FoxPro 9.0 contains more functional enhancements to the data engine than any release since 3.0. From the new and extended functionality in SQL to new data types and a binary index, this release demonstrates the power of a mature development platform for data-centric solutions.

A single SQL SELECT statement can contain more tables, more JOINs, more sub-queries, more nested sub-queries, and more UNIONs than ever before.

Changes to the data engine can be summarized in five major areas:

• SQL Enhancements: Removal of most hard-coded limits, enhanced sub-query and correlation support, support for more complex expressions, and enhanced UNION support.
• Performance: A new index type, enhanced performance of filtered indexes and improved SQL performance with TOP n, MIN()/MAX(), and LIKE.
• Commands and Functions: Greater ability to fine-tune how data is accessed and committed, functions to supplement SQL showplan, and easier immediate expression nesting with ICASE().
• New Data Types: Support for VarChar, VarBinary, and BLOB data types, a new CAST() function, and enhancements to existing functions to control and convert data types.
• Remote Data: Enhanced control over transactions, better visibility regarding fetched records, rowsets returned from the provider, and CursorAdapter enhancements that bring behavior in line with remote views.

Many of these changes improve the Visual FoxPro 9 client/server story by providing stronger interoperability with SQL Server. With support for new data types and removing many limits from the SQL language, it's now easier to develop a single code base that works with the Visual FoxPro 9 native data engine and SQL Server.

Enough overview, let's dig in!

SQL Enhancements

Certainly the best word to describe changes to the SQL sub-language is MORE! There are no longer hard-coded limits to the number of elements in a SQL statement. A single SQL SELECT statement can contain more tables, more JOINs, more sub-queries, more nested sub-queries and more UNIONs than in previous versions.

There are also no hard-coded limits on the number of items in a SQL IN list. In versions prior to Visual FoxPro 9, SQL IN was mapped to the INLIST() function; that dependency has been removed. This change allows an increase in the number of arguments for IN and for better optimization. Visual FoxPro 9 stops evaluating expressions from the list as soon as the match is found. This is helpful if the IN condition is not Rushmore-optimized, as performance can be improved by placing the most-likely-to-match values at the beginning of the list. The total number of items is still indirectly limited by the SYS(3055) function, which controls buffer memory size, so the higher the setting, the more items are supported via IN. (See the Common Error Messages sidebar for more information.)

No Limits?

No hard-coded limits does not mean limitless. Issues such as available memory and expression complexity can still have an impact on whether or not a very long and complex statement can be run, but you'll have to work hard to find many real-world limitations.

Enhanced Sub-Query Support

Sub-queries have always been powerful in the SQL language. They can be used as filters by placing them on the right side of a comparison in the WHERE clause. In Visual FoxPro 9, sub-queries can also be used as part of the SELECT list (called a projection) and in the FROM clause (often called a derived table).

Many of these changes improve FoxPro's client/server capabilities by providing stronger interoperability with SQL Server.

When used as a projection, if the sub-query doesn't return any records, a NULL value is returned. Correlation is supported with projection (more on this in a minute).

A SQL statement that uses projection looks like this:

SELECT ;
   C.CustomerID, ;
   C.CompanyName, ;
   (SELECT YTD_Sales FROM Sales_02 WHERE ;
     C.CustomerID = Sales_02.CustomerID) AS Y02,;
   (SELECT YTD_Sales FROM Sales_03 WHERE ;
     C.CustomerID = Sales_03.CustomerID) AS Y03,;
   (SELECT YTD_Sales FROM Sales_04 WHERE ;
     C.CustomerID = Sales_04.CustomerID) AS Y04 ;
  FROM Customers C 

This SELECT statement returns the customer ID and company name along with year-to-date sales from warehoused tables for the last three fiscal years.

A restriction on a projection is that the sub-query should return only one column and no more than one record for each record in the containing SELECT.

Another valuable use of a projection is when it's used as part of an expression.

SELECT ;
   C.customerID, ;
   C.companyname, ;
   SUM(D.quantity*D.unitprice) AS CustTotal ,;   
   (SUM(D.quantity*D.unitprice) / ;
     (SELECT SUM((quantity*unitprice)-discount) ;
        FROM OrderDetails D2) ;
    )*100 AS PctTotal ;
  FROM Customers C ;
 INNER JOIN Orders O ;
    ON C.customerID = O.customerID ;
 INNER JOIN OrderDetails D ;
    ON O.orderid = D.orderid ;
 GROUP BY C.customerID, C.companyname, O.orderID ;
 ORDER BY pctTotal DESC

This SELECT statement returns customer ID, company name, total sales, and a percent of total sales against all customer sales.

Note that the sub-query in the SELECT list is part of a complex expression that includes an aggregate function. Now that's flexibility!

A derived table as sub-query allows you to treat the results of a sub-query as though it were its own table.

Consider the following example:

SELECT ;
   C.customerid, ;
   P.product_count AS p_count;
  FROM Customers C, ;
     (SELECT c2.customerid, ; 
       COUNT(DISTINCT D.productID) AS p_count ;
        FROM Customers C2 ;
       INNER JOIN Orders O ;
          ON  C2.customerid = O.customerid ;
       INNER JOIN OrderDetails D ;
          ON O.orderid = D.orderid ;
       GROUP BY c2.customerid) AS P ;
 WHERE C.customerID = p.customerID ;
   AND P.p_count >= ;
      (SELECT (COUNT(*)*.50) FROM Products) ;
 ORDER BY p.product_count DESC

This SELECT statement returns customer ID and product count for all customers who have purchased at least 50% of the product line.

Notice that the derived table has an alias of “P” that is designated the same way you would alias a column, using the AS clause (required). It's also important to note that the sub-query can be complex (in this case, joining to two other tables) and that the results from the derived table can be used as a condition of the WHERE clause and in the ORDER BY of the top-most SELECT.

Unlike a projection, the derived sub-query can return more than one column and more than one record. It cannot be correlated. All sub-selects are executed before the top-most SELECT is evaluated.

Sub-queries are also supported in the SET list of a SQL UPDATE statement. Only one sub-query is allowed in a SET clause and if there is a sub-query in the SET clause, a sub-query in the WHERE clause is not allowed.

Better Correlation Support

The SQL UPDATE and SQL DELETE commands now support correlation. A correlated statement includes a FROM clause to relate the records being affected to another table.

For example:

DELETE products ;
  FROM mfg ;
 WHERE mfg.productID = products.productID;
   AND mfg.discontinued = .t.

This DELETE statement deletes all products marked as discontinued from a table provided by the manufacturer.

A correlated UPDATE looks similar:

UPDATE products ;
   SET unitprice = mfg.msrp *.90 ;
  FROM mfg ;
 WHERE mfg.productID = products.productID 

This UPDATE statement sets the unit price for a product at 90% of the manufacturer's suggested retail price.

A derived table, such as a sub-query, allows you to treat the results of a sub-query as though it were its own table.

You may be tempted to use a correlated sub-query as this is also supported. Just be aware that when using a sub-query it's like doing an outer join. For every record that is not found in the sub-query, the value returned is NULL. This may not give the desired results.

UPDATE products ;
   SET unitprice = ;       
       (SELECT ( msrp *.90 ) ;
          FROM mfg ;
        WHERE mfg.productID = products.productID)

This UPDATE statement sets the unit price for a product at 90% of the manufacturer's suggested retail price for every product found in the Manufacturers table. The price for products not found in the Manufacturers table is set to NULL.

Note that this statement operates on every record in the Products table; in the previous statement, only updated records that matched in the Manufacturers table were involved.

View and Query Designers

Unfortunately, due to the complexity of the SQL statements you can write with these enhancements, the Query and View Designers do not support many of the sub-query changes to SQL.

Regardless of whether you are doing remote data access or relying on the powerful native data engine, performance has always been a priority.

Also, with the hard-coded limits of SQL IN removed, the Designers no longer convert IN to INLIST(). The INLIST() function still has a limit of 24 elements.

Enhanced UNION Support

In addition to having no hard-coded limits for the number of UNIONs, you can now use a UNION inside the result set that is used by an INSERT INTO.

You can now also ORDER BY <fieldname> when using UNION. The referenced field must be present in the SELECT list for the last SELECT in the UNION.

Performance

Regardless of whether you are doing remote data access or relying on the powerful native data engine, performance has always been a priority for Visual FoxPro. Visual FoxPro 9 enhances the data engine even further.

Binary Indexes

This new index type is a specialized index, designed for one thing.

INDEX ON DELETED() TAG DELETED BINARY

The new index type can be used with any NOT NULL logical expression. Other restrictions preclude the use of a FOR expression and ASCENDING, DESCENDING, UNIQUE, and CANDIDATE keywords.

SET ORDER TO is not supported and the INDEX ON command sets the current order to 0. Also, you cannot use a Binary index with any Seek operation.

The big advantage of a Binary index is its size. A Binary index for a table with 8,000,000 records is approximately 30 times smaller (1.1Mb versus 31.5Mb). Smaller means faster I/O and faster APPEND and REPLACE, all with the same Rushmore optimization as a non-binary index on the same expression.

There is a trade-off to consider. Rushmore optimization is faster if the amount of records returned is more than 3% of the total records (about 92% faster when all records match the condition). However, Rushmore optimization is slower if the amount of records returned is less than 3% (about two times slower when 0 records match the condition). It is likely that the 3% threshold will become smaller as the number of records in the table increases.

Turning your DELETED indexes into Binary indexes is an easy way to start taking immediate advantage of Visual FoxPro 9 performance enhancements. Just be sure that all clients accessing your data are upgraded, as this new index cannot be read by prior versions.

Rushmore Optimizations

There are a few new Rushmore optimizations that do not require changes to data and index structures.

Top N [PERCENT**],** an optimization made to SQL, provides improved performance. This operation returns only the top number or percent of records in a result set as controlled in the ORDER BY clause. This change in Visual FoxPro 9 eliminates records from the sort process that don't belong in TOP N as early as possible, reducing comparison operations and decreasing file I/O in low memory situations. This also has the side-effect of only returning exactly N [PERCENT] records.

In previous versions, if there was a tie for nth place, all records that matched the tie were included, resulting in getting back more than N records.

If this change in behavior is not desired, consider bracketing the SQL call with SET ENGINEBEHAVIOR 80.

The only limitation to this optimization is that TOP N PERCENT cannot be used unless the entire result set can be read into memory at once.

When appropriate, Visual FoxPro 9 uses filtered indexes to optimize MIN() and MAX() aggregate functions in FOR DELETED() and FOR NOT DELETED() only. This improves MIN()/MAX() performance, if such an index exists.

The Like "sometext%" operator is now fully optimizable when the string ends in a wildcard. (Note that this is not the case when the comparison value begins with a wildcard or when the wildcard is embedded within the string.) This optimization scenario works like WHERE field = “sometext”.

More INDEX Smarts

Visual FoxPro 9 is even smarter in how it utilizes existing indexes to achieve Rushmore optimization. For example:

INDEX ON DELETED() TAG DELETED 

This index is used to optimize both NOT DELETED() and DELETED() conditions without the presence of a tag created by INDEX ON NOT DELETED().

Just like the MIN()/MAX() optimization, Visual FoxPro 9 uses a FOR NOT DELETED() filter on an index to optimize a DELETED() or NOT DELETED() query. Whenever it is possible to determine that a condition should filter on DELETED() or NOT DELETED(), a filtered index FOR DELETED() or FOR NOT DELETED() is used in the event that no non-filtered indexes exist. Take this upgrade opportunity to review the indexes you currently deploy. If you are unable to use a binary index, you may find that with these optimizations, you can at least drop a few existing indexes.

If only indexes filtered FOR NOT DELETED() were used for Rushmore optimization and SET DELETED is ON, additional NOT DELETED() optimization is unnecessary.

Commands and Functions

A few commands and functions have been extended to provide greater control over how and when Visual FoxPro reads and writes data to disk.

Fine-Tune How Data is Accessed and Committed

It is now possible to specify fractions of a second for the second parameter of the SET REFRESH command. The second parameter is used to specify the number of seconds between refreshing local memory buffers with current data from disk. You can also specify a value of ?1, which forces Visual FoxPro to always read data from the hard drive. The lowest setting for the second parameter is .001.

When using expressions that combine VarChar with Character data types, the result is always of the type VarChar.

Setting this value to a low number causes some performance degradation as the number of requests increase, especially across a network, so use it sparingly.

The SYS(1104) function purges memory cached by programs and data, and it clears and refreshes buffers for open tables. In Visual FoxPro 9, a second parameter scopes the operation to a specific work area or alias. This is valuable because using SYS(1104) when a large number of buffered tables are open can result in slow performance while each buffered table refreshes.

The FLUSH command is used to ensure that changes made to tables, indexes, and files are saved to the disk. In Visual FoxPro 9, the FLUSH command has been enhanced in two ways: specifying FLUSH areas, and calling the FlushFileBufferes function.

You can now be specific about the filename, work area, or alias to be FLUSHed. Although this extra granularity is handy, it's the FORCE keyword that is very useful in scenarios where Visual FoxPro 9 writes data to the disk but the operating system keeps the writes cached.

When you use the FORCE keyword, Visual FoxPro 9 includes a call to the Windows API FlushFileBuffers function. This ensures that even operating system buffers are written to disk.

Some examples of using the enhanced FLUSH command include:

FLUSH "c:\data\customers.dbf"
FLUSH "c:\data\customers.dbf" FORCE
FLUSH IN 1 FORCE
FLUSH IN customer FORCE
FLUSH "c:\test.txt" FORCE
FLUSH FORCE

In versions prior to Visual FoxPro 9, using a SQL SELECT statement meant that the results were always pulled from disk. This meant that if you wanted to query uncommitted changes from a buffered table, you were forced to use procedural commands. Now it's possible to specify for each table in a SELECT statement whether to read from the disk or from the local buffer using SET SQLBUFFERING and SELECT ... WITH (Buffering = <lexpr**>).**

Some examples of how to use WITH (BUFFERING ...) include:

SELECT * FROM Customer WITH (BUFFERING = .t.)

SELECT * FROM Orders WITH (BUFFERING = lUseBuffer)

SELECT DISTINCT c.city, o.shipcity ;
  FROM customers C WITH (BUFFERING=.T.) ;
  JOIN orders O WITH (BUFFERING=.T.) ;
    ON c.customerID = o.customerID

Notice that each table referenced has its own WITH BUFFERING clause. If no BUFFERING clause is used, Visual FoxPro 9 respects the SET SQLBUFFERING value (whose default is .f.).

Support for BUFFERING is only available on local Visual FoxPro 9 data. It is not supported on data from back-end databases, so it should not be used with SQL Pass Through.

When working with a table that is involved in ROW buffering, using the WITH BUFFERING command causes the current record to be committed before the statement is executed.

The SET SQLBUFFERING command is scoped to the current data session. When the WITH BUFFERING clause is used, it overrides the SET statement.

CAST()

The new CAST() function is modeled after the SQL Server function by the same name. It is useful both in and out of SQL. Used inside a SQL statement, you can write SQL code that is more TSQL compliant. As you might expect, this function converts a data expression to another data type.

Used within a SQL statement, the CAST() function looks like this:

SELECT CustomerID, ;
       CAST(nAmount*nRate AS N(8,2)) ;
  FROM SALES

SELECT CustomerID, ;
       CAST(nAmount*nRate AS B NOT NULL) ;
 FROM SALES

SELECT CustomerID, ;
       CAST(nAmount*nRate AS C(10)) ;
 FROM SALES

SELECT foo.*, ;
       CAST(NULL as I) AS IntegerField ;
  FROM foo

Notice that there is the ability to specify whether NULLs are acceptable. If not specified, CAST() inherits NULL behavior from the expression, if possible.

ICASE()

Another function, ICASE() emulates TSQL's CASE branching construct. This function is similar to IIF(), the immediate IF function. The value of ICASE() is that it doesn't require the kind of ugly and verbose nesting syntax of IIF().

ICASE() works by requiring condition/result parameter pairings. The first parameter is the condition expression to evaluate, and the second parameter is the result if the condition provided in the first parameter evaluates to True. If the condition evaluates to False, the second parameter is skipped and the next condition/result parameter pair is evaluated. This continues for each parameter pairing. If the parameters are not passed in pairs, Error # 11 is thrown.

Just like the CASE/ENDCASE syntax, there is an Otherwise option that can be passed as the last parameter to the function. If this parameter is not passed and all other condition parameters evaluate to False, ICASE() returns a NULL.

Here is an example of ICASE() used outside of a SQL statement:

nHour = HOUR(DATETIME())
? ICASE( nHour = 8,  "breakfast" , ;
         nHour = 10, "caffeine"  , ;
         nHour = 12, "lunch"     , ;
         nHour = 15, "caffeine"  , ;
         nHour = 18, "dinner"    , ;
         "snack" ;
        )

Up to 100 condition/result parameter pairs can be passed to the ICASE().

SYS(3092) Output to a File

This new SYS() function works in conjunction with SYS(3054), the SQL Showplan function. With SYS(3092), you can specify a filename to which the results of SYS(3054) output are sent.

An example of how to use these functions together is listed here:

SYS(3054,12,"dummyVar")
SYS(3092,"ShowPlan.txt")
OPEN DATABASE HOME(2)+"Northwind\Northwind"

SELECT * ;
  FROM Customers INTO CURSOR temp1

SELECT * ;
  FROM summary_of_sales_by_year ;
  INTO CURSOR temp2

SYS(3092,"")
CLOSE DATA ALL
MODIFY FILE Showplan.txt NOWAIT

If you don't include a variable name as the third parameter for SYS(3054), results are echoed to the current window.

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.

VarChar

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.

VarBinary

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: Comparing Character and VarBinary data types looks like this.

BLOB

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. BLOBs 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 its content is treated 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.

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

Upgrading

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, 0 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 8 to 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.