Sybernet / Supplied Software Release 3.00 Mar 13, 2005

SYBERNET

Sybernet is an application server: a software server that lets thin clients use applications and databases that are managed by the server. The application server handles all the application operations and connections for the clients. Sybernet can be used as a CGI or as an HTTP web server (or both).

The Oracle version of Sybernet uses OCI. The Sybase version uses Client-Library. Supported servers include Oracle9i and ASE 12.5. Supported platforms include Solaris and Mac OS X.

Object security in the database is controlled by the database, not Sybernet. You can execute a stored procedure because the DBA or owner of that object has granted you execute permission on that object. Other application servers log you in with an effective user ID, then log you back in as the owner of this database or schema. Internal tables then determine what applications you are allowed to execute. Sybernet does none of this and relies on the permissions granted to the username that logged into your database.

You can use digital certificates to authenticate users. You do this by placing Sybernet between an SSL web server and the database. The web server will pass either REMOTE_USER or CLIENT_CERT_SUBJECT_E. The former is their authenticated username. The latter represents the user's e-mail address and causes Sybernet to look up their username in your LDAP server.

Sybernet Reserved Words

The following list of reserved words are known internally to Sybernet. Some are read-only (meaning you can interrogate their value, but you can't assign them); some are write-only (meaning you can set their value, but you can't interrogate them); and others are both readable and writeable (meaning you can assign or interrogate their value).

PROCEDURE

USERCODE

PASSWORD

HOSTNAME

FORMAT

DIRECT

SEARCH

METHOD

ADDRESS

EMAIL

SERVERNAME

SERVERPORT

SCRIPTNAME

REFERRER

CLIENT

CONTENTTYPE

DATABASE

PROCESSNAME

COOKIE

FILTER

FROM

SUBJECT

FILLER

DISPOSITION

   <INPUT TYPE=HIDDEN NAME=DISPOSITION VALUE='attachment; filename="labor.xls"'>

    labor.xls

   <INPUT TYPE=HIDDEN NAME=DISPOSITION VALUE='inline'>

SEND_PARTIAL

JSEncode

URLEncode

HTMLEncode

HTTPEncode

HEXEncode

ASCEncode

PICTURE

CLOAK

    declare pictures cursor for
    select KEY
    from   http..HTTP_PICTURES

    open pictures
    fetch pictures into @KEY
    while @@sqlstatus = 0
    begin
        select char(10) + 'insert HTTP_PICTURES'
        ,      char(10) + '('
        ,      char(10) + '    KEY'
        ,      char(10) + '    PICTURE'
        ,      char(10) + ')'
        ,      char(10) + 'values'
        ,      char(10) + '('
        ,      char(10) + '    "' + @KEY + '"'
        ,      char(10) + '    0x'
        ,      '<HEXEncode>'
        ,      PICTURE
        from   HTTP_PICTURES
        where  KEY = @KEY
        select '</HEXEncode>'
        ,      char(10) + ')'
        ,      char(10) + 'go'
        fetch pictures into @KEY
    end
    close pictures
    deallocate cursor pictures

A

Q

S

X

Z

9

.

,

$

<

>

?

    select '<PICTURE ZZZ,ZZZ,ZZZ.ZZ>'
    ,       1234.5678
    ,       '</PICTURE>'

    1,234.56

    select '<PICTURE SZZZ,ZZZ,ZZZ.ZZ>'
    ,       -1234.56
    ,       '</PICTURE>'

    -1,234.56

    select '<PICTURE ZZZZZS>'
    ,       12345
    ,       '<PICTURE>'

    12345+

    select '<PICTURE 999,999,999.99>'
    ,       1234.56
    ,       '</PICTURE>'

    000,001,234.56

    select '<PICTURE $SZZZ,ZZZ,ZZZ.ZZ>'
    ,       1234.56
    ,       '</PICTURE>'

    $1,234.56

    select '<PICTURE ZZZ,ZZZ,ZZZ.ZZAAAAA>'
    ,       1234.5678
    ,       'Hello'
    ,       '</PICTURE>'

    1,234.56Hello

    select '<PICTURE AAA-AA-AAAA>'
    ,      123456789
    ,      '</PICTURE>'

    123-45-6789

The ability to specify more than one occurrence of PROCEDURE in your form screen means a single form request can call several stored procedures, each with different parameters. Why would you do this? One reason is to allow your form request to handle multiple line items. Consider an HTML table where each row contains input fields. You want to submit the entire table and process each row separately. Without this ability you need a unique parameter name for each field which is not only inconvenient, but also impossible if the number of fields in this table exceed the limit allowed by your RDBMS. There is a pretty good example of Form Input and Line Items for Sybase in the original documentation for Sybernet.

What I like to do when processing line items is to put the entire process inside of <script> and </script> tags. This allows me to trap errors and display an alert if there is something I don't like with this line. But since there can be thousands of errors, we can also allow the user to cancel these alerts if we replace them with calls to confirm() instead. To do this, I need to know when the process has begun (so I can build the <script> tag) and when the process has ended (so I can build the </script> tag). Your form screen might look something like this:

    <form method="POST" action="Sybernet.cgi">
    <input type="HIDEEN" name="PROCEDURE" value="SP_LINE_ITEMS">
    <input type="HIDDEN" name="BUTTON" value="PROLOGUE">
    <input type="HIDEEN" name="PROCEDURE" value="SP_LINE_ITEMS">
    <input type="HIDDEN" name="BUTTON" value="PROCESS">
    <input type="TEXT" name="NAME" value="VALUE"> 
    <input type="HIDEEN" name="PROCEDURE" value="SP_LINE_ITEMS">
    <input type="HIDDEN" name="BUTTON" value="PROCESS">
    <input type="TEXT" name="NAME" value="VALUE">
    <input type="HIDEEN" name="PROCEDURE" value="SP_LINE_ITEMS">
    <input type="HIDDEN" name="BUTTON" value="PROCESS">
    <input type="TEXT" name="NAME" value="VALUE">
    <input type="HIDEEN" name="PROCEDURE" value="SP_LINE_ITEMS">
    <input type="HIDDEN" name="BUTTON" value="EPILOGUE">
    </form> 

With this form our stored procedure can detect when the procedure is beginning (PROLOGUE), processing a row (PROCESS), or ending (EPILOGUE). Here's a crude example of what that procedure might look like:

create procedure SP_LINE_ITEMS(BUTTON IN VARCHAR2, NAME IN VARCHAR2)
is
begin
    if (BUTTON = 'PROLOGUE') then
        http.writeln
        (
            '<script>'
        ,   'var'
        ,   '    confirmed = true'
        ,   '    ;'
        )   ;
        return;
    end if;
    if (BUTTON = 'PROCESS') then
    begin
        insert into LINE_ITEMS values (NAME);
        return;
    exception when others then
        http.writeln
        (
            '    if (confirmed)'
        ,   '    confirmed=confirm("Oops!");'
        )   ;
        return;
    end;
    end if;
    if (BUTTON = 'EPILOGUE') then
        http.writeln
        (
            '    alert("Thank you");'
        ,   '</script>'
        )   ;
        return;
    end if;
end SP_LINE_ITEMS;

As long as the user keeps clicking OK, the messages will persist. As soon as they click Cancel the messages will stop displaying.

If the above makes sense, you can also use this facility to process text files and images. The difference here is Sybernet takes care of calling your stored procedure for each line in your text file or (in the case of images) inserting this image into a table. This is accomplished by specifying an ENCTYPE of multipart/form-data in your form request. To upload a text file where each line is processed one at a time in your stored procedure, your form screen will resemble this:

    <form method="POST" action="Sybernet.cgi" enctype="multipart/form-data">
    <input type="HIDEEN" name="PROCEDURE" value="SP_LINE_ITEMS">
    <input type="HIDDEN" name="BUTTON" value="PROLOGUE">
    <input type="HIDEEN" name="PROCEDURE" value="SP_LINE_ITEMS">
    <input type="HIDDEN" name="BUTTON" value="PROCESS">
    <input type="FILE" name="NAME"> 
    <input type="HIDEEN" name="PROCEDURE" value="SP_LINE_ITEMS">
    <input type="HIDDEN" name="BUTTON" value="EPILOGUE">
    </form> 

Images (or any document type) can be inserted directly into a table when PROCEDURE is not a procedure name, but the beginning of an insert statement. Any input fields between this procedure and the next procedure are also inserted into this table. This might be hidden fields or maybe the name of this image (or document).

    <form method="POST" action="Sybernet.cgi" enctype="multipart/form-data">
    <input type="HIDEEN" name="PROCEDURE" value="SP_LINE_ITEMS">
    <input type="HIDDEN" name="BUTTON" value="PROLOGUE">
    <input type="HIDEEN" name="PROCEDURE" value="INSERT INTO HTTP.HTTP_PICTURES">
    <input type="FILE" name="NAME"> 
    <input type="HIDEEN" name="PROCEDURE" value="SP_LINE_ITEMS">
    <input type="HIDDEN" name="BUTTON" value="EPILOGUE">
    </form> 

When uploading images, you probably don't need to detect when the process has begun (but you can if you wish), but you probably want to detect when the process has completed if for nothing else than to display a Thank You for submitting this image. If you're feeling bold, you could even display the image that was just submitted.

All form screens submitted to Sybernet limit the size of each post request. This is a configurable option in the Sybernet Utility that can be changed in real time without bringing down Sybernet. But if you exceed this limit, your request will be rejected.

See Also