LCDS: Reflect DB updates using SQLAssembler

[Robert_Hirst]

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

[Ed_Solovey__ado]

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

[Robert_Hirst]

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.

[Ed_Solovey__ado · Accepted solution]

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

[Robert_Hirst]

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.