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).

asked 15 Feb '11, 01:21

Nick%20Brooks's gravatar image

Nick Brooks
513172031
accept rate: 50%

edited 15 Feb '11, 21:22

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?

(15 Feb '11, 17:23) Volker Barth

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.

permanent link

answered 15 Feb '11, 15:54

Breck%20Carter's gravatar image

Breck Carter
26.3k430600866
accept rate: 21%

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.

permanent link

answered 15 Feb '11, 18:20

Roland%20Smith's gravatar image

Roland Smith
91448
accept rate: 0%

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.

permanent link

answered 16 Feb '11, 08:43

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

@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?

(16 Feb '11, 21:52) Nick Brooks

@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).

(17 Feb '11, 08:48) Volker Barth

We use sa_send_udp in an after insert trigger to inform a waiting Service about new data.

call sa_send_udp( HOSTIP, UDPPORT, 'One Ping Vassili');

Instead of a 30 sec delay it takes less than a second that the remote host processes the added rows.

permanent link

answered 16 Feb '11, 11:09

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.6k243561
accept rate: 17%

What is the "waiting service"? If it isn't dblsn, please explain how it works!

(16 Feb '11, 11:42) Breck Carter

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.

(16 Feb '11, 18:50) Thomas Dueme...
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).

(16 Feb '11, 21:48) Nick Brooks

@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.

(17 Feb '11, 11:19) Thomas Dueme...
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...

(18 Feb '11, 00:26) Nick Brooks

(... 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.

(18 Feb '11, 00:27) Nick Brooks
More comments hidden
showing 5 of 6 show all flat view

We use UDP broadcasts for similar purpose. Pretty simple and cross-platform.

permanent link

answered 02 Mar '11, 11:30

Dmitri's gravatar image

Dmitri
1.5k41132
accept rate: 11%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×137

question asked: 15 Feb '11, 01:21

question was seen: 1,375 times

last updated: 02 Mar '11, 11:30