Loading...
Important Notice - Forums is archived

To simplify things and help our users to be more productive, we have archived the current forum and focus our efforts on helping developers on Stack Overflow. You can post new questions on Stack Overflow or join our Discord channel.

Product icon
TUTORIAL

Vaadin lets you build secure, UX-first PWAs entirely in Java.
Free ebook & tutorial.

Update/refresh display when underlying database is updated

Chris McGee
7 years ago Jul 06, 2015 3:51pm
Kim Leppänen
7 years ago Jul 07, 2015 6:13am
Marius Reinwald
7 years ago Jul 07, 2015 8:00am
Ravinder Singh
7 years ago Jul 07, 2015 10:57am
Michael Steinacher
7 years ago Jul 07, 2015 12:36pm

Hi,

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:

1) 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.

Regards
Michael

Marius Reinwald
7 years ago Jul 07, 2015 1:22pm
Chris McGee
7 years ago Jul 07, 2015 2:20pm