4 Replies Latest reply on Jul 21, 2007 6:19 AM by Robert Hirst

    LCDS:  Reflect DB updates using SQLAssembler

    Robert Hirst Level 1
      I've been using the SQLAssembler quite successfully so far, but I've hit a small snag.

      I'd like to poll for changes to the result of the DB queries and update the clients when a change occurs. Before I'd got into the javadocs in detail I'd assumed that triggering a refreshFill would do what I wanted, and my Java method looked something like this:

      public void refresh()
      {
      DataServiceTransaction dst = DataServiceTransaction.begin(false);
      dst.refreshFill("sql-helpdesk", null);
      dst.commit();
      }

      Having checked the docs I can see that this approach isn't going to work, and that I need to use updateItem on the DST to update each individual changed item, since refreshFill only looks for new or deleted items.

      The first barrier I've hit is that I need to get the object which has changed, and pass it to updateItem. I'm not sure how to go about obtaining the list of objects.

      The way that seems most logical would be to perform the a fill operation on the server which is the same as the Flex clients perform, but store the results on the server, and then at the next poll interval make a new fill and compare it to the last, calling updateItem for each changed item.

      I was hoping there have thought there would be some equivalent to the DataService component as it is used in the Flex client, whereby I could request a service for a particular destination name, and then perform a fill operation. Unfortunately the class named DataService for the Java API seems more to do with managing destinations than actually using them.

      Has anyone else solved the problem of direct changes made to the database not being picked up, aside from clients discarding fills and building them completely from scratch so as to pick up the changes?

      Something which is on my wishlist if I can solve this problem is to extend or adapt the SQLAssembler to actually look for changes rather than just new or deleted items, and then roll out property changes to the clients as part of the refreshFill method. There is probably a good reason why refreshFill doesn't perform the updates, if anyone knows why it doesn't it may save me a journey down a blocked path.

      Also, is the source code for the SQLAssembler available somewhere, or just the HibernateAssembler?

      Thanks in advance,

      Robert Hirst
        • 1. Re: LCDS:  Reflect DB updates using SQLAssembler
          Ed Solovey (adobe)
          Hi Robert,

          It sounds like something other than a Flex client is updating your DB, otherwise LCDS would have taken care of sending updates to all other subscribed clients. In your case, ideally whatever this external event that is updating the DB would also trigger a call to DataServiceTransaction.updateItem() and all clients would be updated. You would then not have to worry about updating clients after the fact. Is this possible for you?

          Ed
          • 2. Re: LCDS:  Reflect DB updates using SQLAssembler
            Robert Hirst Level 1
            Thanks for the reply Ed.

            This is for an internal project which only uses a MDB file, which may be updated by Flex or directly through Access itself, and I'd like any changes made in Access to reflect in Flex fairly rapidly.

            I can to switch to MSSQL Standard or Enterprise if it is 100% necessary, but I'd like to find a solution involving polling if possible, as this is a pretty small scale project which will mainly be used internally, but may also be taken out to customer sites as a demo and will preferably be easy to set up and run on Tomcat and nothing else.

            So assuming for now I want to do things the hard way and use polling instead of using change notification:

            When I call updateItem, I believe the class I need to pass to the method will probably be a Map (it's an Object when in Flex, because I cannot specify a class to use due to an unknown number of columns in my query). I want to use SQLAssembler to get a fill of Map objects, then after another preset interval get another fill of Map objects, compare the two, then use updateItem to tell LCDS which Map objects have changed.

            I imagine one way to get at an SQLAssembler equivalent to the one used in the client side Flex app would be to write code which would parse the data-management-config.xml file, then use the SQLAssembler constructor to make an assembler with identical settings as the relevant destination, then use the fill method on that to build the object list.

            It seems like I'd be reproducing the effort which already takes place when the destination is created, so I am looking at some way to grab the already instantiated SQLAssembler, if such a method exists.

            If that fails or can't be done, I'm considering trying to extend the SQLAssembler class to perform the property change checks when updateFill is called, then use this new class in my destination declaration in the data-management-config.xml file.

            I'd really like to get change polling working, just so I know how it can be done and which point performance issues of this approach makes it necessary to use a server which supports change notification.

            If you have any more ideas let me know; I'm going to carry on hammering away anyway and I'll let you know if I manage to solve this myself.
            • 3. Re: LCDS:  Reflect DB updates using SQLAssembler
              Ed Solovey (adobe) Level 1
              Hi Robert,

              Here is an idea that may be able to save you the work and performance hit of rerunning the fill periodically and maintaining a cache of previously returned values.

              When changes are made through Access, can you define a trigger that would insert the id of the item modified into a changes table? You could then define a server process that would periodically query this table for a list of changed ids since the last execution of this process. For each changed id the process would then query the appropriate table to get the full item and call DataServiceTransaction.updateItem() for it?

              Hope this helps,
              Ed

              • 4. Re: LCDS:  Reflect DB updates using SQLAssembler
                Robert Hirst Level 1
                That seems like the best way to go about things, thanks.

                I don't think normal triggers are supported from straight MDB files under the Jet engine, but there are afterUpdate events which can be fired from forms, so I'll write the code to modify the changes table there, and tell people not to tweak the tables or run update queries as it won't get reflected in the Flex application unless they manually add the row to the changes table.

                Thanks Ed, that's pointed me in the right direction. It'll also add some ammo to the argument that this DB should be moved to a better platform.