prodataset: on defined batch-size procedure.

Posted by mhtan88 on 29-Oct-2009 12:36

Hi all,

I have a problem here.

Example i have 10k of records. i set batch-size to 100. and once requested from .net. it will return t100records how am i going to handle

to retrieve back subsequence from 101 ~ 10k ?

OpenEdge10.0B

my coding example


DEFINE INPUT  PARAMETER pc_query AS CHARACTER   NO-UNDO.
DEFINE OUTPUT PARAMETER DATASET FOR Dsbank.
DEFINE OUTPUT PARAMETER pc_msg AS CHARACTER   NO-UNDO.
QUERY qBank:QUERY-PREPARE(pc_query).
DEFINE DATA-SOURCE srcbank FOR QUERY qBank.
BUFFER ttacp_bank:HANDLE:ATTACH-DATA-SOURCE(DATA-SOURCE srcBank:HANDLE).
BUFFER ttacp_bank:HANDLE:BATCH-SIZE=100.

DATASET Dsbank:FILL().

regards,

TanMH

All Replies

Posted by Admin on 29-Oct-2009 12:41

Setting batch-size to 0 for the second request should do the job (does so on 10.2A).

Posted by Admin on 29-Oct-2009 13:17

Tan,

I believe you need to save off the NEXT-ROWID value and use it on subsequent calls to the fill. Define a rowid variable (SavedRowid) somewhere global. Then:

DEFINE INPUT  PARAMETER pc_query AS CHARACTER   NO-UNDO.
DEFINE OUTPUT PARAMETER DATASET FOR Dsbank.
DEFINE OUTPUT PARAMETER pc_msg AS CHARACTER   NO-UNDO.
QUERY qBank:QUERY-PREPARE(pc_query).
DEFINE DATA-SOURCE srcbank FOR QUERY qBank.
BUFFER ttacp_bank:HANDLE:ATTACH-DATA-SOURCE(DATA-SOURCE srcBank:HANDLE).
BUFFER ttacp_bank:HANDLE:BATCH-SIZE=100.

IF SavedRowid NE ? THEN

    DATA-SOURCE srcBank:HANDLE:RESTART-ROWID = SavedRowid

DATASET Dsbank:FILL().

IF NOT BUFFER ttacp_bank:HANDLE:LAST-BATCH THEN

    SavedRowid = DATA-SOURCE srcBank:HANDLE:NEXT-ROWID.

ELSE

    SavedRowid = ?.

Not tested, but we do the same type of thing using dynamic buffers.

Posted by mhtan88 on 29-Oct-2009 19:40

thanks for reply.

Hi Jim,

Because i'm using .Net call AppServer (by using Prodataset).  So, I can't have any global variable can be save.

so the value SavedRowid can i make it as input-output paarameter?

sorry i want to make it clear, is it the SavedRowID mean. like example i set my batch-size to 100.

savedrowid first time is remember first batch? and if i want to get the second batch from 101 ~ 200.

i judo something like saverowid + 1 will do?

Thank you.

TanMH

Posted by Admin on 30-Oct-2009 04:21

The NEXT-ROWID property at the end of the first batch will return the rowid of the 101th record.

IF NOT BUFFER ttacp_bank:HANDLE:LAST-BATCH THEN

SavedRowid = DATA-SOURCE srcBank:HANDLE:NEXT-ROWID.

When you set the RESTART-ROWID property of the Data-Source widget the AVM will start reading from that row (that will be 101th row).

IF SavedRowid NE ? THEN

DATA-SOURCE srcBank:HANDLE:RESTART-ROWID = SavedRowid

You need to transport the SavedRowid (as a parameter etc.) to the .NET client. That should come over to the client as a Byte[] parameter and you need to store it on the client between requests.

Posted by mhtan88 on 30-Oct-2009 05:07

thank you mike...

I will try it out and come back again.

Posted by mhtan88 on 30-Oct-2009 22:51

hi all,

after i test on the statement. and change the saverowid to input-output. and starting all is ok. but after want to get

the second batch from 101 ~ 200record it prompt me an error when it reached DATASET Dsbank.FILL().

"PROMSGS: Cannot reposition on query which is not defined as SCROLLING. (3164)"

DEFINE INPUT  PARAMETER pc_query AS CHARACTER   NO-UNDO.

DEFINE INPUT-OUTPUT SaveRowID as ROWID NO-UNDO.
DEFINE OUTPUT PARAMETER DATASET FOR Dsbank.
DEFINE OUTPUT PARAMETER pc_msg AS CHARACTER   NO-UNDO.
QUERY qBank:QUERY-PREPARE(pc_query).
DEFINE DATA-SOURCE srcbank FOR QUERY qBank.
BUFFER ttacp_bank:HANDLE:ATTACH-DATA-SOURCE(DATA-SOURCE srcBank:HANDLE).
BUFFER ttacp_bank:HANDLE:BATCH-SIZE=100.

IF SavedRowid NE ? THEN

    DATA-SOURCE srcBank:HANDLE:RESTART-ROWID = SavedRowid

DATASET Dsbank:FILL().    /* 

IF NOT BUFFER ttacp_bank:HANDLE:LAST-BATCH THEN

    SavedRowid = DATA-SOURCE srcBank:HANDLE:NEXT-ROWID.

ELSE

    SavedRowid = ?.

is there any of my defination incorrect?

Thank you.

Regards,

Tan

Posted by Admin on 30-Oct-2009 23:23

Ensure your query has INDEXED-REPOSITION

Posted by Admin on 31-Oct-2009 02:25

Where's the DEFINE QUERY Statement for qBank? Add SCROLLING to that statement:

DEFINE QUERY qBank FOR buffer1, buffer2 SCROLLING.
Posted by mhtan88 on 31-Oct-2009 03:07

thank you all.

I tried  INDEXED-REPOSITION but still prompt me the same problem. i'm not sure why.

but i tried to define scrolling behind my buffer and now is working already. thank you you all....

you all save my life

Posted by mhtan88 on 08-Nov-2009 21:20

hi mike,

I found out the Sorting didn't work fine when i pass by pc_query as " for each bank where bank_activerecord = "A"  by bank_entryDate"

the record sorting something become like below:

1) 22/Jan/2006

2) 11/Feb/2006

3) 12/Mar/2007

4) 14/May/2006

the row number 3 suddently become 2007 and then 2006 again. is it this prodataset open query not supported "BY" in query?

I have been using this open query in 4GL progress for so long and there are no problem.

Regards,

TanMH

Posted by mhtan88 on 09-Nov-2009 00:42

hi all,

I found out fill-where-string is able to do sort. but i'm using OpenEdge10.0B05. and when i assign value in side. it will prompt error "tat attribute is not setable"

is there any alternative way? I can't update OpenEdge10.0B to OpenEdge10.1A due to there is a compatible issue on migration.

Thank ou.

Regards,

Tan

Posted by mhtan88 on 16-Nov-2009 18:35

hi all,

I actually found the solution.  Previously I 'm using static temp-table. and this problem have to use dynamic temp-table, datasource, dataset and set tracking-changes = true after fill().

the sorting is base on temp-table 's index. you need to base on what sorting you want and dynamically generate the index arccordingly.

thanks

Regards,

TanMH

This thread is closed