Solved

call an external command (such as java program but without gui stuff) from a trigger or stored procedure...

Posted on 2004-10-13
5
1,146 Views
Last Modified: 2008-03-17
What i need for my project is: when a sybase table is changed (insert/update etc), the trigger calls an external java program AA to notify my another java program BB about the changes.  The program AA only does 1 thing: sending out an int value to a certain predefined socket, while program BB is waiting there on the socket for incoming message.  
The trigger is like:

create trigger book_u_trig
as
....
if update(title)
  exec xp_cmdshell "AA 9012"
return

where "9012" is the parameter to program AA and it is also the socket number that AA is going to send out an integer value through.  

On the same machine (as Sybase server), i have already run another program BB as
  java BB 9012
which means BB is listening to any incoming message on socket 9012.

The background of such a requirement: my project requires to know "realtime" that a table has been changed, so further actions might be taken.  It will be inefficient for my project to poll the sybase table at short intervals of time period; instead, i want the table itself to notify my program about the changes.  
 
Thanks,
0
Comment
Question by:Lynnzi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12304662
So what's wrong with xp_cmdshell?
0
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 500 total points
ID: 12304810
There's no great answer to this.

xp_cmdshell is probably the easiest to program, but it is not transactional - it's not guaranteed to work. It also has a lot of moving parts - it relies on the external XP Server, which then calls your sender program.

If all you want is to send a message to a particular port, you could look into sp_sendmsg. It sends a varchar(255) to any port on any IP address via UDP. It also isn't guaranteed or transactional, but at least it doesn't rely on the XP Server. It might also allow you to dispense with your AA program for fewer moving parts.

Some caveats - sp_sendmsg is not available for ASE on Windows, and you must first configure "allow sendmsg" to 1 using sp_configure.

If you were willing to spend money on this, you could buy Sybase's XFS premium feature for ASE (only in 12.5+), which allows you to build a proxy table on a directory. You could then have the trigger insert a row to this proxy table, which would result in a file being created in the directory. This still isn't a great answer for you as it requires a paid license and still requires polling, but polling is much cheaper at the operating system level than within the database. I know of a factory floor that submits print jobs in this way.
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12307996
You can also create a custom extended stored procedure as a DLL. Search on "Extended stored procedures" if you're interested in this option.
0
 

Author Comment

by:Lynnzi
ID: 12313977
AA is a java class on Sybase server machine, on which the classpath has been set up proprely.  
Previously, when i simply use "exec xp_cmdshell java AA 9012" in the trigger, the trigger just hangs.  Thus i posted the message to Expert Exchange.  
Just realized that i need to add the classpath into the command within xp_cmdshell as it seems the XP server does not look up in environment variable "CLASSPATH" at all.

So it works now with quite satisfactory response time:

create trigger book_u_trig
as
....
if update(title)
  exec xp_cmdshell "javaw -classpath c:\j2re1.4.2\bin\;c:\hooks\classes AA 9012"
return

Note that no spaces are allowed in the classpath.
0
 

Author Comment

by:Lynnzi
ID: 12313982
Forgot to say, Thanks ALL for your quick response.  
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Postmortem reporting allow us to examine mistakes in a way that focuses on the situational aspects of a failure’s mechanism and the decision-making process of individuals proximate to the failure. Read our guide on how to handle IT post-mortem repor…
This article shows how to use a free utility called 'Parkdale' to easily test the performance and benchmark any Hard Drive(s) installed in your computer. We also look at RAM Disks and their speed comparisons.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question