Sybernet / Supplied Functions Reference
Release 3.00
Mar 27, 2003

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                      VARCHAR2
,   ROW_ID                        VARCHAR2
,   TITLE                         VARCHAR2
,   DESCRIPTION                   VARCHAR2
,   GROUPTYPE                     VARCHAR2
,   EMAIL                         VARCHAR2
,   CC                            VARCHAR2
,   BCC                           VARCHAR2
,   PRIORITY                      VARCHAR2
,   SENDER                        VARCHAR2
,   SCRIPTNAME                    VARCHAR2
,   FORMAT                        VARCHAR2
,   FILTER                        VARCHAR2
,   CONTENTTYPE                   VARCHAR2
,   HOSTNAME                      VARCHAR2
,   USERCODE                      VARCHAR2
,   PLATFORM                      VARCHAR2
,   PASSWORD                      VARCHAR2
,   TIMESTAMP                     DATE
,   STARTTIME                     DATE
,   STOPTIME                      DATE
,   DATENAME                      VARCHAR2
,   DAY                           VARCHAR2
,   HOUR                          VARCHAR2
,   MINUTE                        VARCHAR2
,   RESTART_COUNT                 VARCHAR2
,   DEPENDENT_ROW_ID              VARCHAR2
)
RETURN NUMBER;

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.
restart_count	RESTART_COUNT specifies how many times this procedure should be restarted if it completes unsuccessfully.
dependent_row_id	DEPENDENT_ROW_ID represents the ROW_ID in the HTTP.CRON table of the procedure that should be executed after a successfull completion of this procedure.

Returns

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

CONTENTTYPE

You can specify any contenttype you desire even if that contenttype doesn't exist. When sending your results to an e-mail address, however, Sybernet will act upon some of the most well known values.

Value	Description
text/html	This content type is rendered inline. Sybernet attempts to insert a line-feed at the beginning of each HTML tag if you did not.
text/plain	This content type is rendered inline. Sybernet does not touch this output.
application/html	This content type is rendered as an attachment. Sybernet attempts to insert a line-feed at the beginning of each HTML tag if you did not.
application/rtf	This content type is rendered as an attachment. Sybernet inserts a line-feed at the beginning of each RTF tag if that tag would cause this line to exceed 71 characters.
application/octet-stream	This content type causes your e-mail to be sent using base64 encoding. This content type expects (but does not require) that you also specify the disposition as attachment with an appropriate filename. The filename is important here because it tells your mail client the name and type of document you are sending.
x-uuencode	This content type assumes your stored procedure has already encoded your output using base64.

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 binary_integer;
begin
    row_id:=http.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 binary_integer;
begin
    row_id:=http.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 binary_integer;
begin
    row_id:=http.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 binary_integer;
begin
    row_id:=http.sp_cron_insert
    ( 
        PROCNAME   => './HelloWorld'          -- A C program
    ,   SCRIPTNAME => '../htdocs/datafile'    -- required
    )   ;
end;