|
Sql Anywhere 11.0.1.2527 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. |
|
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. |
|
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. @Volker - That is something we've done with our normal inter-application communication, but not with the database. Is there native support in SQL Anywhere to do so, or are you calling an external Win32 DLL to do so? @Nick: I don't think there's native support for that. We have written an external C++ DLL to do so. - Note, using Win32 events is quite easy as you usually just have to open, set/reset and close the event handle within the external call. - However, when using mutexes, it's possibly a bit harder with newer SQL Anywhere versions (due to the chance that several external calls are run on separate OS threads- cf. http://sqlanywhere-forum.sap.com/questions/823). |
|
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. What is the "waiting service"? If it isn't dblsn, please explain how it works! We have written a WIN32 Program/Service that is listening on the given Port. If it receives a Package (Content doesn't matter) it executes a standard SQL Query to get the Details. We use it to schedule Printjobs. 1
@Thomas - That is was we've also done in the past, where an external Delphi application is listening on a given port. We've recently discovered that we can receive (using the odbc library we use) the MESSAGE TO CLIENT event which could perform the same function, but faster and with less code (on the client side). @Nick could you give an example ? We use a .Net Program for our Reporting needs. For the task I described in my comment we will stick with sa_send_udp because the service has no active connection while he waits for a job. When schedule interval is reached the service creates a new connection and checks for jobs. After all work is done it disconnects. 1
@Thomas - Firstly, I meant "MESSAGE TO CLIENT" statement. In our db library we're able to register a callback method using SQLSetConnectAttr and SA_REGISTER_MESSAGE_CALLBACK - http://dcx.sybase.com/index.html#1101en/dbprogramming_en11/pg-odbcdev-connectattr.html. This callback method gets called whenever a message from the db is sent. By using "MESSAGE TO CLIENT FOR CONNECTION x" we're able to send a message to any connection. We have a table that we register which connections want to listen for particular inserts. We have a trigger that fires on after insert which triggers an event... (... continued) That then looks up which connections are wanting to get notified about the insert and then it sends a "MESSAGE TO CLIENT FOR CONNECTION x" to those connections. More comments hidden
|
|
We use UDP broadcasts for similar purpose. Pretty simple and cross-platform. |
Could you elaborate more, e.g. whether the financial database can access the mentioned database (via proxy tables) or vice versa or if the financial database is "closed". w.r.t. changes?