All geek questions in one place

How to move data from a database to an application?

I want to output data from the database to the application, rather than attaching data to the application. I installed ms sql server and Tomcat Apache server. I have my application in apache tomcat, here I connected to the database. Now I want the database to send data whenever it is updated in the data. But all I know is to get data from the database, this is not a good idea, because the application needs to control the database for updated data, which will trigger a query every 5 seconds, it is also inefficient.

I am google, I have answers to them: "Request notification" here , Sql server Agent Job, to schedule the task automatically. If you have any other suggestion, post it.

+4
source share
1 answer

Of course, there are several possibilities to do this:

  • Implement unsafe CLR startup
  • Implement an unsafe CLR procedure
  • Use xp_cmdshell
  • Web service call
  • Use query notification

You can read a little about them in this discussion: Serial numbers created and modified in SQL Server .

Personally, I would prefer to request notifications by other methods, because it already has fopr support for various cases (e.g. synchronization / asynchronous communication), and you do not need to reinvent the wheel. And in your case, Microsoft is recommended.

Polling is another method you mentioned. This is more like a traditional method, and there may be some performance related fines, but you should not worry about them if you are careful enough. For example, if you already have authentication built into your application, you can create another column in the Users table, which is set if there are any changes associated with this user. And then in your application there can only be a thread that will execute a query every second against this table (even dirty reads with NOLOCK should not be a problem here) and maintain some structure in memory (for example, a threadlike dictionary) which says that the client should be pressed. Another thread is polling your dictionary and when it finds something for the client, it executes a db-request, which retrieves the data and sends it to the client. It looks like a lot of inefficient work, but in the end you get two independent workers who help to separate the problems somewhat; the first is just an informant who performs an “easy” database survey; the second of them extracts real data and performs a server push. You can even optimize push-worker so that when it starts, it checks to see if several clients need some data, and then makes a choice for all those who need it. You probably want the second worker to do less often than the first.

EDIT

If you want to use non-.NET technology to achieve the same functionality, you will need to get more in SQL Server Service Broker. Request notification is a simplified layer built in .NET on top of SQL Server Service Broker, and you will have to create at least part of this layer yourself. This includes queuing, message type, maintenance, and stored procedures using SEND and RECEIVE on the other hand. You will have to take care of the conversation / dialogue yourself. SB is actually a world of asynchronous messages that is configured to work in an RDBMS environment, so you'll see some new TSQL expressions. However, MSDN will help here:

It may also help: External inclusion of a .NET application from Service Broker

An example of how to code a material:

 -- First you have to enable SB for your database USE master ALTER DATABASE Playground SET ENABLE_BROKER GO USE Playground GO -- Then create a message type; usually it will be XML -- because it very easy to serialize/deserialize it CREATE MESSAGE TYPE [//Playground/YourMessageType] VALIDATION = WELL_FORMED_XML GO -- Then create a contract to have a rule for communication -- Specifies who sends which message type CREATE CONTRACT [//Playground/YourContract] ( [//Playground/YourMessageType] SENT BY ANY) GO --Creates queues, one for initiator (1) and one for target (2) CREATE QUEUE MyQueue1 GO CREATE QUEUE MyQueue2 GO -- Finally, configure services that 'consume' queues CREATE SERVICE [//Playground/YourService1] ON QUEUE MyQueue1 ([//Playground/YourContract]) GO CREATE SERVICE [//Playground/YourService2] ON QUEUE MyQueue2 ([//Playground/YourContract]) GO -- Now you can send a message from service to service using contract DECLARE @dHandle uniqueidentifier, @Msg nvarchar(max) BEGIN DIALOG @dHandle FROM SERVICE [//Playground/YourService1] TO SERVICE '//Playground/YourService2' ON CONTRACT [//Playground/YourContract] WITH ENCRYPTION = OFF SELECT @Msg = ( SELECT TOP 3 * FROM Table1 FOR XML PATH('row'), ROOT('Table1')) ;SEND ON CONVERSATION @dHandle MESSAGE TYPE [//Playground/YourMessageType] (@Msg) PRINT @Msg GO -- To get the message on the other end, use RECEIVE -- Execute this in another query window DECLARE @dHandle uniqueidentifier DECLARE @MsgType nvarchar(128) DECLARE @Msg nvarchar(max) ;RECEIVE TOP(1) @dHandle = conversation_handle, @Msg = message_body, @MsgType = message_type_name FROM MyQueue2 SELECT @MsgType SELECT @Msg END CONVERSATION @dHandle GO
+4
source

Source: https://habr.com/ru/post/1487222/

More articles:


All Articles