Q7.8: Calling UNIX from a trigger or a stored procedure

11.5 and above

The Adaptive Server (11.5) will allow O/S calls from within stored procedures. These stored procedures are known as extended stored procedures

Pre-11.5

Periodically folks ask if it's possible to make a system command or call a UNIX process from a Trigger or a Stored Procedure.

Guaranteed Message Processing

The typical ways people have implemented this capability is:
  1. Buy Open Server and bind in your own custom stuff (calls to system() or custom C code) and make Sybase RPC calls to it.
  2. Have a dedicated client application running on the server box which regularly scans a table and executes the commands written into it (and tucks the results into another table which can have a trigger on it to gather results...). It is somewhat tricky but cheaper than option 1.

Sybase SQL Server 10.0.2.5 and Above - syb_sendmsg()

This release includes a new built-in function called syb_sendmsg(). Using this function you can send a message up to 255 bytes in size to another application from the SQL Server. The arguments that need to be passed to syb_sendmsg() are the IP address and port number on the destination host, and the message to be sent. The port number specified can be any UDP port, excluding ports 1-1024, not already in use by another process. An example is: 
1> select syb_sendmsg("120.10.20.5", 3456, "Hello")
2> go
This will send the message "Hello" to port 3456 at IP address '120.10.20.5'. Because this built-in uses the UDP protocol to send the message, the SQL Server does not guarantee the receipt of the message by the receiving application.
Also, please note that there are no security checks with this new function. It is possible to send sensitive information with this command and Sybase strongly recommends caution when utilizing syb_sendmsg to send sensitive information across the network. By enabling this functionality, the user accepts any security problems which result from its use (or abuse).

To enable this feature you should run the following commands as the System Security Officer.

  1. Login to the SQL Server using 'isql'.
  2. Enable the syb_sendmsg() feature using sp_configure. 
    1> sp_configure "allow sendmsg", 1
2> go

1> sp_configure "syb_sendmsg port number", 
2> go

1> reconfigure with override
2> go

Using syb_sendmsg() with Existing Scripts

Since syb_sendmsg() installs configuration parameter "allow sybsendmsg", existing scripts that contain the syntax 
1> sp_configure allow, 1
2> go
to enable updates to system tables should be altered to be fully qualified as in the following: 
1> sp_configure "allow updates", 1
2> go
If existing scripts are not altered they will fail with the following message: 
1> sp_configure allow, 1
2> go
Configuration option is not unique.
duplicate_options
----------------------------
allow updates
allow sendmsg

(return status = 1)

Backing Out syb_sendmsg()

The syb_sendmsg() function requires the addition on two config values. If it becomes necessary to roll back to a previous SQL Server version which does not include syb_sendmsg(), please follow the instructions below.
  1. Edit the RUNSERVER file to point to the SWR SQL Server binary you wish to use.
  2. isql -Usa -P -Sserver_name -n -iunconfig.sendmsg -ooutput_file

Sample C program

#include <stdlib.h> #include <stdio.h> #include <sys/types.h> #include <sys/socket.h> #include <netinet/in.h> #include <arpa/inet.h> #include <unistd.h> #include <fcntl.h> main(argc, argv) int argc; char *argv[]; { struct sockaddr_in sadr; int portnum,sck,dummy,msglen; char msg[256]; if (argc < 2) { printf("Usage: udpmon <udp portnum>\n"); exit(1); } if ((portnum=atoi(argv[1])) < 1) { printf("Invalid udp portnum\n"); exit(1); } if ((sck=socket(AF_INET,SOCK_DGRAM,IPPROTO_UDP)) < 0) { printf("Couldn't create socket\n"); exit(1); } sadr.sin_family = AF_INET; sadr.sin_addr.s_addr = inet_addr("0.0.0.0"); sadr.sin_port = portnum; if (bind(sck,&sadr,sizeof(sadr)) < 0) { printf("Couldn't bind requested udp port\n"); exit(1); } for (;;) { if((msglen=recvfrom(sck,msg,sizeof(msg),0,NULL,&dummy)) < 0) printf("Couldn't recvfrom() from udp port\n"); printf("%.*s\n", msglen, msg); } }