refresh problem

michael peirc

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

readonly result set / refresh problem

We have a browse (grid) that gets its sql statement optionally from a
query tool. Hence we cannot be sure if it will receive a live result
set. This grid doubleclicks to open a form for editing. If we edit the
form, we need to be able to update the data in the grid without opening
/ closing the query (too slow). But 'refresh', invalidate etc don't do
this.

Any ideas?

cordially
michael peirce

Glen Meredit

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:readonly result set / refresh problem

michael,
I had to something similar for a client. It's been a while. If I recall I
"shared" the data between the two forms. Just let the edit form "use unit"
of the grid form. It will then be able to access all objects - including
tables. then when changes are made on the edit form, then saved they s/b
automatically reflected in your grid. Make sure your edit form is created
after your grid form. The only problem is - you must editform.ShowModal. In
my case that wasn't a problem.
Help?

Quote

michael peirce wrote:
> We have a browse (grid) that gets its sql statement optionally from a
> query tool. Hence we cannot be sure if it will receive a live result
> set. This grid doubleclicks to open a form for editing. If we edit the
> form, we need to be able to update the data in the grid without opening
> / closing the query (too slow). But 'refresh', invalidate etc don't do
> this.

> Any ideas?

> cordially
> michael peirce

Fabi

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:readonly result set / refresh problem

Quote

michael peirce wrote:
>We have a browse (grid) that gets its sql statement optionally from a
>query tool. Hence we cannot be sure if it will receive a live result
>set. This grid doubleclicks to open a form for editing. If we edit the
>form, we need to be able to update the data in the grid without opening
>/ closing the query (too slow). But 'refresh', invalidate etc don't do
>this.

>Any ideas?

I'm not sure to have understood what you meant. could you explain again?

michael peirc

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:readonly result set / refresh problem

Fabio

We have a grid form / edit form as a master detail. They work off a query.
The problem is that the query on the grid can be changed by the user from a
list of stored queries. If the query returns a r/o result set - when we save
on the edit form we have to close/open the query on the grid form to get a
refresh. What we need is a grid.Query.Refresh that will upate the query on
the grid when the query on the form is updated.

thanks
michael peirce

Quote

Fabio wrote:
> michael peirce wrote:

> >We have a browse (grid) that gets its sql statement optionally from a
> >query tool. Hence we cannot be sure if it will receive a live result
> >set. This grid doubleclicks to open a form for editing. If we edit the
> >form, we need to be able to update the data in the grid without opening
> >/ closing the query (too slow). But 'refresh', invalidate etc don't do
> >this.

> >Any ideas?

> I'm not sure to have understood what you meant. could you explain again?

Team

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:readonly result set / refresh problem

TQuery does not support the Refresh method. You have to open/close.

Juan

Quote

michael peirce wrote:

> Fabio

> We have a grid form / edit form as a master detail. They work off a query.
> The problem is that the query on the grid can be changed by the user from a
> list of stored queries. If the query returns a r/o result set - when we save
> on the edit form we have to close/open the query on the grid form to get a
> refresh. What we need is a grid.Query.Refresh that will upate the query on
> the grid when the query on the form is updated.

> thanks
> michael peirce

> Fabio wrote:

> > michael peirce wrote:

> > >We have a browse (grid) that gets its sql statement optionally from a
> > >query tool. Hence we cannot be sure if it will receive a live result
> > >set. This grid doubleclicks to open a form for editing. If we edit the
> > >form, we need to be able to update the data in the grid without opening
> > >/ closing the query (too slow). But 'refresh', invalidate etc don't do
> > >this.

> > >Any ideas?

> > I'm not sure to have understood what you meant. could you explain again?

michael peirc

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:readonly result set / refresh problem

Juan

Yeah, we know. But it's very slow so we've been looking hard for a work around.

cordially
michael peirce

Quote

Juan Jimenez (TeamB) wrote:
> TQuery does not support the Refresh method. You have to open/close.

> Juan

> michael peirce wrote:

> > Fabio

> > We have a grid form / edit form as a master detail. They work off a query.
> > The problem is that the query on the grid can be changed by the user from a
> > list of stored queries. If the query returns a r/o result set - when we save
> > on the edit form we have to close/open the query on the grid form to get a
> > refresh. What we need is a grid.Query.Refresh that will upate the query on
> > the grid when the query on the form is updated.

> > thanks
> > michael peirce

> > Fabio wrote:

> > > michael peirce wrote:

> > > >We have a browse (grid) that gets its sql statement optionally from a
> > > >query tool. Hence we cannot be sure if it will receive a live result
> > > >set. This grid doubleclicks to open a form for editing. If we edit the
> > > >form, we need to be able to update the data in the grid without opening
> > > >/ closing the query (too slow). But 'refresh', invalidate etc don't do
> > > >this.

> > > >Any ideas?

> > > I'm not sure to have understood what you meant. could you explain again?

Didzis Klavin

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:readonly result set / refresh problem

You can speed it up if you
1. double check the design of your query so that it is executed faster
2. redesign it (or a whole data model) so that it returns a smaller result set. Or
teach your customers to group data in smaller portions so your detail count becomes
smaller

3. Crazy idea but might work:

RequestLive:=False;
CachedUpdates:=True;
UpdateObject (assign a valid UpdateSQL which does nothing)

Then simply edit the row after returning from edit dialog for that particular row.
Certainly you are going to have separate SQL and (if cached updates used for edit
dialog) UpdateSQL for this form.
This way you are "fooling" BDE making it think you have updated data in your grid
query.

HTH

Didzis

Quote

michael peirce wrote:
> Juan

> Yeah, we know. But it's very slow so we've been looking hard for a work around.

> cordially
> michael peirce

> Juan Jimenez (TeamB) wrote:

> > TQuery does not support the Refresh method. You have to open/close.

> > Juan

> > michael peirce wrote:

> > > Fabio

> > > We have a grid form / edit form as a master detail. They work off a query.
> > > The problem is that the query on the grid can be changed by the user from a
> > > list of stored queries. If the query returns a r/o result set - when we save
> > > on the edit form we have to close/open the query on the grid form to get a
> > > refresh. What we need is a grid.Query.Refresh that will upate the query on
> > > the grid when the query on the form is updated.

> > > thanks
> > > michael peirce

> > > Fabio wrote:

> > > > michael peirce wrote:

> > > > >We have a browse (grid) that gets its sql statement optionally from a
> > > > >query tool. Hence we cannot be sure if it will receive a live result
> > > > >set. This grid doubleclicks to open a form for editing. If we edit the
> > > > >form, we need to be able to update the data in the grid without opening
> > > > >/ closing the query (too slow). But 'refresh', invalidate etc don't do
> > > > >this.

> > > > >Any ideas?

> > > > I'm not sure to have understood what you meant. could you explain again?

Team

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:readonly result set / refresh problem

Well, this is the kind of problem that usually benefits from giving the
query's SQL statement and the selection requirements some careful
thought. Have you thought about the possibility of refining the query to
return a smaller return set and enhance the performance? Also, give some
thoughts to indexes, if you have a query and are requesting an order by
using fields that are not in an index, the query will be slower than if
you make the order by components into an index.

Juan

Quote

michael peirce wrote:

> Juan

> Yeah, we know. But it's very slow so we've been looking hard for a work around.

> cordially
> michael peirce

> Juan Jimenez (TeamB) wrote:

> > TQuery does not support the Refresh method. You have to open/close.

> > Juan

> > michael peirce wrote:

> > > Fabio

> > > We have a grid form / edit form as a master detail. They work off a query.
> > > The problem is that the query on the grid can be changed by the user from a
> > > list of stored queries. If the query returns a r/o result set - when we save
> > > on the edit form we have to close/open the query on the grid form to get a
> > > refresh. What we need is a grid.Query.Refresh that will upate the query on
> > > the grid when the query on the form is updated.

> > > thanks
> > > michael peirce

michael peirc

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:readonly result set / refresh problem

Didzis

You should come work here! Those were the paths we've been runninng down today.
Suprisingly, non of them quite worked (the query's come from a variety of sources so we
can't be sure what's in them at design time). The updateSQL thing SHOULD have worked
but we ended up having to use open / close after all with a whole lot of meely mouthing
in the background to get performance back to anything approaching normal.

Three cheers for the bde. NOT

Thanks for the help - those were all good ideas.

cordially
michael peirce

Quote

Didzis Klavins wrote:
> You can speed it up if you
> 1. double check the design of your query so that it is executed faster
> 2. redesign it (or a whole data model) so that it returns a smaller result set. Or
> teach your customers to group data in smaller portions so your detail count becomes
> smaller

> 3. Crazy idea but might work:

> RequestLive:=False;
> CachedUpdates:=True;
> UpdateObject (assign a valid UpdateSQL which does nothing)

> Then simply edit the row after returning from edit dialog for that particular row.
> Certainly you are going to have separate SQL and (if cached updates used for edit
> dialog) UpdateSQL for this form.
> This way you are "fooling" BDE making it think you have updated data in your grid
> query.

> HTH

> Didzis

Fabi

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:readonly result set / refresh problem

Quote

michael peirce wrote:
>What we need is a grid.Query.Refresh that will upate the query on
>the grid when the query on the form is updated.

If you need to refresh the data your client shows, you have to do it through
SQL and I don't think there is a faster way to do this in Delphi than using
a TQuery Close/Open pair. If performance is poor I believe that your data
structure on the server should be tuned.

Regards,

Fabio

Other Threads

1. Updating ReadOnly Result Sets w/ MSACCESS

2. Q: Does setting Table.ReadOnly := False set the current record pointer to BOF

3. DBNavigator Refresh and ReadOnly table

4. problems returning live result set from query

5. TADOStoredProc w/result set, clUseServer problem

6. Delphi with Oracle - Case Sensitivity Problem with Live Result Set

7. Problem with output parameters and result set

8. Problems while opening a query with empty result set (Sybase)

9. Problems with Live Result Sets with MS SQL 7

10. Any suggestions? Stored Procedures Result Set problem