At 02:26 PM 26/07/2004 -0500, you wrote:

>I have a TIB_Query that selects rows from a stored procedure. The problem
>is when I call refresh for this TIB_Query using a "RefreshAction" of
>raKeepRowNum or raKeepDataPos, and the current row is the last row of data
>in the result set, the data for that row will not show up in a connected
>TIB_CtrlGrid. All the columns of that row when accessed with "FieldByName"
>return null as well. If I cause a scroll of the dataset programmatically or
>by clicking on any other row in the TIB_CtrlGrid the data will show up both
>on the TIB_CtrlGrid as well as when accessed with "FieldByName".
>
>When I use a RefreshAction of raOpen the refresh works correctly but the
>currently selected row always is the first row, which is not what I want.

The cause is that, of course, there is no table or view underlying the
dataset, so there is nothing for the grid to request. The way selectable
stored procedures work is that, when SUSPEND is called, the engine
literally suspends execution of the SO and waits for the application to
request the most recently generated output row.

When you open a SSP and look at the output in a grid with n visible rows,
you see the first n rows of output. When you scroll, the application
fetches the next n rows...and so on, until the SSP finishes looping. At
that point, all the output rows will be in the buffer.

A refresh clears the buffer and executes the SSP again; so the whole
process of fetching begins again.

You can call a SSP with a WHERE clause on the input parameters, to limit
the rows that will be buffered for the application. This is advisable when
your output is likely to be more than about 200 rows.

>The stored procedure pulls columns from multiple tables. It will sometimes
>even generate an additional row of data as needed by the application. It
>generates a row number for each row it outputs and that column will always
>be unique but if a row of data is removed from the database the row number
>will change on all data that is output after the deleted row so it is not
>necessarily a good candidate for keylinks.

No; ideally you should design your SSP with the key columns as input
parameters, since this binds the keys directly to the procedure's
execution: the input part of the SP then doesn't process rows that the
application doesn't want.

>Does anyone have any idea why this behaves this way. Since the data does
>show up after the result set is scrolled I would assume something is keeping
>it from getting buffered before that time.

Correct; the whole "catchment" of rows doesn't exist in the buffer until
the SP completes. This is different from a regular DSQL select, where you
have a "moving window" of rows in the buffer with large datasets. If the
buffer doesn't have a row that it needs, it dynamically "moves the window"
until the required row is contained in the buffer. This isn't possible
with SSP output, since there are no tables to target with WHERE clauses.

If the output isn't huge, you could try putting the dataset into a
BeginBusy...EndBusy loop before calling Refresh; then call FetchAll on the
dataset instead of Open. Then see whether you get the desired results with
the chosen RefreshAction...

Helen

>Thanks
>
>Jerry Sands
>
>
>
>
>[Non-text portions of this message have been removed]
>
>
>
>
>___________________________________________________________________________
>IB Objects - direct, complete, custom connectivity to Firebird or InterBase
> without the need for BDE, ODBC or any other layer.
>___________________________________________________________________________
>http://www.ibobjects.com - your IBO community resource for Tech Info papers,
>keyword-searchable FAQ, community code contributions and more !
>Yahoo! Groups Links
>
>
>
>