CURSOR (Visual Adelia and Adelia Web Contexts)

Section for use

DECLARATION

Syntax

CURSOR CursorName SeriesEntities Distinct SelectedColumns AdeliaCondition SortOption OptimizeRows MaxRows Block

CURSOR CursorName EntityName Distinct SelectedColumns AdeliaCondition SortOption OptimizeRows MaxRows Upd

CURSOR CursorName DynQuery OptimizeRows MaxRows

Description

This instruction defines a cursor CursorName for the entity or entities SeriesEntities.

It obeys the (optional) condition AdeliaCondition and the sequence for certain fields, being defined by the *SORT parameter, the fields concerned by SeriesSortFields and for each field, the sorting order Order. The order can be either *ASC for ascending or *DESC for descending. Where the Order is not specified, the default setting will be *ASC.

Click here for the explanation of the SortOption element.

By default, the cursor returns the values of all the entities' columns. However, it is possible to retrieve only the values of the columns which are specified in SelectedColumns.

When *DISTINCT is specified, the cursor returns the distinct rows only (considering all the columns or the columns selected in SelectedColumns).

It is possible to have the SQL optimizer improve the query execution in order to retrieve the first n rows of the cursor more rapidly (*OPT parameter).

Moreover, you can also ask the optimizer to retrieve the first m cursor's rows only (*ROWS parameters).

The *BLOCK parameter indicates that reading will be performed by record size block. Even with this parameter, the READ_NX_SQL_C only returns one record at a time (automatic management by the multi-row read buffering instruction).

The *UPD parameter enables the UPD_SQL_C and DELETE_SQL_C instructions to be used with this cursor. It can only be used if the cursor concerns a single entity, and if there are no sorting criteria. In addition, if you sort the cursors, ensure that your sorting function is compatible with the destination DBMS. Certain DBMS systems either do not support sorting conditions or else impose special constraints.

If the cursor is used by the UPD_SQL_C instruction, you must specify the fields in the entity SeriesEntityFields that are to be updated.

When used alone *UPD instructs the system to update all the fields (except the possible auto increment-type field).

Important: On PC platforms, the use of *UPD requires programs to be compiled in

commitment control mode.

The QueryVar parameter is an alphanumeric variable that contains a selection SQL query.

The DynQuery parameter is either an alphanumeric variable containing a selection SQL query or an SQL statement previously declared via the SQL_STATEMENT 4GL instruction.

Important notes relating to update (*UPD) cursors:

• An update cursor's behavior is not defined if the transaction is validated (VALIDATE_UPD) or if the program runs without a validation mode. Depending on the DBMS, the cursor may be closed or behave inconsistently (locks released by the COMMIT).
• Some database management systems do not implement a comprehensive positioned update feature (MySQL, PostgreSQL, certain ODBC drivers, DB2 EveryPlace and SQL Server CE mobile bases). In such cases, positioned updates are performed by generating a selection clause based on the read data.
  It is therefore important that the selected columns include a unique access key for the table, failing which the update operation (UPD_SQL_C or DELETE_SQL_C) could affect more than one row.

• Versions prior to V6R1 of OS/400 do not accept sort fields that are also fields to be updated.

Example

* Declaration of a cursor reading CUSTOMERS and COMPANY, sorted by customer's name.

CURSOR CompCustCur CUSTOMER, COMPANY  *COND(CUS_COMP_COD = CPY_COMP_COD) *SORT(CUS_CUST_NAM)

* Declaration of a cursor reading ORDERS, sorted by amount.

CURSOR Cursor1 ORDERS *COND(OAMOUNT > 1,000) *SORT(OAMOUNT)

* Declaration of a cursor reading ORDERS, with possible update for Item price field.

CURSOR Cursor2 ORDERS *COND(OAMOUNT > 1,000) *UPD(OITEM_PRICE)

* Declaration of a cursor which allows to update all fields from the AGREEMENTS entity,

CURSOR CURS_AGREEMENTS AGREEMENTS *COND(AINSUR_ID = :ZINSU_ID) *UPD

* Only retrieve columns B1 and B2 of the BASE_SQL entity when reading the cursor.

* Tell the SQL optimizer that the first ten rows have to be retrieved as quickly as possible.

CURSOR C1 BASE_SQL *COL(B1, B2) *COND(B > 50) *OPT(10)

** Cursor allowing to retrieve only 2 file's fields and

** the first 100 rows corresponding to the read criterion.

CURSOR CURS_Wine_Type Wine_Type *col(TY_WINE_TYPE_CODE, TY_WINE_TYPE_DESC) -

*cond((TY_MAX_ALCOHOL_PERCENT - TY_MIN_ALCOHOL_PERCENT) > 3) *rows(100)

Other example

In the declaration block:

ALPHA(200) WQUERY

NUM_P(3,0) WFIELD1

CURSOR C1 :WQUERY

In the execution block:

WQUERY = 'SELECT FIELD1 FROM Schema.mytable WHERE FIELD1 > 10'

OPEN_SQL_C C1

READ_NX_SQL_C C1 :WFIELD1

...

CLOSE_SQL_C C1

↑ Top of page