Creating an array in Visual FoxPro with SQL

It's easy to create an array with SQL in FoxPro by using the INTO ARRAY clause in an SQL SELECT statement. This example creates an array of customer details:

SELECT Cust_ID ;
  FROM Customer ;
  INTO ARRAY laCust ;
  WHERE Country = 'England'

The SQL statement will create an array of the right size and shape to hold the records retrieved. This table does hold some records for customers from England so this code will create an array holding their names.

In the real world you will not know whether or not any records are going to meet the criterion of the WHERE clause and if no records match then nothing will be returned and the array will not be created. Any later attempt to refer to this non-existent array will cause an error.

The solution is to use the _TALLY memory variable to test whether any records have been returned:

IF _TALLY = 0
   *-- The array has not been created by the SQL.
   *-- Create a single-element array and store ' '
   *-- in the only element.
   DIMENSION laCust[1]
   laCust[1] = SPACE (1)
ENDIF

You don't get this problem when using SQL to create a cursor or table. FoxPro can create an empty cursor or table if no records are selected but it cannot create an array of length zero.

FoxPro maintains the value in _TALLY automatically and updates it whenever any of the following commands are used:

  • APPEND FROM
  • AVERAGE
  • CALCULATE
  • COPY TO
  • COUNT
  • DELETE
  • INDEX
  • PACK
  • REINDEX
  • REPLACE
  • SELECT - SQL
  • SORT
  • SUM
  • TOTAL
  • UPDATE

Strangely enough, you can alter the value held in the _TALLY memory variable:

_TALLY =1

Goodness knows why this should have read-write status.