Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Pete Sass
  Where is Pete Sass?
 Marathon, Ontario
 Canada
 Pete Sass
 Tags
Subject: Flush Command
Thread ID: 224805 Message ID: 224805 # Views: 105 # Ratings: 3
Version: Visual FoxPro 9 SP2 Category: General VFP Topics
Date: Wednesday, April 15, 2009 7:58:57 PM         
   


Afternoon from Canada,

Been several postings on the VFP flush command. Thought I would post this thread just
so everyone understands what this command is all about.

Under VFP 9.0 here is what the command does:
General: Saves table and index changes to disk.
You can use FLUSH to explicitly save to disk all changes that you make to all open tables and indexes. You can also save changes to a specific table by specifying a work area, table alias, or path and name of a currently opened file.
If you do not specify nWorkArea, cTableAlias, or cFileSpec, FLUSH applies to all open tables and indexes in the current data session.
If you use the FORCE parameter: Calls the Windows API FlushFileBuffers function for all affected files but does not apply to temporary files and files opened as read-only.

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 = ).

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.

Hope this info. helps in determining if you need to use the FLUSH command.

Pete "the IceMan", from the Great White North of Canada.

COMPLETE THREAD

Flush Command Posted by Pete Sass @ 4/15/2009 7:58:57 PM
RE: Flush Command Posted by Bernard Bout @ 4/17/2009 6:09:04 AM
RE: Flush Command Posted by Pete Sass @ 4/21/2009 11:01:19 PM