Thread: howto determine rows count to be returned by DECLARE ... SELECT ...

Konstantin Izmailov wrote:
> Dear Community,
> I'm working on implementation of virtual grid using DECLARE... SELECT....
> Advantage of virtual grid is that it loads only rows that a user is willing
> to see (with FETCH).
>
> However, it is not clear how to determine max rows count that the cursor can
> return. The count is necessary for two purposes: render scrollbar and
> support jumping to the last rows in the grid.
>
> The only obvious solution is to execute SELECT COUNT(1) ... before declaring
> the cursor.
>
> Is there a better solution?

Not really. The whole point of the server is that it doesn't fetch all
the rows, and until you've fetched them all you don't know how many
there are.

> Can the cursor return total rows count or is there a way to position cursor
> to the last row? (Then the number of roundtrips to server can be less by 1
> and virtual grid can render last rows in reverse order).

See the manuals for details on FETCH.

--
   Richard Huxton
   Archonet Ltd

Konstantin Izmailov <pgfizm@gmail.com> writes:

> However, it is not clear how to determine max rows count that the
> cursor can return. The count is necessary for two purposes: render
> scrollbar and support jumping to the last rows in the grid.

You can MOVE LAST, it'll tell you how many rows are in there, then MOVE
FIRST or wherever then FETCH 10 or your page size in rows.

Regards,
--
dim