SET/NEXT and SET/PREVIOUS Processing (SELECT/ORDER BY)
A SET statement followed by a NEXT in a LOOP structure is the most common Clarion method to process records sequentially. When the SQL Accelerator drivers encounter a SET/NEXT combination, they generate an SQL SELECT statement with an ORDER BY clause based on the KEY component fields. The KEY component fields are determined by the KEY names in the SET statement. For example, the SQL driver translates this Clarion code
Ord FILE,PRE(Ord),DRIVER('SQLDriver'),NAME('ord')
NameDate KEY(+Ord:Name,+Ord:Date),NAME('DateKey')
Record RECORD
Name STRING(12),NAME('NameId')
Date DATE,NAME('OrderDate')
Type STRING(1),NAME('OrderType')
Details STRING(20),NAME('OrderDetails')
END
END
CODE
Ord:Name = 'SMITH'
SET(Ord:NameDate,Ord:NameDate)
LOOP
NEXT(Ord)
!… some processing
END
into a SELECT statement similar to:
SELECT NameId,OrderDate,OrderType,OrderDetails FROM Ord
WHERE (NameID >= 'SMITH')
ORDER BY NameID, OrderDate
The SET(file) statement (to process in file order, not keyed order) only supports the NEXT statement. Any attempt to execute a PREVIOUS statement when processing in file order causes ERRORCODE 80 (Function Not Supported).
NULL Fields
When you read a row with NULL values from an SQL table, the Clarion record buffer contains an empty string for string fields, or a 0 for numeric fields, and NULL(field) returns TRUE for the field. If the field's contents are later changed to a non-empty or non-zero value then NULL(field) returns FALSE.
If you want to change a NULL field to non-null, but still blank or zero, then you must call SETNONULL(field) to reset the null flag.
If you wish to clear a field to NULL that was previously non-null then call SETNULL(field) or SETNULL(record). SETNULL() clears the contents of the field or record and resets the null flag.
When adding a new record to a file, by default all blank fields are added as blank or zero fields, not as NULL. If you want to force a field to be added with a NULL value, then you must call SETNULL(field) or SETNULL(record) to null all the fields.