Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to push the data from database?

Author  Topic 

niren
Starting Member

18 Posts

Posted - 2013-06-17 : 09:14:44
I want to push the data from database to application instead of application pull the data.I have installed ms sql server and apache tomcat server. I have my application in apache tomcat, here I made connection to database. Now I want database send the data whenever there is update in data. But all I know is fetch the data from database is not good idea, because application needs to monitor the database for updated data will lead to fire the query for every 5 sec, this is not efficient as well.
Suggest me good way to tackle this, refer me good link.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-17 : 09:39:51
provide some details of the purpose of your application. What your application is doing with the data when it receives data from the DB?

To me this resembles somehow to a reporting tool, where an "updated" information is required to be sent out to a client etc???

Cheers
MIK
Go to Top of Page

niren
Starting Member

18 Posts

Posted - 2013-06-17 : 10:00:43
Actually I have two database, and one application in tomcat server. one database is to get the data from user, whenever user update the data automatically the application should have the updated data and do some computation and store the computed data in another database. Here there is no user interaction for application, his work is only update the database and see the computed data in other database.
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-17 : 10:55:22
You can push data within databases but to application may be not possible (I won't say not possible since i am trying to understand your scenario). Its always an application that uses the data not the data that uses an application.

So what I've perceived as following

Database A: is a user facing database, and DML operations is performed into this database.
Application X: gets the changed/updated data from Database A, then applying computation on the changed/updated data and stores the result to another DB (Database B).


So my questions are

1) both databases are in SQL Server and reside on Same server (machine)?
2) Whats the need for an explicit application for computation? What kind of computation are you trying to perform on the data. Can you illustrate with an example? Btw, you can do computation in SQL server too, unless there is some real need that cannot be acheived marely using tSQL.

Cheers
MIK
Go to Top of Page

niren
Starting Member

18 Posts

Posted - 2013-06-18 : 07:17:49
What you assume about my problem is correct. I give the answer for your question

1) both databases are in SQL Server and reside on Same server (machine)?

Just for testing I have installed two sql server in same system, Later we will have in different machine. I have tested two sql server in my system separately, they are working fine.
2) Whats the need for an explicit application for computation? What kind of computation are you trying to perform on the data. Can you illustrate with an example?

Am going to use webMathematica application for computation, as per I know webMathematica handle large set of data(1 million) very well and I have some packages to do computation as well.

So now I want Database A to send updated data to webMathematica app, this explicit application do computation and store the updated data in Database B.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-06-18 : 07:30:59
You might want to look at Query Notifications: http://msdn.microsoft.com/en-us/library/ms130764.aspx

I've not used them myself, so I can't say for sure they can do what you need.
Go to Top of Page

niren
Starting Member

18 Posts

Posted - 2013-06-19 : 10:00:09
As per @robvolk advice I think Query Notifications will be useful for me. I have searched a lot, all I found is the application will get the notification when there is a change in database. Now I want to know how to use them correctly, my application is webMathematica run in tomcat with jsp code. I don't know how to configure my application to get notification from Database A. I found some examples through internet but they were only in VB.NET. Can't I use query notification using JSP code or using my application.
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-19 : 10:54:49
I am not sure as if there could be a way to guide/direct SQL server (or any other dbms) so that it sends data "by itself" to a particular application. As I mentioned earlier that "Its always an application that uses the data not the data that uses an application."

You may need to be a bit different in your approach, considering you're using a third party application (webMathematica)

Database <-----> Logic <------> webMathematica app

All you need to code a logic in some language(asp, php, etc in whichever you want) which will call a stored procedure after every five seconds (the stored procedure will be having the logic of pulling the desired data). And then pass the resulting data to the webMathematica

Cheers
MIK
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-06-19 : 11:52:18
How about this, whenever data changes in db A, a ssis package job runs, and send the updated data to your web server (tomcat) as xml file. You make your math app consume that xml as needed instead of pull data from db live?

Go to Top of Page

niren
Starting Member

18 Posts

Posted - 2013-06-22 : 07:21:03
@MIK 2008 you are right I should have more clear in my question itself. As you mentioned earlier that "Its always an application that uses the data not the data that uses an application." Now I'll explain it clearly I have number of application in Tomcat server and I have two Databases A & B. The Database A will send the message to application initially says that database getting changed. Then the application execute the query to fetch the data from database A, do computation and store the data in Database B.

In my scenario user doesn't have access for application(java, webMathematica, etc.) he has only access for Database A. Initially I start the tomcat server and both sql server. Corect me if I wrong as far as I understand Database can communicate the tomcat server through either http requset or Tcp\IP port(am not sure which way database use to communicate the application).

Assume the database A getting communicated with Tomcat server. But Tomcat server has two or more application( java, webMathematica, etc. ).

1.My doubt is when there is change in Database A it sends message to Tomcat server, how the tomcat server knows which application it should direct the message to since it has many application as I said earlier?

2.I want to know Is it possible to send the message from Database A to application without user interaction of application( java, webMathematica, etc.)?
Go to Top of Page
   

- Advertisement -