November 16, 1997 Version 1.9 
Sybernet Form Maker
 

Introduction

The Sybernet Form Maker is a Java applet that is invoked from a Sybase stored procedure that allows you to "paint" a form screen that is equipped to handle a variety of built-in functions.

Painting allows you to move columns, labels, and buttons anywhere on your screen. Columns may be defined as simple text fields or you can redefine them as checkboxes, radio buttons, select lists, or textareas.

The built-in functions provide buttons that allow you to search, delete, modify, and create rows in your table. You can even define your own buttons to add almost any functionality you desire or override the buttons that are provided by default.

By default, labels are created automatically for each column name in your table or for each parameter in your procedure. You can change these labels value, erase them, assign them to behave like hyper-text links, and even add more labels should you desire. Labels can also be defined with a particular color, style, font , or size.

When a table is selected and the Sybernet Form Maker applet is loaded, a new window is created where you edit your screen. If this is the first time you've selected this table, every column from the table and all buttons available are displayed on this screen. You will want to delete those items that are not needed and move those that are to where you want them to reside on the screen.

It takes a few moments to load the applet so be patient while this occurs. After you select a table from your database to edit, a new cover page is created containing a hyper-text link to the procedure that will be compiled by the applet. Do not click on this link until your procedure has been successfully compiled.

Requirements

To paint and compile your stored procedure you will need a Java aware browser.

To run your procedure you will need a JavaScript aware browser that also recognizes dynamic HTML. The stored procedure that is compiled uses the <LAYER> tag to perform absolute positioning. Layers are available in Netscape's Communicator 4.0. Navigator versions 3.0 and less do not recognize layers and may not be used to run your procedure. If you attempt to run the compiled application with a browser that does not satisfy these requirements, an appropriate messages is displayed and the invocation is denied.

To add functionality to your procedure--above and beyond what the Sybernet Form Maker already provides--you will need to understand JavaScript. An understanding of the Sybernet SDK will also be useful.

Invoking the Form Maker

Go to Sybernet Utilities from the Sybernet Main Menu and click the link for Syberform. Anonymous access is also available by pointing your browser to:

http://mis.sri.com/cgi-bin/Sybernet.cgi$sp_html_Syberform

As with other utilities in this menu, Syberform allows you to drill down and up through the desired database. Syberform allows you to choose a table, view, or procedure.

When selecting a table or view and Syberform has already created a procedure to access that table, you will be given two or more choices. The first choice is identified by the name of that table or view and means you are starting a new application. The other choices will be procedures that you created with Syberform and means you can resume modifications to that application.

When selecting a procedure, Syberform first determines if that procedure was created with the Sybernet Form Maker. If that is the case, you will resume editing in that application; otherwise, you are telling Syberform that you want to create an application that invokes a stored procedure.

What to do First

Order is not important, but one of the first things you want to do is define the key columns for your application. Failure to supply one or more columns as a key will cause the compilation to fail. Failure to supply key columns that do not form a unique key will cause your stored procedure to operate on more than one row; this will be most confusing when you go and delete a row.

By default, the first column in your table is selected as the only key. If this column does not form part of the key, simply click that column's name from the Keys menu item to deselect it.

Since keys are assigned a weight, you will want to select the desired columns in order from the Keys menu item.

Key columns do not have to appear on your form. Any field that is not wanted can be selected and then erased from the screen by selecting the Clear menu item of the Edit menu.

What to do Next

Eliminate any columns and labels you do not want to appear on this form.

Eliminate any buttons you do not need; for example, if the form is for inquiry purposes only, get rid of those buttons that cause an update or insert to your table; the Save, Undo, Drop, and New buttons are good candidates here.

Although you can add more labels and buttons at any time (and you probably want to), it doesn't make any sense to add columns to your form when your application is built to access a table. It does make sense, however, when you are accessing a stored procedure.

Selecting Items

You can select columns, labels, and buttons by clicking that item with your mouse. To select a graphic, however, you must select that item's edge. To select multiple items, use your mouse to create a rectangle around the items you wish to select. When your mouse is released, all items that intersect this rectangle are selected and high-lighted. For graphic items, the rectangle must intersect an edge.

You can also select any one item by selecting that item's name from one of the three menus (Columns, Labels, Buttons). Note that labels have the same name as the column they correspond to. Although you can change a label's value to something more readable, it will always be referred to as the name of the column that it spans.

Graphics do not appear in the menu bar. The only way to select them is with your mouse.

Moving Elements

To move an item, simply put your mouse on that object and drag it to the position desired.

To move a group of items, use your mouse to create a rectangle around the items you wish to move. When your mouse is released, all items that intersect this rectangle are selected and high-lighted. Grab any one of these selected items and move the whole lot to the position desired.

Stretching Items

Graphic items and items of type text, select, and textarea can be stretched (or resized) by clicking on either the lower-right or lower-left corner of that item and moving the mouse appropriately to stretch that item. Be sure to unselect an item before attempting to stretch it, however, because selected items are moved.

Textareas can also be stretched downward or upward. This is the only way to decrease or increase the number of rows for a textarea.

Graphics too can be stretched downward or upward. In fact, this is the only way to change their size. The value of size from the menu bar controls a graphic item's border.

In each case, when you release your mouse, the item's size might change slightly from what you have specified. This occurs because the number of characters and rows fall into well defined bounds.

Aligning Items

Select the items to be aligned as described above by creating a rectangle with your mouse around the items to be aligned. Then choose the appropriate menu item from the Align menu.

The Menu Bar

While every attempt has been made to make the menu bar as intuitive as possible, some features may, at first, be confusing. This section attempts to explain how the menu bar works.

Defining Initial values

Your input fields may or may not require initial values. If they do, simply select those items and change their value with the Attributes... menu item from the Edit menu.

Initial values will appear in those fields when the user clicks the New button. Initial values are also needed for checkboxes and select fields, though the latter can be gotten from a stored procedure that you write.

You may also use Procname to specify initial values. The difference is this. Value is used to specify static values while Procname allows yo to specify dynamic values; for example, a date field could be specified statically as "Nov 25, 1997" using Value or dynamically as getdate() using Procname.

Defining Select Fields

Select fields create a drop-down list of names. There are several ways to populate this list:

Defining Checkbox Fields

Checkboxes need a value to determine if that item is checked. To define the value for a checkbox, select that item and change its value with the Attributes... menu item of the Edit menu.

By default, unchecked items return NULL when the form is submitted. This is analogous with how checkboxes behave in regular HTML forms; however, by separating its checked value and its unchekced value with a comma, you can control both states.

For example, if the Value for a checkbox is "Yes" and the form is saved, "Yes" is written to the database when the item is checked, and NULL is written when the item is unchecked.

To allow both Yes and No, simply specify Value as "Yes,No" without any gratuitous spaces.

In a new form, the unchecked option (implied or not) is used as the default value.

Defining Radio Fields

As in HTML radio buttons are grouped by name. Having one radio button doesn't make much sense, so you will want to add new radio buttons from the File menu and change their name so all the button names in a group match.

Radio buttons also require an initial value, and you will not be able to compile your application until this has been done. Simply select the Attributes... menu item from the Edit menu and enter a value for each radio button that you have defined.

Because of the way layers behave, SyberForm must use the JavaScript onClick method to set and reset radio buttons. You can override onClick, but if you do you should manually call the function setRadio passing the keyword this as the only parameter or return false from your onClick code; for example, 

    if (someFunction())
        setRadio(this);
    else
    {
        this.selected=false;
        return false;
    }
It may seem odd that SyberForm allows you rename form elements, but in reality you may only change the name of elements that you created with the New... menu item. Only when you select the Compile... menu item does SyberForm check for invalid combinations (such as duplicate names where they are not permitted).

Frames, Forms, Layers, and Names

The Sybernet Form Maker uses frames. This makes it possible to update the fields on your screen without actually redrawing the entire page each time a request is made. The two frames created by the Sybernet Form Maker are called NORTH and CENTER. Three other frames are also available, but they must be defined by you in the Preferences menu item. They are WEST, EAST, and SOUTH. More about these in the section on Frames (described below).

NORTH represents the target frame for all requests. This is the frame that actually receives data from Sybase. It is the responsibility of this frame to update the values in the CENTER frame.

CENTER represents the target frame for all input. This is the frame that contains your form, labels and buttons. Because of the way that layers work, there are actually several forms created in this frame. The most important form, the form that is actually submitted to Sybase, is called FORM.

The <LAYER> tag allows the Sybernet Form Maker to perform absolute positioning. As powerful as this is, each form element within a layer requires its own pair of <FORM> tags. By themselves, these individual forms cannot be submitted so their contents must be copied to the main form called FORM.

A form element at the layer level is referred to with the following syntax:

        parent.CENTER.document.{element}.document.{element}.{element}
where {element} is a column from your table; for example, if you need to get the value of lastName at the layer level, you would specify this:

        parent.CENTER.document.lastName.document.lastName.lastName.value
You probably do not need to worry about accessing a form element inside of a layer tag because you can copy all element values to the main form using a built-in JavaScript function called copyform().

When your form is submitted, each element (at the layer level) is copied to the main form using this function. Thus to access lastName in the main form you could write the following:

        parent.CENTER.document.FORM.lastName.value
or 
        window.document.FORM.lastName.value
when the same frame (CENTER) refers to this form. In either case, the data is valid only after is has been copied with copyform.

Key columns (whether or not they are disabled) cannot be modified by the user, but they can be modified by you. Actually, this is usually a bad idea since you can cause some strange results, but you will need the ability to access their value when adding functionality to your application.

Key columns are identified by transposing the actual column they span; for example, if ROW_ID is a key, the key column is called DI_ROW. Key columns never appear at the layer level, and it is thus not necessary to call copyform() to retrieve their values from the main form.

So to access the key column for ROW_ID, you would simply say

        window.document.FORM.DI_WOR.value
And if ROW_ID is a real input field on your screen, you probably still want to refer to DI_WOR because the user may have altered the former.

More on Frames

By default, two frames are created for your application: NORTH and CENTER. Three other frames are also available if you decide to use them. They are WEST, EAST and SOUTH.

Pictorially, these frames (when all are defined) resemble the following:

North
West
Center
East
South

NORTH and CENTER are required and reserved. Do not reference these frames unless you absolutely know what you are doing or unexpected results are sure to occur.

WEST, EAST and SOUTH are optional; for example, the output from the FIND button is written to a new window that it creates. You can send these results to one of the optional frames by defining a value for that frame and setting the Target for the Find button to the name of that frame.

A Frame can be disabled by setting its value to 'NULL.' A value of 0 still means the frame is created, but occupies very little space on your screen. Setting the value of the NORTH frame to 0 is a good way to hide this frame from your application, should you desire.

If you are familiar with HTML frames, and I hope you are, the allowed values should come as no surprise. Frame size can be specified as an absolute value in pixels (60), a percentage (20%), whatever is left over from the other frames (*), or the keyword NULL.

Built-in JavaScript Functions

This section documents some of the JavaScript functions that are built when your procedure is compiled and executed.

To add even more functionality to your buttons or completely rewrite how they behave, you can specify or override your own onClick code for these buttons.

Creating Hyper-Text Links

You can create hyper-text links with your labels by specifying any combination of Procname, onClick, and Target.

To call a procedure that does not rely on the current context of your form, simply specify that procedure's name for the value of Procname.

        http..sp_html_show
To call a procedure that does rely on the current context of your form because you want to pass the values of fields on this form, a little more work is required. The simplest way is to call copyform and then construct your URL in the onClick method; for example, the following example of onClick calls the same procedure as above except that is passes the current value of lastName.

        copyform();
        this.href='Sybernet.cgi$http..sp_html_show?'
        + window.document.FORM.lastName.value;
Notice that this.href specifies the name of the Sybernet CGI while the previous example does not. The reason is that Procname causes this to occur automatically while the latter does not.

To avoid JavaScript errors, it is safer to call the JavaScript function escape to URI encode your parameters:

        copyform();
        this.href='Sybernet.cgi$http..sp_html_show?'
        + escape(window.document.FORM.lastName.value);
And to pass multiple parameters you'll want to specify the parameter name and value as illustrated by the following:

        copyform();
        this.href='Sybernet.cgi$http..sp_html_show?'
        + '@lastName=%22'
        + escape(window.document.FORM.lastName.value)
        + '%22,@firstName=%22'
        + escape(window.document.FORM.firstName.value)
        + '%22';
You may also want to link your form to another form created by the Sybernet Form Maker. This too is possible, but you must follow the rules, of course.

In the following examples, sp_form_container contains a unique key field called ROW_ID. sp_form_component has a non-unique key field having the same name. This is a one-to-many (or parent to child) relationship.

If the context is not important, you may call these procedures as described above, but if the context is important, then you may invoke these stored procedures with one or more key fields that you defined when these procedures were defined.

        this.href='Sybernet.cgi$http..sp_form_component?
        @ROW_ID=%22' + window.document.FORM.DI_WOR.value + '%22';
This won't work, however, when our key value is null. To determine this condition we can examine an internal hidden variable on our form called HIDDEN. This variable contains the value "Update" when updating a form and "Insert" when creating a new row. Here's a better solution:

        var s='';
        if (window.document.FORM.HIDDEN.value=='Insert')
        {
            alert('You may not see any children until you have selected a parent!');
            return false;
        }
        s='%22' + window.document.FORM.DI_WOR.value + '%22';
        this.href='Sybernet.cgi$http..sp_form_component?@ROW_ID=' + s;
When sp_form_component is invoked with the above code, only the ROW_ID's that equal the ROW_ID passed to this procedure are available; for example, clicking the First, Next, Last, Back, and Find buttons will limit their scope to the specified ROW_ID.

Notice that the key field (ROW_ID) is gotten from the reverse of same in the current application. Note too--and this is extremely important--that @BUTTON was not specified. This is important because procedures built by the Sybernet Form Maker rely on this to determine the current state. In our case, the entire form--frames and all--need to be built and a NULL value for @BUTTON will accomplish this.

If no target is specified, it will default to "_self" which is probably not what you want because output is sent to the CENTER window. Instead, target should either be "_parent" to occupy the current window or any name that is not already defined to send it to its own window.

To send the invocation to a new window, specify target to something other than one of the following:

By sending the output to its own window, you do not have to worry about returning control to the parent. The user can simply click the close box and resume editing in the parent window, or they can leave the child window open and just click between the two as desired.

If you set the target to "_parent," then when the user returns to that parent (by clicking a hyper-text link that you have defined), you don't want the same restriction of scope that was imposed on the child. Since the default invocation assumes a parent to child relationship, you need to tell this application that this is a child to parent relation. In other words, you want all parents to be available.

To specify a child to parent relationship, simply set @isParent to TRUE; for example, here is how the component application returns to the container application:

    var s='';
    s='%22' + window.document.FORM.DI_WOR.value + '%22';
    this.href='Sybernet.cgi$http..sp_form_container?@isParent=%22TRUE%22,@ROW_ID=' + s;
Since ROW_ID is specified, the window is positioned at that row number, and since isParent is TRUE, all rows in the table will be available.

Creating Buttons

You may be pleasantly surprised to learn that buttons are quite similar to hyper-text links. Buttons are usually used to submit a form, but hyper-text links can do this to, but we won't be discussing how you might do that. Instead, we will be concentrating on how these buttons work and how you can create your own buttons that actually do something useful.

All buttons use the JavaScript onClick event handler to submit your form. Even if you leave this blank, code is added to submit your form.

To inhibit a button from actually submitting your form, all you need to do is add the JavaScript code return false; for example, here's the onClick code for a button that displays an alert and nothing else:

    alert('Please do not press this button again!'); return false;
When your form is submitted, the value of your button is copied to the hidden field called BUTTON. This is similar to how submit buttons normally work. If you modify the procedure created by Syberform, you will want to use this value when you write your own code.

In a form screen that invokes a stored procedure, we might want to add the ability to return the results of that stored procedure in RTF. This is easily accomplished by adding a new button, giving it a value that implies something meaningful, and specifying the following onClick code:

    copyform();
    window.document.FORM.FORMAT.value='RTF';
The HOME button simply sets the value of Procname to "sp_html_login" and the value for Target to "_parent." onClick is left blank. This works because specifying a value for Procname automatically executes that procedure and does not submit the form. In other words, "return false" is implied when Procname is supplied.

You can't pass parameters with this method, but you can override the value of the reserved word PROCEDURE which accompanies this button allowing you to do almost anything you want, including passing parameters. Allow me to explain

Buttons are self-contained forms. Normally they do nothing except submit the main form screen when the user presses a button, but you can override this default behavior by specifying a value for Procname. Here is what the HOME button looks like to your browser:

    <FORM METHOD="POST" ACTION="Sybernet.cgi" TARGET="_parent">
    <INPUT TYPE="HIDDEN" NAME="PROCEDURE" VALUE="sp_html_login">
    <INPUT TYPE="HIDDEN" NAME="FORMAT" VALUE="SUPPRESSED">
    <INPUT TYPE="HIDDEN" NAME="CONTENTTYPE" VALUE="text/html">
    <INPUT TYPE="HIDDEN" NAME="FILTER" VALUE="none">
    <INPUT TYPE="SUBMIT" VALUE="HOME" onClick="return true;">
Your onClick code can change the values of any of these elements, and since PROCEDURE is not just limited to procedure names, you could specify any valid transact-SQL statement you might require, including an execute statement.

    this.form.PROCEDURE.value='sp_html_form_compiler @ROW_ID='
    + window.document.FORM.DI_WOR.value;
    this.form.FILTER.value='CGI';
Notice that not only are we calling a stored procedure, but also passing to it a parameter whose value is determined from the current context. And since the value is also a key, I did not need to call copyform() before getting this element's value.

To make this onClick work, however, you will want to specify a value for Procname (so that PROCEDURE is declared) and set a value for Target. You can also use one of the optional frames if you want the results to go to the same window.

Here's the onClick code for a button that calls the same stored procedure whose output goes to another window:

    window.open
    ('Sybernet.cgi$sp_html_form_compiler?@FORMAT=%22SUPPRESSED%22,@FILTER=%22CGI%22,@ROW_ID='
        +   window.document.FORM.DI_WOR.value
    ,   'Compile'
    ,   'width=200,height=100'
    );
    return false;
It should look very similar to the examples above on creating hyper-text links except that it judiciously returns false as the last thing it does.

More Fun with JavaScript

Your onChange code is executed whenever a user changes the value of a form's element. Typical uses would be to validate the data in that field. Here's an example that scrolls the current window to the value specified: 
    window.document.FORM.DI_WOR.value=this.value;
    window.document.FORM.BUTTON.value='GOTO';
    window.document.FORM.submit();
In this example, ROW_ID is a unique key on our form. We set the internal key ID_WOR (the reverse of ROW_ID) to this.value and set out Button value to the keyword GOTO. We then submit the form.

When your form screen invokes a stored procedure, the default output window is the CENTER frame. You might wish to override this in your onClick code and set this to "_parent" (which will use the entire screen) or (even cooler) define a non-null value for one of the optional frames and send the results there: 

    copyform();
    window.document.FORM.target='EAST';
Of course it's up to you to make sure that the background colors are identical if that is important. FORM.target is reset to 'NORTH' only if your application accesses a table or view.

Your label or button might call sp_html_graph which allows you to make both pie and bar charts from the values in any column of any table. Set the value of onClick to something like this:

        window.open
        ('Sybernet.cgi$http..sp_html_graph
            ?@FILTER=%22CGI%22
            ,@TABLENAME=%22http..component%22
            ,@COLUMNNAME=%22TYPE%22'
        ,'Graph'
        ,'width=500,height=300'
        );
You're right! This has nothing to do with the Form Maker, but it's a neat example, don't you think?

Frequently Asked Questions

Q: How do I do a join between two or more tables?

A: Create a view.

Q: Can I use the Sybernet Form Maker to call a stored procedure instead of accessing a table?

A: Yes! When the Sybernet Form Maker is invoked, you can choose any table, view, or a stored procedure. In the latter case, your form is constructed from the parameters that are passed to this procedure as well as some hidden fields which allow you to change the value of some of the Sybernet reserved words (i.e., FORMAT, FILTER and CONTENTTYPE).

Built-in functions like Next, First, Last, and Prev are disabled since the Sybernet Form Maker has no way of knowing whether or not you have implemented these or even if they actually mean anything.

Instead, when you select a stored procedure, the built-in buttons are Submit and Home.

Q: Can I define my own buttons?

A: Yes, use the File menu and select New/Button. Use the Edit menu to override onClick, Procname, and target to make these buttons do something.

Q: My form screen is perfect, but do I have to re-draw this every time I want to make a change?

A: No. Your screen definitions are stored in a Sybase table so that the next time you select this table your current configuration is already armed.

Q: Do I have to recompile every time I make changes to my form?

A: Surprisingly, no! There are actually very few things that you might modify that actually require a re-compilation of your stored procedure. This means selecting Save from the Edit menu will take care of most of your updates.

Q: So what things might I change that do require compilation? I'm interested because I want to modify the stored procedure that the Sybernet Form Maker creates and do not want to lose my work.

A: Changing the number of selected columns or the key columns will require recompilation. Also, changing the title or background color will mean like-wise. In either case, it is always judicious to save a copy of your changes to a separate procedure with sp_rename.

Trouble Shooting

There are many reasons why your procedure may fail to compile. The actual reason will be in the Java Console. Here is a list of things I can think of right off.

You may also experience problems running your application. Here are some that I have had trouble with. In each case, it was my fault and not the fault of the application built by the Form Maker.