Release 3.00
Jan 29, 2007
|
Sybernet / Welcome To Sybernet
Release 3.00 Jan 29, 2007 |
|
This document introduces you to Sybernet and explains how to write stored procedures for the World Wide Web. Sybernet was originally written in 1995 on a Macintosh for Sybase. It was then ported to unix and then to Linux. The version discussed here is the latest version for Oracle that runs under both Sun Solaris and Mac OSX.
Sybernet removes the requirement for developing CGI scripts in order to support dynamic HTML page development. Anyone with stored procedure experience and modest HTML knowledge can develop live dynamic pages on their server.
There are several competitive (and more popular) products that accomplish the same thing as Sybernet. They require that you write static HTML pages with special tags that invoke your SQL statements whose output is embedded in your page's result. You probably invoke more than one SQL statement in your page which requires separate calls to Oracle. There is nothing wrong with this approach because it works. Creating your HTML in a stored procedure is just as simple and means only one call is required to get this output out of Oracle.
Security is also important. Sybernet plays no role in this and relys on Oracle's ability to do this better than I could ever do. You log in once and execute as many stored procedures as you like, all with the same connection. Passwords are unique to each user and that user's permissions determines what application they have access to. Username and passwords are not stored in external files.
The following example illustrates how to create a simple stored procedure that writes a string to your browser:
create or replace procedure HelloWorld
is
begin
http.write('Hello World');
return;
end;
http.write and http.writeln are two procedures for sending output to Sybernet. Sybernet then redirects that output to your browser, a unix disk file, or to Sybercron. We will discuss Sybercron in our next episode.
If you are familiar with Oracle's facility for creating web pages, you know that it uses the DBMS_OUTPUT package. That package suffers from two major flaws: You cannot generate more than one million characters, nor can you see these characters until your stored procedure has completed. Sybernet suffers from none of these limitations. Your stored procedure can generate an unlimited output stream and that stream is written asynchronously even while your stored procedure is still executing.
The following example illustrates how to create an HTML page:
create or replace procedure HelloWorld
is
begin
http.writeln
(
'<html>'
, '<head>'
, '<title>Hello World</title>'
, '</head>'
, '<body>'
, '<h1>Hello World</h1>'
, '</body>'
, '</html>'
) ;
return;
end;
As illustrated, both write and writeln accept one or more parameters. They both rely on Oracle's ability to implicitly convert numeric and date values to varchars. writeln differs from write because each non-null parameter is suffixed with a line-feed. Most of the time your browser will ignore these characters. These line-feeds are quite useful when you are debugging your output by doing a view source. It is, however, more effecient to use write (and insert your own line-feeds) when speed is important.
My personal preference is to call writeln for each line of output while concatenating strings that should not contain line-feeds. While this is the least efficient, it is (in my opinion) the most readable.
create or replace procedure HelloWorld
is
title varchar(30) := 'Hello World';
begin
http.writeln('<html>');
http.writeln('<head>');
http.writeln('<title>' || title || '</title>');
http.writeln('</head>');
http.writeln('<body>');
http.writeln('<h1>' || title || '</h1>');
http.writeln('</body>');
http.writeln('</html>');
return;
end;
In case you are interested, this is probably the best place to stop reading. Knowing write and writeln is enough for you to go away and write your own procedures. For those that are interested, however, we will explore some of the facilities that Sybernet provides.
Sybernet provides three facilities for creating a common look and feel in your application: SP_HTML_FRAME, SP_HTML_PREFERENCES and SP_HTML_MENUBAR. While frames are easy to create, the first procedure allows you to create frames by name (instead of by position) while also providing a consistent look and feel. Your stored procedure is suppose to call SP_AUDIT and SP_HTML_FRAME will do this automatically for you.
The second procedure is used to create the body tag. While you can pass your own preferences (such as the background color), this procedure attempts to use your user's defined preferences.
The third procedure creates the standard menu bar for navigating through Sybernet. Without any parameters the user is presented with buttons to return to the main menu screen, cancel a request, change their preferences, get instructions, and log out. You can even arm the instructions button so the user is presented with instructions about your application.
create or replace procedure HelloWorld
(
button in varchar2 := null
)
is
begin
if (button is null) then
http.sp_html_frame
(
PROCNAME => 'HTTP.HELLOWORLD'
, VERSION => 'Version 1.0'
, TITLE => 'Hello Warld'
, CENTER => '*'
, SOUTH => '100'
, BORDER => '0'
) ;
return;
end if;
if (button = 'CENTER') then
http.writeln('<html>');
http.sp_html_preferences;
http.writeln('<h1>Hello World</h1>');
http.writeln('</body>');
http.writeln('</html>');
return;
end if;
if (button = 'SOUTH') then
http.writeln('<html>');
http.sp_html_preferences;
http.sp_html_menubar;
http.writeln('</body>');
http.writeln('</html>');
return;
end if;
end;
What is missing from the above procedure is a standard source heading. What I would like to know is who wrote this procedure and what is its purpose? I also would like to know when and what was the last change to this procedure. Here is the convention that I use:
create or replace procedure HelloWorld
/******************************************************************************
**
**
**
** Version: 3.00
**
** %%%% % % %%%% %%%%% %%%% % % %%%%% %%%%%
** % % % % % % % % %% % % %
** %%% % %%%%% %%% %%%% % % % %%% %
** % % % % % % % % %% % %
** %%%% % %%%% %%%%% % % % % %%%%% %
**
** "Displays Hello World"
**
** Copyright (c) 2002 by Denis D. Workman
**
** All rights reserved
**
** No part of this program may be reproduced in any form or by any
** means, electronic or mechanical, including photocopy, recording
** or any information storage and retrieval system, without prior
** permission in writing from the author.
**
**
**
*******************************************************************************/
/* */
/* PATCH 001 Initial idea (11/09/2002) */
/* */
/******************************************************************************/
(
button in varchar2 := null
)
is
begin
if (button is null) then
http.sp_html_frame
(
PROCNAME => 'HTTP.HELLOWORLD'
, VERSION => 'Version 1.0'
, TITLE => 'Hello Warld'
, CENTER => '*'
, SOUTH => '100'
, BORDER => '0'
) ;
return;
end if;
if (button = 'CENTER') then
http.writeln('<html>');
http.sp_html_preferences;
http.writeln('<h1>Hello World</h1>');
http.writeln('</body>');
http.writeln('</html>');
return;
end if;
if (button = 'SOUTH') then
http.writeln('<html>');
http.sp_html_preferences;
http.sp_html_menubar;
http.writeln('</body>');
http.writeln('</html>');
return;
end if;
end;
Whenever you make a change (no matter how trivial), it should be documented in the patch heading. Including the patch number on the affected line is useful, but not required. What is important is that I have some description of the last change made to this procedure.
create or replace procedure HelloWorld
/******************************************************************************
**
**
**
** Version: 3.00
**
** %%%% % % %%%% %%%%% %%%% % % %%%%% %%%%%
** % % % % % % % % %% % % %
** %%% % %%%%% %%% %%%% % % % %%% %
** % % % % % % % % %% % %
** %%%% % %%%% %%%%% % % % % %%%%% %
**
** "Displays Hello World"
**
** Copyright (c) 2002 by Denis D. Workman
**
** All rights reserved
**
** No part of this program may be reproduced in any form or by any
** means, electronic or mechanical, including photocopy, recording
** or any information storage and retrieval system, without prior
** permission in writing from the author.
**
**
**
*******************************************************************************/
/* */
/* PATCH 001 Initial idea (11/09/2002) */
/* PATCH 002 Correct spelling of World (11/25/2002) */
/* */
/******************************************************************************/
(
button in varchar2 := null
)
is
begin
if (button is null) then
http.sp_html_frame
(
PROCNAME => 'HTTP.HELLOWORLD'
, VERSION => 'Version 2.0'
, TITLE => 'Hello World' -- PATCH 002
, CENTER => '*'
, SOUTH => '100'
, BORDER => '0'
) ;
return;
end if;
if (button = 'CENTER') then
http.writeln('<html>');
http.sp_html_preferences;
http.writeln('<h1>Hello World</h1>');
http.writeln('</body>');
http.writeln('</html>');
return;
end if;
if (button = 'SOUTH') then
http.writeln('<html>');
http.sp_html_preferences;
http.sp_html_menubar;
http.writeln('</body>');
http.writeln('</html>');
return;
end if;
end;
Typically, a stored procedure needs to request some information from the user before it can display the desired results or report. Sybernet provides several procedures for creating form screens. If you are already comfortable with creating form screens, there is no reason why you can't use write or writeln to do the same. Using Sybernet, however, allows you to do some things that you can't do with a simple input field.
This procedure creates a drop-down list of all usernames in your database. When submitted, a list of all objects owned by that user is displayed in an HTML table.
This procedure also uses the package MYSELF to determine the procedure name, title, and version dynamically. Specifying a non-static string for procedure name means only the procedure heading needs to be changed if I decide to change its name. Getting the title dynamically means the title displayed matches the title on the menu screen.
create or replace procedure SelectObjects
/******************************************************************************
**
**
**
** Version: 3.00
**
** %%%% % % %%%% %%%%% %%%% % % %%%%% %%%%%
** % % % % % % % % %% % % %
** %%% % %%%%% %%% %%%% % % % %%% %
** % % % % % % % % %% % %
** %%%% % %%%% %%%%% % % % % %%%%% %
**
** "Displays objects owned"
**
** Copyright (c) 2002 by Denis D. Workman
**
** All rights reserved
**
** No part of this program may be reproduced in any form or by any
** means, electronic or mechanical, including photocopy, recording
** or any information storage and retrieval system, without prior
** permission in writing from the author.
**
**
**
*******************************************************************************/
/* */
/* PATCH 001 Initial idea (11/09/2002) */
/* */
/******************************************************************************/
(
button in varchar2 := null
, username in varchar2 := null
)
is
begin
if (button is null) then
http.sp_html_frame
(
PROCNAME => HTTP.MYSELF.NAME
, VERSION => HTTP.MYSELF.VERSION
, TITLE => HTTP.MYSELF.TITLE
, CENTER => '*'
, SOUTH => '100'
, BORDER => '0'
) ;
return;
end if;
if (button = 'CENTER') then
http.sp_html_preferences;
http.writeln('<center>');
http.writeln('<br>');
http.sp_html_form
(
METHOD => 'POST'
, NAME => 'FORM'
, JAVASCRIPT => 'FALSE'
) ;
http.sp_html_input
(
TYPE => 'HIDDEN'
, NAME => 'PROCEDURE'
, VALUE => HTTP.MYSELF.NAME
) ;
http.sp_html_input
(
TYPE => 'SELECT'
, NAME => 'USERNAME'
, VALUE => 'SELECT USERNAME FROM ALL_USERS'
, CHECKED => USER
) ;
http.sp_html_input
(
TYPE => 'SUBMIT'
, NAME => 'BUTTON'
, VALUE => 'SUBMIT'
) ;
http.sp_html_form;
http.writeln('</center>');
return;
end if;
if (button = 'SOUTH') then
http.writeln('<html>');
http.sp_html_preferences;
http.sp_html_menubar;
http.writeln('</body>');
http.writeln('</html>');
return;
end if;
if (button = 'SUBMIT') then
http.sp_html_preferences;
http.writeln('<table border=1 align=center>');
http.writeln('<tr>');
http.writeln('<th>OBJECT_NAME</th>');
http.writeln('<th>OBJECT_TYPE</th>');
http.writeln('<th>CREATED</th>');
http.writeln('<th>LAST_DDL_TIME</th>');
http.writeln('<th>STATUS</th>');
http.writeln('</tr>');
for r in (select * from all_objects where owner = username order by object_name, object_type) loop
http.writeln('<tr>');
http.writeln('<td>' || r.object_name || '</td>');
http.writeln('<td>' || r.object_type || '</td>');
http.writeln('<td>' || r.created || '</td>');
http.writeln('<td>' || r.last_ddl_time || '</td>');
http.writeln('<td>' || r.status || '</td>');
http.writeln('</tr>');
end loop;
http.writeln('</table>');
return;
end if;
end;
If you run this example in your database and notice that the first page is slow loading, there is a simple solution and easy fix to speed things along. I suppose I could give you the solution, but since it is documented, I'll leave that as an exercise to the reader.
