Introduction

Mary Ann logs into Sybase every Monday morning. She does this by first selecting the URL to the Sybernet sign-on screen. She then enters her username and [Enigma] password. Then she selects the desired form screen from the Sybernet main menu. Now she enters her org number on this screen and clicks on the SUBMIT button. In a matter of only few seconds, her report appears on her screen.

There's a better solution: just send Mary Ann this report to her E-Mail address. Not only does this eliminate several redundant steps on her end, but also the job that does this can be scheduled when Sybase is not busy doing other work.

Unless you override the default Content-type, the results of your stored procedure are mailed with a Content-type of text/html. This means someone receiving this mail will want to have an E-mail program that recognizes this type. NetScape Navigator 3.0 and above is such a program.

The following example illustrates how EMAIL may be specified in a URL request:

    select '<A HREF="Sybase.cgi$http..sp_html_who?@EMAIL=%22nospam@nospams.com%22>Click</A>'
	

    select '<INPUT TYPE="TEXT" NAME="@EMAIL" VALUE="nospam@nospams.com">'

The MacOS version of Sybernet does not allow EMAIL to be assigned in this fashion and will ignore any attempts to do so.

    create table CRON_GROUPS
    (
        GROUPNAME   varchar(30)     not null /* Name of group                                   */
    ,   EMAIL       varchar(255)    not null /* An E-Mail address (may not be a groupname)      */
    ,   TIMESTAMP   datetime            null /* The datetime this entry added to /etc/aliases   */
    )

sp_cron_insert allows you to specify only those columns that you are interested in. With some intelligence, the remaining columns are calculated automatically! You can even update entries in the CRON table with this procedure when ROW_ID is not null.

sp_cron_insert returns -1 if the insert failed or the ROW_ID of this entry if the insert succeeded.

Let's take a look at a few examples.

The first example illustrates how you would insert an entry in table CRON that is executed every Christmas at 8 o'clock in the morning:

    exec sp_cron_insert
	
        @PROCNAME   = "http.dbo.sp_html_xmas"
    ,   @EMAIL      = "nospam@nospams.com"
    ,   @DATENAME   = "DECEMBER"
    ,   @DAY        = "25"
    ,   @HOUR       = "8"
	

    exec sp_cron_insert
	
        @PROCNAME   = "http.dbo.sp_html_who"
    ,   @EMAIL      = "nospam@nospams.com"
    ,   @STARTTIME  = "JANUARY 1, 1997"
    ,   @STOPTIME   = "DECEMBER 31, 1997"
    ,   @DATENAME   = "MONDAY"
    ,   @HOUR       = "12"
	

    exec sp_cron_insert
	
        @PROCNAME   = "http.dbo.sp_html_who"
    ,   @EMAIL      = "nospam@nospams.com"
    ,   @TIMESTAMP  = "December 24, 1996"
    ,   @DATENAME   = "DAY"
    ,   @HOUR       = "8"
    ,   @MINUTE     = "45"
	

    exec sp_cron_insert

        @PROCNAME   = "./HelloWorld"          -- A C program
    ,   @SCRIPTNAME = "../htdocs/datafile"    -- required

Since TIMESTAMP and DATENAME were not specified, sp_cron_insert creates an entry in the CRON table that will be executed immediately and then deleted.

Parameters to your C program are gotten from the table CRON_PARAMETERS. Each VALUE in this table is passed to your program in its argv array. Parameter values in CRON_PARAMETERS should also not contain \n because this character is used to delimit these parameters when they are sent to Sybercron on UNIX.

It is important to specify an explicit path and filename when executing scripts in this fashion because you do not know the path where Sybercron is executing from. Permissions are important too; if Sybercron cannot read this file, it will be unable to execute this command.

./Syberload is a built-in function that allows you to upload UNIX files into Sybase. Files uploaded with this mechnism may contain any Transact-SQL statements and may be batched with the keyword GO.

The following example illustrates how to upload a Sybase script file:

    begin transaction
    exec @ROW_ID = http..sp_cron_insert
    
        @PROCNAME = "./Syberload"             -- The built-in function Syberload.
    ,   @SCRIPTNAME = "/dev/null"             -- I need to give it some name
    ,   @PLATFORM = "SPOCK"                   -- From where should this be executed.
    
    if @ROW_ID != -1
    insert http..CRON_PARAMETERS
    (
        ROW_ID
    ,   NAME
    ,   VALUE
    )
    values
    (
        @ROW_ID
    ,   "arg1"
    ,   "/home/denis/sybase/scriptfile"       -- Name of Sybase script file.
    )

    if @ROW_ID != -1
    insert http..CRON_PARAMETERS
    (
        ROW_ID
    ,   NAME
    ,   VALUE
    )
    values
    (
        @ROW_ID
    ,   "arg2"
    ,   "nospam@nospams.com"                  -- An e-mail address or filename for sending the results to.
    )

    if @ROW_ID != -1
    insert http..CRON_PARAMETERS
    (
        ROW_ID
    ,   NAME
    ,   VALUE
    )
    values
    (
        @ROW_ID
    ,   "arg3"
    ,   "text/html"                           -- Content-type (if arg2 is an e-mail address).
    )

    if @ROW_ID != -1
    insert http..CRON_PARAMETERS
    (
        ROW_ID
    ,   NAME
    ,   VALUE
    )
    values
    (
        @ROW_ID
    ,   "arg4"
    ,   "Here are your results"               -- Subject of e-mail (if arg2 is an e-mail address).
    )
	
    commit transaction
    return @ROW_ID                            -- Success if > -1.

The remaining arguments are optional. The second argument (arg2) can be either an e-mail address or the name of a disk file. It's an e-mail address if it contains an at-sign in its value. If it's a disk file name, the file is opened for append. If the second argument is not specified, the results of your script are written to the Sybernet log file.

The third argument (arg3) allows you to specify the content-type (text/html or text/plain), and the fourth argument (arg4) allows you to give a title for the subject of your e-mail.

All arguments are passed by order, not by name. You can't specify a Content-type unless you also specify a e-mail address, for example.

NOTE: Versions 2.18 and below of Sybercron do not recognize the optional attributes documented here.

sp_cron_insert_parameter

    exec sp_cron_insert_parameter
	
        @ROW_ID = @ROW_ID
    ,   @NAME = "EMPLID"
    ,   @VALUE = substring(user_name(),2,5)

    declare @VALUE varchar(30)
    select @VALUE = substring(user_name(),2,5)
    exec sp_cron_insert_parameter
	
        @ROW_ID = @ROW_ID
    ,   @NAME = "EMPLID"
    ,   @VALUE = @VALUE

The ability to insert directly into CRON_PARAMETERS has not been removed.

sp_cron_wait

It is now possible to call Sybercron and wait for its return result by calling the stored procedure sp_cron_wait. sp_cron_wait expects the ROW_ID returned from sp_cron_insert (and also the the ROW_ID passed to sp_cron_insert_parmater) as its only parameter; for example, the example above could be modified as follows:

    declare @ROW_ID int
    declare @RESULT int
    begin transaction
    exec @ROW_ID = http..sp_cron_insert

        @PROCNAME = "./shell2.csh"
    ,   @PLATFORM = "SPOCK"
    ,   @SENDER = "nospam@nospams.com"
    ,   @EMAIL = "nospam@nospams.com"

    if ( @ROW_ID != -1 )
    exec @ROW_ID = http..sp_cron_insert_parameter

        @ROW_ID
    ,   "argv1"
    ,   "nospam@nospams.com"

    if ( @ROW_ID != -1 )
    exec @ROW_ID = http..sp_cron_insert_parameter

        @ROW_ID
    ,   "argv2"
    ,   "ade-lj4"

    if ( @ROW_ID != -1 )
        commit transaction
    else
        rollback transaction
	
    exec @RESULT = http..sp_cron_wait @ROW_ID
	
    if @RESULT >= 0
        select "Success!  Your job completed successfully!"
    else
        select "Oops! Your job failed!"

Remember that your browser will timeout after 5 minutes without any response. Calling sp_cron_wait so that you can display this result is not always practical.

Example

    exec @X1 = http..sp_cron_insert @PROCNAME = "sp_task_1"
    exec @X2 = http..sp_cron_insert @PROCNAME = "sp_task_2"

    exec @R1 = http..sp_cron_wait @X1
    exec @R2 = http..sp_cron_wait @X2

    if ( @R1 >= 0 and @R2 >=0 )
        exec sp_task_3

When sp_html_cron_register is invoked, three windows are built on your screen. They are the Directory Window, the Input Window, and the Output Window:

The Directory Window

At the top of this list is a hypertext link that identifies the current database. To change the current database simply click on this link. A menu of all databases on the system is displayed in this window. Just click on the database you want and a new directory will be created.

If there are any registered procedures in the current database, they will appear in this window. You can edit an existing entry by clicking on its hyper-text link. You can create a new entry by selecting a procedure name from the list that follows. You can also create a new entry by editing one of the registered procedures and clicking on the COPY button.

Clicking on the hyper-text link of a registered procedure will do one of two things: If the procedure name is unique--in other words, it appears in the CRON table only once--, selecting that procedure will take you to the detail edit for that procedure. If the procedure name is not unique, a summary window is invoked sorted by E-Mail address which displays the parameters passed to each invocation. Simply find the one you want to edit and click on its hyper-text link.

The Input Window

• UPDATE

  UPDATE appears if this procedure has already been registered. You can change any values on this form (except the procedure name) and modify this entry by clicking on this button.

• CREATE

  CREATE appears if this procedure has not been registered. That is, you selected a procedure name from the directory window that was not followed by a ROW_ID. Use this button to add a entry to the CRON Table.

• DELETE

  Use the DELETE button to remove an entry from the CRON table.

• COPY

  The COPY button duplicates the current entry. You can then change one or more columns and press CREATE to add a new entry, leaving the original untouched.

• REPORT

  The REPORT button will generate a report of all entries in the CRON Table. It will also generate a hyper-text link for each procedure that, when clicked, will allow you edit the entries for that procedure.

  This report will also make sure that information in the CRON table is valid. This includes checking that your procedure actually exists, that parameter names to this procedure are still valid, and whether or not the URLEncoded form of some parameters do not exceed 255 characters. Any errors in this regard are displayed in red and the summary at the bottom of this page will reflect these errors.

  The REPORT button only reports on registered procedures in the current database.

• GROUPS

  The GROUPS button will generate a report of all entries in the GROUPS Table. It will also generate a hyper-text link for each group name that, when clicked, will allow you to edit the entries in that group.

  If an E-Mail address appears to be a group name, a hyper-text link is also created for that group name that may be similarly edited.

  The GROUP (or alias) editor displays 10 entries from this table at a time. An entry is deleted by simply setting that field to NULL. Clicking on the buttons SAVE, NEXT, or PREV will update the current page (regardless of whether you've added or deleted an entry) and perform the required action.

  If you scroll to the end (or more quickly click on PREV from page 1), you will see one or more fields for adding new addresses to this group. You may specify any E-Mail address, and if that address also exists in PDS, the employee corresponding to the employee will be shown as well.

• RELOAD

  The RELOAD button rebuilds your directory window. This is convenient when you have deleted or added a procedure.

The Output Window

The first thing this procedure does it look for any TIMESTAMPs in table GROUPS that are in a NULL state. A NULL value for TIMESTAMP in any entry in table GROUPS means that the alias file (/etc/aliases) needs to be rewritten. If this is the case, a current list of aliases is returned to Sybercron.

If any procedures are ready for execution, its next TIMESTAMP is calculated and updated, and the run statement for this procedure is built. If for some reason the execution of this procedure should fail or an error result was returned by Sybase, it will not be re-executed until its next scheduled date is reached.

Sybercron was written before Sybernet was multi-tasking. This meant that Sybercron would distribute tasks among the available servers. Sybercron now takes advantage of Sybernet's multi-tasking ability and will initiate as many tasks as there are connections available.

Whereas the load used to be distributed, Sybercron now initiates tasks from the same port that Sybercron is suppose to listen at.

The previous limit for the number of asynchronous tasks initiated by Sybercron was 5 (the number of servers minus one). The current limit is now the maximum number of connections minus the number of active connections minus one. During peek periods, the average number of user connections (per server) is between 20 and 30. During unpeak periods, Sybercron now has the ability to execute about 60 tasks simultaneously.

Sybercron resides in your cgi-bin directory and recognizes the following command line arguments:

• -U username

  Username is your Sybase username. If not specified, the current value for LOGINNAME (an environment variable) is used.

• -P password

  Password is your Sybase password. If not specified, it will be prompted from stdin.

• -S hostname

  Hostname is the name of your Sybase data server. Your DSQUERY environment variable will be used if not specified.

• -I identity

  Identity corresponds to PLATFORM in the CRON table. This option makes it possible to run Sybercron from several different UNIX hosts. The default value for Identity is SPOCK.

• -T seconds

  seconds determines the number of seconds between each iteration. That is, Sybercron calls sp_html_cron successfully until there is nothing left to do, then it waits for the number of seconds specifed by seconds.

  The default is 300 seconds (or 5 minutes) between iterations.

  If seconds is 0, Sybercron will exit after processing the first batch. This make it possible to call this program from another application; for example, you may prefer to use the UNIX cron facility to call Sybercron.

• -C socket

  This option allows you to specify the starting socket number of the Sybernet server. Normally, Sybercron gets this information from the configuration file. To allow Sybercron to run without interrupting or impacting real users, you may wish to dedicate one or more servers for this facility.

• -D

  The -D option turns on debugging. This writes all output destined for the log file to stdout as well.

• -X

  This option starts Sybercron with defaults for all of the above.

If a procedure is ready to execute, its run statement is returned and submitted for execution as a separate, asynchronous process. Sybercron will continue to call sp_html_cron until there are no more procedures ready to run.

C programs, on the other hand, are executed in the main thread. Future calls to sp_html_cron will not occur until that program has exited.

After all procedures ready for execution have been submitted, Sybercron sleeps until the next iteration.

The safest way to terminate Sybercron is by sending a -QUIT signal; for example,

More Examples

Group Type

GROUPTYPE helps Sybercron determine how to interpret E-Mail addresses in the GROUPS table. If specified as "Y", Sybercron will recurse on each E-Mail address in the CRON_GROUPS table, invoking the specified procedure once for each E-Mail address.

Pretend that the next three procedures have been registered with a GROUPTYPE of Y. These procedures will be executed once for each E-Mail address in the GROUPS table, and that E-Mail address is also passed to these procedures.

The E-Mail address passed to these procedures determines a range of orgcodes that this user is allowed see.

sp_html_envelope_1

    create procedure sp_html_envelope_1(@EMAIL_ADDRESS varchar(255) = NULL)
    as
    declare
        @ts_dt  varchar(10)         -- (current) week ending date
    ,   @org_id varchar(10)         -- org id   

        declare orgs cursor for select 
        select org_id
        from    http.dbo.CRON_EMAILS
        ,       deltek.deltek.USER_REPORTING_PRIVILEGE
        where   NAME = login_name and ADDRESS  = @EMAIL_ADDRESS
	
        /*
        ** Get the current week ending date
        */
        select  @ts_dt = convert(varchar,dateadd(weekday,-datepart(weekday,getdate())-7,getdate()),101)

        /*
        ** Step through our cursor for each org I can see and run report.
        */
        open orgs
        fetch orgs into @org_id
        if @@sqlstatus = 2
        begin
            select '<HTML>'
            select '<BODY>'
            select '<H2>I''m sorry...</H2>'
            select 'You are not listed to receive any Sold Time reports.<P>'
            if exists(select * from http.dbo.CRON_EMAILS where ADDRESS = @EMAIL_ADDRESS)
            begin
                select 'Although your E-Mail address and username in Sybase is valid, '
                select 'you have not been granted access to see any orgs.<P>'
            end else
            begin
                select 'You must have a valid username in Sybase that maps to your '
                select 'E-Mail address.  This is not the case so no reports can be '
                select 'created.<P>'
            end
            select '</BODY>'
            select '</HTML>'
        end
    
        while @@sqlstatus = 0
        begin
            exec http.dbo.run_sold_time_report @org_id, @ts_dt
            fetch orgs into @org_id
        end
        close orgs
        deallocate cursor orgs
        return

sp_html_envelope_2

    create procedure sp_html_envelope_2(@EMAIL_ADDRESS varchar(255) = NULL)
    as
    declare
        @ts_dt  varchar(10)         -- (current) week ending date
    ,   @org_id varchar(10)         -- org id
    ,   @ROW_ID int                 -- ROW_ID from insert.

        declare orgs cursor for 
        select  org_id
        from    http.dbo.CRON_EMAILS
        ,       deltek.deltek.USER_REPORTING_PRIVILEGE
        where   NAME = login_name and ADDRESS  = @EMAIL_ADDRESS

        /*
        ** Get the current week ending date
        */
        select  @ts_dt = convert(varchar,dateadd(weekday,-datepart(weekday,getdate())-7,getdate()),101)

        select '<HTML>'
        select '<HEAD><TITLE>Sold Time Report Scheduler</TITLE></HEAD>'
        select '<BODY>'

        /*
        ** Step through our cursor for each org I can see and run report.
        */
        open orgs
        fetch orgs into @org_id
        if @@sqlstatus = 2
        begin
            select '<H1>I''m sorry...</H1>'
            select 'You are not listed to receive any reports.<P>'
            if exists(select * from http.dbo.CRON_EMAILS where ADDRESS = @EMAIL_ADDRESS)
            begin
                select 'Although your E-Mail address and username in Sybase is valid, '
                select 'you have not been granted access to see any orgs.<P>'
            end else
            begin
                select 'You must have a valid username in Sybase that maps to your '
                select 'E-Mail address.  This is not the case so no reports can be '
                select 'created.<P>'
            end
        end else
        begin
            select '<H2>Your Sold Time Reports are being scheduled now.</H2>'
            select '<OL>'
        end
    
        while @@sqlstatus = 0
        begin
            exec @ROW_ID = http.dbo.sp_cron_insert
        
                @PROCNAME = "http.dbo.run_sold_time_report"     -- name of procedure
            ,   @EMAIL = @EMAIL_ADDRESS                         -- where it goes

            /*
            ** And put the parameters in the CRON_PARAMETERS table.
            */
            if ( @ROW_ID != -1 )
            exec @ROW_ID = http..cron_insert_parameters
            
                @ROW_ID = @ROW_ID
            ,   @NAME = "org_id"
            ,   @VALUE = @org_id
            
            if ( @ROW_ID != -1 )
            exec @ROW_ID = http..cron_insert_parameters
            
                @ROW_ID = @ROW_ID
            ,   @NAME = "ts_dt"
            ,   @VALUE = @ts_dt
        
            select '<LI>Org ID ' + @org_id + ' (' + @ts_dt + ')'
        
            fetch orgs into @org_id
        end
        close orgs
        deallocate cursor orgs

        select '</OL>'  
        select '</BODY>'
        select '</HTML>'
        return	

sp_html_envelope_3

    create procedure sp_html_envelope_3(@EMAIL_ADDRESS varchar(255) = NULL)
    as
    declare
        @ts_dt  varchar(12)         -- (current) week ending date
    ,   @org_id varchar(10)         -- org id
    ,   @ROW_ID int                 -- ROW_ID from insert.
    ,   @SCRIPTNAME varchar(63)     -- Output filename.

        declare orgs cursor for 
        select  org_id
        from    http.dbo.CRON_EMAILS
        ,       deltek.deltek.USER_REPORTING_PRIVILEGE
        where   NAME = login_name and ADDRESS  = @EMAIL_ADDRESS

        /*
        ** Get the current week ending date
        */
        select  @ts_dt = convert(varchar,dateadd(weekday,-datepart(weekday,getdate())-7,getdate()),101)

        select '<HTML>'
        select '<HEAD><TITLE>Sold Time Report Scheduler</TITLE></HEAD>'
        select '<BODY>'

        /*
        ** Step through our cursor for each org I can see and run report.
        */
        open orgs
        fetch orgs into @org_id
        if @@sqlstatus = 2
        begin
            select '<H1>I''m sorry...</H1>'
            select 'You are not listed to receive any reports.<P>'
            if exists(select * from http.dbo.CRON_EMAILS where ADDRESS = @EMAIL_ADDRESS)
            begin
                select 'Although your E-Mail address and username in Sybase is valid, '
                select 'you have not been granted access to see any orgs.<P>'
            end else
            begin
                select 'You must have a valid username in Sybase that maps to your '
                select 'E-Mail address.  This is not the case so no reports can be '
                select 'created.<P>'
            end
        end else
        begin
            select '<H2>Your Sold Time Reports are being scheduled now.</H2>'
            select 'You may select these reported by pointing your browser '
            select 'to any of the following URLs.<P>'
            select '<UL>'
        end
    
        while @@sqlstatus = 0
        begin
            select @SCRIPTNAME = "../htdocs/" + @EMAIL_ADDRESS + @org_id + '.html'

            exec @ROW_ID = http.dbo.sp_cron_insert
        
                @PROCNAME = "http.dbo.run_sold_time_report"     -- name of procedure
            ,   @SCRIPTNAME = @SCRIPTNAME                       -- name of file.

            /*
            ** And put the parameters in the CRON_PARAMETERS table.
            */
            if ( @ROW_ID != -1 )
            exec @ROW_ID = http..cron_insert_parameters
            
                @ROW_ID = @ROW_ID
            ,   @NAME = "org_id"
            ,   @VALUE = @org_id
            
            if ( @ROW_ID != -1 )
            exec @ROW_ID = http..cron_insert_parameters
            
                @ROW_ID = @ROW_ID
            ,   @NAME = "ts_dt"
            ,   @VALUE = @ts_dt
		
            select '<LI><A HREF="HTTP://mis.sri.com/' + @EMAIL_ADDRESS + @org_id + '.html">'
            select 'ORG ' + @org_id
            select '</A>'        
            fetch orgs into @org_id
        end
        close orgs
        deallocate cursor orgs

        select '</UL>' 
        select '</BODY>'
        select '</HTML>'
        return

Note: This method of sending reports (however cool) is very insecure!

Sybercron Utilities

sp_print

sp_print is a stored procedure in sybsystemprocs that allows you to create a disk file and then print that file to a true printer device. It does all of this in one procedure call.

Here is the prototype of sp_print:

    create procedure sp_print
    (   @PROCNAME          varchar(92)                -- This is your stored procedure.
    ,   @PARAM1            varchar(255) = NULL        -- 1st unnamed parameter (optional)
    ,   @PARAM2            varchar(255) = NULL        -- 2nd unnamed parameter (optional)
    ,   @PARAM3            varchar(255) = NULL        -- 3rd unnamed parameter (optional)
    ,   @SHELLSCRIPT       varchar(255) = "lpr.csh"   -- Name of shell script.
    ,   @FILENAME          varchar(255) = NULL        -- Name of disk file (optional).
    ,   @DEVICENAME        varchar(30)                -- Printer name.
    ,   @SENDER            varchar(255)               -- Your e-mail address.
    )

SHELLSCRIPT is the name of the shell script that will print the output result of your stored procedure. You may write your own shell script or use one of the scripts already provided for this purpose:

• HTML2PS.csh (converts HTML to postscript)
• lpr.csh (for printing plain text)
• PORTRAIT.csh (plain text)
• LANDSCAPE.csh (plain text)

FILENAME is the name of the disk file that is created. If not specified, a unique filename will be invented.

DEVICENAME must be a valid device name from the table CRON_DEVICENAMES in the http database. Currently, the following device names are recognized:

• ade-lj4
• hp4mv-aa261
• hp4mv-ad231
• hp4mv-ad236
• hp5ad136
• hp5m-ad136
• hp4000-ae024
• hp8000-ad231
• tech01

All of the above scripts will remove your file as soon as it has been scheduled to print with lpr. LANDSCAPE.csh and PORTRAIT.csh are available on KIRK only.

Example

    create procedure sp_HelloWorld
    as
        select '<HTML>'
        ,      '<BODY BGCOLOR=WHITE>'
        ,      '<H1>Hello World</H1>'
        ,      '</BODY>'
        ,      '</HTML>'
        return 0

    exec sp_print

        @PROCNAME = "sp_HelloWorld"
    ,   @SHELLSCRIPT = "HTML2PS.csh"
    ,   @DEVICENAME = "hp5ad136"
    ,   @SENDER = "nospam@nospams.com"

Example

The procedure that actually creates the HTML would need two parameters: BUTTON which tells it what to do and EMPLNO which tells it who to do it to.

A cursor, a while loop, and a call to sp_print is about all you will need to accomplish this:

    create procedure sp_schedule_PA_Reprints (@EFFDT datetime)
    as
        declare @EMPLID char(12)

        declare people cursor for
        select EMPLID
        from   PS_JOB_CURRENT
        where  EFFDT >= @EFFDT

        open people
        fetch people into @EMPLID
        while @@sqlstatus = 0
        begin
            exec sp_print

                @PROCNAME    = "HRPRD.dbo.sp_html_reprint"
            ,   @PARAM1      = "REPORT" -- corresponds to BUTTON.
            ,   @PARAM2      = @EMPLID  -- corresponds to EMPLNO.
            ,   @SHELLSCRIPT = "HTML2PS.csh"
            ,   @DEVICENAME  = "hp4mv-ad236"
            ,   @SENDER      = "yamini@unix.sri.com"

            fetch people into @EMPLID
        end
        close people
        deallocate cursor people
        return 0

Waiting on the ROW_ID returned from sp_print will wait for the completion status of the stored procedure. It does not wait on the completion status of HTML2PS.csh, however.

While useful in its own right, sp_print is also a great example on how to perform multiple tasks with Sybercron.

sp_Syberscript

sp_Syberscript in the http database is a stored procedure that invokes a shell script. Simply register this procedure and pass the name of your script and any parameters to the parameters of this procedure. Sybercron will schedule it for repeated execution just like any other stored procedure.

Example

    create procedure sp_StartScript
    as
        exec sp_Syberscript

                @SHELLSCRIPT = "./updateDirectoryServer.csh"
        ,       @PLATFORM    = "SPOCK"
        ,       @ARG1        = "ldif.update"
        ,       @ARG2        = "ldif.result"

        select '<HTML>'
        ,      '<BODY BGCOLOR=WHITE>'
        ,      '<H1>Your shell script has been started!</H1>'
        ,      '</BODY>'
        ,      '</HTML>'

        return 0

sp_sendmail

The prototype for sp_sendmail is as follows:

    create procedure sp_sendmail
    (   @SUBJECT          varchar(255)                -- Subject:
    ,   @TO               varchar(255)                -- To:
    ,   @FROM             varchar(255)                -- From:
    ,   @MESSAGE          varchar(255)                -- Body:
    ,   @CONTENTTYPE      varchar(30) = "text/plain"  -- text/plain or text/html.
    ,   @TIMESTAMP        datetime    = NULL          -- When to deliver (NULL if now).
    )

Example

    exec sp_sendmail

        @FROM = "nospam@nospams.com"
    ,   @TO = "nospam@nospams.com"
    ,   @SUBJECT = "This is some mail from sp_sendmail"
    ,   @MESSAGE = "Can you read this?"

sp_writetape

The prototype for sp_writetape is as follows:

    create procedure sp_writetape
    (   @SHELLSCRIPT      varchar(255)                 -- Name of shell script.
    ,   @SENDER           varchar(64)                  -- Your e-mail address.
    )

Example

    exec sp_writetape

        @SHELLSCRIPT = "/usr/local/home/operator/vsptape.csh"
    ,   @SENDER      = "kenneth.bailey@sri.com"

    Subject: PLEASE HANG SCRATCH TAPE ON ASTRO TO WRITE
    From: kenneth.bailey@sri.com
    To: operators@unix.sri.com
 
    Please mount a scratch tape on Astro to write!

    Instructions: 

    1. Mount a scratch tape and note the serial number.
    2. /usr/local/home/operator/vsptape.csh BXXXXX

    where BXXXXX is the serial number of the tape.

    Then please send me a reply saying the tape was successfully written.

    Thanks from kenneth.bailey@sri.com.

    #!/bin/csh

    # $1 serialno

    if ( $1 == "" ) then
        echo usage: $0 serialno
        echo Error: serialno expected
        exit 1
    endif

    cd /Sybernet

    #
    # Initialize globals...
    #
    set email      = kenneth.bailey@sri.com
    set LABELTYPE  = standard
    set SERIALNO   = $1
    set MAXRECSIZE = 242
    set BLOCKSIZE  = 242
    set UNITS      = characters
    set EXTMODE    = ebcdic
    set INTMODE    = ascii
    set VID        = SRI
    set FILENAME   = /Sybernet/document/vsptape.out

    umask 0
    set PROMPT = "nothing"
    echo ""
    echo "#PLEASE MOUNT SCRATCH TAPE ["$SERIALNO"] THEN ENTER OK TO TO GO"
    read $PROMPT

    echo "#WRITING "$FILENAME" TO TAPE..."
    /usr/local/bin/writetape                            \
        -LABELTYPE=$LABELTYPE                           \
        -SERIALNO=$SERIALNO                             \
        -MAXRECSIZE=$MAXRECSIZE                         \
        -BLOCKSIZE=$BLOCKSIZE                           \
        -UNITS=$UNITS                                   \
        -EXTMODE=$EXTMODE                               \
        -INTMODE=$INTMODE                               \
        -V=$VID                                         \
        $FILENAME                           

    if ( $status == "0" ) then
        echo ""
        echo "*******************************************************"
        echo "                         SUCCESS"   
        echo "*******************************************************"
        echo ""
        /usr/ucb/mail -s 'Tape was successfully created' $email < /dev/null
    else     
        echo ""
        echo "********************************************************"
        echo "                 WARNING - TAPE NOT CREATED"
        echo "********************************************************"
        echo "" 
        echo "#PLEASE RESTART IF PROBLEM WAS WITH TAPE DEVICE"
        echo ""
        /usr/ucb/mail -s 'OOPS - tape not created' $email < /dev/null
    endif

sp_permissions

This procedure can be used to save and restore permissions on any object in your database.  It was specifically designed for restoring permissions on a stored procedure after it has been dropped and recreated, but can also be used on any object.

Normally, permissions in the database are maintained by Dorothy Sloma  This means she will both grant and revoke permissions as requests are made.  When a programmer makes a change to a stored procedure, the procedure must be dropped before it can be recreated.  Dropping a procedure means you lose all permissions on this object.  When the permissions are not restored, this makes Dorothy extremely unhappy (not to mention the user who wants to execute this application).  sp_permissions was designed to eliminate this problem.

Here is the prototype for sp_permissions:

create procedure sp_permissions
(
    @BUTTON varchar(30)
,   @OBJECTNAME varchar(92)
)

BUTTON is one of SAVE or RESTORE.  SAVE causes the current permissions to be captured.  RESTORE applies those permissions back again.  OBJECTNAME is the name of this object in your database.

In your script that recreates a stored procedure you would invoke this procedure at the beginning of your script to save this procedure's permissions and at the end of your script to restore them.

If your procedure does not compile, you do not need to do anything special.  The next time you call sp_permissions with BUTTON=SAVE, the procedure will detect that your procedure does not exist and will hold on to the last permissions that were valid.

Example

    use HRPRD 
    go 
    exec sp_permissions @BUTTON = "SAVE", @OBJECTNAME = "sp_html_example" 
    go 
    drop procedure sp_html_example 
    go 
    create procedure sp_html_example 
    as 
        /* 
        ** Main body of procedure. 
        */ 
        return 0 
    go 
    if object_id("sp_html_example") != NULL 
    begin 
        select "<BR>Restoring permissions:<P>" 
        select "<SEND_PARTIAL>" 
        exec sp_permissions @BUTTON = "RESTORE", @OBJECTNAME = "sp_html_example" 
    end 
    go 

The test on object_id is not required.  If your procedure did not compile, sp_permissions will know this and not do anything.

Note too sp_permissions calls sp_cron_wait to wait for Sybercron to complete.  It then displays the complete list of users or groups who have been granted execute permission on your stored procedure.

Sybercron Tables

Program Notes

Deadlocks

This problem has been corrected.

Zombies

It is interesting to note that this particular bug has only of late become problematic. There are now more tasks being initiated and the chances that two or more finished at the same time has simply aggravated the situation.

This problem has been corrected.

Empty Files

If you have not guessed, only Sybercron attempts to connect, issue a command and create e-mail in one fell swoop.

This problem has been corrected.