I know this is probably a more advanced topic and I’m most likely not quite ready for it yet, but I’d like to go ahead and put this out there now anyway in the hopes that there might be a relatively simple way for this to be implemented.
I have a Java application using a Swing GUI that I’m porting over to be a web app with Vaadin. Most of the time, the app will be displaying a treetable full of data along with some other components. The data is pulled from a MS SQL Server 2012 database. Currently, my Swing desktop application just refreshes the treetable every 30 seconds (using a Swing.Timer) with the data from the database.
I’m sure I can do the same thing once I port this over to a Vaadin web app, but I’d like to see if I can take it one step further: instead of just updating every 30 seconds, have it automatically update whenever the database tables are updated. Obviously, this means some sort of tweaking has to be done to the MS SQL Server so that it sends out some kind of “signal” to inform the web server that an update just occurred and that it should refresh all the clients that are currently connected to it. Does anyone have an idea on how this can be done, or even
if it can be done?
If the database is populated through your business logic (and not directly from some other source), then you should be sending that event, that now the data has changed. I’m not sure if MS SQL even has any events for modifications - at least I haven’t heard of such a feature (I might be wrong). Anyway, check
this forum post from yesterday, it discussed this exact same problem.
Additionally to what i wrote on the post Kim linked to, you might be able to either:
A: Create a “Trigger” for Ms SQL which then sends the information that an update occured to the Vaadin app. (For example by sending a Http message to the app’s servlet. (Never really worked with triggers myself but mysql and oracle has them and after a quick google search it look like ms sql does to)
B: Create a seperate desktop app that checks for updates every x seconds/minutes/hours and then sends the information to the app.
As my knowledge, trigger will work only with in the database. Means if you call trigger on any insertion within table,
then what ever action is performed by trigger is within in the database only.
Means trigger can’t send any message or response to UI or server by itself. what we can do is , we can update status
column of any other table on insertion of any row within the table with the help of trigger.
but there is also one problem with that, for getting status is updated or not, then also we have to go to database frequently
for checking whether status is updated or not.!!
And with that, problem is remain same. Means on regular interval we have to go to database call on frequent basis.
I don’t know whether trigger has such functionality to send any response to server or UI on insertion of any row in table by itself without any database call.
If yes, Please share with me that…!!
some time ago i had the same problem. I solved it similar Marius mentioned with a trigger.
You need an after insert/update trigger. Here the steps:
As sa user allow usage of MSXML COM object for SQL Server:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
grant exec on sp_OACreate to dbuser
go
grant exec on sp_OAMethod to dbuser
go
grant exec on sp_OAGetErrorInfo to dbuser
go
grant exec on sp_OADestroy to dbuser
go
Then restart SQL Server to take effect …
2) Create trigger which will call webservice in your application server
CREATE TRIGGER [dbo]
.[tr_state]
ON [dbo]
.[tblData]
AFTER UPDATE
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @Id as Int;
Declare @Object as Int;
Declare @url as varchar(100) = 'http://192.168.0.78:8080/web/input3';
select @Id = dataId from inserted;
Declare @Body as varchar(200) =
'{
"id": "' + @epcWE + '",
"state": "update"
}'
Exec sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'post', @url, 'false'
Exec sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
Exec sp_OAMethod @Object, 'send', null, @body
Exec sp_OADestroy @Object
END
This trigger calls a REST Service on my Application Server which updates my UIs.
Be carefull this is only a workaround and performance is not very good.
You can achieve the same with SQL Server CLR Trigger.
Also: Some databases for example Oracle even support sending JMS messages directly through jdbc drivers. Maybe your database also support something similar.
If everything fails i would recommend the way of having a small dedicated “desktop” application to check for updates that can then handle the update in whatever way you want.
It wll likely be several days (if not weeks) before I give any of this a try, but your responses have definitely given me a solid starting point. I’ll no doubt have some questions when I get to that point (such as that performance hit @mstein2 mentioned), but we’ll cross that bridge when we come to it.