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.
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 */
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.
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.
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)).
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).
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.