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:
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. There are some records
for customers from England in this table so this particular
example 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
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
|
