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.

Related Items

Alvechurch Data - Microsoft FoxPro and Access Database Development and Training

Alvechurch Data are based close to Birmingham and provide Microsoft Access and Visual FoxPro training, development and support.

Read More

Autocomplete in Visual Fox Pro

Autocomplete in Visual Fox Pro

The textbox class in Visual FoxPro 9 has a new Autocomplete property which shows the user the previous values that have been entered in that textbox.

Read More

FoxPro Functions

FoxPro has always had functions like FREAD and FWRITE to read and write files at a low level. They can handle files which defeat the STRTOFILE and FILETOSTR functions.

Read More

Development Services

Alvechurch Data specialise in Microsoft Access and Visual FoxPro databases and have fifteen years experience developing databases for small business use.

Read More

Access and Visual FoxPro Training

Microsoft Access and Visual FoxPro training courses and workshops offered by Alvechurch Data in Birmingham.

Read More