User Tools

Site Tools


sql_driver_behavior.htm
Navigation:  SQL Drivers >====== Reading data from an SQL server ====== Previous pageReturn to chapter overviewNext 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

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.

sql_driver_behavior.htm.txt · Last modified: 2021/04/15 15:57 by 127.0.0.1