Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025

 


Article

How to set the records to batch for a ProDataSet temp-table

« Go Back

Information

 
Article Number000022084
EnvironmentOpenEdge 10.0B
Question/Problem Description
How to set the records to batch for a ProDataSet temp-table
How to limit the number of records sent to the client for a ProDataSet temp-table
How to use the BATCH-SIZE attribute
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
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()..
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()..
Workaround
Notes
Attachment 
Last Modified Date9/13/2015 5:23 PM