Refresch screen via network

[Marc Vanzegbroeck]

Hello,

I have a planning-program that is used by 20 people on a network.
Each time that someone change something, the screens of the other users must be updated.
Now I have build a timer that check each 10seconds a field in a database. When it is changed, I refresh the screen.
It's working very nice.
I was wondering that there is an other way to activate the refresh?
Is the a class like 'onchange evaluate function'?
The problem is that now i can take 10seconds to update the screen. I can make the time shorter, but don't want to overload the program.

Thanks,
Marc

[MarcoBoschi]

In a phone directory program
in the table there is a index key for a field containing DTOS(DATE()) + STRTRAN(TIME(),":","")
Now is "20120111113632"
When a record is modified in this field I write DTOS(DATE().....


I define a timer with interval 1000 ACTION update_LIST( )

In FUNCTION UPDATE_LIST()
I match this field with STATIC cLastUpdate containing the last value (if modified)
If tyey are different a oBrw:refresh() is performed.

[Marc Vanzegbroeck]

Marco,

That's what I'am doing now. When someone change something, I increase the value of a numeric field.
I have define a timer that calls a function that check each 10 seconds if the value has been change.

Regards,
Marc

[MarcoBoschi]

Perfect

[reinaldocrespo]

When working with ADS you can issue a database notification. Then have the clients subscribe to that notification. That works really nice. If you are using ADS, then I can send you some code that shows you how to do it. The nice thing is that while a notification isn't received, you don't refresh.

If you are not using ADS, then I think your approach is the simplest. I would think that something similar to ADS notifications could be implemented so that whenever an event happens a message is broadcasted over tcp/ip then have a thread on your .exe always listening for messages with certain signature.


Reinaldo.

[Marc Vanzegbroeck]

Reinaldo,

I was using DBF-files, but now I have changed to SQLite. I don't know that it i also possible to send a database notification.

Regards,
Marc

[Marc Vanzegbroeck]

Reinaldo,

It seems that SQLite also have database notification.
Can you give me an example how you you this with ADS.

Regards,
Marc

[fraxzi]

Hi,

ADS Notification is a great example. Put a trigger.. The client can received and do some action..
All you need to do is to know which dialog is active and do the ::update() or ::refresh() to reload variable's value..

2 cents.

[fraxzi]

Reinaldo,

It seems that SQLite also have database notification.
Can you give me an example how you you this with ADS.

Regards,
Marc

First.. create a trigger 'AFTER UPDATE' with sp_signalevent( [see documentation for parameters..] ) on your Table inside Data Dictionary..

Second.. create a second ADS connection on you app.. you will use this connection to listen for the trigger.. with sp_CreateEvent( [see documentation for parameters..] )..

Third.. set a timer on your app to check for incoming signal event.. with sp_WaitForAnyEvent( [see documentation for parameters..] ).. if the EventCount returned non-zero then you have an update
event.. do your stuff..


What I don't like is the timer thing in xHarbour... if should be a thread.. constantly monitoring some-kinda-like-that... multi-threading in xHarbour may not be fully developed.. correct me if i'm wrong.


Reinaldo is certainly expert in ADS.. I'm just a student.

[Marc Vanzegbroeck]

Thanks Frances forte information.

I will check that this is also possible with SQLite.

Marc

[reinaldocrespo]

Hi.

I'm a student of ADS just like Frances and I have to add that I've learned things from him too.

The notification itself is issued by the server in response to an action as defined on a trigger. The triggers needs to be created only once. In my case, the trigger gets created by the init program that creates a new database or when updating a datadictionary. Here is sample source code to create the trigger from code:

Code: Select all  Expand view  RUN

   oAdsQuery:cSql += ;
      "CREATE TRIGGER [SignalEvent AfterUpdate] ON encounters AFTER UPDATE BEGIN \n" + ;
      "   DECLARE @EncTotChrgs MONEY;                                    \n" +;
      "   DECLARE @OldTotChrgs MONEY;                                    \n" +;
      "   DECLARE @cEncounter CHAR( 13 );                                \n" +;
      "                                                                  \n" +;
      "   SET @EncTotChrgs = ( SELECT EncTotChrgs FROM __new );          \n" +;
      "   SET @OldTotChrgs = ( SELECT EncTotChrgs FROM __old );          \n" +;
      "   SET @cEncounter = ( SELECT encounter FROM __new );             \n" +;
      "                                                                  \n" +;
      "   IF @EncTotChrgs <> @OldTotChrgs THEN                           \n" +;
      "      EXECUTE PROCEDURE sp_SignalEvent(                           \n" +;
      "                        'EncChrgsUpdated',                        \n" +;
      "                        True, 2,                                  \n" +;
      "                        @cEncounter + ',' +CAST( @EncTotChrgs AS SQL_CHAR ) );\n" +;
      "   END;                                                           \n" +;
      "                                                                  \n" +;
      "END NO MEMOS PRIORITY 1;                                          \n" +;
      "                                                                  \n"

   oAdsQuery:Run()
 

I agree with Frances, it would be much better if you could spawn a new thread only to wait for notifications. xHarbour does not seem to work well with multithreads. However, here is outline of how to listen for notifications from the server without a timer using xharbour background tasks. The code is in production and works well.

Code: Select all  Expand view  RUN

      //create the event notification using stored procedure sp_createevent()
      "EXECUTE PROCEDURE sp_CreateEvent( 'EncChrgsUpdated', 2 ); \n"
...
    //wait for events using stored procedure
      //this script gets executed inside ListenForMyEventOnBckgrnd().  
       //We are only initializing here.
    ::oEventQuery:cSql := " \n" +;
    "EXECUTE PROCEDURE sp_WaitForEvent( 'EncChrgsUpdated', 0, 0, 0 ); \n"
...

   ::nIdle := hb_IdleAdd( {|| hb_BackGroundRun() } )
   ::hTask := hb_BackGroundAdd( {|| ::ListenForMyEventOnBckgrnd() }, _WAIT, .T. )
...
//this method gets executed on the background by hb_backgroundAdd().

/* This method will listen for a specific ADS notification from the server on the
background.  oEventQuery sql statment is defined at the beginin in method :New().  The
specific notification we are interested on is called: EncChrgsUpdated
The only notification from the server that we are interested here
are notifications on new charges begin posted so that the StatusPanel xbrowse
as well as the graphs may be updated on real time.
*/
METHOD ListenForMyEventOnBckgrnd() CLASS StatusPanel
   LOCAL aParms := {}
   LOCAL nPos := 0
   LOCAL lChanged := .F.
   LOCAL aLine, nAdd, aRow

   ::oEventQuery:aResultSet := {}

   //Listening timeout is set to zero.  Everytime this background method runs
   //it will check if a notification has been issued since last check.
   ::oEventQuery:cAlias := NIL
   ::oEventQuery:Run()

   /*Uncomment the line below to monitor this backgound task working
   DEBUG ::oEventQuery:aResultSet  /**/

   //update and refresh graph + xbrowse grid with new info.  :aResultSet contains
   //the encounter number and EncTotChrgs amount separated by a comma.
   FOR EACH aLine IN ::oEventQuery:aResultSet

      aParms := hb_aTokens( aLine[ 3 ], "," )

      IF aLine[ 2 ] < 1 .OR. LEN( aParms ) < 2    ;LOOP   ;ENDIF

      ::oUpdtQry:aParameters := { aparms[ 1 ] }
      ::FetchSPData( ::oUpdtQry )

      //::oUpdtQry:aResultSet should never be empty.  This is just a precaution.
      IF EMPTY( ::oUpdtQry:aResultSet )   ;LOOP      ;ENDIF

      IF ( nPos := AScan( ::oAdsQuery:aResultSet, { |e| e[ COL_ENCOUNTERID ] == aParms[ 1 ] } ) ) > 0

         ::OnUpdateRow( nPos, ::oUpdtQry:aResultSet[ 1 ] ) //, Val( aParms[ 2 ] ) )

      ELSE //new Encounter has been inserted into Encounters.adt

         ::OnInsertRow( AClone( ::oUpdtQry:aResultSet[ 1 ] ) )

      ENDIF

      lChanged := .T.

   NEXT

   IF lChanged
      ::oBrw:Refresh()
      ::UpdateGraph( .t. )
   ENDIF

RETURN NIL
 

Again, this is fwh + xHarbour 1.2.1 + bcc 5.82 code that works with ADS 10 perfeclty. You can always read the help files and search about ADS notifications system on the devzone to see more detail.

Best regards,



Reinaldo.

[fraxzi]

Thanks Reinaldo for enlightening us!


Marc, exactly not the timer.. Like Reinaldo.. I use the background task too..


I hope you find your solution.. and share here with us.

[Marc Vanzegbroeck]

Thanks Reinaldo for the example.

I will try to make it work with SQLite and let you know the result.

Regards,
Marc

[Marc Vanzegbroeck]

Reinaldo,

I have look to your example and add a trigger to the database that increase a field in a table (same field that I have used before that whas checked by te function called by the timer), each time something changed in the database.

Then I use the 'hb_BackGroundAdd' function to call by function that check if the field is changed, and refresh the sceen if so.
Is this than the same as you do? I see that you use EVENTS in the SQL, but SQLite doesn't have that, but I think you only use that event to change a filed in the SQL to let the Background know that something is changed. Is this correct? I did this directly in the trigger.

The table 'refresh' is holding the field 'nr' that in incremented each time something change.

Code: Select all  Expand view  RUN

CREATE TRIGGER planning_update UPDATE ON planning BEGIN UPDATE refresh SET nr = nr + 1 where ID = 1;END;

And in the background function I check the change of the field.

What is the value of '_WAIT' that you are using in hb_BackGroundAdd( {|| ::ListenForMyEventOnBckgrnd() }, _WAIT, .T. )?

Thanks,
Marc

[reinaldocrespo]

Code: Select all  Expand view  RUN

#define _WAIT   10000      //10 Seconds
 

On my sample code, ADS will issue a notification (Message id EncChrgsUpdate) anytime the field EncTotChrgs on table Encounters.dbf changes. The waiting background job simply sits (forever) and waits for the event to be signaled to do its thing. Here is some text that explains how event notifications from the server works with ads. And remember, this works with dbf/ntx dbf/cdx, dbf/vfp, adt/adi tables with the "Local server" (as in free) as well as with the Remote server.

Event notifications are a mechanism that allows an action at the server to proactively notify clients that an event they are interested in has occurred.
Clients use the canned procedure sp_CreateEvent to register for event notifications. Once registered, the client can call sp_WaitForEvent or sp_WaitForAnyEvent to efficiently wait for the event to be signaled. Events are signaled using the sp_SignalEvent procedure.

The signal function sp_SignalEvent has a parameter that controls whether or not the event is signaled immediately if in a transaction or if it is signaled when the transaction is committed.
Advantage supports many-to-one event delivery. If an event is signaled X times between calls to one of the wait procedures, rather than receiving each signal individually, the wait function returns a count of how many times the event has been signaled since the last wait operation.

If an event is created with the ADS_EVENT_WITH_DATA option, the event can be signaled with a data string which will be returned when the signal is received. Currently only string data is allowed. The typical use of this string data is to provide a method of locating the record or table for which a signal is sent, however any string data can be used.

So, in short, with notifications you never poll (go check if something has happend). Instead, when something does happen you are notified. But you must be listening for the notification (in my case the stored procedure sp_WaitForEvent), otherwise you won't hear it. This would work best with a 2nd thread. But AFAIK, xharbour can't really do threading. If someone has had better luck with multiple threads, please do jump in.

Chris Franz describes how to work with notifications on his blog:
http://blog.advantageevangelist.com/2010/06/ads-10-tip-9-notifications-with-data.html


Reinaldo.