Fetch-Last Statement (for scrollable cursors)

Syntax

Stat = "fetch" ident "last".

The fetch-last statement sets the current row position of the result set to the last row fetched by a previous sequential fetch-next statement. The fetch-last statement is needed to be able to continue with a fetch-next statement at the row position that was fetched last by a previous fetch-next statement. This must be done when fetch-next statements were interrupted by fetch-row or fetch-set statements that updated the current row position.

Example

pos: current row position in result set
max: last row in result set fetched by a
fetch-next statement
c1:SelArticle... /* max = 1; pos= 1 */
fetch c1 next 20; /* max = 21; pos= 21
*/
fetch c1 row 10; /* max = 21; pos= 10 */
fetch c1 last; /* max = 21; pos= 21 */
fetch c1 next 20; /* max = 41; pos= 41
*/
fetch c1 row 1; /* max = 41; pos= 1 */
fetch c1 next 20; /* max = 21; pos= 21
*/

Example: Fetch-all; Fetch-set

var
v_artname : string;
v_artno, v_price : number;
dcltrans
transaction TMain
var
artname : string;
artno, price : number;
result set c1;
begin
c1: SelArticle();
fetch c1 all;
fetch c1 set;
c2: UpdCurArticle();
end TMain;
dclsql
SelArticle:
SELECT articlenumber, price, name
INTO :v_artno, :v_price, :v_artname
FROM article
WHERE price > 1000
ORDER BY price DESC;
UpdCurArticle:
UPDATE article
SET price = price*0.9
WHERE CURRENT OF c1;

The example shows a transaction that simulates displaying all rows of the result set of the SQL SELECT command SelArticle (fetch c1 all). Subsequently one row of the result set is selected randomly (fetch c1 set) and the selected row is updated with the SQL UPDATE command UpdCurArticle.

Example: Fetch-all; Fetch-set-unique

var
v_artname : string;
v_artno, v_price : number;
dcltrans
transaction TMain
var
i: number;
result set c1;
begin
c1: SelArticle();
fetch c1 all;
for I := 1 to 3 do
fetch c1 set unique;
if eos(c1) then exit end;
c2: UpdCurArticle();
end;
end TMain;
dclsql
SelArticle:
SELECT articlenumber, price, name
INTO :v_artno, :v_price, :v_artname
FROM article
WHERE price > 1000
ORDER BY price DESC;
UpdCurArticle:
UPDATE article
SET price = price*0.9
WHERE CURRENT OF c1;

The example shows a transaction that simulates displaying all rows of the result set of the SQL SELECT command SelArticle (fetch c1 all). Subsequently three different rows of the result set are selected randomly (fetch c1 set) and the selected rows are updated with the SQL UPDATE command UpdCurArticle. If there are no rows left to update (eos(c1)=true), the for loop is canceled.

Example: Fetch-next; Fetch-set; Fetch-last

var
v_artname : string;
v_artno, v_price : number;
dcltrans
transaction TMain
var
I : number;
result set c1;
begin
c1: SelArticle();
while not eos(c1) do
fetch c1 next 20;
for I := 1 to 3 do
fetch c1 set 20 unique;
if eos(c1) then exit end;
c2: UpdCurArticle();
end;
end TMain;
dclsql
SelArticle:
SELECT articlenumber, price, name
INTO :v_artno, :v_price, :v_artname
FROM article
WHERE price > 1000
ORDER BY price DESC;
UpdCurArticle:
UPDATE article
SET price = price*0.9
WHERE CURRENT OF c1;

The example shows a transaction which simulates displaying 20 rows of the result set of the SQL SELECT command SelArticle (fetch c1 next 20). Subsequently three different rows of the displayed rows are selected randomly (fetch c1 set 20 unique) and the selected rows are updated with the SQL UPDATE command UpdCurArticle. Then the next 20 rows of the result set are displayed (fetch c1 next 20). This proceeding is repeated until no rows are left in the result set (while not eos(c1)).

Example: Updating/deleting multiple rows of a result set

dclrand
rUpdEvent: RndBin(0.25);
dcltrans
transaction TMain
var
i: number;
result set c1;
begin
c1: SelArticle();
fetch c1 all;
/* simulate time for user input actions
*/
wait 10.0;
for I := 1 to rows(c1) do
if rUpdEvent then
fetch c1 row I;
c2: UpdCurArticle();
end;
end;
end TMain;
dclsql
SelArticle:
SELECT articlenumber, price, name
INTO *, *, *
FROM article WHERE articlenumber <= 50
ORDER BY articlenumber;
UpdCurArticle:
UPDATE article
SET price = price*0.9
WHERE CURRENT OF c1;

The example shows a transaction that simulates displaying all rows of the result set formed by the SQL SELECT command SelArticle. Subsequently a wait statement is used to simulate user times for entering input data. The random variable rUpdEvent specifies the probability for updating a row in the result set (25% of all rows in the result set should be updated). If a row is to be updated (if rUpdEvent), the row position is set to the row to be updated (fetch c1 row I) and a SQL UPDATE command with a CURRENT OF cursor is executed (UpdCurArticle).

Example: Updating/deleting multiple rows without using scrollable cursors

dclrand
rDelEvent: RndBin(0.1);
var
v_artno: number;
dcltrans
transaction TMain
const
MAX_ROWS := 100;
var
rowCount, I : number;
anoArr : array[MAX_ROWS] of number;
begin
rowCount := 0;
c1: SelArticle2();
while not eos(c1) and (rowCount<MAX_ROWS)
do
rowCount := rowCount+1;
anoArr[rowCount] := v_artno;
fetch c1 next 1;
end;
wait 10.0; /* simulate user think time */
for I := 1 to rowCount do
if rDelEvent then
v_artno := anoArr[i];
c2: DelArticle();
end;
end;
end TMain;
dclsql
SelArticle2:
SELECT articlenumber, price, name
INTO :v_artno, *, *
FROM article
WHERE articlenumber <= 50
ORDER BY articlenumber;
DelArticle:
DELETE FROM article WHERE articlenumber
= :v_artno;

The example shows a transaction that simulates displaying up to MAX_ROWS rows of the result set formed by the SQL SELECT command SelArticle2. The transaction does not use scrollable cursors for result set operations. So the articlenumber of a row in the result set is used to reselect rows in the result set. The while loop fetches the article numbers for the rows into the array anoArr. Subsequently a wait statement is used to simulate user times for entering input data. The random variable rDelEvent specifies the probability for deleting a row in the result set (10% of all rows in the result set are to be deleted). To select the row to be deleted, use the array anoArr.