Using a "Safe Select" to preserve your grid

A common problem when working with local cursors in VFP is that when they are used as the record source for a grid re-querying the cursor causes the grid to lose some, or all, of its settings. The reason that this happens is that whenever you execute a SQL Select statement in VFP, any existing target cursor gets closed, deleted and re-created. Since the underlying data source to which the grid is bound just got closed and deleted the grid loses its binding.

This is easily demonstrable, consider the following:

*** Run a basic select into a cursor
SELECT * FROM account INTO CURSOR JUNK NOFILTER
? DBF( ‘junk’ ) && Returns F:\TEMP\00002PL5008K.TMP
*** Repeat the query
SELECT * FROM account INTO CURSOR JUNK NOFILTER
? DBF( ‘junk’ ) && Returns F:\TEMP\00002PL500AQ.TMP

As you can see, the second run of the query creates a new cursor that is opened with the same alias as the first. A quick check on disk will show that the first cursor has indeed been deleted. The consequence, for the grid is that it has to re-build itself from scratch. It does this by deleting all of the existing columns and creating new columns for the new record source. The result is, of course, that all column specific settings are lost and, if your grid used custom columns, or controls they are all replaced by base classes (or, since the introduction of member classes, whatever member class was originally specified). However, any instance specific settings are lost and have to be re-created. This is not a good scenario!

One solution to this problem that I often see offered in on-line forum discussions is to set the grid’s RecordSource to an empty string before querying the cursor and restore it afterwards, like this:

*** Save the recordsource and blank it out
WITH ThisForm.Grid
  lcBoundTo = .RecordSource
  .RecordSource = ‘’
  *** Run the new query here
  SELECT  FROM <table> INTO CURSOR 
  .RecordSource = lcBoundTo 
ENDWITH 

And this works fine, providing that the grid displays columns in natural order!
The reason for the proviso is that what actually happens in this scenario is that when you clear the record source for the grid, the control sources for each column are also cleared. When the grid re-binds to the new alias it no longer has specific control sources for its columns and the result is that the grid now displays the data based on its position in the record source. The first column shows column 1 of the record source, the second displays column 2 and so on.

Typically what happens next is that the questioner goes off, tries the solution, finds that their grid is now completely messed up and comes back with a “what do we do now?”. The next stage is usually to suggest that they change their entire methodology and replace the cursor with a parameterized view. This is perfectly valid and a parameterized view will work just fine in this scenario.

However, parameterized views have one serious limitation. Since views must be pre-defined, it is not easy to create a view that will accept an ad-hoc filter condition and it is usually this requirement that has driven the person to use a cursor in the first place. For example, suppose that in a customer enquiry screen the requirement is to allow a user to specify any combination of First Name, LastName, Social Security Number, City, State, Order Number. Constructing a parameterized view to handle this would be difficult, if not actually impossible.

A solution which addresses all of the issues is a technique called a ‘safe select’.

The idea is that since cursors are always created on the local user’s workstation, and are always opened exclusive, we can use the ZAP command to clear, without closing, a cursor. Then, instead of running a query directly into the working cursor we use an intermediate (or ‘dummy’) cursor as the target for the query and simply append the results from that into the working cursor. Here is the code for a ‘safe select’

*** Create the working cursor
SELECT * FROM account WHERE 0 = 1 INTO CURSOR curacct READWRITE
*** Now run the real query
SELECT * FROM account WHERE name LIKE ‘Sm%’ INTO CURSOR curdummy
*** Clear the working cursor and append the results
SELECT curacct
ZAP IN curacct
APPEND FROM DBF( ‘curdummy’ )
USE IN curDummy

Since the working cursor is never closed, there is no effect on any control (including grids) which is using it as a data source. This technique allows us to maintain the flexibility of a cursor without the undesirable side effects caused by the repeated opening and closing as it is re-queried. Of course if you had to write this code every time you want to query a cursor it could get a little tedious, but we have a fully object oriented environment in VFP, why not just add a method to your root form class which accepts two parameters - the query string and the name of the target cursor. Then the code is nicely encapsulated and is available to any form that needs it. You can even augment the code so that if the target alias does not exist, it is created by the method. Here is my basic 'SafeSelect' method which expects to receive a query string that does NOT include the INTO clause - that is added here:

LPARAMETERS tcSql, tcAlias
LOCAL lnSelect, lcSql

*** Preserve Work Area
lnSelect = SELECT(0)

*** Cursor does not exist
IF NOT USED( tcAlias )
  *** Create it directly
  lcSql = tcSql + " INTO CURSOR " + tcAlias + " READWRITE"
  &lcSql
ELSE
  *** Cursor does exist, use a safe select here
  lcSql = tcSql + " INTO CURSOR curdummy"
  &lcSql
  *** Clear and Update the woirking cursor
  SELECT (tcAlias)
  ZAP IN (tcAlias)
  APPEND FROM DBF( 'curdummy' )
  USE IN curdummy
ENDIF

*** Restore work area and return status
SELECT (lnSelect)
RETURN USED( tcAlias )

As for the READWRITE clause if you are still using a version of VFP that does not support this, there is a way to do it, but I am not going to tell you because you should have UPGRADED. There is no reason not to and a lot of very good reasons why you should - the main one being to encourage Microsoft to keep developing VFP for example....