# How to push the data from database?

I want to push the data from database to Mathematica application instead of application(MM App) 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.

Thanks

-
Wouldn't it be more appropriate to ask this in a forum where the people know more about mssql server? Here maybe? –  sebhofer Jun 20 '13 at 7:42

You could use a trigger in your database which fires when data is added or changed in your table to execute a piece of code; I don't know sqlserver but I assume that a trigger can execute some c++ or .Net code. That in turn could pass the data into Mathematica through NetLink. Mathematica would have to be set up to listen for the data of course. Actually all your trigger needs to do is wake Mathematica up and get it to query the data, you don't need to pass the data through the trigger.

I've done similar things in the (distant) past with oracle and java.

The Devil's Advocate suggests that this is a lot of work. If querying the database every 5 seconds fast enough and do you really care about the (tiny) efficiency loss of doing this way? For a modern db to conclude "nothing to send" if well set up is a trivial load. In addition you get a free heart-beat check on your database. You will know if its crashed or gone offline within 5 seconds in Mathematica. If you perform a database push you will only know its broken when the data doesn't arrive.

...Stefan's answer below just beat me ;)

-
i got first! ;) well that was nearly unisono :) good morning btw –  Stefan Jun 20 '13 at 7:53
@yamareth we can solve this using Service broker concept of DB. know we are working on that. –  subbu Jul 10 '13 at 6:45

Trigger an external event

Edit

Don't you dare to close this question! :)

I've to say, that for the Java/MathPackage part of this answer I've used Workbench plugin within eclipse/Juno and again I've to agree with @halirutan that this is not a time saver at all. It took me more time to configure this etc. than to write the code.

I am already a user of the IntelliJ plugin and I have to say decent work gentlemen!

I'm looking forward for more advanced versions for IntelliJ plugin!

If there's anything I can do, just let me know...but this was painful.

Edit end

This might be not the most elegant solution, but why not insert a trigger for your specific table on update/insert which calls one of the extended procedures in ms sql server?

Database side

If now there is an update to your data table your trigger gets fired and the extended procedure (they are all starting with the preamble xp_) is, either touching an external file in order to modify the file timestamp, or is exporting the whole table as a csv file into that specific folder, where your file system monitor is watching/observing.

One specific fine tuning you could do is, that your trigger is (after insert) copying only the new inserted rows/rows which got updated into that file or you create a mirrored table and copy the updated data to this one, from where it is exported.

Mathematica side

On the Mma side you could establish a file system monitor like this:

Begin["subbu"];
fileName = "/tmp/update"; (* or /tmp/update.csv *)
lastModified = {};
updateQ :=
With[{modificationDate = FileDate[fileName, "Modification"]},
If[
lastModified == modificationDate,
False,
lastModified = modificationDate; True
]
];
If[updateQ, Print["Changed"], (##1 &)[]], {2, \[Infinity]}];
End[];


You can activate now your file system watcher with:

StartScheduledTask[subbutask];
....
StopScheduledTask[subbutask];


The rest is common Mma practice handling external files ...

Hope that this proposal, if not solve your specific problem, but gives you a hint how to achieve the solution you have in your mind.

Edit:

Just for the sake of completeness, since I didn't know that the OP's question was about webMathematica and especially that there is no ScheduledTask support in it.

Here is a JLink version of the answer which should work with webMathematica as well (Documentation states that it is possible to include external Java libraries). The rest remains, as described above.

At first the Java code:

package de.mma.filesystem;

import java.io.File;
import java.io.IOException;
import java.nio.file.FileSystems;
import java.nio.file.Path;
import java.nio.file.StandardWatchEventKinds;
import java.nio.file.WatchEvent;
import java.nio.file.WatchKey;
import java.nio.file.WatchService;
import java.util.List;

public class DirWatcher {
static String msg;
public static void watch(final File dir) throws IOException {
final WatchService watcher = FileSystems.getDefault().newWatchService();

Path path = dir.toPath();
try {
final WatchKey bDirWatchKey = path.register(watcher,
StandardWatchEventKinds.ENTRY_MODIFY,
StandardWatchEventKinds.ENTRY_CREATE,
StandardWatchEventKinds.ENTRY_DELETE);
public void run() {
System.out.println("Watching: "+ dir.getName());
while(true) {
try {Thread.sleep(1000);} catch (InterruptedException e) {}
List<WatchEvent<?>> events = bDirWatchKey.pollEvents();
for(WatchEvent<?> event:events) {
System.out.println(dir.getName()+" event: #"+event.count()+","+event.kind()+" File="+event.context());
msg = new StringBuffer(dir.getName()+" event: #"+event.count()+","+event.kind()+" File="+event.context()).toString();
}
}
}
}).start();
} catch (IOException x) {
x.printStackTrace();
}
}

public static String getMessages() {
return msg;
}

public static void main(String args[]) {
try {
DirWatcher.watch(new File("/tmp/test"));
} catch (IOException e) {
e.printStackTrace();
}
}
}


And here's the Mathematica package:

BeginPackage["FileWatcher", {"JLink"}]

RegisterDir::usage = "RegisterDir  "

GetMessage::usage = "GetMessage  "

DisplayDebugConsole::usage = "DisplayDebugConsole  "

Begin["Private"]

(* you can do this here or in your notebook environment...however *)
ReinstallJava[CommandLine -> "/usr/bin/java"];
AddToClassPath["where the directory with the class files located"];

Clear[RegisterDir]
RegisterDir[directory_String] :=
Module[{},
DirWatcherwatch[JavaNew["java.io.File", directory]]
]

Clear[GetMessage]
GetMessage[] := DirWatchergetMessages[]

Clear[DisplayDebugConsole]
DisplayDebugConsole[] :=
JavaBlock@Module[{cw},
cw = ConsoleWindowgetInstance[];
cw@setLocation[100, 100];
cw@setSize[450, 400];
cw@show[];
cw@setCapture[BitOr[ConsoleWindowSTDERR , ConsoleWindowSTDOUT]];
]

End[]

EndPackage[]


You don't really need a special written package, since the Java code has such a simple interface.

With this you can register directory paths (that path, which contains the file you are writing from your trigger procedure in your database) and you'll get the specific events from DirWatcher.

Using it is extremely simple:

<< FileWatcher

DisplayDebugConsole[] (* handy tool to see stderr, stdout messages from jvm *)

RegisterDir["/tmp/test"] (* the directory you are observing *)

GetMessage[] (* if nothing is in the queue it is Null, otherwise it will display the specific event that did happen on a specific file inside the directory *)


You could trigger GetMessage after some equidistant interval.

Sorry for the late additional answer, but with this temperatures here I incline to have a south-spanish working attitude ;) Siesta!

-
Thanks for your answer,ScheduledTask concept is not working in WebMathematica` .Just Know I tried... –  subbu Jun 20 '13 at 10:23
didn't realize you were using webmathematica. how easy is it to call a java libary from webmathematica? –  Stefan Jun 20 '13 at 10:54