Browse DevX
Sign up for e-mail newsletters from DevX


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

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

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 FlushFileBuffers 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.

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.

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.

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