Board index » delphi » readonly result set / refresh problem

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

 

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

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?

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?

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?

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?

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?

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

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

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