DBA > Job Interview Questions > Sybase Interview Questions and Answers

Calling OS commands from a trigger or a stored p

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(Continued from previous question...)

Calling OS commands from a trigger or a stored procedure in Sybase

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 ASE 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 ASE. 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 ASE 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 ASE 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", <port number>

2> go

1> reconfigure with override  -- Not necessary with 11.0 and above
2> go

The server must be restarted to set the port number.

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)

(The above error is a little out of date for the latest releases of ASE, there are now 8 rows that contain "allow", but the result is the same.)

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 ASE version which does not include syb_sendmsg(), please follow the instructions below.

  1. Edit the RUNSERVER file to point to the SWR ASE binary you wish to use.
  2. isql -Usa -P<sa password> -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);
    }
}

(Continued on next question...)

Other Job Interview Questions