Clarion Community Help

| **Navigation:**  SQL Drivers >====== Reading data from an SQL server ====== | [[logging in to the sql server.htm|{{btn_prev_n.gif|Previous page}}]][[coming future.htm|{{btn_home_n.gif|Return to chapter overview}}]][[server side auto incrementing in clarion 6 3 sql file drivers.htm|{{btn_next_n.gif|Next page}}]] |
|  ||

**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**

{{tipbox.jpg|TipBox.jpg}}

**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.