Question on SmartRefresh

erikfandersen · Post by **erikfandersen** » Mon 02 Oct 2006 13:03

Hello!

I have set up my database to work with TCustomSmartQuery.SmartRefresh and it appears to work fine.

I have one question though. In my application I distinguish between a table and a query. For this I use TOraTable and TSmartQuery.

My problem is that I always want my table objects to be syncronized with the underlying database table but query objects should not. A change via a query or a table object should refresh all table objects but a change via a table object should only refresh other table objects.

I have been experimenting with DependEvents and RefreshEvent but can't quite get this to work. Is this at all possible? In all my tests both the table objects and the query objects are refreshed.

Regards,
Erik F. Andersen

Plash · Post by **Plash** » Tue 03 Oct 2006 13:55

If you want that:
- a change via query refreshes all queries and all tables;
- a change via table refreshes all tables only;
then set RefreshEvent and DependEvents properties as following:

Code: Select all

  Query1.RefreshEvent := 'QUERY_CHANGE';
  Query2.RefreshEvent := 'QUERY_CHANGE';
  Table1.RefreshEvent := 'TABLE_CHANGE';
  Table2.RefreshEvent := 'TABLE_CHANGE';
 
  Query1.DependEvents := 'QUERY_CHANGE';
  Query2.DependEvents := 'QUERY_CHANGE';
  Table1.DependEvents := 'TABLE_CHANGE;QUERY_CHANGE';
  Table2.DependEvents := 'TABLE_CHANGE;QUERY_CHANGE';

erikfandersen · Post by **erikfandersen** » Tue 03 Oct 2006 15:46

Thanks,

I have however found one big problem with SmartRefresh. SmartRefresh seem to commit transactions automatically and I don't want this.

I only want the tables and queries in my own session to be refreshed.

Is this possible?

Regards,
Erik

erikfandersen · Post by **erikfandersen** » Wed 04 Oct 2006 07:07

Hello again!

I tracked down the problem to the function SendRefreshEvent in unit OraSmart.

There is a line:
RefreshAlerter.Session.Commit; // commit sending message

that causes the transaction to be commited prematurely. If I change this to:
if (not RefreshAlerter.Session.InTransaction) then
RefreshAlerter.Session.Commit; // commit sending message

then it appears to work as I expected. Tables in my own session are refreshed and the transaction remains active.

Do you think my changes are feasible or is there trouble ahead?

There is another thing though. I'm not sure if this is just Oracle or it is ODAC. Tables in my session does not seem to be refreshed when there is a transaction active in the current session and the table is changed from another session. What do you think?

Regards,
Erik

Plash · Post by **Plash** » Wed 04 Oct 2006 12:13

TSmartQuery component commits transaction only if its AutoCommit property is True. If AutoCommit is False other queries will be refreshed
when transaction is commited. This is designed behavior.

We can not reproduce the problems you have described. Please specify ODAC version you use. If it is possible send to ODAC support address complete sample that demonstrates the problems and include script to create server objects.

erikfandersen · Post by **erikfandersen** » Wed 04 Oct 2006 13:46

I just installed the latest ODAC version for Delphi7 and the problem is the same as I have already described.

My queries have AutoCommit set to true but I believe that the transaction should only be commited by ODAC if there is not already a transaction active.

The line 523 in OraSmart.pas reads:
RefreshAlerter.Session.Commit; // commit sending message

I want to change this to
if (not RefreshAlerter.Session.InTransaction) then
RefreshAlerter.Session.Commit; // commit sending message

AutoCommit is very useful in many situations but imho it is not up to ODAC to commit transactions prematurely if I have explicitely started one from my own code.

Regards,
Erik

Plash · Post by **Plash** » Thu 05 Oct 2006 11:25

We have fixed the problem that SmartRefresh commits a transaction. The fix will be included in next build of ODAC.

The problem that a table is not refreshed when changes are made from another session is probably caused by change that you have made in ODAC source.
To make ODAC work correctly replace line

Code: Select all

  TRefreshAlerter(RefreshAlerter).SelfEvents := not AutoCommit;

with

Code: Select all

  TRefreshAlerter(RefreshAlerter).SelfEvents := not AutoCommit or RefreshAlerter.Session.InTransaction;

and replace line

Code: Select all

  if AutoCommit then begin

with

Code: Select all

  if AutoCommit and not RefreshAlerter.Session.InTransaction then begin

in procedure SendRefreshEvent.