SELECT - SQL Command - WITH Clause - dv_foxhelp91.hhc


[WITH (BUFFERING = 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.

The following code shows a summary of the main clauses of the SELECT - SQL Command:

	Copy Code
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]

Copy Code

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.

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.

	Copy Code
SELECT * FROM customers WITH (BUFFERING=.T.)

The following example shows that each table referenced in the FROM clause needs a WITH BUFFERING clause.

	Copy 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

Copy 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

SELECT - SQL Command - WITH Clause

Parameters

Remarks

Example 1

Example 2

See Also

Reference

Concepts

Other Resources