Java + Ms SQL Server 2008 R2 after insert trigger notification

Question

I need to write web application using java webgui Vaadin Framework which will be real-time updated with new records inserted to MsSQL database by MsWin2k8R2 NPS.

Since NPS stores logs to files and/or DB, I prefer DB, but I'd like to know how my java program can be notified by SQL server when NPS server inserts data to database and then re-fetch or extract from SQL server notification and display it in browser.

I already have Java<->Ms SQL connection established.

What I already know:

• SqlDependency class could be used but it is only for .NET and I'm working in Java
• SQL Server Service Broker - this is some kind of a feature in SQL Server which can be used for this but I currently don't understand correctly how to do it and what it really is
• SQL Extended Events - same as above
• SQL Notifications - same as above
• SQL CRL Triggers - trigger written in C++, C# or maybe in Java which is executed on specified situation but this is done synchronously and will slow down whole operation

Now. I've found this post: https://stackoverflow.com/a/534831/1235977

I would suggest having a trigger on the table that calls the SQL Server Service Broker, that then (asynchronously) executes a CLR stored procedure that does all your work in a different thread.

But I don't know how to do this.

So what I need is: Simple table in SQL Server which when inserted data into will notify my Java program somehow. It can be using REST web services, JMS or anything what is language independent.

Please provide step by step examples/solutions.

Answer 1

You can use trigger functionality of SQL. There is a solution:

1. SQL Triggrer for table that calls Stored procedure
2. The stored procedure calls some c# code that will REST service by http.
3. Your back-end will process http requestes from stored proc.