Sybernet / Supplied Functions Reference
Release 3.00
Mar 03, 2005

HTTP.SP_CRON_INSERT

SP_CRON_INSERT is a stored procedure that allows you to submit procedures or UNIX executables to Sybercron. Sybercron is an automatic process that periodically looks for things to run in Oracle.

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.

Syntax

function sp_cron_insert
(
    @PROCNAME                      VARCHAR(92)
,   @ROW_ID                        VARCHAR(9)
,   @TITLE                         VARCHAR(255)
,   @DESCRIPTION                   VARCHAR(255)
,   @GROUPTYPE                     CHAR(1)
,   @EMAIL                         VARCHAR(255)
,   @CC                            VARCHAR(255)
,   @BCC                           VARCHAR(255)
,   @PRIORITY                      CHAR(1)
,   @SENDER                        VARCHAR(255)
,   @SCRIPTNAME                    VARCHAR(255)
,   @FORMAT                        VARCHAR(30)
,   @FILTER                        VARCHAR(30)
,   @CONTENTTYPE                   VARCHAR(30)
,   @HOSTNAME                      VARCHAR(30)
,   @USERCODE                      VARCHAR(30)
,   @PLATFORM                      VARCHAR(30)
,   @PASSWORD                      VARCHAR(30)
,   @TIMESTAMP                     DATETIME
,   @STARTTIME                     DATETIME
,   @STOPTIME                      DATETIME
,   @DATENAME                      VARCHAR(30)
,   @DAY                           VARCHAR(30)
,   @HOUR                          VARCHAR(30)
,   @MINUTE                        VARCHAR(30)
)

Parameters

Parameter	Description
procname	PROCNAME is the name of your stored procedure. To be compatible with the REGISTER table in the HTTP database, PROCNAME should include db_name(), owner, and name. Owner should not be implied. Specify dbo if that is what you mean. One exception is stored procedures in sybsystemprocs. Those procedures should neither specify db_name() nor owner. PROCNAME may also be a UNIX executeable or interpreter file if PROCNAME begins with either a period (.) or slash (/). Interpreter files are assumed to begin with #!; anything else is assumed to an executeable. Parameters to your C program are gotten from the table CRON_PARAMETERS, but instead of creating NAME=VALUE pairs as is done for stored procedures, only the VALUE is passed in the standard argv array. Parameters should also not include \n since this is used to separate each parameter VALUE. ./Syberload is a built-in function that allows you to upload UNIX files into Sybase.
row_id	ROW_ID uniquely identifies rows in this table. ROW_ID is primarily for the declaration of CRON_INDEX which allows sp_html_cron to declare a cursor for update. It is also used by sp_cron_insert to determine if a row is being updated (when not null) or inserted (when null). ROW_ID also points to the parameters table, CRON_PARAMETERS. This table is used to pass parameters to your stored procedure.
title	TITLE allows you to specify a title for this procedure. TITLE is used as the subject of your e-mail message. It also appears in the Sybercron Monitor.
description	DESCRIPTION is similar to TITLE except that it is suppose actually describe what this procedure does.
grouptype	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 GROUPS table, invoking the specified procedure once for each E-Mail address. Any other value means that the same report is sent to the same E-Mail address.
email	EMAIL may be either an E-Mail address or a GROUPNAME from table GROUPS. Note that a GROUPNAME does not actually have to exist in table GROUPS. sp_html_cron will skip over any procedures that don't actually have any addresses assigned. This allows you to add valid addresses at a later time.
cc	CC allows you to carbon copy your e-mail.
bcc	BCC allows you to blank-carbon copy your e-mail.
priority	PRIORITY determines your e-mail priority. Highest High Normal Low Lowest
sender	SENDER (or FROM) optionally specifies the sender of this E-Mail. If not specified, the default value is Sybernet. Sybernet must be a valid alias in /etc/aliases.
scriptname	SCRIPTNAME or EMAIL is required. If EMAIL is specified, SCRIPTNAME simply represents the name of the temporary file that is created before sendmail is called. If EMAIL is not specified, SCRIPTNAME represents the file name that is to be created and saved. In general, SCRIPTNAME should include a path to your htdocs directory; for example, ../htdocs/report1.
format	FORMAT determines the output format of your stored procedure. The default is SUPPRESSED.
filter	FILTER determines the filter option. The default is NULL, no filtering.
contenttype	CONTENTTYPE determines the Mime Content-type. The default is text/html.
hostname	HOSTNAME is a server name in your interfaces file. This field is optional if all of your procedures are executed at the same host because Sybercron logs into Sybernet with a default server name. If you specify HOSTNAME and do not specify USERCODE, a remote procedure call is invoked by appending PROCNAME to HOSTNAME (i.e., exec server.database.owner.procname). The remote server name must be valid and defined with sp_addserver
usercode	deprecated
platform	PLATFORM specifies the name of the UNIX system that is to invoke your procedure or script. PLATFORM corresponds to the identity value passed to Sybercron in the -I option. If you specify a value for PLATFORM that does not exist, your procedure or script will never be executed. The default value for PLATFORM is SPOCK.
password	deprecated
timestamp	TIMESTAMP determines the date and time that this procedure should next be executed. The procedure sp_html_cron is the stored procedure called from UNIX that determines what procedures are to be executed. This procedure uses a Better Late Than Never approach in this determination. That is, if your stored procedure is suppose to be executed on Monday's at 8 AM and the CRON job running from UNIX was restarted on Wednesday, your procedure will be selected for execution. If TIMESTAMP is NULL when inserted with sp_cron_insert, the next execution date is automatically calculated based on your value of DATENAME. If both TIMESTAMP and DATENAME are NULL, your procedure is scheduled for immediate execution and is then discarded from the CRON table. NULL values for TIMESTAMP are valid, but sp_html_cron will not select any entries in the CRON table for execution if their TIMESTAMP is NULL.
starttime	STARTTIME represents the earliest date that this procedure may be executed. A NULL value for STARTTIME means your procedure can be executed now.
stoptime	STOPTIME represents the latest date that this procedure may be executed. A NULL value for STOPTIME means your procedure can be executed forever. The Better Late Than Never approach affects STOPTIME this way. If TIMESTAMP (representing the next execution of this procedure) is less than or equal to STOPTIME, the procedure is still executed as long as TIMESTAMP is less than or equal the current date and time. This makes it possible (when TIMESTAMP and STOPTIME are equal) to schedule a procedure that is executed only once.
datename	DATENAME determines when your procedure is actually executed. DATENAME is one of the following values: ONCEONLY (once only, then discarded) HOUR (every hour) DAY (every day) WEEKDAY> (Monday through Friday) SUNDAY (every Sunday) MONDAY (every Monday) TUESDAY (every Tuesday) WEDNESDAY (every Wedneesday) THURSDAY (every Thursday) FRIDAY (every Friday) SATURDAY (every Saturday) MONTH (every month, executed on DAY) JANUARY (every January) FEBRUARY (every Febrary) MARCH (every March) APRIL (every April) MAY (every May) JUNE (every June) JULY (every July) AUGUST (every August) SEPTEMBER (every September) OCTOBER (every October) NOVEMBER (every November) DECEMBER (every December) Note that a NULL value for DATENAME will cause this entry in the CRON table to be deleted after it has been selected for execution. Calling sp_cron_insert with a NULL value for both TIMESTAMP and DATENAME is a convenient way to insert an entry for immediate execution while also deleting that entry as soon as it is submitted.
day	DAY corresponds to the day of the month. This value is meaningful only when DATENAME is MONTH or corresponds to one of the months of they year.
hour	HOUR represents the hour of the day.
minute	MINUTE represents the minute of the day.

Returns

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

Example 1

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:

declare
    @row_id int
begin
    exec @row_id=http.dbo.sp_cron_insert

        @PROCNAME   = 'http.dbo.sp_html_xmas'
    ,   @EMAIL      = 'nospam@nospams.com'
    ,   @DATENAME   = 'DECEMBER'
    ,   @DAY        = '25'
    ,   @HOUR       = '8'
end

Example 2

The next example runs sp_html_who every Monday at noon. This example also specifies both a STARTTIME and STOPTIME that limits the execution of this procedure for the year 1997 only.

declare
    @row_id int
begin
    exec @row_id=http.dbo.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'
end

Example 3

The next example runs the same procedure every day at 8:45 AM. Notice that in this example TIMESTAMP is specified (and not calculated automatically). This allows us to run the procedure immediately (assuming TIMESTAMP is less than or equal to sysdate).

declare
    @row_id int
begin
    exec @row_id=http.dbo.sp_cron_insert

        @PROCNAME   = 'http.dbo.sp_html_who'
    ,   @EMAIL      = 'denisunix.sri.com'
    ,   @TIMESTAMP  = 'December 24, 1996'
    ,   @DATENAME   = 'DAY'
    ,   @HOUR       = '8'
    ,   @MINUTE     = '45'
end

Example 4

PROCNAME may also be a UNIX executeable or interpreter file. Sybercron distinguishes these names from stored procedure because they begin with either a period (.) or a slash (/); for example to execute the C program HelloWorld in your cgi-bin directory you could use the following statement:

declare
    @row_id int
begin
    exec @row_id=http.dbo.sp_cron_insert

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