SELECT - SQL Command - WITH Clause
The WITH clause allows you to apply a single command to existing SELECT statements without modifying those statements.
For the complete syntax, see SELECT - SQL Command.
[WITH (BUFFERING = lExpr) ]
Parameters
lExpr
Logical data type. The following table lists the values for lExpr.lExpr
Setting
True (.T.)
Data in the SQL-SELECT statement is based on buffered data, if available.
False (.F.)
(Default) Data in the SQL-SELECT statement is based on data written to disk only.
Remarks
The following code shows a summary of the main clauses of the SELECT - SQL Command:
SELECT Select_List
FROM Table_List
...[WITH (BUFFERING = lExpr)]
[WHERE Conditions]
[GROUP BY Column_List]
[UNION Clause]
[HAVING Conditions]
[ORDER BY Column_List]
[INTO Clause | TO Clause ]
[Additional_Display_Options]
For more information about a particular clause of the SQL SELECT command, see the following topics:
If you set BUFFERING to True (.T.), you can query data from a local buffered cursor, which may include records that have been updated but not committed. Otherwise, your results include only records committed to disk.
If the cursor uses row buffering, then current record is committed before the statement is executed.
If the cursor uses table buffering, then the modified current record is saved into the buffer.
The WITH clause to specify BUFFERING is supported on local Visual FoxPro data only and not on data from back-end databases.
If you set BUFFERING to False (.F.), then your SELECT statement pulls data from cursor on disk (which may be different to what is in buffer). If you do not include the BUFFERING statement, then your results are based on the setting for SET SQLBUFFERING Command. By default, your queries pull data from disk. A SELECT statement WITH BUFFERING set to True (.T.) overrides the SET SQLBUFFERING setting.
The WITH BUFFERING option is effective in the following cases:
The work area does not have an alias specified to support the cursor referenced in the statement.
The cursor is not buffered.
The cursor acts as the target for update or delete operations.
Example 1
Description
The following example shows a simple statement referencing a single table. You need to have a work area that has the alias "Customers" in order for the WITH clause to be effective.
Code
SELECT * FROM customers WITH (BUFFERING=.T.)
Example 2
Description
The following example shows that each table referenced in the FROM clause needs a WITH BUFFERING clause.
Code
SET MULTILOCKS ON
CREATE TABLE Table1 (Field1 N(10), Field2 N(10))
CREATE TABLE Table2 (Field3 N(10), Field4 N(10))
CURSORSETPROP("Buffering", 5, "Table1")
CURSORSETPROP("Buffering", 5, "Table2")
INSERT INTO Table1 VALUES (1, 1)
INSERT INTO Table2 VALUES (0, 100)
SELECT Table1.*, Table2.* ;
FROM FORCE Table1 ;
JOIN Table2 ;
ON Field1>=Field3 AND Field2>0 AND Field4<100
REPLACE Field4 WITH 99 IN Table2
SELECT Table1.*, Table2.* ;
FROM FORCE Table1 ;
JOIN Table2 ;
ON Field1>=Field3 AND Field2>0 AND Field4<100
SELECT Table1.*, Table2.* ;
FROM FORCE Table1 WITH (BUFFERING=.T.) ;
JOIN Table2 WITH (BUFFERING=.T.) ;
ON Field1>=Field3 AND Field2>0 AND Field4<100