Creating an array 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:

If all goes well, the SQL statement will create an array of the right size and shape to hold the records retrieved. This example is simple, there are some records for customers from England in this table so this piece of SQL will create an array holding their names.

The real world is more complicated because you will not know whether or not any records are going to meet the criterion of the WHERE clause. 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 test the _TALLY memory variable to test whether any records have been returned:

You don't get this problem when using SQL to send information to other destinations such as a cursor or table. FoxPro can create an empty cursor or table if no records happen to be selected but it cannot create an array of length zero.

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

Strangely enough, the _TALLY memory variable is not read-only. You can alter the value that it holds:

but it's difficult to know why you'd want to do this.