Lesson 3: Managing a cursor with open and forEach

Cursors are SQL constructs that behave like a bookmark, pointing to a row and allowing you to step through a series of search results (that is, table rows) one at a time. EGL can manage cursors for you, but it also provides the open and forEach statements to take advantage of cursors.

In the previous lesson, you may have noticed that the delete statement included the keyword noCursor. Whereas the get statement selects one or more rows from an entire table, the delete statement normally operates on a row indicated by a cursor that was created by a previous statement. The noCursor keyword indicates that no such cursor exists and that the statement should use a WHERE clause to identify the row in the same way as the default get statement.

The EGL open statement creates a cursor and indicates a result set, or a set of one or more rows that the cursor can move through. Instead of accessing the cursor directly, you use other EGL statements to retrieve the next row from the result set as indicated by the cursor, or perform operations on the entire result set, using the cursor to step through the rows one at a time.

To create a cursor and result set in EGL, use the open statement to identify one or more rows and give the new result set a name and a temporary variable to put the row indicated by the cursor into:

tempItem Item;
open allExpensiveItems for tempItem;

In this example, tempItem is a single record variable from the SQLRecord part representing the ITEMS table of the database. The code for tempItem specifies that EGL will use the cursor to move rows into this record one at a time. The code open allExpensiveItems gives the result set a name; allExpensiveItems is an identifier, not a variable, strictly speaking.

Like a get statement, EGL creates an SQL SELECT statement from the open statement, which you can make explicit and edit. The default SELECT statement for the open statement example above looks like this:

tempItem Item;

open expensiveItems for tempItem with
  #sql{
    select
      EGL.ITEM.ITEM_ID, EGL.ITEM.NAME, EGL.ITEM.IMAGE, 
      EGL.ITEM.PRICE, EGL.ITEM.DESCRIPTION
    from EGL.ITEM
    where
      EGL.ITEM.ITEM_ID >= :tempItem.ItemId
    order by
      EGL.ITEM.ITEM_ID asc
  };

This default WHERE clause specifies only that each item must have an ITEM_ID value higher than the one before. (EGL manages the SQL DECLARE CURSOR and OPEN CURSOR statements and does not show them in the explicit SQL.)

To select particular rows to add to the result set, you must edit the WHERE clause, just like with the get statement. For example, you can select only the items with a cost higher than a given variable:

tempItem Item;
maxCost Price = 500;

open expensiveItems for tempItem with
  #sql{
    select
      EGL.ITEM.ITEM_ID, EGL.ITEM.NAME, EGL.ITEM.IMAGE, 
      EGL.ITEM.PRICE, EGL.ITEM.DESCRIPTION
    from EGL.ITEM
    where
      EGL.ITEM.PRICE >= :maxCost
    order by
      EGL.ITEM.ITEM_ID asc
  };

Now the expensiveItems result set contains all of the rows from the database with a PRICE column greater than or equal to the maxCost variable, which is set to 500.

To access these rows, use the forEach statement, which runs a block of code in a loop, applying that code to each item in the result set. During each iteration of the loop, EGL moves the row indicated by the cursor into the temporary variable and advances the cursor to the next row in the result set. For example, the following code prints out values from each item with a price greater than 500:

tempItem Item;
maxCost Price = 500;

open expensiveItems for tempItem with
  #sql{
    select
      EGL.ITEM.ITEM_ID, EGL.ITEM.NAME, EGL.ITEM.IMAGE, 
      EGL.ITEM.PRICE, EGL.ITEM.DESCRIPTION
    from EGL.ITEM
    where
      EGL.ITEM.PRICE >= :maxCost
    order by
      EGL.ITEM.ITEM_ID asc
  };
  
foreach (tempItem)
  SysLib.writeStdout(tempItem.Name :: " " ::
    tempItem.Description :: " $" :: tempItem.Price);
end

You can also use get next to step through the results manually. In this case you must detect whether there are any more results in the result set, such as by testing the value of sysVar.sqlData.sqlcode, which is set to zero as long as there are more results in the result set:

tempItem Item;
maxCost Price = 500;

open expensiveItems for tempItem with
  #sql{
    select
      EGL.ITEM.ITEM_ID, EGL.ITEM.NAME, EGL.ITEM.IMAGE, 
      EGL.ITEM.PRICE, EGL.ITEM.DESCRIPTION
    from EGL.ITEM
    where
      EGL.ITEM.PRICE >= :maxCost
    order by
      EGL.ITEM.ITEM_ID asc
  };

// Retrieve the first row.
get next tempItem;

// As long as there are more rows,
// move the next row into the temporary variable
// and print its values. 
while (sysvar.sqlData.sqlcode == 0) 
  SysLib.writeStdout(tempItem.Name :: " " ::
    tempItem.Description :: " $" :: tempItem.Price);
  get next tempItem;
end

The get statement has a variety of other operations that you can use with an open cursor and result set:

When using get next, you can specify the result set, rather than the temporary variable as in the previous example. For example, get next from expensiveItems moves the next row from the result set into the temporary variable; this code is equivalent to get next tempItem. Similarly, you can use the result set name in the forEach statement; forEach (from expensiveItems) is equivalent to forEach (tempItem).
get current retrieves the row currently indicated by the cursor without moving the cursor to the next row.
get first and get last retrieve the first and last row in the results, respectively.
get previous retrieves the row prior to the cursor, the converse of get next
get absolute(position) retrieves the row at a particular position in the result set. For example, get absolute(5) tempItem retrieves the fifth row in the result set, and get absolute(-2) tempItem retrieves the second to last row.
get relative(position) retrieves the row at a particular position relative to the cursor's current position. For example, get relative(3) tempItem retrieves the third row following the cursor's current position, and get relative(-2) tempItem retrieves the second row before the cursor's current position. get relative(0) tempItem is equivalent to get current tempItem.

To use these positional get statements other than the get next, you must indicate that the result set is scrollable, or that you intend to change the cursor's position manually, rather than step through one row at a time from beginning to end. Add the scroll keyword into the open statement as in this example:

open expensiveItems scroll for tempItem;

EGL automatically closes the cursor when it encounters any of several conditions, including a get next statement that returns no result because the cursor is at the end of the end of the result set, another open statement on the same result set, or the end of the program. If you are finished with the cursor and result set, you can close them with the EGL close statement, as in this example:

close expensiveItems;

Once the cursor and result set are closed, get next and other statements that depend on the cursor no longer work.

Stepping though a result set

In this lesson, you will step through a result set with the forEach statement to retrieve items from the database above a certain price.

Create a new program in the programs package named selectItems.

Remove the default code from the program so it looks like this:

package programs;

program selectItems type BasicProgram {}
  
  function main()
  end
  
end

Add a function to handle any errors EGL may encounter while accessing the database:

function handleDBException(exception SQLException)
  SysLib.writeStdout("Error accessing database. "::
    "See Troubleshooting section");
  SysLib.writeStdout(exception.message);
end

You will learn more about handling errors encountered in database access later in this tutorial.

Add a function to retrieve rows from the database based on a Price parameter for the maximum cost:
```
function printExpensiveItems(maxPrice Price in)
  
end
```
The Price dataItem part is based on the DECIMAL primitive type. It is defined in the file eglderbyr7/primitivetypes/data.
If you did not use content assist to add the Price variable in the function declaration, add the following import statement to the top of the file, just below the package statement:
```
import eglderbyr7.primitivetypes.data.*;
```
Remember that you can use content assist to complete the names of keywords and types by typing the first few letters and then pressing Ctrl+Space. When you insert a type with content assist, such as the Price dataItem part, content assist also adds the appropriate import statement to the file.
Within the new function, add an open statement to create a result set and cursor. You will also need a temporary Item variable to hold the row indicated by the cursor:
```
  tempItem Item;
  open expensiveItems for tempItem;
```
Again, if you did not use content assist to insert the Item record type, add an import statement to bring the Record part into scope:
```
import eglderbyr7.data.Item;
```

Make the SQL code explicit by placing the cursor on the open statement, right-clicking, and then clicking SQL Statement > Add. The open statement looks like this:

  open expensiveItems for tempItem with
    #sql{
      select
        EGL.ITEM.ITEM_ID, EGL.ITEM.NAME, EGL.ITEM.IMAGE, 
        EGL.ITEM.PRICE, EGL.ITEM.DESCRIPTION
      from EGL.ITEM
      where
        EGL.ITEM.ITEM_ID >= :tempItem.ItemId
      order by
        EGL.ITEM.ITEM_ID asc
    };

Change the WHERE clause in the explicit SQL code to select only the rows that have a EGL.ITEM.PRICE column value that is greater than or equal to the maxPrice variable passed to the function:
```
  open expensiveItems for tempItem with
    #sql{
      select
        EGL.ITEM.ITEM_ID, EGL.ITEM.NAME, EGL.ITEM.IMAGE, 
        EGL.ITEM.PRICE, EGL.ITEM.DESCRIPTION
      from EGL.ITEM
      where
        EGL.ITEM.PRICE >= :maxPrice
      order by
        EGL.ITEM.ITEM_ID asc
    };
```
Remember to include the colon before the maxPrice variable to indicate that it is an EGL variable, not an SQL keyword or value.

After the open statement, add a forEach loop that prints the information about the items to the console:

  forEach (tempItem)
    SysLib.writeStdout(tempItem.Name :: " " ::
      tempItem.Description :: " $" :: tempItem.Price);
  end

The completed function looks like this:

function printExpensiveItems(maxPrice Price in)
  
tempItem Item;
open expensiveItems for tempItem with
  #sql{
    select
      EGL.ITEM.ITEM_ID, EGL.ITEM.NAME, EGL.ITEM.IMAGE, 
      EGL.ITEM.PRICE, EGL.ITEM.DESCRIPTION
    from EGL.ITEM
    where
      EGL.ITEM.PRICE >= :maxPrice
    order by
      EGL.ITEM.ITEM_ID asc
  };
  
forEach (tempItem)
  SysLib.writeStdout(tempItem.Name :: " " ::
    tempItem.Description :: " $" :: tempItem.Price);
end

Call the function from the main function and pass a value for maxPrice:

function main()
  try
    printExpensiveItems(200);
  onException(exception SQLException)
    handleDBException(exception);
  end
end

Generate and run the program.

The program prints out the names of the items for sale in the database with a price equal to or greater than the value passed to the function. For example, passing 200 to the function yields the following results:

Laptop PC Great little machine.  Take it anywhere with you. $1111.11
Combination Safe Keep your valuables safe and secure.  Put 'em in here. $222.22
Desktop PC Lots'a power, at the right price.  Includes monitor. $888.55

Here is the complete code of the selectItems.egl file. If you see any errors marked by red X symbols in the file, make sure your code matches the code in this file:Completed selectItems.egl file after lesson 3.

Lesson 3: Managing a cursor with open and forEach

Stepping though a result set

Lesson checkpoint