How To Requery a SQL-Select Only Changing a Variable

MORE INFORMATION

A common assumption is that to obtain a new result set you must reissue the SQL query. This is not always the case if you have an index set defined on the field used in the WHERE clause. It does not matter if the field in the WHERE clause is part of a compound or single field index.

The reason Visual FoxPro is able to return a different result set is that Visual FoxPro internally sets a FILTER on the indexed field in the WHERE clause.

The following code demonstrates how changing a variable then issuing a BROWSE changes the result set. The code uses the Customer table that has an index tag on the country field. If there is no index that contains country, the result set does not update. In Visual FoxPro 5.0a, use the Customer table in the \vfp5a\samples\data\ directory. If using Visual FoxPro 6.0, use the Customer table in the "c:\program files\microsoft visual studio\msdn98\98vs\1033\samples\vfp98\data\ directory.

Sample Code

   ***** Begin code sample *****
   IF "5.00"$VERSION()
      dbfpath = HOME()+"samples\data\"
   ELSE
      dbfpath = HOME(2)+"data\"
   ENDIF
   CLEAR
   PUBLIC acountry(1),i
   mcountry=" "
   SELECT DISTINCT(country) ;
      FROM dbfpath+"customer" ;
      WHERE !EMPTY(country) ;
      ORDER BY country ;
      INTO ARRAY acountry

   SELECT company,contact,city,country ;
      FROM customer ;
      WHERE UPPER(country)=mcountry ;
      INTO CURSOR QUERY
   BROWSE NOWAIT
   WAIT WINDOW "Hit a key to see next country..." TIME .5

   FOR i= 2 TO ALEN(acountry)
      mcountry=UPPER(acountry(i))
      BROWSE NOWAIT
      WAIT WINDOW "Hit a key to see next country..." TIME .5
   ENDFOR
   RELEASE WINDOW QUERY
   ***** End code sample *****