Kbase P87487: How to set the records to batch for a ProDataSet temp-table
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
07/10/2004 |
|
Status: Unverified
GOAL:
How to set the records to batch for a ProDataSet temp-table
GOAL:
How to limit the number of records sent to the client for a ProDataSet temp-table
GOAL:
How to use the BATCH-SIZE attribute
FACT(s) (Environment):
OpenEdge 10.0B
FIX:
The following are two programs that show an example about the BATCH-SIZE attribute.
Batch-size is primarily intended to limit the number of rows added to a top buffer in a dataset, or to a non-top buffer whose parent table probably has only 1 record in it. However, it can be set at any level of the hierarchy.
/*---------------------------------------------------------
Program: wMain.w
note: Run this procedure first.
---------------------------------------------------------*/
DEFINE TEMP-TABLE ttOrder LIKE Order.
DEFINE TEMP-TABLE ttOrderLine LIKE OrderLine.
DEFINE DATASET dsSource FOR ttOrder, ttOrderLine
DATA-RELATION OrderOrderLine FOR ttOrder, ttOrderLine RELATION-FIELDS(ordernum, ordernum).
&Scoped-define WINDOW-NAME C-Win
&Scoped-define FRAME-NAME DEFAULT-FRAME
DEFINE VAR C-Win AS WIDGET-HANDLE NO-UNDO.
DEFINE BUTTON bnNextBatch
LABEL "Get Next Batch"
SIZE 18 BY 1.14.
DEFINE BUTTON BUTTON-1
LABEL "Get First Batch of Records"
SIZE 31 BY 1.14.
DEFINE VARIABLE fiBatchSizeOLine AS INTEGER FORMAT ">>9":U INITIAL 0
LABEL "Batch-Size for OrderLine Table"
VIEW-AS FILL-IN
SIZE 14 BY 1 NO-UNDO.
DEFINE VARIABLE fiBatchSizeOrder AS INTEGER FORMAT ">>>>9":U INITIAL 0
LABEL "Batch-size for Order table"
VIEW-AS FILL-IN
SIZE 14 BY 1 NO-UNDO.
DEFINE VARIABLE fiLastBatchOrder AS LOGICAL FORMAT "YES/NO":U INITIAL NO
LABEL "Last-batch for Order Table"
VIEW-AS FILL-IN
SIZE 14 BY 1 NO-UNDO.
DEFINE VARIABLE tgAppend AS LOGICAL INITIAL no
LABEL "Append"
VIEW-AS TOGGLE-BOX
SIZE 13 BY .81 NO-UNDO.
DEFINE QUERY brOrder FOR
ttOrder SCROLLING.
DEFINE QUERY brOrderLine FOR
ttOrderLine SCROLLING.
DEFINE BROWSE brOrder
QUERY brOrder NO-LOCK DISPLAY
ttOrder.CustNum FORMAT ">>>>9":U
ttOrder.Ordernum FORMAT "zzzzzzzzz9":U
ttOrder.OrderDate FORMAT "99/99/99":U WIDTH 24.6
WITH NO-ROW-MARKERS SEPARATORS SIZE 50 BY 9.52 FIT-LAST-COLUMN.
DEFINE BROWSE brOrderLine
QUERY brOrderLine NO-LOCK DISPLAY
ttOrderLine.Ordernum FORMAT "zzzzzzzzz9":U
ttOrderLine.Linenum FORMAT ">>9":U
ttOrderLine.Itemnum FORMAT "zzzzzzzzz9":U WIDTH 29.8
WITH NO-ROW-MARKERS SEPARATORS SIZE 55 BY 9.29 FIT-LAST-COLUMN.
DEFINE FRAME DEFAULT-FRAME
BUTTON-1 AT ROW 1.48 COL 2
bnNextBatch AT ROW 1.48 COL 35
tgAppend AT ROW 2.91 COL 35
fiBatchSizeOrder AT ROW 3.86 COL 31 COLON-ALIGNED
fiLastBatchOrder AT ROW 5.05 COL 31 COLON-ALIGNED
fiBatchSizeOLine AT ROW 5.05 COL 91 COLON-ALIGNED
brOrder AT ROW 6.24 COL 2
brOrderLine AT ROW 6.24 COL 59
WITH 1 DOWN NO-BOX KEEP-TAB-ORDER OVERLAY
SIDE-LABELS NO-UNDERLINE THREE-D
AT COL 1 ROW 1
SIZE 114.8 BY 15.14.
IF SESSION:DISPLAY-TYPE = "GUI":U THEN
CREATE WINDOW C-Win ASSIGN
HIDDEN = YES
TITLE = "LAST-BATCH and BATCH-SIZE example"
HEIGHT = 15.19
WIDTH = 115
MAX-HEIGHT = 30.19
MAX-WIDTH = 168.6
VIRTUAL-HEIGHT = 30.19
VIRTUAL-WIDTH = 168.6
RESIZE = yes
SCROLL-BARS = no
STATUS-AREA = no
BGCOLOR = ?
FGCOLOR = ?
KEEP-FRAME-Z-ORDER = yes
THREE-D = yes
MESSAGE-AREA = no
SENSITIVE = yes.
ELSE {&WINDOW-NAME} = CURRENT-WINDOW.
ASSIGN fiLastBatchOrder:READ-ONLY IN FRAME DEFAULT-FRAME = TRUE.
IF SESSION:DISPLAY-TYPE = "GUI":U AND VALID-HANDLE(C-Win)
THEN C-Win:HIDDEN = no.
ON END-ERROR OF C-Win /* LAST-BATCH and BATCH-SIZE example */
OR ENDKEY OF {&WINDOW-NAME} ANYWHERE DO:
IF THIS-PROCEDURE:PERSISTENT THEN RETURN NO-APPLY.
END.
ON WINDOW-CLOSE OF C-Win /* LAST-BATCH and BATCH-SIZE example */
DO:
/* This event will close the window and terminate the procedure. */
APPLY "CLOSE":U TO THIS-PROCEDURE.
RETURN NO-APPLY.
END.
ON CHOOSE OF bnNextBatch IN FRAME DEFAULT-FRAME /* Get Next Batch */
DO:
RUN getBatchs.
END.
.
ON CHOOSE OF BUTTON-1 IN FRAME DEFAULT-FRAME /* Get First Batch of Records */
DO:
DATASET dsSource:EMPTY-DATASET().
RUN getBatchs.
END.
ASSIGN CURRENT-WINDOW = {&WINDOW-NAME}
THIS-PROCEDURE:CURRENT-WINDOW = {&WINDOW-NAME}.
ON CLOSE OF THIS-PROCEDURE
RUN disable_UI.
PAUSE 0 BEFORE-HIDE.
MAIN-BLOCK:
DO ON ERROR UNDO MAIN-BLOCK, LEAVE MAIN-BLOCK
ON END-KEY UNDO MAIN-BLOCK, LEAVE MAIN-BLOCK:
RUN enable_UI.
IF NOT THIS-PROCEDURE:PERSISTENT THEN
WAIT-FOR CLOSE OF THIS-PROCEDURE.
END.
PROCEDURE disable_UI :
DELETE WIDGET C-Win.
IF THIS-PROCEDURE:PERSISTENT THEN DELETE PROCEDURE THIS-PROCEDURE.
END PROCEDURE.
PROCEDURE enable_UI :
DISPLAY tgAppend fiBatchSizeOrder fiLastBatchOrder fiBatchSizeOLine
WITH FRAME DEFAULT-FRAME IN WINDOW C-Win.
ENABLE BUTTON-1 bnNextBatch tgAppend fiBatchSizeOrder fiLastBatchOrder
fiBatchSizeOLine brOrder brOrderLine
WITH FRAME DEFAULT-FRAME IN WINDOW C-Win.
{&OPEN-BROWSERS-IN-QUERY-DEFAULT-FRAME}
VIEW C-Win.
END PROCEDURE.
PROCEDURE getBatchs :
DO WITH FRAME {&FRAME-NAME}:
FIND LAST ttOrder NO-LOCK NO-ERROR.
IF tgAppend:CHECKED THEN
RUN fillds.p (INPUT IF AVAILABLE(ttOrder) THEN ttOrder.ordernum ELSE 0,
INPUT INT(fiBatchSizeOrder:SCREEN-VALUE),
INPUT INT(fiBatchSizeOLine:SCREEN-VALUE),
OUTPUT DATASET dsSource APPEND).
ELSE
RUN fillds.p (INPUT IF AVAILABLE(ttOrder) THEN ttOrder.ordernum ELSE 0,
INPUT INT(fiBatchSizeOrder:SCREEN-VALUE),
INPUT INT(fiBatchSizeOLine:SCREEN-VALUE),
OUTPUT DATASET dsSource).
OPEN QUERY brOrder FOR EACH ttOrder NO-LOCK INDEXED-REPOSITION.
OPEN QUERY brOrderLine FOR EACH ttOrderLine NO-LOCK INDEXED-REPOSITION.
ASSIGN fiLastBatchOrder:SCREEN-VALUE = STRING(BUFFER ttOrder:LAST-BATCH)
bnNextBatch:SENSITIVE = NOT BUFFER ttOrder:LAST-BATCH.
END.
END PROCEDURE.
/*---------------------------------------------------------
END wMain.w
---------------------------------------------------------*/
/*---------------------------------------------------------
Program: fillds.p
---------------------------------------------------------*/
DEFINE VARIABLE retok AS LOGICAL NO-UNDO.
DEFINE TEMP-TABLE ttOrder LIKE Order.
DEFINE TEMP-TABLE ttOrderLine LIKE OrderLine.
DEFINE DATASET dsSource FOR ttOrder, ttOrderLine
DATA-RELATION OrderOrderLine FOR ttOrder, ttOrderLine RELATION-FIELDS(ordernum, ordernum).
DEFINE INPUT PARAMETER piLastOrder AS INTEGER NO-UNDO.
DEFINE INPUT PARAMETER piOrderSize AS INTEGER NO-UNDO.
DEFINE INPUT PARAMETER piLineSize AS INTEGER NO-UNDO.
DEFINE OUTPUT PARAMETER DATASET FOR dsSource.
DEFINE QUERY qOrder FOR Order.
QUERY qOrder:QUERY-PREPARE('FOR EACH order NO-LOCK WHERE order.ordernum > ' + STRING(piLastOrder)).
DEFINE DATA-SOURCE dsOrder FOR QUERY qOrder.
DEFINE DATA-SOURCE dsOrderLine FOR OrderLine.
/* attach the data-sources to the dataset buffers */
BUFFER ttOrder:handle:ATTACH-DATA-SOURCE(DATA-SOURCE dsOrder:HANDLE,?,?,?).
BUFFER ttOrderLine:handle:ATTACH-DATA-SOURCE(DATA-SOURCE dsOrderLine:HANDLE,?,?,?).
BUFFER ttOrder:HANDLE:BATCH-SIZE = piOrderSize.
BUFFER ttOrderLine:HANDLE:BATCH-SIZE = piLineSize.
DATASET dsSource:FILL()..