Examples

Since our buttons are no longer part of our form, we want to declare a hidden field that will be used to store the value of our button. In the following form declaration, the name of this button will be called BUTTON.

       exec sp_html_form

           @NAME = "myForm"
       ,   @PROCNAME = "sp_html_form"
       ,   @FORMAT = "SUPPRESSED"
	   
       exec sp_html_input

           @TYPE="HIDDEN"
       ,   @NAME="BUTTON"

       exec sp_html_input

           @TYPE="HIDDEN"
       ,   @NAME="SEARCH"

       exec sp_html_form

The first example illustrates how to define a reset button:

       exec sp_html_button

           @TYPE="RESET"
       ,   @NAME="BUTTON"
       ,   @VALUE="Reset"
       ,   @FORM="myForm"

While this looks like a regular reset button, it can actually reside in a different window or frame. If your buttons do not, you should probably define your submit buttons with sp_html_input.

Here is how to define the same button as an image:

       exec sp_html_button

           @TYPE="UNDO"
       ,   @NAME="BUTTON"
       ,   @VALUE="Reset"
       ,   @FORM="myForm"
       ,   @STATUS="Reset information on this page"

The next example illustrates how to define a submit button:

       exec sp_html_button

           @TYPE="SAVE"
       ,   @NAME="BUTTON"
       ,   @VALUE="Save"
       ,   @FORM="myForm"
       ,   @STATUS="Click here to save your changes"

Here's a similar example that first asks the user if they really want to save their change and then calls a JavaScript function (defined by you) before the form is submitted:

       exec sp_html_button

           @TYPE="SAVE"
       ,   @NAME="BUTTON"
       ,   @VALUE="Save"
       ,   @FORM="myForm"
       ,   @CONFIRM="You really want to save this?"
       ,   @onClick="if (!validate(myForm)) return false;"
       ,   @STATUS="Click here to save your changes"

The next example illustrates how to override the form submission and call another procedure:

       exec sp_html_button

           @TYPE="HOME"
       ,   @PROCNAME="sp_html_login"
       ,   @TARGET="_parent"
       ,   @FORM="Home"
       ,   @NAME="Home"
       ,   @VALUE="Home"
       ,   @STATUS="Return to the Sybernet main menu"

The next example illustrates how you can prompt your user for some text. It uses the PROMPT option, and only the value of your button (indicated by NAME) and the text that was entered when the prompt was displayed is returned to your procedure:

       exec sp_html_button

           @TYPE="FIND"
       ,   @PROCNAME="sp_html_form"
       ,   @TARGET="Frame2"
       ,   @NAME="Button"
       ,   @VALUE="Find"
       ,   @STATUS="Search for some text in this file"
       ,   @PROMPT = "SEARCH"

If you create more than one button with this procedure, you should probably include the whole thing in a table so that the buttons are exactly where you want them. Here is an example of how you might create a tool bar:

       select "<TABLE BORDER=4 BGCOLOR=BLACK><TR><TD ALIGN=CENTER>"
       select "<TABLE BORDER=0 WIDTH=100% BGCOLOR=BLACK CELLPADDING=0 CELLSPACING=0><TR><TD>"

       exec sp_html_button @TYPE="SAVE", @NAME="BUTTON", @FORM="MyForm", @VALUE="Save"
       exec sp_html_button @TYPE="NEXT", @NAME="BUTTON", @FORM="MyForm", @VALUE="Next"
       exec sp_html_button @TYPE="LAST", @NAME="BUTTON", @FORM="MyForm", @VALUE="Last"

       select "</TD></TR></TABLE>"
       select "</TD></TR></TABLE>"

sp_html_href

To add the ability to create a directory of hyper-text links, we've added some parameters to do this. In particular, DESCRIPTION will cause the anchor and description to be separated by paragraph tags.

All of the parameters are optional (which doesn't make a lot of sense, but it is consistent with HTML) and they are all varchars.

• PROCNAME

  PROCNAME is the name of the stored procedure to invoke. PROCNAME corresponds to the direct argument in a URL request.

  If not NULL, PROCNAME is appended to the name of the Sybernet CGI.

• SEARCH

  SEARCH represents the parameters to PROCNAME. sp_html_href will automatically URL Encode this string so you no longer have to worry about special characters when invoking a procedure.

  Be aware that if you are passing multiple parameters to a procedure that the entire length of this string does not exceed 255 characters. If it does, you will need to construct this argument the old fashion way.

  SEARCH corresponds to the Search argument in a URL request.

• HREF

  HREF allows you to specify the URL yourself. This would be used when you need to point this link to something other than a stored procedure. If specified, PROCNAME and SEARCH are ignored.

• CHECKED

  If DESCRIPTION is not NULL, the value in CHECKED is compared to the value in SEARCH. If equal, the color of the anchorText will be red; otherwise, it will be white.

• TARGET

  TARGET optionally specifies the windowName to be used for output when this link it clicked. If not specified, output returns to the same window.

• anchorText

  anchorText is the text to display at the anchor. If not specified, the value of SEARCH is used instead.

• DESCRIPTION

  If specified, DESCRIPTION is displayed after the anchor object. It will also separate that anchor with a <P> tag. In addition, a non-NULL value of DESCRIPTION determines the color of the anchorText. The CHECKED option determines what that color will be.

• STATUS

  The value of STATUS is written to the status line when a MouseOver event occurs on this link.

• onMouseOver

  A mouseOver event occurs once each time the mouse pointer moves over an object from outside of that object.

• onMouseOut

  A mouseOut event occurs each time the mouse pointer leaves an object.

• onClick

  A click event occurs when a link is clicked. The onClick event handler executes JavaScript code when a click event occurs.

• IMAGE

  If specified, IMAGE represents the name of an image in the images directory and this image is used in lieu of anchorText.

• NAME

  NAME represents the name of your image. This is optional unless you need to refer to that image name in JavaScript.

• BORDER

  BORDER defines the size of the border that bounds your image. When 0, no border occurs.

Examples

       exec sp_html_href

           @PROCNAME = "sp_html_login"
      ,    @anchorText = "Return to the Sybernet Main Menu screen"
      ,    @STATUS = "Click here to return to the main menu"

       exec sp_html_href

           @PROCNAME = "sp_html_template"
      ,    @SEARCH = "Order Pizza"
      ,    @CHECKED = @SEARCH
      ,    @DESCRIPTION = "Click here to order your pizza on the world wide web."

Since the name of the Sybernet CGI is automatically included when PROCNAME is not NULL, you wouldn't use this procedure to create anchor tags that point to anything other than a Sybase stored procedure; however, JavaScript is quite powerful, and this can be accomplished by the following example:

       exec sp_html_href

           @PROCNAME = NULL
      ,    @anchorText = "Sybernet Home Page"
      ,    @onClick = "this.href='http://Sybernet.sri.com/'"

       exec sp_html_href

           @PROCNAME = NULL
      ,    @anchorText = "Open Another Window"
      ,    @onClick = "window.open('','Sybernet','width=600,height=160');this.href='http://Sybernet.sri.com/'"
      ,    @TARGET = "Sybernet"

http..sp_html_menubar

The parameters allowed to this procedure include the following:

• PROCNAME

  PROCNAME is the name of the stored procedure that should be executed when the user clicks on the Instructions image on the menubar.

• SEARCH

  SEARCH are any arguments that you wish to pass to this procedure; for example, you might wish to have one help procedure defined that displays help text based on the parameter passed to it.

  While the SEARCH string could be appended to PROCNAME in the usual way, passing it separately guarantees that this string will be valid because it will automatically be URL Encoded.

• STATUS

  STATUS is written to the window's status line when a mouseOver event occurs on this link.

• TARGET

  When TARGET is specified, a new window is created which will be used for the output text of your stored procedure. When NULL, output is sent to the same window.

• WIDTH

  WIDTH specifies the width of your new window in pixels. The default is 200. WIDTH is ignored if TARGET is NULL.

• HEIGHT

  HEIGHT specifies the height of your new window in pixels. The default is 400. HEIGHT is ignored if TARGET is NULL.

• RESIZEABLE

  RESIZEABLE determines if the new window created can be resized. Allowed values are yes and no. The defulat is yes.

• SCROLLBARS

  SCROLLBARS determines if the new window created will have scroll bars. Allowed values are yes and no. The default is no.

Examples

        exec http..sp_html_menubar

            @PROCNAME = "sp_html_help"
        ,   @SEARCH = "Step 3"
        ,   @STATUS = "Click here for information on how to use this tool!"

The next example works exactly the same way except that it creates a new browser window to send this information.

        exec http..sp_html_menubar

            @PROCNAME = "sp_html_help"
        ,   @SEARCH = "Step 3"
        ,   @TARGET = "HelpWindow"
        ,   @WIDTH = "400"
        ,   @HEIGHT = "600"

sp_audit

The prototype for sp_audit looks like this:

    create procedure sp_audit
    (
        @PROCNAME          varchar(92)             -- Name of procedure.
    )

• TIMESTAMP - The datetime this procedure was called.
• USERNAME - suser_name().
• HOSTNAME - host_name().
• PROCNAME - The procedure name.

Example

    select @PROCID = @@procid
    exec sp_getProcname @PROCID, @PROCNAME output
	
    exec http..sp_audit @PROCNAME

Example

    create procedure sp_example
    as
        /*
        ** Call sp_audit and pass my procedure name to it.
        */
        exec http..sp_audit "sp_exumple"
        return 0

sp_html_frame

When PeopleSoft was installed last year, all of the web reports that were created used a common convention that made it possible to develop these applications in the shortest time possible while providing a common look and feel to every report.

The common look and feel was accomplished by a stored procedure called sp_html_frame. This look and feel was not only seen by the user in what was rendered on their browser, but also by the developer because the internal working of every procedure followed the same convention. The point is, without close inspection it is difficult to tell the difference between Personnel Roster #5 and Employees Compensation Report.

Here is the prototype of sp_html_frame:

    create procedure sp_html_frame
    (   @BUTTON            varchar(92) = NULL      -- NULL or name of frame to build.
    ,   @PROCNAME          varchar(92) = NULL      -- Name of procedure.
    ,   @VERSION           varchar(60) = NULL      -- Version of procedure.
    ,   @TITLE             varchar(92) = NULL      -- Title on North frame.
    ,   @NORTH             varchar(10) = "70"      -- Size of north frame.
    ,   @CENTER            varchar(10) = NULL      -- Size of center frame.
    ,   @WEST              varchar(10) = NULL      -- Size of west frame.
    ,   @EAST              varchar(10) = NULL      -- Size of east frame.
    ,   @SOUTH             varchar(10) = NULL      -- Size of south frame.
    ,   @onLoad            varchar(255)= NULL      -- JavaScript onLoad event.
    ,   @BORDER            varchar(10) = "1"       -- Size of border around each frame.
    )

The frames are called NORTH, SOUTH, EAST, WEST, and CENTER. That is what they are called and that is how you will refer to them in your application. The NORTH frame is required and is built automatically by sp_html_frame. The other frames are optional are are built only when non-null values are given to their name.

When all frames are defined they resemble the following layout:

Example 1

BUTTON's sole purpose is to represent the state of your application. When it is null, your application is initializing and will call sp_html_frame. When it is not null, its value might be the name of the frame you are to populate, or it might be the value of a button on one of your form screens.

    /******************************************************************************/
    /*                                                                            */
    /* PATCH 001 Initial release                                     (08/01/1999) */
    /*                                                                            */
    /******************************************************************************/
    create procedure sp_example_1
    (   
        @BUTTON varchar(30) = NULL
    )
    as
    declare
        @PROCID                         int                 -- @@procid
    ,   @PROCNAME                       varchar(92)         -- db_name().owner.name.
    ,   @TITLE                          varchar(64)         -- Report title.
    ,   @VERSION                        varchar(16)         -- Report version.

        /*
        ** Initialize...
        */
        select @PROCID = @@procid
        exec sp_getProcname @PROCID, @PROCNAME output
        select @VERSION = "Version 1.01"
        select @TITLE = DESCRIPTION
        from   http..HTTP_REGISTER
        where  PROCNAME = @PROCNAME

        if @BUTTON is NULL
        begin
            /*
            ** Tell sp_html_frame to build the frames.
            */
            exec sp_html_frame

                @BUTTON = NULL
            ,   @PROCNAME = @PROCNAME
            ,   @VERSION = @VERSION
            ,   @TITLE = @TITLE
            ,   @SOUTH = "*"
            return 0
        end

        if @BUTTON = "SOUTH"
        begin
            /*
            ** sp_html_frame is telling me to populate the south frame.
            */
            select '<HTML>'
            ,      '<BODY BGCOLOR=WHITE>'
            ,      '<CENTER>'
            ,      replicate('<BR>',20)
            ,      'South'
            ,      '</CENTER>'
            ,      '</BODY>'
            ,      '</HTML>'
            return 0
        end

Example 1 - explained

    select @PROCID = @@procid
    exec sp_getProcname @PROCID, @PROCNAME output

PROCNAME is passed to sp_html_frame and is displayed in the upper-left hand corner of the NORTH frame. Your users probably don't care about this name, but you do.

VERSION is useful too, and like PROCNAME it is also displayed on the NORTH frame. It's suppose to be incremented after each change to your application.

    select @VERSION = "Version 1.01"

The single line patch comments at the top of this procedure go hand-in-hand with the version number of your program. Everytime a change is made to the source (no matter how trivial), add a description of that change to the patch heading and increment the version number.

In the Naked Gun series staring Leslie Neilson, one of the funnier bits was at the beginning of the show when the announcer would tell you the name of the episode while the name was printed at the bottom of the screen. The two were never the same, you see.

    select @TITLE = DESCRIPTION
    from   http..HTTP_REGISTER
    where  PROCNAME = @PROCNAME

In this application we are using only two frames: NORTH and SOUTH. The remaining frames can be passed as NULL or omitted from the call to sp_html_frame.

    exec sp_html_frame

        @BUTTON = NULL
    ,   @PROCNAME = @PROCNAME
    ,   @VERSION = @VERSION
    ,   @TITLE = @TITLE
    ,   @SOUTH = "*"

    @SOUTH = "*"

sp_html_frame will cause your application to be invoked for each frame that is not NULL, and it does this by passing that frame name for the value of BUTTON. In this example, BUTTON will be assigned to SOUTH. Your application will test for this value and populate the frame that corresponds to this value.

    if @BUTTON = "SOUTH" 
    begin
        /*
        ** sp_html_frame is telling me to populate the south frame.
        */
    end

Example 1 - illustrated

Example 2

    /******************************************************************************/
    /*                                                                            */
    /* PATCH 001 Initial release                                     (08/01/1999) */
    /* PATCH 002 Add form screen and JavaScript to look up employees (08/02/1999) */ 
    /*                                                                            */
    /******************************************************************************/
    create procedure sp_example_2
    (
        @BUTTON             varchar(30) = NULL     -- State of procedure.
    ,   @employeenumber     varchar(5)  = NULL     -- employee number.
    )
    as
    declare
        @PROCID             int                    -- @@procid
    ,   @PROCNAME           varchar(92)            -- db_name().owner.name.
    ,   @TITLE              varchar(64)            -- Report title.
    ,   @VERSION            varchar(11)            -- Report version.
    ,   @onLoad             varchar(255)           -- JavaScript onLoad event.

        select @PROCID = @@procid
        exec sp_getProcname @PROCID, @PROCNAME output
        select @VERSION = "Version 1.02"
        select @TITLE = DESCRIPTION
        from   http..HTTP_REGISTER
        where  PROCNAME = @PROCNAME
    
        if @BUTTON is NULL
        begin
            exec sp_html_frame
        
                @PROCNAME    = @PROCNAME
            ,   @VERSION     = @VERSION
            ,   @TITLE       = @TITLE
            ,   @SOUTH       = "*"
        
            return 0
        end
    
        if @BUTTON = "SOUTH"
        begin
            /*
            ** Build the Center frame.
            */
            select '<HTML>'
            ,      '<BODY BGCOLOR=WHITE onLoad="window.document.FORM.employeenumber.focus();window.document.FORM.employeenumber.select();">'
            ,      '<CENTER>'
            ,      replicate('<BR>',2)
            ,      '<FONT SIZE=5 COLOR=NAVY><B><I>' + @TITLE + '</I></B></FONT>'
            ,      replicate('<BR>',2)

            exec sp_html_form
        
                @NAME = "FORM" 
            ,   @PROCNAME = @PROCNAME
            ,   @FORMAT = "SUPPRESSED"
            ,   @METHOD = "POST"
            ,   @TARGET = "NORTH" 

            exec sp_html_input
        
                @TYPE = "HIDDEN"
            ,   @NAME = "BUTTON"
            ,   @VALUE = "LOOKUP"  

            select '<TABLE BORDER=4 BGCOLOR=LIGHTGREY CELLPADDING=10 CELLSPACING=1>'
            ,      '<TR>'
            ,      '<TD>'
            ,      '<TABLE BORDER=0 WIDTH=100%>'
            ,      '<TR>'
            ,      '<TD>EMPLID:</TD>'
            ,      '<TD>'
        
            exec sp_html_input
        
                @TYPE = "TEXT"
            ,   @NAME = "employeenumber"
            ,   @SIZE = "5"
            ,   @MAXLENGTH = "5"
            ,   @onChange = "this.form.submit();"
        
            select '</TD></TR><TR><TD>NAME:</TD><TD>'
        
            exec sp_html_input
        
                @TYPE = "TEXT"
            ,   @NAME = "cn"
            ,   @SIZE = "30"

            select '</TD></TR><TR><TD>TITLE:</TD><TD>'
        
            exec sp_html_input
        
                @TYPE = "TEXT"
            ,   @NAME = "title"
            ,   @SIZE = "30"
        
            select '</TD></TR><TR><TD>E-MAIL ADDRESS:</TD><TD>'
        
            exec sp_html_input
        
                @TYPE = "TEXT"
            ,   @NAME = "mail"
            ,   @SIZE = "30"        

            select '</TD></TR></TABLE>'
            ,      '</TD></TR></TABLE>'
            ,      '<P>'
            ,      replicate('<BR>',2)
            ,      '<FONT COLOR=NAVY>Enter an employee number and press the TAB key</FONT>'
            ,      '</CENTER>'
        
            exec sp_html_form
        
            select '</BODY>'
            ,      '</HTML>'
            return 0
        end
    
        if @BUTTON = "LOOKUP"
        begin
            exec sp_html_frame
        
                @PROCNAME     = @PROCNAME
            ,   @VERSION      = @VERSION
            ,   @TITLE        = @TITLE
            ,   @WEST         = NULL
            ,   @BUTTON       = "NORTH"
        
            select '<SCRIPT>'
            ,      'parent.SOUTH.document.FORM.cn.value="'
            ,      cn
            ,      '";'
            ,      'parent.SOUTH.document.FORM.title.value="'
            ,      title
            ,      '";'
            ,      'parent.SOUTH.document.FORM.mail.value="'
            ,      mail
            ,      '";'
            ,      'parent.SOUTH.document.FORM.employeenumber.focus();'
            ,      'parent.SOUTH.document.FORM.employeenumber.select();'
            ,      '</SCRIPT>'
            from   http..ldif_view
            where  employeenumber = @employeenumber
        
            if ( @@rowcount = 0 )

            select '<SCRIPT>'
            ,      'parent.SOUTH.document.FORM.cn.value="";'
            ,      'parent.SOUTH.document.FORM.title.value="";'
            ,      'parent.SOUTH.document.FORM.mail.value="";'
            ,      'parent.SOUTH.document.FORM.employeenumber.focus();'
            ,      'parent.SOUTH.document.FORM.employeenumber.select();'
            ,      '</SCRIPT>'

            return 0        
        end

Example 2 - explained

    exec sp_html_form
        
        @NAME = "FORM" 
    ,   @PROCNAME = @PROCNAME
    ,   @FORMAT = "SUPPRESSED"
    ,   @METHOD = "POST"
    ,   @TARGET = "NORTH"

    exec sp_html_input
        
        @TYPE = "HIDDEN"
    ,   @NAME = "BUTTON"
    ,   @VALUE = "LOOKUP"

Again, the result of our submit is targeted at the NORTH frame and BUTTON is assigned to LOOKUP. The code for that function first calls sp_html_frame to redraw the NORTH frame. The result is that the NORTH flickers ever so briefly.

The JavaScript that is created then populates the fields on our form. Since our code is now running in the NORTH frame, it qualifies these field names appropriately:

    <SCRIPT>
    parent.SOUTH.document.FORM.cn.value="Mary Smith";
    </SCRIPT>

    exec sp_html_frame
        
        @PROCNAME     = @PROCNAME
    ,   @VERSION      = @VERSION
    ,   @TITLE        = @TITLE
    ,   @WEST         = NULL
    ,   @BUTTON       = "NORTH"
    ,   @onLoad       = "alert('Oops!\n \nEmployee number not found.');"

    exec sp_html_frame
        
        @PROCNAME     = @PROCNAME
    ,   @VERSION      = @VERSION
    ,   @TITLE        = @TITLE
    ,   @WEST         = NULL
    ,   @BUTTON       = "NORTH"
    ,   @onLoad       = "if (confirm('Are you sure?')) window.document.FORM.submit();"

Because I modified the original source, I commented this change in the patch heading and incremented the version number.

Example 2 - illustrated