Advanced PowerBuilder

Cursors

Till now, in the embedded SELECT statements, we were able to retrieve only one row. Cursors are useful to retrieve more than one result row, and it allows you to process one row at a time. Similar to the cursor on a computer screen, a cursor indicates the position in the result set. The cursor points to a single row of data and can only scroll forward, one row at a time. In other words, an application can retrieve one single row, or move through the result set, one row at a time.

Declaring and Executing a Cursor

To use a cursor in an application, you need to define it using the DECLARE statement. This allows you to define both the cursor name and the associated SQL statement, you want to execute. Simply defining the cursor with the DECLARE statement doesn't automatically execute the specified SQL statement - it's merely a declarative statement.

Creating a Cursor

When you paste a cursor declaration in your PowerScript, PowerBuilder allows you to select a table and then it creates the template for you.

Before pasting the statement in your script, PowerBuilder prompts for the cursor name.

For example, you could declare as follows, to retrieve all product numbers and descriptions from the product_master:

DECLARE lProductMasterCursor1 CURSOR FOR
SELECT product_no,
item_product_description
FROM product_master ;

lProductMasterCursor1 is the name of the cursor we refer to in the following scripts.

Executing the Cursor

An OPEN statement actually executes the cursor:

OPEN lProductMasterCursor1 ;
If SQLCA.SQLCODE <> 0 then
//
End If

Again, we can use SQLCODE to provide error handling.

Isolation Levels

Watcom supports isolation levels with the OPEN <cursor name> statement, but, PowerBuilder doesn't support it. The isolation level specifies the kind of actions that are not permitted while the current transaction executes. The ANSI standard defines three levels of isolation for the SQL transactions:

Level 1 prevents dirty reads.
Level 2 prevents non-repeatable reads.
Level 3 prevents both types of reads and phantoms.

Dirty Reads

A dirty read occurs when one transaction modifies a row and a second one reads it, before the first has been able to commit the change. If the first transaction rolls back the changes, the information read by the second transaction becomes invalid.

For example, Tran1 reads the row for product_no 10, with a product_balance of 120, and updates it to 100, but the transaction is not yet committed. In the mean time, Tran2 reads the same row and gets the product_balance as 100. Now Tran1 issues a RollBack, which sets the product_balance to its previous value of 120. At this moment, Tran2 thinks the product_balance as 100, when it really is 120:

Non-Repeatable Reads

A non-repeatable read occurs when one transaction reads a row, and then a second transaction modifies that row. If the second transaction commits its change, subsequent reads by the first transaction yield different results.

For example, Tran1 reads the row for product_no 10 and sets the product_balance to 120. Now Tran2 reads the same row, and updates it to 80 and commits. If Tran1 then reads the same row again, it gets 80, instead of 120. That means, reading the same row is resulting in two different values.

Phantom Rows

A phantom row occurs when one transaction reads a set of rows which satisfy search criteria, before a second transaction modifies the data ( through an INSERT, DELETE, UPDATE and so on ). If the first transaction repeats the read with the same search conditions, it obtains a different set of rows.

Suppose Tran1 issues the following SELECT statement:

SELECT count(*) FROM product_master
WHERE product_balance < product_reorder_level;

and gets 75 rows. If Tran2 then does some processing, which makes the product_balance of another 10 items fall below the product_reorder_level, and now when Tran1 issues the same statement again, it gets 85 rows instead of 75.

The difference between "No-Repeatable reads" and "Phantom Reads" is that, the former one is dealing with a single row value, where as the later is dealing with the result from a set of rows.

The FETCH Statement

To retrieve each row in a cursor and load it into host variables, you use a FETCH statement. This statement allows you to fetch one row at a time, moving through the result set, after each FETCH. You need to check SQLCA.SQLCODE to find the end of the result set. Have a look at the following example:

FETCH lProductMasterCursor1
INTO :lProductNo, :lProductDesc ;

Do While SQLCA.SQLCODE <> 100
   // Do While SQLCA.SQLCODE = 100
   // Some processing such as loading into a ListBox, etc.
   FETCH lProductMasterCursor1
      INTO :lProductNo, :lProductDesc ;
Loop

CLOSE lProductMasterCursor1 ;

Once all rows are returned, we close the cursor with the CLOSE command.

Cursors and Transaction Objects

The DECLARE statement uses a SQLCA Transaction Object. If you need to use a different Transaction Object, you have to add a USING <TransactObject_Name> clause at the end of the SELECT part of the DECLARE statement. For example:

DECLARE lProductMasterCursor1 CURSOR FOR
SELECT product_no, product_description
FROM product_master
USING TranObjForSQLServer ;

You can specify the Transaction Object name only in the DECLARE statement. There is no need to specify it in the OPEN, FETCH and CLOSE statements.

Scrollable Cursors

You can also make a cursor scroll. It can move forwards or backwards through the result set, or move to an absolute or relative position in the result set. In other words, an application can retrieve the next or previous row of data, retrieve a specific row of data, or retrieve a row of data which is at a specified distance from the current row.

Even though PowerBuilder supports scrollable cursors, being able to use them depends on the back-end support. You can use the following commands while using scrollable cursors:

FETCH PRIOR
FETCH FIRST
FETCH NEXT
FETCH LAST

If you don't specify any clause after the FETCH, it is NEXT by default. Sybase System 10 doesn't support scrollable cursors, but SQL AnyWhere supports scrolling to an absolute position or relative position in the cursor result set.

Updating Through a Cursor

An application can update or delete a row to which the cursor currently points in the result set, using CURRENT. For example:

UPDATE "product_master"
SET "product_balance" = product_balance + 100
WHERE CURRENT OF lProductMasterCursor1 ;

In this example, instead of using a WHERE clause, we give the name of the cursor. This updates the row at the current cursor position - suppose you fetch four items and issue this command, the fourth row is the one that gets updated. Updating a cursor doesn't change the row position in the result set. There are few points to remember when updating using a cursor:

1.	A fetch, update or delete returns an error, only if any of the columns were changed since the last read, even if the column is not included in the SELECT list. If any of the rows in the result set has been deleted, it creates a hole, and if you try to fetch it, it results in an error. You can get around this by using a Dynamic Scroll cursor. The SQL AnyWhere syntax for declaring a cursor is: DECLARE <CursorName> [ SCROLL | NO SCROLL | DYNAMIC SCROLL] CURSOR FOR <Statement> [ FOR UPDATE | FOR READ ONLY ] You simply specify whether you want scrolling, no scrolling or dynamic scrolling. A dynamic cursor won't return an error, but will skip the changed row and fetch the next row. The final clause of the declaration statement specifies whether you want to allow updates or make it a read-only.
2.	When using aggregate functions, DISTINCT options, GROUP BY clauses, ORDER BY clauses or UNION operators, the cursor is not updateable, also, when you specify FOR UPDATE, the table should have at least one unique index, otherwise it results in an error.
3.	You can't paint this UPDATE statement with a WHERE CURRENT OF clause from the Edit/Paste SQL menu option, if you have declared a cursor in the script. Only when you declare a cursor as a shared, instance or global cursor can you paste the SQL, by selecting Declare from the menu option.

Deleting through a Cursor

To delete the current row in a cursor result set, the procedure is similar to updating:

DELETE FROM "product_master"
WHERE CURRENT OF lProductMasterCursor1 ;

As with updating through a cursor, deleting a row is not allowed if the SELECT list contains aggregate functions or uses a GROUP BY clause.

Home	Previous Lesson: Embedded SQL - PowerBuilder Implementation Next Lesson: Stored Procedures