Sql Anywhere 22.214.171.1247
We are needing to write an application that will take data from a table in a SQL Anywhere database and send the data (after alteration) through our business layer to our financials database. Data will be inserted into the table in < 1 minute intervals. We need to push the data as soon as possible after insertion.
We have considered: - Sending a TPC/UDP message to the application which is called by an after trigger on the table. - Sending a 'MESSAGE to client for connection x' which is called by an after trigger on the table. - Having the application poll the table every x seconds.
What is the most efficient way of notifying this application when data is inserted into the table? Have we missed another option?
Thanks for your help.
Edit - As requested by Volker: Yes, the financial database could see this database, however, the data that needs to go to the financial database needs to be processed first which cannot be done in the financial database - there is some processing that our business layer does which the financial database cannot (and won't).
Another possibility might be to launch the application via xp_cmdshell.
Personally, I would poll the table periodically: very simple logic. If that query is expensive then perhaps create a single row "notification" table to be polled.
answered 15 Feb '11, 15:54
I like Breck's second idea. A trigger could do an insert into a Notification table. The app would select from that table periodically and delete the rows once they have been processed.
answered 15 Feb '11, 18:20
In case both systems (your application and the financial database) run on the same machine, you could also use OS specific interprocess communication (IPC) facilities to notify another process.
E.g. on Windows, you could use named Win32 Events to notify another process that something has occured within your app.
Note, I don't claim that this is the easiest or most appropriate way to do (or something a typical application/database developer is concerned with), but I guess IPC might be the fastest way to notify another process.
We use a similar facility to trigger SQL Remote (in batch mode) when a message file arrives from the outside or when a central application has something to "hand over" to the remotes.
answered 16 Feb '11, 08:43
We use sa_send_udp in an after insert trigger to inform a waiting Service about new data.
Instead of a 30 sec delay it takes less than a second that the remote host processes the added rows.
answered 16 Feb '11, 11:09
We use UDP broadcasts for similar purpose. Pretty simple and cross-platform.
answered 02 Mar '11, 11:30