How to use the FetchAsNeeded property in Visual FoxPro (157047)

MORE INFORMATION

You can use the FetchAsNeeded property to optimize data retrieval through a remote view. By setting this property to .T., and setting the FetchSize property to 50, as an example, the query gets only 50 records at a time from the server. Returning a subset of the data results in shorter wait times than returning the entire data set.

The following example demonstrates how the FetchAsNeeded property works. The example assumes that you have a DataSource named "SQLTest" setup for SQL Server's Pubs database:

1. Enter the following code into a program (prg):

        OPEN DATA (SYS(2004)+"samples\data\testdata.dbc")
        CREATE CONNECTION Myconn DATASOURCE "SQLTest" USERID "sa"
        CREATE SQL VIEW Roysched_remote_view CONNECTION  Myconn ;
        AS SELECT * FROM Roysched
   
        =DBSETPROP("roysched_remote_view","view","Fetchasneeded",.T.)
        =DBSETPROP("roysched_remote_view","view","fetchsize",10)
   
        USE Roysched_remote_view

2. Run the program and observe the status bar. You should see "Record: 1/10." This shows that only 10 records have been retrieved into the current cursor.
3. From the Command window issue the command GO 10. The status bar should now read "Record: 10/10."
4. Now issue the command SKIP 1. The status bar should read "Record: 11/20." This shows that once you skip past the last record in the set, Visual FoxPro retrieves the next 10 records.
5. Now issue the command GO BOTTOM and notice that the entire result set is retrieved. Any command that requires to go to the end of file retrieves the entire result from the server. Commands like Browse, Locate For, Count, Report Form, Reccount, and others fetch all the records in the data set.
   
   NOTE: Keep this in mind if you intend to use FetchAsNeeded.
   
   If you want to use this feature with a grid on a form, you should not use any buffering mode for the view, form, or the grid.