next »

[David H.]

Hi All,

As much as I like the paged mode display array, I have a problem that the number of records to be displayed must be known in advance. If the data being displayed is sourced from a database (and it usually is) then you would expect this to be derived by a SELECT COUNT(*) statement, which can take a while to run if the number of rows is large, regardless of indexing. This additional delay has been noticed by our users as it can add several seconds to the time they have to wait for their data to appear...

So I was wondering if we could have a virtual scrollbar paged mode option, which does not require the total number of records to be known in advance, where the scrollbar continues to operate, rescaling itself as the user scrolls or pages through the array. If the user scrolls to the end, and the total number of records is known and the scroll bar behaves normally.

This is how we deal with this situation currently on v3.5.xx BDL where we can use the undocumented ON KEY(arraygoto) and fgl_scrollbarcurr() to achieve this behavior.

Any thoughts/suggestions?

David

[Sebastien F.]

Hi David,

Fast answer, did not test this:

Maybe you could try following:

- Define the initial number of rows as big as possible (should be a 4bytes int).
- Start the DISPLAY ARRAY, let the user page/scroll and count the rows during ON FILL BUFFER.
- Once you reach the end of the result set (NOTFOUND), you can reset the number of rows with set_count() or DIALOG.setArrayLength("sr",n)

Cheers,
Seb

[Sebastien F.]

David,

I think I answered too fast: After doing some tests is appears that changing the number of rows (with DIALOG.setArrayLength()) inside ON FILL BUFFER makes the dialog loop...

Before we had paged DISPLAY ARRAY, I always suggested programmers to limit the number of rows retrieved by a query.
This is still true with paged mode:
Does it really make sense to let the end user browse in 5 000, 10 000 or 100 000 rows?
Should he not refine the search instead?

So I would suggest following programming pattern:

1) Issue the CONSTRUCT and get the WHERE part
2) Enable SQL interruption to let the user cancel the queries (this works now with almost all databases in 2.10)
3) Execute the SELECT COUNT(*) to get row count; Handle SQL interruption and get back to CONSTRUCT if asked.
4) If the row count is greater as 500 or 1000 rows, warn user and ask if he wants to refine search -> get back to CONSTRUCT.
5) Start the page DISPLAY ARRAY with the scroll cursor...

Seb

[Rene S.]

Hello David,
your request is very legal.
Counting the data before calling DISPLAY ARRAY is annoying (and costs
resources).
I clearly point out: the ON FILL BUFFER feature was introduced to use
any number of rows in the DISPLAY ARRAY instruction.

I would like to implement your request by setting the arr_count to -1.
What's the idea: until the real array size is unknown, we guess the size to
be one page greater then the maximum row-index we ever used.

See this really working example (notice 2.10 is required. You must set
the environment variable FGL_USENDIALOG to use the new multiple dialog
implementation in traditional DISPLAY ARRAY).

This is similar what you can obtain when testing queries in SQL server,
they have the same growing scrollbar.

Rene

--this file: fill_buffer.4gl
DATABASE stores
MAIN
    DEFINE a DYNAMIC ARRAY OF LIKE systables.tabname
    DEFINE l, s, i, guessed_count, real_count, max_row, page_size INT

    OPEN FORM f FROM "fill_buffer"
    DISPLAY FORM f

    LET page_size = 5
    LET max_row = 0
    LET guessed_count = page_size
    LET real_count = NULL
    CALL set_count(guessed_count)
    DECLARE cu SCROLL CURSOR FOR
    SELECT tabname FROM systables
    OPEN cu

    DISPLAY ARRAY a TO r.*
    ON FILL BUFFER
        CALL a.CLEAR()
        LET s = fgl_dialog_getbufferStart()
        LET l = fgl_dialog_getbufferLength()
        IF real_count IS NULL THEN
            DISPLAY "on fill buffer:", s, s + l - 1, guessed_count
        END IF
        FOR i = 1 TO l
            FETCH ABSOLUTE s cu INTO a
            IF status == NOTFOUND AND real_count IS NULL THEN
                LET real_count = s - 1
                DISPLAY "setArrayLength - finally: ", real_count
                CALL DIALOG.setArrayLength("r", real_count)
                CONTINUE DISPLAY
            END IF
            LET s = s + 1
        END FOR
        -- next block is not required if the runtime supports
        -- ON FILL BUFFER and unknown arr_count.
        IF real_count IS NULL AND s - 1 > max_row THEN
            -- We have been asked for a row we've never seen
            LET max_row = s - 1
            DISPLAY "max_row:", max_row
            -- guess we have more rows...
            LET guessed_count = max_row + page_size
            CALL DIALOG.setArrayLength("r", guessed_count)
            DISPLAY "setArrayLength - guessed: ", guessed_count
        END IF
    END DISPLAY
END MAIN

--this file: fill_buffer.per
LAYOUT
GRID
{
<t t1        >
 array
[a1          ]
[a1          ]
[a1          ]
[a1          ]
[a1          ]
}
END
END
ATTRIBUTES
a1=FORMONLY.a1;
INSTRUCTIONS
SCREEN RECORD r(a1);

[David H.]

Hi Rene,

Yes thanks, that is exactly what we are looking for...! If in a future version this can be implemented as you say by setting the arr_count to -1, do you think it will still require FGL_USENDIALOG to function as well?

Cheers,

David

[Rene S.]

Hi David,
> do you think it will still require FGL_USENDIALOG to function as well

future versions of fgl will not need the environment variable FGL_USENDIALOG. The variable controls which code line will be used for traditional dialog statements. The old code line will be removed in a future version. Remember: we have not removed the old code in 2.10 to avoid compatibility problems by adding multiple dialogs.

BTW: the implementation of the request is trivial, should be done in 2.11

Rene

[Neil M.]

Just a small warning:
While it is possible to declare a scroll cursor for a very large record set, some databases actually try and keep the current record set in memory. Allowing users to do such a select could potentially bring your database down or least cause the server OS to seriously slow down while swapping.

Which regards to the virtual scrollbar, this actually in the GWC old versions for a while and the customers involved complained that it made the scrollbar useless to them.
Our email/contact software we use also does this virtual scrollbar type things I personally find it horrible to use.
I'll be surprised if the customers prefer this to a short wait will the count is executed.

[David H.]

Hi Neil,

Thats interesting as all our BDL customers have a virtual scrollbar and we had no complaints until they upgraded to Genero and got delayed by the additional overhead of SELECT COUNT(*), where in worst case scenarios I've seen it take up to a 35 secs to run..!

Thankfully, if Rene implements this only when arr_count = -1 we can both be happy.

Cheers,

David

[David H.]

BTW: the implementation of the request is trivial, should be done in 2.11

Rene

Hi Rene,

Any news on what version this enhancement will appear in? I don't see any mention of it at https://4js.com/techdocs/genero/fgl/devel/DocRoot/User/NewFeatures.html yet...

TIA.

David

[Rene S.]

Hi David,
you're right, 2.11 is out. We put this feature on the 2.20 feature list.
Rene