Release 3.00
Mar 03, 2005
|
Sybernet / Welcome To Sybernet
Release 3.00 Mar 03, 2005 |
|
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. There is also a version of Sybernet for Oracle. The version discussed here is the latest version for Sybase 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 Sybase. 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 Sybase.
Security is also important. Sybernet plays no role in this and relys on Sybase'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 an HTML page:
create procedure HelloWorld
as
begin
select '<html>'
select '<head>'
select '<title>Hello World</title>'
select '</head>'
select '<body>'
select '<h1>Hello World</h1>'
select '</body>'
select '</html>'
return
end
It is more efficent if you can reduce the number of select statements. This is accomplished by separating each string with a comma. The other thing we can do is add a line-feed to each line so we can debug the output by doing a view source. Without the line-feeds you end up with one long line which is impossible to debug. Here is a better way.
create procedure HelloWorld
as
declare
@title varchar(30)
begin
select @title = 'Hello World'
select char(10) + '<html>'
, char(10) + '<head>'
, char(10) + '<title>' + @title + '</title>'
, char(10) + '</head>'
, char(10) + '<body>'
, char(10) + '<h1>Hello World</h1>'
, char(10) + '</body>'
, char(10) + '</html>'
return
end
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 procedure HelloWorld
(
@BUTTON varchar(30) = null
)
as
begin
if (@BUTTON is null)
begin
exec sp_html_frame
@PROCNAME = 'http.dbo.HelloWorld'
, @VERSION = 'Version 1.0'
, @TITLE = 'Hello Warld'
, @CENTER = '*'
, @SOUTH = '100'
, @BORDER = '0'
return
end
if (@BUTTON = 'CENTER')
begin
select '<html>'
exec http.dbo.sp_html_preferences
select '<h1>Hello World</h1>'
select '</body>'
select '</html>'
return
end
if (@BUTTON = 'SOUTH')
begin
select '<html>'
exec http.dbo.sp_html_preferences
exec http.dbo.sp_html_menubar
select '</body>'
select '</html>'
return
end
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:
/******************************************************************************
**
**
**
** 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) */
/* */
/******************************************************************************/
create procedure HelloWorld
(
@BUTTON varchar(30) = null
)
as
begin
if (@BUTTON is null)
begin
exec sp_html_frame
@PROCNAME = 'http.dbo.HelloWorld'
, @VERSION = 'Version 1.0'
, @TITLE = 'Hello Warld'
, @CENTER = '*'
, @SOUTH = '100'
, @BORDER = '0'
return
end
if (@BUTTON = 'CENTER')
begin
select '<html>'
exec http.dbo.sp_html_preferences
select '<h1>Hello World</h1>'
select '</body>'
select '</html>'
return
end
if (@BUTTON = 'SOUTH')
begin
select '<html>'
exec http.dbo.sp_html_preferences
exec http.dbo.sp_html_menubar
select '</body>'
select '</html>'
return
end
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.
/******************************************************************************
**
**
**
** 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) */
/* */
/******************************************************************************/
create procedure HelloWorld
(
@BUTTON varchar(30) = null
)
as
begin
if (@BUTTON is null)
begin
exec sp_html_frame
@PROCNAME = 'http.dbo.HelloWorld'
, @VERSION = 'Version 1.0'
, @TITLE = 'Hello World'
, @CENTER = '*'
, @SOUTH = '100'
, @BORDER = '0'
return
end
if (@BUTTON = 'CENTER')
begin
select '<html>'
exec http.dbo.sp_html_preferences
select '<h1>Hello World</h1>'
select '</body>'
select '</html>'
return
end
if (@BUTTON = 'SOUTH')
begin
select '<html>'
exec http.dbo.sp_html_preferences
exec http.dbo.sp_html_menubar
select '</body>'
select '</html>'
return
end
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 roll your own. 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.
/******************************************************************************
**
**
**
** 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 (03/03/2005) */
/* */
/******************************************************************************/
create procedure SelectObjects
(
@BUTTON varchar(30) = null
, @USERNAME varchar(30) = null
)
as
declare
@user_name varchar(30)
begin
if (@BUTTON is null)
begin
exec sp_html_frame
@PROCNAME = 'http.dbo.SelectObjects'
, @VERSION = 'Version 3.0'
, @TITLE = 'Display Objects Owned'
, @CENTER = '*'
, @SOUTH = '100'
, @BORDER = '0'
return
end
if (@BUTTON = 'CENTER')
begin
select @user_name = user_name()
exec http.dbo.sp_html_preferences
select '<center>'
select '<br>'
exec http.dbo.sp_html_form
@METHOD = 'POST'
, @NAME = 'FORM'
, @JAVASCRIPT = 'FALSE'
exec http.dbo.sp_html_input
@TYPE = 'HIDDEN'
, @NAME = 'PROCEDURE'
, @VALUE = 'http.dbo.SelectObjects'
exec http.dbo.sp_html_input
@TYPE = 'SELECT'
, @NAME = 'USERNAME'
, @VALUE = 'SELECT name FROM sysusers'
, @CHECKED = @user_name
exec http.dbo.sp_html_input
@TYPE = 'SUBMIT'
, @NAME = 'BUTTON'
, @VALUE = 'SUBMIT'
exec http.dbo.sp_html_form
select '</center>'
return
end
if (@BUTTON = 'SOUTH')
begin
select '<html>'
exec http.dbo.sp_html_preferences
exec http.dbo.sp_html_menubar
select '</body>'
select '</html>'
return
end
if (@BUTTON = 'SUBMIT')
begin
exec http.dbo.sp_html_preferences
select '<table border=1 align=center>'
select '<tr bgcolor=lightgrey>'
select '<th>OBJECT_NAME</th>'
select '<th>OWNER</th>'
select '<th>TYPE</th>'
select '<th>CREATED</th>'
select '</tr>'
select '<tr>'
, '<td>'
, sysobjects.name
, '</td>'
, '<td>'
, sysusers.name
, '</td>'
, '<td>'
, sysobjects.type
, '</td>'
, '<td>'
, sysobjects.crdate
, '</td>'
, '</tr>'
from sysobjects
, sysusers
where sysobjects.uid = sysusers.uid and sysusers.name = @USERNAME
select '</table>'
return
end
end
You can also make this table sortable. If you do this on the server side, you will have to copy and paste the case where BUTTON = SUBMIT (or declare a cursor) for each column to be sorted. You could also sort this table from the client side by calling the subprogram sortTable from the javascript package. To do the latter you need to add about 5 more lines.