Fox Fakeout: When a SQL-Select cursor is really a table

One of my customers who is a developer described a form he built with the following scenario:

1. Private Datasession, single DBF table
2. Load event method opens the table and runs a SQL-Select of all the records into a cursor
3. Display of the records in the cursor in a grid. Grid RecordSource is the name of the cursor.
4. Observes the data change in the grid when users are changing the data in the base table on other machines in the office.
5. There is not code to rerun the SQL-Select, and no timers on the form.

His question to me is: how the heck does this happen? Good question.

His assumption was that the cursor data was stagnant since he queried it from the base table into the temporary cursor. Initially I agreed with him and we both thought we were going crazy, but then I remembered something I observed many years ago.

When you perform a SQL-Select and the VFP data engine determines it can be Rushmore optimized, behind the scenes VFP creates a new workarea, which is the same as the open table in another workarea via the USE AGAIN, and applies a Rushmore optimized filter to the new workarea. In essence, the cursor is really passing through the raw table information in the grid he used to display the data. So changes made to the table by the other users in the system are reflected in the grid based in the settings of SET REFRESH.

At first we thought we were nuts, and I had to step through the debugger to ensure the grid was really bound to the cursor and not directly to the table.

One way to avoid this gotcha is to use the INTO CURSOR .... NOFILTER clause on the query. It will slow things down a bit since it is forcing the cursor to be written to disk, but you will not get the filtered table, you get a real stagnant cursor.

Hopefully you will remember this situation if you run into the weird and maybe unexpected behavior.