There is a facility known as the Common Gateway Interface or CGI that extends the functionality of a Web Server. This feature makes it possible to pass information from the client to an external application (known as the CGI application) which processes the information and returns the result to the client through the Web Server. Sybernet is a CGI that talks to a Sybase database.
In his article on CGI applications, Jon Wiederspan writes:
When trying to understand how CGI applications work, the first thing to learn is the difference between the server and the client. WebSTAR is [a] server. Mosaic, MacWeb, and Netscape are all clients. The client software is smart. It knows how to interpret HTML, how to handle partial URL's and URL's with strange extra information like search strings, and how to find servers. The server software is stupid. It does fine as long as it is fed the right information, but if a client sends bad information, the server has no idea what to do. Therefore, in most transactions it is the client that's doing all the cool stuff and the server is just passing back a file or an error code."One of the things you will be learning is how to tell Sybernet to invoke your stored procedure. Obviously, there are a lot of stored procedures in this world, and Sybernet needs to know the name of the stored procedure you want to invoke.
We can't possibly cover everything there is to know about HTML or stored procedures, but we
will learn enough for you to go away and write your own. In this guide we will be creating
two stored procedures: a form screen and a report screen.
We will be using input fields on our form screen to pass information to Sybernet and to our
report screen procedure. Sybernet needs to know the name of the stored procedure and how to
format the result set. The report screen needs to know the value of the author's ID.
Somewhere in this document there is a list of reserved words that have particular meaning to
Sybernet: they do something special. Any identifier that Sybernet does not recognize, however, is
considered a parameter to your stored procedure. Earlier versions of Sybernet required Sybase
parameters to be preceded by an at-sign (@). For POST
requests, this is no longer true. This change allows you to use your Sybase names with
JavaScript
Parameters are passed to stored procedures explicitly by name; for example,
One of the facilities of HTML form screens is the ability to
pass hidden fields from your form to the Server. Of course, as far as Sybernet is concerned
the field does not need to be hidden, but we hide the field because we don't want the user
to see this: not because we have something to hide (no pun intended), but because it is
information that they do not need to see. All they are interested in is entering the
appropriate information and getting their report. Besides, hiding the field makes your
form less cluttered.
@EMPID="14568"
While we've had requests for other platforms (NT and AIX to name two), MacOS, Solaris, and Linux
are the only ones available at the moment. Although Sybernet must run from the same platform
as your web server, the Sybase SQL server can exist on any platform.
The WebSTAR server at SRI International can be found at
http://Sybernet.sri.com/. The Solaris version running under a NetScape server is at
http://mis.sri.com/netscape.html. And the
Linux version running under an Apache server is at
http://Linux.sri.com/.
Because of a feature called "hidden" fields which was explained earlier, make sure that
your browser supports this feature. What's that thing on the PC called--I can never remember
its name--something like NetSurf? Well, don't use it! It doesn't support this feature.
You also need a Web Browser to register your stored procedure. You can do this manually with
something like ISQL, but I don't plan to document how you would do that in this guide.
What I recommend, however, is the Stored Procedure Editor that
I wrote especially for this purpose, and which (coincidentally) is accessed from your Web Browser.
And for those of you that are curious the Stored Procedure Editor is a Sybase stored procedure.
While I concede it may take as much as 30 seconds to learn how to use it properly, what better place
to write your own stored procedures than from the World Wide Web.
HTML doesn't take long to learn. And it seems everybody these days has their own WEB page so
I'll assume you already know how to do this. If you're thinking of (or are used to using) a
WYSIWYG HTML editor, you might as well give up on that idea. That facility won't help
you in the middle of a stored procedure.
Have I explained this clearly enough? Let's say someone wants you to create a report for them on
their Web browser. They want to specify, for example, an employee number, and you are to create
a report for that particular employee.
You can call your stored procedures anything you like. If you think you might be creating
a lot of them, I would definitely adopt a convention that allows you to keep everything
straight.
For homework you should find out why some stored procedures are kept in sybsystemprocs.
The obvious answer is so that anyone can use them. What is the other answer, though?
Do not confuse this field with the name of the stored procedure that created this form. In the
example above, the name of the stored procedure that created this form is sp_authors_form,
and the stored procedure that we want to execute is going to be identified by the value in this field.
In our example, we want to execute the procedure sp_authors_report.
A new formatting option, SUPPRESSED, has recently been added to Sybernet that has none
of these features. The SUPPRESSED option may be a better choice to use because you
don't have to remember any of these rules. This option simply streams each result set to the Web
Browser (via WebSTAR) without any formatting. In many cases the results are identical because of
the way HTML is interpreted, but the SUPPRESSED option will produce noticeable results
when using the PRE tag.
For more information about the NONE and SUPPRESSED options (and examples
of both), please refer to the section on Formatting Result Sets.
I strongly suggest that you make all your parameters optional. You should check whether or not they
are null and display an appropriate error message if this is not allowed. The error messages
from Sybase (via Sybernet) may not be understood by your user; for example,
What appears to be a redundant <BR> tag will actually cause our
result set to be separated by a blank line in case more than one author is found.
This statement alone (if nothing else in this guide) should illustrate how powerful it is to get
this information from Sybase as opposed to writing static HTML
files.
Even though our Report Screen procedure doesn't do much, this is where most of your work will be done.
And although there are guide lines for the Form Screen procedure, there are none for the Report Screen.
If you want to create sub-forms in this procedure, go right ahead. You can even have hypertext links
that point to different areas on your report. You can call other stored procedures. Really, the
possibilities are endless.
FORMAT=SUPPRESSED tells Sybernet to stream all result set data without any formatting.
The SUPPRESSED option may be a better choice when generating reports that use the
PRE tag. It may also be a better choice because you do not have to remember any of the
rules imposed by the NONE option. And although the results are usually identical,
the SUPPRESSED option will usually generate less output than that produced by the
NONE option.
Let's take a closer look at these two options and see how they compare. You decide which option
you prefer to use.
Consider the following table:
In the first example (when FORMAT=NONE) notice that there is blank space between
each column and numeric data is right-aligned. Also note that the column
width for state is 5 positions: the actual column data is 2 positions, but the column heading
takes up 5 positions. This may or may not be what you want, especially since no column heading
is displayed when format is NONE.
One solution is to override the column heading name with the syntax provided for
just this purpose. For example, if I wanted
When using the SUPPRESSED option, formatting is completely controlled by your stored
procedure. Again, many times the interpretation of HTML makes these results identical. Here is
how you might use the SUPPRESSED option to format your result set to resemble the
results produced when format is NONE.
Ordered lists <OL> and Unordered lists <UL>,
as you already know, separate each list item by preceding them with <LI>.
Here is how you would create an Unordered List from a result set:
And finally, an anomaly about string expressions whose lengths are calculated at run-time.
For some reason (and none that I know), Sybase sets the column heading of a string expression
to 255 if that expression must be calculated at run-time. Consider the following example:
The problem with this design is that the user must constantly click the BACK button if they
want to generate another report for a different author. One solution is to always generate
the form screen.
Frames certainly add another level of complexity, but I will show how their use can be
simplified by using stored procedures. One reading the documentation for Frames would be
inclined to think they need several stored procedures: one for the basic layout and Frameset
and one for each frame that you declare. And if you were not using stored procedures, this
is the correct path (except the stored procedures would be static text files).
The following example expands on our original procedure
The other change worth noting is to our FORM declaration. We've added the parameter @WINDOW
as a hidden field and assigned it the value OUTPUTWINDOW. When the form is submitted, this
value is passed to our procedure. The expression TARGET="OUTPUTWINDOW" which has been added
to our FORM tag instructs NetScape to send the output results to the window named OUTPUTWINDOW.
I purposely declared @WINDOW before @au_id in our procedure heading so I could talk about it
here. If you allow your procedure to be invoked with a URL request that specifies a default
author ID, then you'll want to reverse this order (just to make the invocation as
natural as possible); for example, a URL request of
You can put input fields, radio buttons, check boxes and buttons in a table using the TABLE
tag. In most cases, NetScape will make everything pretty, but you will probably need to do
some tweaking to get everything just the way you want. Tweaking is beyond the scope of this
manual, and I leave it to you the reader to explore this at your own leisure.
The evaluation for @NEXT is simply retrieving the next author ID from the authors table.
If @au_id is null or the last author ID in this table, @NEXT is assigned to the first
author ID. In other words, it wraps around to the beginning.
The VALUE for our SUBMIT button is NEXT . is an
HTML construct that forces our browser to place a space there. This makes all of our buttons the
same size, just in case you were wondering.
Tables are a good way to control exactly where on the screen your buttons appear, and if you
don't want the border, just set it to zero. The following example creates a button on the
far right of your screen:
The procedure that invokes
user defined preferences is called
The procedure for creating the menu bar is called
And of course this is what we shall do in this section. The example we are
about to discuss creates a form screen for the authors table in the Pubs2
database. Buttons on this form will allow you to update or delete existing
entries, add new entries, and scroll forwards and backwards through this
table. Our concentration here will be how these buttons and this procedure
interact. The actual display is left as simple as possible so that we don't
get too hung up with the HTML part of this application, though that is very
important too.
Our procedure heading will contain a parameter called @BUTTON
which tells us which function the user is requesting. The remaining parameters
correspond to the column names in the authors table.
As the procedure starts to execute the first thing we want to do is check the
value of our @BUTTON. You might possibly think we
should build our form screen first, but this is not correct. If the value for
our button is to delete a row, it wouldn't make any sense showing this row
first and then deleting it. A more natural approach is to delete it first
and then (possibly) show the next row in the table.
Our button will be NULL when the procedure is first executed. Detect this
condition and set it to something that makes more sense: perhaps retrieving
the first row in the table?
Now we want to handle the case when our button is either
NEXT or PREV.
You might also think the code for implementing the NEXT and
PREV button's is quite clever. Hardly! In fact I usually
try to stay away from the aggregate functions, but this is not always possible.
What are we suppose to do when multiple columns form our key? How do we make
these buttons perform then? The answer has nothing to do with HTML
or Sybernet, but it does rely on Sybase to do the work. With the proper
WHERE condition, ORDER BY, and some way
to limit the number of rows returned, this can be accomplished easily.
We can control the number of rows returned using a cursor. The examples here,
however, will use the SET ROWCOUNT statement to accomplish this.
The code for one key column is obvious. Let's assume that our key columns is
called KEY1. Here is the code for our NEXT
button with one key column:
Now this is clever, but unfortunately it does not work when our key columns can
be null. In that case we can easily account for nulls with the ISNULL
function, but its use is not so straight-forward because you will need to be familiar
with your data to use it.
Suppose KEY3 is an int that can be null, but will never have a real value less than 1. The
code for our NEXT button for this case is as follows:
This limitation--in a moment we will conclude there is no such limit--usually means we
append a unique number to each column of each row that is passed to our procedure as well
as impose a limit on how many rows we are actually going to handle in our form. Consider
the following declaration:
I'm certainly not going to write code for each child, though one would be inclined to think
this is required. Instead, I would write another procedure that would handle the update or
insert of each child separately.
Here is our first attempt at writing this procedure:
Now we can start building the form.
Consider too that if we also included JavaScript to validate this form before it is
submitted, we would have to deal with these unique names as well.
This was a lot of work to show you how this used to be accomplished. With Sybernet
version 1.73 and above the ability to handle line items has been greatly improved by allowing
a single form screen to call (or recurse) on more than one stored procedure.
In particular, when our form is submitted, what we'd like to have happen is to call
Here is how our procedure looks now that we can call a procedure multiple times in the same form:
It's a very elegant solution to a very nasty problem. My only regret is that it is a
solution I did not think of myself.
This change means that Sybernet now runs as an asynchronous CGI application (though its name
remains unchanged). I suspect we will have to change this name in the future (to add the acgi extension),
but for now we will stick with the old name.
To invoke the Send Partial facility, simply return the text <SEND_PARTIAL>
. If this 14
character string is matched exactly (it is case sensitive), Sybernet flushes the current
result set output to WebStar who then flushes it to the client while keeping the connection
open. Here are some examples that will work:
TIP: If your browser is timing out because your stored
procedure is not returning results fast enough, issuing a <SEND_PARTIAL>
could alleviate this. This option, however, is not an excuse for writing
poorly designed or inefficient stored procedures.
The following example illustrates an HTML form page
that will generate an INSERT statement. Sybernet determines this by
interrogating the PROCEDURE value for your submitted form:
Here is an example of a slightly more sophisticated trigger that can determine if the user
is updating, deleting, or selecting a blob:
Yes, that was quite a bit of code to swallow. Give it a second look to see what's really
going on and I think you'll appreciate it as much as I. And remember, it's only an example
of how you might go about creating your own trigger.
An image retrieved from a stored procedure can be bound by HTML in the usual way. Here
is an example that does just that:
If the aforementioned URL is specified, the Sybernet.cgi will attempt to execute
this procedure. If the user has not connected, they are automatically connected
as "guest." If the invocation of this procedure does not cause a security error,
it is executed and the string "ORG,024" is passed as a parameter. In this case it
does not matter what the parameter name to this procedure is called, but it is
important (at least in this example because one is specified) that a parameter
is declared.
Let's start with the basics of URI encoding. Anything in the Direct or Search argument
that is not a letter, digit, or underscore should probably be encoded. URI Encoding
simply replaces that character with a percent sign followed by its two character
Hexadecimal representation; for example, the Hexadecimal value for an Ascii [double]
quote is 22. The URI Encoding of an Ascii quote is therefore "%22."
Although spaces can be represented by %20 (since a space is denoted by a Hexadecimal 20),
a plus-sign can be used instead. So, The URI encoding of the string
becomes
Your browser would complain vehementally at the former and gladly pass the latter to your
web server. There are other exceptions as to which characters need to be encoded, by
the way. Commas and at-signs (we use those a lot in stored procedures) can safely be
left as they are, but encoding won't hurt here either.
We can see an example of this in action by entering the following in the Site Location
of your browser:
This won't work because the space was not encoded. Change it to
and you will see how URI encoding works.
There are lots of ways to URI encode your parameters to Sybase, but not until version
1.87 of Sybernet has there been a safe or easy way to do this that would work on
database columns. You could call a stored procedure to do the encoding, but the
maximum string length that can be passed to a stored procedure is 255 characters as
you already know.
Version 1.87 of Sybernet introduced a new tag that will do the encoding automatically
for you. URI Encoding is turned on with the <URLEncode> tag,
and URI encoding is turned of with the </URLEncode> tag. This
case-sensitive tag is unique to Sybernet and will not work in normal HTML
files. It may only be used with FORMAT=SUPPRESSED.
Here is an example that creates a list of hyper-text links that invokes a stored
procedure which passes au_fname and au_lname from the authors table in pubs2. It
is extremely important to encode these name in case there are special characters
in someone's name:
Notice too that the blob was selected separately. Blobs must be the last column
of a multi-column select statement, so I just put it on a line by itself.
Sybernet will also perform command substitution and assignment
against internal CGI variables if that variable is named as a parameter value in the Search
argument; for example,
What you are suppose to learn and what I am trying to convey is that you can
construct hypertext links that use this method; for example, your stored
procedure might generate a HTML page that contains a link (using this syntax)
to another stored procedure. Consider the following example:
You can write your own stored procedure to handle image maps, and I will show you an example
of an imagemap stored procedure that does exactly that. The procedure is not very sophisticated,
but it does illustrates the basics of creating such a function. If you come up with anything
more elaborate, please share it with me.
The first thing we need is an image that is defined as an image map that also invokes our
imagemap stored procedure when the user selects it.
The client software passes the coordinates of the click to this procedure in the
COOKIE is a read-only reserved word in Sybernet. You can retrieve the cookies associated
with a connection by including this name in a POST or GET request. First,
let's take a look at a stored procedure that receives this string.
The following stored procedure uses a method of POST to retrieve your cookies:
Sybernet creates its own HTTP header for every page generated by a stored procedure. Before
delivering this page, however, it scans the result sets of your stored procedure to see if
your stored procedure is building its own HTTP header. If another HTTP header is detected,
Sybernet strips the HTTP header that it built before delivering your HTML page.
The following stored procedure creates its own HTTP header that is nearly identical to the
HTTP header built by Sybernet.
Have you noticed that sometimes your browser displays a running percentage of how much
of your HTML has been retrieved? It can do this when a "Content-length" entry has been
included in the HTTP header. Sybernet does not include Content-length in the HTTP headers
that it builds because it has no way of knowing how large your result set will be. You
probably do not know either, but if you did, you could create your own HTTP header
and include a Content-length. This is hardly worth the effort!
Consider the following example:
I bet you're still not impressed, am I right? Here's another example which exploits the
full power of the CGI filtering option:
Writing stored procedures with this method is fairly straight-forward, but things get
complicated when dealing with quotes. Consider an HTML table that sets its width to
100%. When using FORMAT=NONE or FORMAT=SUPPRESSED, this table tag would be created
with the following statement:
When FILTER=ECHO, Sybernet will save the entire result set stream created by your
stored procedure. Then your
stored procedure is recalled repeatedly with chunks of 254 characters until the
original result set has been sent. The data is passed to your procedure in its
last parameter.
Let's take a look at a procedure that uses the ECHO option.
When @BLOB is null, this is the first time it has been called and it selects the data
that it needs. Although this procedure is selecting a Text field, you can select any
field type and any number of fields before the procedure returns.
When the datalength of @BLOB is exactly equal to 254 characters, the procedure saves
this information to a table and returns. The last chunk will always be less than 254
characters so the procedure knows there is more to come.
The third state is when @BLOB is neither NULL nor 254 characters in length. This is
the last chunk being sent, so the procedure saves this as well and processes the
table it just populated.
I now have the ability to select the information from the table as VARCHAR's (as
opposed to Text or Image) and perform such functions on it as SUBSTRING and
CHARINDEX. These functions are not available with BLOB's.
Filters are independent of format and content-type. Filters are built-in
preprocessors that do something with your result set before sending them
to your browser; for example, the CGI filter sends your result sets back
to Sybase before processing your output results.
Format determines how Sybernet will format your output results; for
example, the SUPPRESSED option streams all result set data to your browser
without any translation or formatting.
Content-type is ignored by Sybernet. Content-type is a directive for
your browser that tells it what to do with this data; for example,
Content-type "application/rtf" might spawn a copy of MicroSoft Word or
Claris Works, depending upon how this is defined in your browser options.
Setting Content-type to "application/rtf" will itself not produce a valid RTF
document (since Sybernet ignores Content-type). Instead, you would either
specify Content-type as "application/rtf" and then generate RTF in your
stored procedure, or, more simply, specify format as RTF. The RTF format
option automatically sets Content-type to "application/rtf."
Filters and Content-type never determine format; however, some values of
Format may specify either or both of these options, overriding any values
you may have specified. The following table explains this further:
FORMAT=CGI and FORMAT=ECHO are now obsolete options. They will be removed on a
future release. Instead you should use the appropriate filter option and format
to accomplish the same thing.
Does it make sense to you that we need to tell Sybernet when your initial form is
submitted that executes a stored procedure what the content-type is suppose to
be? This is true, by the way, for all internal Sybernet options that we want to
set. Consider the following example which allows the user to choose between
HTML or RTFFeatures
Now that you know a little bit more about CGI's here is a list of features (in no particular order)
that Sybernet provides:
What you will need:
A Web Server
The Sybernet CGI was originally written for WebSTAR,
a Web Server for the MacOS. Sybernet is also available for Solaris and Linux platforms
running either NetScape, NCSA, CERN, or Apache servers.A Web Browser
You will want to have a Web Browser, naturally, if for no other reason than to test your
procedure on the World Wide Web. All of my development work has been done using the Macintosh
version of Netscape. Ideally, you would really want to test your procedure on as many platforms
as your users might expect to use because they all work differently: the Netscape version for
the PC is different from the one for Unix as well as the one for Macintosh.A Stored Procedure Editor
You will need something to write and edit your stored procedure. If you're comfortable with
using ISQL in conjunction with vi or emacs, go right ahead. I've used them myself and they
work just fine for our purposes.Knowledge of HTML syntax
There are several places where you can find information. Two of my favorites are
A Beginner's Guide to HTML and
Mosaic for X version 2.0 Fill-Out Form Support. Both of these links contain information
that you will want to have.
Writing your stored procedure:
Well, you're actually going to write two stored procedures. The first procedure is your
form screen that you will register with the Stored Procedure Register facility. The form screen
is where you gather parameters for the report screen procedure. This is the procedure that
creates the report.This is what's going to happen:
Log on on Sybase and create the following two procedures: once this is done you can read the explanation
that follows. Don't forget to grant
the appropriate permissions on these stored procedures, however.The FORM screen:
create procedure sp_authors_form
as
select '<H1>Search Authors</H1>'
select "Please enter author's ID in the space provided and press report."
select '<P>'
select '<FORM METHOD="POST" ACTION="Sybernet.cgi">'
select 'Author: <INPUT TYPE="TEXT" NAME="au_id">'
select '<P>'
select '<INPUT TYPE="HIDDEN" NAME="PROCEDURE" VALUE="sp_authors_report">'
select '<INPUT TYPE="HIDDEN" NAME="FORMAT" VALUE="NONE">'
select '<INPUT TYPE="SUBMIT" VALUE="Report"><BR>'
select '</FORM>'
return
The REPORT screen:
create procedure sp_authors_report(@au_id varchar(11) = NULL)
as
select '<BR>' + au_fname + ' ' + au_lname
, '<BR>' + address
, '<BR>' + city + ', ' + state + ' ' + postalcode
, '<BR>'
, '<BR>' + 'Phone: ' + phone
, '<BR>'
from pubs2..authors
where au_id = @au_id
if @@rowcount = 0
begin
select '<STRONG>'
select @au_id + ' not found!'
select '</STRONG>'
end
return
Explaining the Form Screen:
This is the procedure heading. sp_authors_form is the name of the stored procedure.create procedure sp_authors_form
Seems simple enough. I should have put this on the first line and I wouldn't have to explain it.
Hey, it's just part of the syntax. I can't anymore explain why you have to say as anymore
than I can tell you why statements don't end in a semi-colon.as
What is significant about this line is that it contains HTML syntax, enclosed in single quotes,
and preceded by the word select. Another important point is that this statement results
in one complete result set. Although I prefer clarity over compactness, it is actually more
efficient for you to concatenate strings with the string operator + rather than having
a select statement for each string expression. On the down-side, you cannot return anymore than
255 characters in this manner. Anyway, something to consider.select '<H1>Search Authors</H1>'
Fortunately, Sybase allows strings to be enclosed by single or double quotation marks.
I prefer the former because there is a lot of stuff that needs double quotes, but for cases
where you need to show possession or contractions the double quotes are most useful.select "Please enter author's ID in the space provided and press report."
<P> starts a new paragraph. I'm looking for a blank line right about here.select '<P>'
This statement defines the beginning of a form screen and tells the Web Server how and where to send
this information.select '<FORM METHOD="POST" ACTION="Sybernet.cgi">'
Form defines the beginning of a form screen.FORM
Method tells the Web Server how to send this information. There are two methods, but
POST is the most flexible.METHOD="POST"
This instruction tells the Web Server the name of the CGI application. With some
servers you may need to qualify Sybernet's name by preceding it with the name of
your cgi-bin directory (ACTION="/cgi-bin/Sybernet.cgi").ACTION="Sybernet.cgi"
Here we are prompting the user for an author ID.select 'Author: <INPUT TYPE="TEXT" NAME="au_id">'
INPUT says we are defining an input field.INPUT
Defines a text field for typing information.TYPE="TEXT"
Here we define the name of the field. In this case the field name is a parameter called NAME="au_id"au_id which
will be passed to our stored procedure. Sybernet assumes it's a parameter because the name au_id is not a
reserved word.
Another line break please.select '<P>'select '<INPUT TYPE="hidden" NAME="procedure" VALUE="sp_authors_report">'
Defines another input field.INPUT
Defines a hidden field that the user does not see.TYPE="HIDDEN"
This is similar to the above example except this time the name of this field is a reserved word
recognized by Sybernet. The value passed in this field is the name of the stored procedure that
we want it to invoke.NAME="PROCEDURE"
This expression specifies the value for the field called procedure. Essentially, we are declaring a variable
called procedure and assigning it a value of VALUE="sp_authors_report"sp_authors_report.select '<INPUT TYPE="hidden" NAME="format" VALUE="none">'
Defines another input field.INPUT
Defines a hidden field that the user does not see.TYPE="HIDDEN"
This is another field definition for Sybernet. In this case we are telling Sybernet how we want
the output to be formatted. NONE as its name implies instructs Sybernet to not perform
any formatting on the output.NAME="FORMAT"
FORMAT=NONE tells Sybernet to not perform any formatting on the result set of the stored
procedure. This is not entirely correct because the NONE option attempts to format
columnar data produced by a result set. Column headings (if they exist) are suppressed, column
widths are determined by the maximum of the column heading and column data, each column of a result
set is separated by a single space, and numeric data is right-aligned.VALUE="NONE"select '<INPUT TYPE="SUBMIT" VALUE="Report"><BR>'
Another input field. This one is different from the others, however.INPUT
Type SUBMIT is what the user clicks on when they want to submit their form.TYPE="SUBMIT"
This expression simply labels the submit button.VALUE="Report"
This tag simply terminates the end of your form.select '</FORM>'
Congratulations! You made it. This is the end of your stored procedure.return
Explaining the Report Screen:
After all that work and we've only covered the stored procedure that creates the form screen. Well,
I'm just as tired as you are. But we're in luck: we've covered the difficult part. All that's left
to do is select our author from the authors table and format it with HTML!
How hard can that be?Here is the Report Screen Procedure again:
create procedure sp_authors_report(@au_id varchar(11) = NULL)
as
select '<BR>' + au_fname + ' ' + au_lname
, '<BR>' + address
, '<BR>' + city + ', ' + state + ' ' + postalcode
, '<BR>'
, '<BR>' + 'Phone: ' + phone
, '<BR>'
from pubs2..authors
where au_id = @au_id
if @@rowcount = 0
begin
select '<STRONG>'
select @au_id + ' not found!'
select '</STRONG>'
end
return
Actually, there are only a few things to note about this procedure:
create procedure sp_authors_reportsp_authors_report is the same name that we defined for the PROCEDURE value
in sp_authors_form.
You want to make sure that the parameter names in your stored procedure match with the parameter names
you declared on your form screen.(@au_id varchar(11) = null)
if @au_id is null
begin
select '<H1>Error...</H1>'
select "An author's ID is required!"
return
end
...
Here we actually select this information from Sybase and display the results in HTML.select '<BR>' + au_fname + ' ' + au_lname ... from pubs2..authors
If no row results were returned, display an appropriate message.if @@rowcount = 0
Registering your procedure:
The main menu screen is dynamically created each time a user logs on to Sybase with their Web browser. The
sign-on screen on my home page instructs Sybernet to invoke the procedure SP_HTML_LOGIN
if a successful connection is made. This procedure reads the HTTP database which contains a list of
stored procedure names that it will use to create this menu. Before your form procedure can appear in this
menu, however, it must be registered. And you must use your Web browser to do this!
One of the menu options that you can select from the main menu screen is called
Stored Procedure Register. And yes, the Stored Procedure Register
was registered the same way your procedure will be. Select this link to see the form screen for the
Register program.Here are the steps to register your Form Screen procedure:
Your procedure is now registered! Go back to the main menu screen and run it. But remember, if you press
the BACK button to return to the main menu, your procedure will not be there. This is the old menu that
NetScape has been saving for you. Simply click on NetScape's Reload button, and your
procedure will appear.
Formatting Result Sets:
FORMAT=NONE tells Sybernet to not perform any formatting on the result set of the stored
procedure. This is not entirely correct because the NONE option attempts to format
columnar data produced by a result set. This may or may not be what you desire.
Column headings (if they exist) are suppressed, column widths are determined by the maximum of the
column heading and column data, each column of a result set is separated by a single space, and
numeric data is right-aligned. CREATE TABLE authors
(
au_id varchar(11) not null
, au_lname varchar(40) not null
, au_fname varchar(20) not null
, phone char(12) not null
, address varchar(40) null
, city varchar(20) null
, state char(2) null
, country varchar(12) null
, postalcode char(10) null
)
When FORMAT=NONE and the result set is produced by the statements
select '<PRE>'
select *, '<BR>' from pubs2..authors
select '</PRE>'
The results is
807-91-6654 Panteley Sylvia 301 946-8853 1956 Arlington Pl. Rockville MD USA 20853
846-92-7186 Hunter Sheryl 415 836-7128 3410 Blonde St. Palo Alto CA USA 94301
When FORMAT=SUPPRESSED the result of this same expression is
807-91-6654PanteleySylvia301 946-88531956 Arlington Pl.RockvilleMDUSA20853
846-92-7186HunterSheryl415 836-71283410 Blonde St.Palo AltoCAUSA94301
These are very different results, but one of the reasons is that we are using
the PRE tag, which the Web browser identifies
as preformatted text.state to occupy exactly two
positions on output, I could tell Sybase this by using the following statement: select state "ST" from pubs2..authors
A more elegant solution is to use the convert function. The convert function is primarily used for
converting between data types, but you can also use it to truncate or expand column data. The same
effect is achieved with the following statement: select convert(char(2),state) from pubs2..authors
The above rule does not apply when exactly one column is returned from your result set. In this case
the results are identical to when FORMAT=SUPPRESSED. select convert(char(11),au_id)
, ' '
, convert(char(40),au_lname)
, ' '
, convert(char(20),au_fname)
, ' '
, convert(char(12),phone)
, ' '
, convert(char(40),address)
, ' '
, convert(char(20),city)
, ' '
, convert(char(5),state)
, ' '
, convert(char(12),country)
, ' '
, convert(char(10),postalcode)
, char(13)
from pubs2..authors
Another feature about the NONE and SUPPRESSED format options is that
Sybernet does not put carriage-returns or line-feeds in the output. This is most desirable! select '<UL>'
select '<LI>', au_lname + ', ' + au_fname from pubs2..authors
select '</UL>'
Select boxes precede each item with the <OPTION> tag. Here is how you might make one of those: select '<SELECT NAME="@name" SIZE=1>'
select '<OPTION>', name from sysobjects where type = "P" order by name
select '</SELECT>'
Textarea's are separated by carriage-returns (at the end of each line). Here is how to create a Textarea: select '<TEXTAREA ROWS=20 COLS=80>'
select *, char(13) from pubs2..authors
select '</TEXTAREA>'
Tables are slightly more complicated, but you can create some incredible results from just a few
select statements. Here is how you might create a table: select '<TABLE BORDER=6 WIDTH=90%>'
select '<TR>'
select '<TH>soc-sec-num</TH>'
select '<TH>Last Name</TH>'
select '<TH>First Name</TH>'
select '</TR>'
select '<TR>'
, '<TD>' + au_id + '</TD>'
, '<TD>' + au_lname + '</TD>'
, '<TD>' + au_fname + '</TD>'
, '</TR>'
from pubs2..authors
select '</TABLE>'
The Table tag is a good example where the results produced from FORMAT=NONE and
FORMAT=SUPPRESSED are the same; however, while the result appears to be the same,
the SUPPRESSED option generates less output. declare @width int
select @width = 10
select '***'
, space(@width)
, '***'
When format is NONE, the result of these statements produce the following:
If space(10) were used in lieu of space(@width), the results would be
as expected. When format is SUPPRESSED, this anomaly does not occur and the results
are as follows:
Note that no space is inserted between result set columns when format is SUPPRESSED.
Tips and Techniques
This section concentrates on the different ways you can combine HTML and stored procedures. You want
to stop thinking of HTML as a static language (a simple text file), and concentrate on how you can
go about using both to their best advantage. Even a simple greeting can be made flexible as
illustrated in the following:
if datepart(hour,getdate()) < 12
select 'Good morning'
else
if datepart(hour,getdate()) < 18
select 'Good afternoon'
else
select 'Good evening'
One thing you might want to think about is combining your form and report screens into one
procedure. The obvious benefit is that all of your code and logic is concentrated in a
single stored procedure. create procedure sp_authors(@au_id varchar(11) = NULL)
as
if @au_id is NULL
begin
select '<H1>Search Authors</H1>'
select "Please enter author's ID in the space provided and press report."
select '<P>'
select '<FORM METHOD="POST" ACTION="Sybernet.cgi">'
select 'Author: <INPUT TYPE="TEXT" NAME="au_id" VALUE="' + @au_id + '">'
select '<P>'
select '<INPUT TYPE="HIDDEN" NAME="PROCEDURE" VALUE="sp_authors">'
select '<INPUT TYPE="HIDDEN" NAME="FORMAT" VALUE="NONE">'
select '<INPUT TYPE="SUBMIT" VALUE="Report"><BR>'
select '</FORM>'
end else
begin
select '<BR>' + au_fname + ' ' + au_lname
, '<BR>' + address
, '<BR>' + city + ', ' + state + ' ' + postalcode
, '<BR>'
, '<BR>' + 'Phone: ' + phone
from pubs2..authors
where au_id = @au_id
if @@rowcount = 0
begin
select '<STRONG>'
select @au_id + ' not found!'
select '</STRONG>'
end
end
return
The sp_authors_form and sp_authors_report procedures (from our tutorial) are now
combined into a single procedure called sp_authors. If @au_id is null,
we build the form, and if it's not null we build the report. Also notice that the PROCEDURE
value is sp_authors so that we call our self when the user presses the Report Button. create procedure sp_authors(@au_id varchar(11) = NULL)
as
select '<H1>Search Authors</H1>'
select "Please enter author's ID in the space provided and press report."
select '<P>'
select '<FORM METHOD="POST" ACTION="Sybernet.cgi">'
select 'Author: <INPUT TYPE="TEXT" NAME="au_id" VALUE="' + @au_id + '">'
select '<P>'
select '<INPUT TYPE="HIDDEN" NAME="PROCEDURE" VALUE="sp_authors">'
select '<INPUT TYPE="HIDDEN" NAME="FORMAT" VALUE="NONE">'
select '<INPUT TYPE="SUBMIT" VALUE="Report"><BR>'
select '</FORM>'
select '<BR>' + au_fname + ' ' + au_lname
, '<BR>' + address
, '<BR>' + city + ', ' + state + ' ' + postalcode
, '<BR>'
, '<BR>' + 'Phone: ' + phone
from pubs2..authors
where au_id = @au_id
if @@rowcount = 0 and @au_id != NULL
begin
select '<STRONG>'
select @au_id + ' not found!'
select '</STRONG>'
end
return
Notice that the input field for au_id now contains the value of @au_id. For input
fields of type TEXT (as in this example), the desired effect is achieved by simply including
its value, but for input fields of type SELECT (where a list is displayed), you'll want to
make sure that the current value of @au_id is selected and that its location in this list is
where the user would expected it. select '<SELECT NAME="@au_id" SIZE=1>'
if @au_id is null
begin
select '<OPTION>', au_id from pubs2..authors order by au_id
end else
begin
select '<OPTION>', au_id from pubs2..authors where au_id < @au_id order by au_id
select '<OPTION SELECTED>', @au_id
select '<OPTION>', au_id from pubs2..authors where au_id > @au_id order by au_id
end
select '</SELECT>'
If @au_id is null, we simply retrieve the entire list of author ID's from the authors
table and order them by au_id; otherwise, we first retrieve those IDs less
than the value of @au_id, then the value for @au_id which we also indicate as SELECTED, then
those IDs greater than the value of @au_id. And the whole thing is ordered,
just as the user would want it to be!Frames
Our last example for sp_authors solved one problem and introduced another. Although
the user no longer has to press the BACK button to enter a
new author ID, they may
not like the fact that our form appears in the same window as the report, especially when
they go to print it. Frames (available with NetScape 2.0 and above) can be used to rectify
this problem.sp_authors, but
creates two frames: one for the form and one for the report. To do this, we've added another
parameter to our stored procedure called @WINDOW. This parameter will be used to determine
the state of our stored procedure. When it is null, we build the layout for our frames, and
when it is not null, its value corresponds to the frame we are to build. Here we go. create procedure sp_authors (@WINDOW varchar(16) = null,@au_id varchar(11) = null)
as
if @WINDOW is NULL
begin
select '<HTML>'
select '<HEAD>'
select '<FRAMESET ROWS="30%,70%">'
select '</HEAD>'
select '<FRAME SRC="Sybernet.cgi$sp_authors?INPUTWINDOW" NAME="INPUTWINDOW">'
select '<FRAME SRC="Sybernet.cgi$sp_authors?OUTPUTWINDOW" NAME="OUTPUTWINDOW">'
select '</FRAMESET>'
select '</BODY>'
select '</HTML>'
return
end
if @WINDOW = "INPUTWINDOW"
begin
select '<H1>Search Authors</H1>'
select '<FORM METHOD="POST" ACTION="Sybernet.cgi" TARGET="OUTPUTWINDOW">'
select '<INPUT TYPE="hidden" NAME="@WINDOW" VALUE="OUTPUTWINDOW">'
select 'Author: <INPUT NAME="@au_id" VALUE="' + @au_id + '"><P>'
select '<INPUT TYPE="hidden" NAME="procedure" VALUE="sp_authors">'
select '<INPUT TYPE="hidden" NAME="format" VALUE="none">'
select 'Press here: <INPUT TYPE="submit" VALUE="Report">'
select '</FORM>'
return
end
if @WINDOW = "OUTPUTWINDOW" and @au_id != NULL
begin
select '<BR>' + au_fname + ' ' + au_lname
, '<BR>' + address
, '<BR>' + city + ', ' + state + ' ' + postalcode
, '<BR>'
, '<BR>' + 'Phone: ' + phone
from pubs2..authors
where au_id = @au_id
if @@rowcount = 0
begin
select '<STRONG>'
select @au_id + ' not found!'
select '</STRONG>'
end
return
end
Notice the select statements for FRAME SRC when @WINDOW is NULL. In those we are saying that
the source is this procedure (sp_authors) and we are passing to this procedure non-null
values for @WINDOW. Those values are INPUTWINDOW and OUTPUTWINDOW. They are appended to the
procedure name by preceding them with a question-mark. They happen to be the same name we also
define for these frames. http://Sybernet.sri.com/Sybernet.cgi$sp_authors?846-92-7186
should generate the report for @au_id 846-92-7186. In this example (because it was not specified),
@WINDOW will be null. We need to modify our frame source when we build the frames to pass
the value for @au_id to our input and output windows.
select '<FRAME SRC="Sybernet.cgi$sp_authors?'
select '@au_id=%22' + @au_id + '%22%2C@WINDOW=%22INPUTWINDOW%22" '
select 'NAME="INPUTWINDOW">'
select '<FRAME SRC="Sybernet.cgi$sp_authors?'
select '@au_id=%22' + @au_id + '%22%2C@WINDOW=%22OUTPUTWINDOW%22" '
select 'NAME="OUTPUTWINDOW">'
You don't have to name each parameter as long as they are in the correct order, but I did so
here to be as explicit as possible. Ideally, we should also make sure that NULL values for
@au_id are NULL when they arrive at our frame. The expression '@au_id=%22' + @au_id + '%22'
becomes @au_id="" when @au_id is null. Sybase interprets this as a space, which is neither
null nor what we desire. One solution is to pass the keyword NULL as illustrated by the
following:
if @au_id is null
select '@au_id=NULL'
else
select '@au_id=%22' + @au_id + '%22'
select '%2C@WINDOW=%22OUTPUTWINDOW%22" '
The Advanced Topics section of this guide explains how to pass multiple
parameters to a stored procedure in greater detail. You will normally not need to construct
such complicated expressions, but we are doing so here because our stored procedure can be
invoked directly with a URL request.Tables and Forms
Something else you might want to consider adding to your forms is making them look more like
a form screen rather than just a bunch of badly misplaced input fields on your screen. This
too adds still another layer of complexity, but I think the results are well worth the effort,
and your users will probably thank you as well. select '<TABLE BORDER=6>'
select '<FORM METHOD="POST" ACTION="Sybernet.cgi" TARGET="OUTPUTWINDOW">'
select '<INPUT TYPE="hidden" NAME="@WINDOW" VALUE="OUTPUTWINDOW">'
select '<INPUT TYPE="hidden" NAME="procedure" VALUE="sp_authors">'
select '<INPUT TYPE="hidden" NAME="format" VALUE="none">'
select '<TR><TH COLSPAN=2 ALIGN=CENTER><FONT SIZE=4>Search Authors</TH></TR>'
select '<TR>'
select '<TD>Author: <INPUT NAME="@au_id"><TD>'
select '<TD><INPUT TYPE="SUBMIT" VALUE="Report"></TD>'
select '</TR>'
select '</FORM>'
select '</TABLE>'
Never terminate your form inside of the <TD> and </TD> tags. This has the unpleasant
effect of causing a break inside of the table data cell. Instead of
select '<TD><INPUT TYPE="SUBMIT" VALUE="Report"></FORM></TD>'
do this
select '<TD><INPUT TYPE="SUBMIT" VALUE="Report"></TD></FORM>'
Buttons
Since HTML allows more than one pair of form tags in a document, you can take advantage
of this by adding "buttons" to your input frame. When an input field has a type of SUBMIT,
a button is drawn which contains the label you specified in its VALUE clause. select '<INPUT TYPE="SUBMIT" VALUE="REPORT">'
We can create many buttons in this manner and make them do something if each is bound by
corresponding FORM tags. Here is an example that adds a NEXT button to
our frame: select '<FORM METHOD="POST" ACTION="Sybernet.cgi" TARGET="INPUTWINDOW">'
if @au_id is null
select @NEXT = min(au_id) from pubs2..authors
else
select @NEXT = min(au_id) from pubs2..authors where au_id > @au_id
if @NEXT is null
select @NEXT = min(au_id) from pubs2..authors
select '<INPUT TYPE="HIDDEN" NAME="PROCEDURE" VALUE="sp_authors">'
select '<INPUT TYPE="HIDDEN" NAME="@WINDOW" VALUE="INPUTWINDOW">'
select '<INPUT TYPE="HIDDEN" NAME="@au_id" VALUE="' + @NEXT + '">'
select '<INPUT TYPE="SUBMIT" VALUE=" NEXT ">'
select '</FORM>'
Notice that we are sending the results of this button to our INPUTWINDOW frame. The theory for
this is that our INPUTWINDOW would grab the value for @au_id and display other useful information
corresponding to that author ID, say their name. Our example
does not, but hopefully you get the idea. select '<TABLE BORDER=0 WIDTH=100%>'
select '<TR>'
select '<TD ALIGN=RIGHT>'
select '<FORM>'
select '<INPUT TYPE="SUBMIT" VALUE="REPORT">'
select '</TD>'
select '</FORM>'
select '</TABLE>'
And remember, table cells can contain other tables. So, if you have an entire weekend to blow,
you can probably create an input frame where every every button is just where you want them.More Buttons
SUBMIT buttons can also be named, and that name can be passed to your stored procedure.
select '<INPUT TYPE="SUBMIT" NAME="@BUTTON" VALUE="NEXT">'
select '<INPUT TYPE="SUBMIT" NAME="@BUTTON" VALUE="SAVE">'
select '<INPUT TYPE="SUBMIT" NAME="@BUTTON" VALUE="PREV">'
Your stored procedure not only gets the entire form that was submitted, but also the
name of the button that your user clicked. This is much simpler to code for than our
first example which defined an entire form to handle our NEXT button. In practice,
you will find it necessary to use both methods.Still More Buttons
JavaScript allows you to validate your form data on the client side before your
form is sent to Sybase; for example, you may wish to validate an input field for
numeric data before it is submitted. One of my favorite uses is illustrated by
the following:
select '<INPUT'
+ ' TYPE="SUBMIT"'
+ ' NAME="@BUTTON"'
+ ' VALUE="DROP"'
+ ' OnClick="return confirm(''Are you sure want to delete this entry?'')"'
+ '>'
This button causes a JavaScript alert to be displayed on the user's web browser
warning them that they are about to delete something. This makes our stored
procedure simpler to write because we do not have to code for this when the
form is submitted.User Preferences and the Menu Bar
This section discusses how your stored procedure can invoke user defined preferences (such
as background and foreground colors) and the menu bar that you see on some of the HTML
pages. Both of these facilities are enabled by calling stored procedures.sp_html_preferences. This procedure (if you
use it) sets the <BODY> tag to invoke these preferences, so it must be called
directly after the <TITLE> tag; for example,
select '<HTML>'
select '<TITLE>Hello World</TITLE>'
exec http..sp_html_preferences
...
select '</BODY>'
select '</HTML>'
A new version of sp_html_preferences now allows an optional parameter to be
passed that can set TABLE and FONT preferences. The parameter name is @TAG
and can be either TABLE or FONT. The TABLE value creates the beginning table tag. You
must supply the ending table tag as illustrated in the following example:
exec http..sp_html_preferences "TABLE"
select '<TR><TH>Hello!</TH></TR>'
select '</TABLE>'
The FONT value to sp_html_preferences sets the user-define font size. Actually, this is
done automatically when sp_html_preferences is called to generate the <BODY> tag, but
there are places (like inside of a table) where the font size is ignored.sp_html_menubar and can be called
from anywhere within the <BODY> tag; for example,
select '<HTML>'
select '<TITLE>Hello World</TITLE>'
select '<BODY>'
...
exec http..sp_html_menubar
...
select '</BODY>'
select '</HTML>'
It is not required that you call these procedures, and in some cases (such as for reports) they
may not be suitable.Putting It All Together
Here is our finished product using frames to build the input and output windows, a table to build the
form screen, and @au_id declared before @WINDOW so the procedure can be invoked with a URL request
that specifies an author ID. create procedure sp_authors (@au_id varchar(11) = null,@WINDOW varchar(16) = null)
as
if @WINDOW is NULL
begin
select '<HTML>'
select '<HEAD>'
select '<FRAMESET ROWS="40%,60%">'
select '</HEAD>'
select '<FRAME SRC="Sybernet.cgi$sp_authors?'
if @au_id is null
select '@au_id=NULL'
else
select '@au_id=%22' + @au_id + '%22'
select '%2C@WINDOW=%22INPUTWINDOW%22" '
select 'NAME="INPUTWINDOW">'
select '<FRAME SRC="Sybernet.cgi$sp_authors?'
if @au_id is null
select '@au_id=NULL'
else
select '@au_id=%22' + @au_id + '%22'
select '%2C@WINDOW=%22OUTPUTWINDOW%22" '
select 'NAME="OUTPUTWINDOW">'
select '</FRAMESET>'
select '</BODY>'
select '</HTML>'
return
end
if @WINDOW = "INPUTWINDOW"
begin
select '<HTML>'
exec http..sp_html_preferences
select '<TABLE BORDER=6>'
select '<FORM METHOD="POST" ACTION="Sybernet.cgi" TARGET="OUTPUTWINDOW">'
select '<INPUT TYPE="hidden" NAME="@WINDOW" VALUE="OUTPUTWINDOW">'
select '<INPUT TYPE="hidden" NAME="procedure" VALUE="sp_authors">'
select '<INPUT TYPE="hidden" NAME="format" VALUE="none">'
select '<TR><TH COLSPAN=2 ALIGN=CENTER><FONT SIZE=4>Search Authors</TH></TR>'
select '<TR>'
select '<TD>Author: '
select '<SELECT NAME="@au_id" SIZE=1>'
if @au_id is null
begin
select '<OPTION>', au_id from pubs2..authors order by au_id
end else
begin
select '<OPTION>', au_id from pubs2..authors where au_id < @au_id order by au_id
select '<OPTION SELECTED>', @au_id
select '<OPTION>', au_id from pubs2..authors where au_id > @au_id order by au_id
end
select '</SELECT>'
select '</TD>'
select '<TD><INPUT TYPE="SUBMIT" VALUE="Report"></TD>'
select '</TR>'
select '</FORM>'
select '</TABLE>'
exec http..sp_html_menubar
select '</BODY>'
select '</HTML>'
return
end
if @WINDOW = "OUTPUTWINDOW"
begin
select '<HTML>'
exec http..sp_html_preferences
if @au_id != NULL
begin
select '<BR>' + au_fname + ' ' + au_lname
, '<BR>' + address
, '<BR>' + city + ', ' + state + ' ' + postalcode
, '<BR>'
, '<BR>' + 'Phone: ' + phone
from pubs2..authors
where au_id = @au_id
if @@rowcount = 0
begin
select '<STRONG>'
select @au_id + ' not found!'
select '</STRONG>'
end
end
select '</BODY>'
select '</HTML>'
return
end
The examples presented in this section were meant only as that, and hopefully I gave you some
ideas on how to write your own stored procedures.Advanced Form Input
When we think of the web, we naturally think of how easy it is to retrieve
information with our browser. You certainly wouldn't use the web or HTML to
create honest to goodness applications, now would you? create procedure sp_html_authors
(
@BUTTON varchar(12) = null
, @au_id varchar(11) = null
, @au_lname varchar(40) = null
, @au_fname varchar(20) = null
, @phone char(12) = null
, @address varchar(40) = null
, @city varchar(20) = null
, @state char(20) = null
, @country varchar(12) = null
, @postalcode varchar(10) = null
)
as
The column names are passed as parameters and not declared as local variables
because these values are going to be passed to this procedure when the form
is submitted. If they were declared locally, we could not capture any changes
the user might have made. This is an important point. if @BUTTON is NULL
/*
** Just display the first entry in the list if there is one.
*/
select @BUTTON = "NEXT"
If the button value is DROP, we can go ahead and
delete this row, but change the button value (again) to something that
makes sense. if @BUTTON = "DROP"
begin
delete pubs2..authors
where au_id = @au_id
select @BUTTON = "NEXT"
end
If the button value is SAVE, we can go ahead and
save this information; however, we don't know whether or not we are suppose
to update an existing row or insert a new row into our table. This is
easily determined by asking Sybase.
if @BUTTON = "SAVE"
begin
/*
** Is this an update or insert?
*/
if exists(select * from pubs2..authors where au_id = @au_id)
begin
update pubs2..authors
set au_lname = @au_lname
, au_fname = @au_fname
, phone = @phone
, address = @address
, city = @city
, state = @state
, postalcode = @postalcode
, country = @country
where au_id = @au_id
end else
begin
insert pubs2..authors
( au_id
, au_lname
, au_fname
, phone
, address
, city
, state
, postalcode
, country
)
values
( @au_id
, @au_lname
, @au_fname
, @phone
, @address
, @city
, @state
, @postalcode
, @country
)
select @BUTTON = "NEW"
end
end
Notice that when a row is inserted we change the button value to
NEW. We could just as easily retrieve this
new row from Sybase by leaving the button alone. In fact, this is a good
idea if other columns in a table are filled in automatically when the
row is inserted, perhaps by a trigger. if @BUTTON = "NEXT"
begin
select @au_id = min(au_id)
from pubs2..authors
where au_id > @au_id
if @au_id is NULL
select @au_id = min(au_id)
from pubs2..authors
end
if @BUTTON = "PREV"
begin
select @au_id = max(au_id)
from pubs2..authors
where au_id < @au_id
if @au_id is NULL
select @au_id = max(au_id)
from pubs2..authors
end
If the button's value is NEW, we want to set the
parameters to NULL or give them a default value. If it's anything else, we
want to retrieve this information from Sybase. if @BUTTON = "NEW"
begin
select @au_id = null
select @au_lname = null
select @au_fname = null
select @phone = null
select @address = null
select @city = null
select @state = "CA"
select @postalcode = null
select @country = "USA"
end else
begin
select @au_lname = au_lname
, @au_fname = au_fname
, @phone = phone
, @address = address
, @city = city
, @state = state
, @postalcode = postalcode
, @country = country
from pubs2..authors
where au_id = @au_id
end
Finally, we are ready to build our screen. By this time we've handled all cases
for our button, even the case when our button was NULL. select '<HTML>'
select '<HEAD><TITLE>authors</TITLE></HEAD>'
select '<BODY>'
select '<H2 ALIGN=CENTER>Pubs2 Authors</H2>'
/*
** Start the form.
*/
select '<FORM METHOD="POST" ACTION="Sybernet.cgi">'
select '<INPUT TYPE="HIDDEN" NAME="PROCEDURE" VALUE="sp_html_authors">'
select '<INPUT TYPE="HIDDEN" NAME="FORMAT" VALUE="SUPPRESSED">'
/*
** Nothing fancy here, just our input fields with a simple label.
*/
select '<INPUT TYPE="TEXT" NAME="au_id" VALUE="' + @au_id + '" SIZE=20 MAXLENGTH=20>au_id<BR>'
select '<INPUT TYPE="TEXT" NAME="au_lname" VALUE="' + @au_lname + '" SIZE=20 MAXLENGTH=40>au_lname<BR>'
select '<INPUT TYPE="TEXT" NAME="au_fname" VALUE="' + @au_fname + '" SIZE=20 MAXLENGTH=20>au_fname<BR>'
select '<INPUT TYPE="TEXT" NAME="phone" VALUE="' + @phone + '" SIZE=12 MAXLENGTH=12>phone<BR>'
select '<INPUT TYPE="TEXT" NAME="address" VALUE="' + @address + '" SIZE=20 MAXLENGTH=40>address<BR>'
select '<INPUT TYPE="TEXT" NAME="city" VALUE="' + @city + '" SIZE=20 MAXLENGTH=20>city'
select '<INPUT TYPE="TEXT" NAME="state" VALUE="' + @state + '" SIZE= 2 MAXLENGTH= 2>state'
select '<INPUT TYPE="TEXT" NAME="postalcode" VALUE="' + @postalcode + '" SIZE= 5 MAXLENGTH=10>postalcode<BR>'
select '<INPUT TYPE="TEXT" NAME="country" VALUE="' + @country + '" SIZE=12 MAXLENGTH=12>country<P>'
And now the buttons. This is about as simple as it's going to get. Too simple,
in fact. Ideally, we should add some JavaScript to these buttons so that the
user doesn't accidentally change something they don't mean to change; for example,
a JavaScript confirm on the DROP button is a good idea here. select '<INPUT TYPE="SUBMIT" NAME="BUTTON" VALUE="SAVE">'
select '<INPUT TYPE="SUBMIT" NAME="BUTTON" VALUE="DROP">'
select '<INPUT TYPE="SUBMIT" NAME="BUTTON" VALUE="NEXT">'
select '<INPUT TYPE="SUBMIT" NAME="BUTTON" VALUE="PREV">'
select '<INPUT TYPE="SUBMIT" NAME="BUTTON" VALUE="NEW">'
/*
** Finish our form and HTML.
*/
select '</FORM>'
select '</BODY>'
select '</HTML>'
return
There are several things missing from this application that you will
definitely want to add. The application should check @@error after
any table changes to make sure it worked. You would also
want to add code to make sure that a new entry doesn't actually update
an existing entry. This is possible because we allowed the user to enter
any au_id. Since au_id is NULL after the user clicks the NEW
button, I could change the button value for SAVE
to something like CREATE. I would
then know whether the user was creating or updating a row in this table. set rowcount 1
select @KEY1 = KEY1
from authors
where KEY1 > @KEY1
order by KEY1
set rowcount 0
Here is the code for two key columns: set rowcount 1
select @KEY1 = KEY1
, @KEY2 = KEY2
from authors
where KEY1 = @KEY1 and KEY2 > @KEY2
or KEY1 > @KEY1
order by KEY1, KEY2
set rowcount 0
And here is the code for three key columns: set rowcount 1
select @KEY1 = KEY1
, @KEY2 = KEY2
, @KEY3 = KEY3
from authors
where KEY1 = @KEY1 and KEY2 = @KEY2 and KEY3 > @KEY3
or KEY1 = @KEY1 and KEY2 > @KEY2
or KEY1 > @KEY1
order by KEY1, KEY2, KEY3
set rowcount 0
There's a pattern to this madness which hopefully you see. The BACK
button is almost identical except we test for less than instead of greater than and
order the keys columns in a descending fashion. Here is the BACK
button for three key columns: set rowcount 1
select @KEY1 = KEY1
, @KEY2 = KEY2
, @KEY3 = KEY3
from authors
where KEY1 = @KEY1 and KEY2 = @KEY2 and KEY3 < @KEY3
or KEY1 = @KEY1 and KEY2 < @KEY2
or KEY1 < @KEY1
order by KEY1 desc, KEY2 desc, KEY3 desc
set rowcount 0
Make sure you check @@rowcount. @@rowcount tells you whether or not there were any more
rows found. Eventually you'll either reach the end or beginning of your table. You might
want display an alert or just wrap around to the beginning again. set rowcount 1
select @KEY1 = KEY1
, @KEY2 = KEY2
, @KEY3 = KEY3
from authors
where KEY1 = @KEY1 and KEY2 = @KEY2 and isnull(KEY3,0) > isnull(@KEY3,0)
or KEY1 = @KEY1 and KEY2 > @KEY2
or KEY1 > @KEY1
order by KEY1, KEY2, KEY3
set rowcount 0
Notice we ISNULL both the column and our local variable. This is
most subtle, but it is correct since @KEY3 can be null.Form Input and Line Items
The example above is typical of how a stored procedure combined with HTML
can be used for form input. When dealing with a single row, the code to do this is fairly
straight forward, but dealing with an unknown number of rows (such a line items) can be
very difficult since parameter names to Sybase stored procedures must be unique. create table children
(
surname varchar(30)
, givenname varchar(30)
, birthdate datetime
)
The theory here is that a parent (based on surname) can have zero or more children. Our
goal is to create a form screen that allows any number of children to be edited. We might
also want the ability to add additional children should the need arise. create procedure update_child
(
@surname varchar(30)
, @givenname varchar(30)
, @birthdate datetime
)
as
if @surname is null
/*
** nothing to update.
*/
return
if exists(select * from children where surname = @surname and givenname = @givenname)
/*
** update the birthdate.
*/
update children
set birthdate = @birthdate
where surname = @surname and givenname = @givenname
else
/*
** It's a new arrival.
*/
insert children
(
surname
, givenname
, birthdate
)
values
(
@surname
, @givenname
, @birthdate
)
return
You might image that the procedure that creates our form is a bit on the clumsy side,
and you would be correct. Since there is a limit to the number of parameters I can pass
to a stored procedure, I would be restricted to the number of rows I could edit. I might
even be compelled to limit the number of rows simply because each parameter name must be
unique. Ten seems a reasonable number, but for the sake of brevity we will limit the number
of children we can edit to four. create procedure update_child_form
(
@surname varchar(30) = "Workman"
, @surname_1 varchar(30) = NULL -- child 1.
, @givenname_1 varchar(30) = NULL -- child 1.
, @birthdate_1 datetime = NULL -- child 1.
, @surname_2 varchar(30) = NULL -- child 2.
, @givenname_2 varchar(30) = NULL -- child 2.
, @birthdate_2 datetime = NULL -- child 2.
, @surname_3 varchar(30) = NULL -- child 3.
, @givenname_3 varchar(30) = NULL -- child 3.
, @birthdate_3 datetime = NULL -- child 3.
, @surname_4 varchar(30) = NULL -- child 4.
, @givenname_4 varchar(30) = NULL -- child 4.
, @birthdate_4 datetime = NULL -- child 4.
)
as
declare
@givenname varchar(30)
, @birthdate datetime
, @number_of_children int
We probably need a cursor to step through our table so we can not only append a
unique number to each variable name, but also limit the number of children displayed. declare child cursor for
select givenname
, birthdate
from children
where surname = @surname
order by birthdate
select @number_of_children = 0
Since our procedure is self contained--it calls itself--and our update procedure can handle
null values, here is a good time to call that procedure. exec update_child
@surname = @surname_1
, @givenname = @givenname_1
, @birthdate = @birthdate_1
exec update_child
@surname = @surname_2
, @givenname = @givenname_2
, @birthdate = @birthdate_2
exec update_child
@surname = @surname_3
, @givenname = @givenname_3
, @birthdate = @birthdate_3
exec update_child
@surname = @surname_4
, @givenname = @givenname_4
, @birthdate = @birthdate_4
Fairly straight-forward, but can you now image what this procedure would look like if I
were to allow 10 or more editable rows? select '<HTML>'
, '<HEAD><TITLE>Children</TITLE></HEAD>'
, '<BODY>'
select '<FORM ACTION="POST" ACTION="Sybernet.cgi">
, '<INPUT TYPE="HIDDEN" NAME="PROCEDURE" VALUE="update_child_form">'
, '<INPUT TYPE="HIDDEN" NAME="FORMAT" VALUE="SUPPRESSED">'
Here is where it gets ugly. Fortunately, our cursor is going to do the dirty work,
but creating unique names just so we can edit more than one row is very annoying. open child
fetch child into @givenname, @birthdate
while @@sqlstatus = 0 and @number_of_children < 4
begin
select '<INPUT TYPE="TEXT" NAME="surname_'
, @number_of_children
, '" VALUE="'
, @surname
, '">'
, '<INPUT TYPE="TEXT" NAME="givenname_'
, @number_of_children
, '" VALUE="'
, @givenname
, '">'
, '<INPUT TYPE="TEXT" NAME="birthdate_'
, @number_of_children
, '" VALUE="'
, @birthdate
, '"><BR>'
fetch child into @givenname, @birthdate
select @number_of_children = @number_of_children + 1
end
close child
deallocate cursor child
select '<INPUT TYPE="SUBMIT" VALUE="Submit">'
select '</FORM>'
, '</BODY>'
, '</HTML>'
return
As you can see none of this is very clean except for our update_child
procedure which is concerned only with updating a single child. And to account for
those prolific parents having more than 4 children I may have to throw in a
NEXT button to handle the next batch.update_child automatically for each child on our form, and to do so all you
need to do is assign multiple invocations to that procedure with the Sybernet reserved word
PROCEDURE. Sybernet now detects multiple invocations and builds the batch
accordingly. In fact, you are not limited to just procedure calls: any Transact-SQL statement
is valid for the PROCEDURE value. create procedure update_child_form
(
@surname varchar(30) = "Workman"
)
as
select '<HTML>'
, '<HEAD><TITLE>Update Children</TITLE></HEAD>'
, '<BODY>'
select '<FORM METHOD="POST" ACTION="Sybernet.cgi">'
select '<TABLE BORDER=0>'
, '<TR>'
, '<TH>Surname</TH>'
, '<TH>Givenname</TH>'
, '<TH>Birthdate</TH>'
, '</TR>'
select '<TR>'
, '<INPUT TYPE="HIDDEN" NAME="PROCEDURE" VALUE="update_child">'
, '<TD>'
, '<INPUT TYPE="TEXT" NAME="surname" VALUE="'
, surname
, '"></TD><TD>'
, '<INPUT TYPE="TEXT" NAME="givenname" VALUE="'
, givenname
, '"></TD><TD>'
, '<INPUT TYPE="TEXT" NAME="birthdate" VALUE="'
, birthdate
, '"></TD></TR>'
from children
where surname = @surname
order by birthdate
select '<INPUT TYPE="HIDDEN" NAME="PROCEDURE" VALUE="update_child_form">'
, '<INPUT TYPE="HIDDEN" NAME="FORMAT" VALUE="SUPPRESSED">'
, '<INPUT TYPE="HIDDEN" NAME="surname" VALUE="' + @surname + '">'
, '</TABLE>'
, '<INPUT TYPE="SUBMIT" VALUE="Submit">'
, '</FORM>'
, '</BODY>'
, '</HTML>'
return
Everything is nicely aligned in a table, and I don't care how many children you have. The
procedure turns around and calls itself as the very last thing since our update procedure
is not normally going to generate a result set.
Sending Partial Result Sets
It is now possible to send partial result sets through the Sybernet CGI using WebStar's Send Partial
event. In essence, this means stored procedures can now use the facility known as "Server Push"
which is supported by NetScape version 1.1 and above. A description of Server Push and Client Pull
is described in the document
An Exploration of Dynamic Documents. select "<SEND_PARTIAL>"
select "</TABLE>, "<SEND_PARTIAL>", "<TABLE>"
Here are some examples that won't work:
select "<send_partial>"
select "</TABLE>" + "<SEND_PARTIAL>" + "<TABLE>"
In the last example, you cannot embed this string within another string.
Handling Text and Image Data Types
This section describes how stored procedures can be used to store and retrieve BLOB's
(Binary Large OBjects) into and from a Sybase database. In Sybase, a BLOB is a data type
of TEXT or IMAGE. The sections that follow will discuss how
BLOB's are selected (this won't hurt), how to store them (this will hurt a little), and
how to write a stored procedure that will retrieve and display a real image.
Selecting Text and Image Data.
The retrieval of BLOB's from a stored procedure is very easy because
all you have to do is select that column the same way as you would select any column type;
however, BLOB's that occur in a multi-column selection must occur after the last non-BLOB
data type. Consider the following table declaration:
create table template
( id int not null
, status char(6) null
, blob text null
, picture image null
)
Here are some examples of selections that are valid:
select id, blob from template where id = 14568
select blob from template
select status, id, blob from template
Here is an example of a selection that is invalid:
select blob, id from template where id = 14568
In this last and invalid example if you must select the blob column
before the id column, you want to break your select into two separate
statements; for example,
select blob from template where id = 14568
select id from template where id = 14568
is a legal way to select a BLOB type before a non-BLOB type.Storing Text and Image Data
Sybernet now handles the ability to insert data into a table directly. A stored procedure
can perform this task, but stored procedures are limited by their inability of accepting
parameters whose length is greater than 255 characters. For non-BLOB data types stored procedures
work very well; however, for data types of TEXT and IMAGE you can use
the Transact-SQL INSERT statement which can accept up to 100K bytes of data for
each column. create procedure sp_html_blob_form (@id int = null)
as
select '<FORM METHOD="POST" ACTION="Sybernet.cgi">'
select '<INPUT TYPE="hidden" NAME="procedure" VALUE="insert template">'
select '<INPUT TYPE="hidden" NAME="format" VALUE="SUPPRESSED">'
select '<INPUT TYPE="radio" NAME="status" VALUE="update">Update'
select '<INPUT TYPE="radio" NAME="status" VALUE="delete">Delete'
select '<INPUT TYPE="radio" NAME="status" VALUE="select">Select'
select 'What name: <INPUT NAME="id">'
select 'What text: <TEXTAREA NAME="blob" ROWS=10 COLS=80>'
select blob from template where id = @id
select '</TEXTAREA>'
select 'Press here to submit form: '
select '<INPUT TYPE="submit" VALUE="GO"><P>'
return
The problem that exists with this method is that an insert statement does not
generate a result set. How do you intercept the insertion and generate an HTML page for
the user? The answer, of course, is a trigger. Triggers (if they exist) are fired
automatically by the Sybase SQL Server whenever a row is inserted, deleted or updated on a
table. A simple (if inelegant) trigger that might be defined for the above table might
resemble the following:
create trigger sp_html_blob_trigger on template for insert
as
select '<H1>Thank you for your input!</H1>
return
This is not very exciting and the results are unimpressive; however, this simple example
does illustrate how an insert statement combined with a trigger can generate a response. create trigger sp_html_blob_trigger on template for insert
as
declare
@id int
, @status char(6)
select @id = id, @status = status from inserted
if @status = "update"
begin
delete template where id = @id and status = null
update template set status = null where id = @id and status = "update"
end else
if @status = "delete"
begin
rollback transaction
delete template where id = @id and status = null
end else
rollback transaction
exec sp_html_blob_form @id
return
This example assumes that permanent rows are stored with a null value in their
status column. When an update occurs, the row corresponding to this id and
whose status column is null is deleted; the row is then updated to set the status field to
null. When a delete occurs, the transaction is rolled back (to delete the record just
inserted) and the permanent row (if it exists) is also deleted. The select option simply
rolls back the current transaction so that we don't store the row just created by this
insert. And finally, a new form page is created by calling sp_html_blob_form.Retrieving Images
Ah, now we've come to the fun part. I've already explained that all you have to do to retrieve
an image or text data type is to select it. But how do you retrieve an image from Sybase and
have it appear as a real picture? Selecting it alone will not do the trick because the default
Content-type is HTML. Selecting an image in this mode will simply produce a
stream of funny looking characters on your screen. Here is an example of a stored procedure
that will override the default Content-type and produce a real image from the
above table:
create procedure sp_html_picture (@id char(5) = null)
as
declare
@CRLF char(2)
select @CRLF = char(13)+char(10)
select "HTTP/1.0 200 OK"
select @CRLF
select "Server: WebSTAR/1.2 Sybernet/CGI"
select @CRLF
select "MIME-Version: 1.0"
select @CRLF
select "Content-Length: "
select datalength(picture) from template where id = @id
select @CRLF
select "Content-type: image/gif"
select @CRLF
select @CRLF
select picture from template where id = @id
return
I won't go into the details of this procedure, but bear in mind this stored procedure
cannot be mixed with other Content-types. Suffice it to say that it will
generate the desired GIF from the table template, but you won't be able to bound this image
with HTML directly. Read on, read on. select '<H3>This is your picture</H3>'
select '<IMG SRC="Sybernet.cgi$sp_html_picture?' + @id + '">'
select 'There it is!<P>'
In this example we are declaring our image in the usual way; however, instead of a filename
that has the suffix GIF, we are invoking the stored procedure sp_html_picture
and passing it @ID as the parameter. Pretty cool or what?
Advanced Topics
This section discusses some of the more advanced features that are available with
Sybernet. In it we will discuss such things as URL's (and how they are used in
this environment), redirection, and image maps. All of which can be performed with
Sybase stored procedures. You'll want to check this section regularly as new features
are added.URL's
A URL (Universal Resource Locator) tells a Web browser where and how to fetch some
information. I don't plan to describe all of its flavors (primarily because I am
not capable of doing so), but I do want to describe how URL's relate to this
environment and how you can use them to your advantage. Of the many forms of URL's
that exist, the one you are most familiar with is the HTTP URL.
The general syntax of an HTTP URL that invokes the Sybernet CGI is
described by the following:
http://Sybernet.sri.com/Sybernet.cgi$Direct Argument?Search Argument
Sybernet.sri.com represents the IP address or host where this information
is found. Sybernet.cgi indicates the name of the CGI application. The
Direct Argument (if specified) represents the name of the Sybase
stored procedure that is to be executed (sans one special name), and the
Search Argument (if specified) is interpreted as a single (unnamed)
string parameter or a combination of named and unnamed parameters to that procedure.
Consider the following URL: http://Sybernet.sri.com/Sybernet.cgi$sp_html_property?ORG,024
This example instructs the Web browser to invoke the CGI called Sybernet.cgi at
Sybernet.sri.com and execute the procedure sp_html_property and pass the string
parameter "ORG,024."URL's and URI Encoding
Unfortunately, some characters in the Direct and Search arguments are not allowed
unless they are URI (Universal Resource Identifier) encoded. This encoding is
not unique to Sybernet. In fact, Sybernet has little to do with all of this until
your extra path information is actually received and decoded. Getting that string to
Sybernet, however, is going to make you scratch your head wondering why things aren't
working. select '<BR>'
, '<A HREF="Sybernet.cgi$sp_html_list?'
, '<URLEncode>' -- Turn on Encoding
, '@au_fname="'
, au_fname
, '"'
, ','
, '@au_lname="'
, au_lname
, '"'
, '</URLEncode>' -- Turn off Encoding
, '">'
, au_fname + ' ' + au_lname
, '</A>'
from pubs2..authors
Notice that au_fname and au_lname are enclosed in quotes because we are passing
strings to our stored procedure. The quotes conflict with the quotes that bound
our HREF string and are thus URI Encoded. The end result
after URI decoding is something that looks like this: exec sp_html_list @au_fname="Michelle", @au_lname="O'Leary"
As mentioned, the <URLEncode> and </URLEncode>
tags are case-sensitive. They may not be embedded inside of other strings; for
example,
select '<URLEncode>This will not work!</URLEncode>'
but
select '<URLEncode>'
, 'This will work!'
, '</URLEncode>'
Version 1.87 also introduced two additional tags to aid you in creating other string
expressions that require some form of encoding. They are the <JSEncode>
and <HTMLEncode> tags. They are similarly disabled by
including a forward slash in the usual manner.The JSEncode Tag
<JSEncode> encodes a JavaScript string. In a JavaScript string,
for example, a line-feed or carriage-return must be escaped. <JSEncode>
encodes every character that requires escaping in a JavaScript string including
forward slashes. Forward slashes are escaped because there is one particular phrase
that JavaScript would be confused by. Here is an example that assigns a text field in
Sybase to a JavaScript variable: select '<SCRIPT>'
, 'var s="'
, '<JSEncode>'
select blob from somewhere
select '</JSEncode>'
, '";'
, '</SCRIPT>'
I'm not suggesting that this string expression is not going to be too large for
your browser, but I am suggesting that the string will be correct and you will
not get a JavaScript error!The HTMLEncode Tag
<HTMLEncode> encodes an HTML string.
Normally you don't need to worry about such a function because you are hopefully
already familiar with the data you are sending to the web, but consider the
following example: select '<TR>'
, '<TD>'
, au_lname
, '</TD>'
, '<TD>'
, address
, '</TD>'
, '</TR>'
from pubs2..authors
What could possibly go wrong with this? Nothing as long as au_lname and address
do not contain a less-than sign. An address of
1234 Elm Street <apt 4
would display only 1234 Elm Street. Like I said, you know what your data looks
like and you don't want to start embedding the <HTMLEncode>
tag everytime you select data from a column, but for those cases where this is
required, this tag will be most handy. So, if address is a real candidate for
this option, this is how your select statement would look. select '<TR>'
, '<TD>'
, au_lname
, '</TD>'
, '<TD>'
, '<HTMLEncode>'
, address
, '</HTMLEncode>'
, '</TD>'
, '</TR>'
from pubs2..authors
Finally, all of these special encoding tags may be nested; however, only encoding
for the deepest tag will be performed. If you attempt to turn off encoding that
is not active, the tag is ignored, subject to any encoding that might be taking
place.Passing parameters using the Search Argument
As I mentioned, parameters in the Search argument may be named, and you do this
exactly like you would pass named parameters to a Sybase stored procedure. In fact,
this facility allows you to pass numeric and hexadecimal constants in the usual way,
but non-numeric data will be explicitly quoted by Sybernet. It is recommended,
therefore, that you quote non-numeric data yourself to avoid any confusion. A Search
argument of
@number=123, @mask=0xff, @option=null, @name=Hunter
is translated to
@number=123, @mask=0xff, @option=null, @name="Hunter"
This appears to make sense (and note that "null" is unquoted), but a Search argument of
Hello World, @name=Hunter
will be translated to
"Hello World, @name=Hunter"
which is probably not what you want (but it could be). To produce more predictable
results, you should explicitly quote each parameter value; for example,
"Hello World", @name="Hunter"
will produce the desired affect. Of course, the reason why Sybernet interprets strings
this way is that it makes it very easy to pass a single (unnamed) string parameter to
a stored procedure. @address, @method=@method
could be expanded into
"128.18.20.143", @method="POST"
Somewhere in this guide is a list of Sybernet internal variables
that may be retrieved and/or assigned in this manner. select 'If you need more information, please press '
select '<A HREF="Sybernet.cgi$sp_html_info">here</A>.
In this example, a hypertext link is constructed that will execute the stored
procedure sp_html_info when it is selected. Notice that the IP address
was not specified. This is allowed and will default to Sybernet.sri.com
because that is the name of the server that generated this link.Redirection
Redirection is a method whereby the Web Client is redirected to another location. This
location, however, must have a server that handles the request; for example, your
stored procedure might want to issue a redirect when the user requests information that
is not available through the Sybernet CGI (i.e., it is not available by calling Sybase
stored procedures). The following stored procedure will cause such a redirection to
the MIS.SRI.COM home page. create procedure sp_html_redirect
as
declare
@CRLF char(2)
select @CRLF = char(13)+char(10)
select 'HTTP/1.0 302 FOUND"
select @CRLF
select 'Server: WebSTAR/1.2 Sybernet/cgi'
select @CRLF
select 'Location: http://mis.sri.com/'
select @CRLF
select @CRLF
return
A more general example of this procedure would accept a string parameter (e.g., a varchar)
that would be inserted at the desired location. Also note that redirection
can not be embedded within any other context: you are not allowed to bound this procedure
call with HTML. An example of a hypertext link that invokes this procedure might resemble
the following: select 'To return to the <STRONG>MIS</STRONG> home page select '
select '<A HREF="Sybernet.cgi$sp_html_redirect">this</A>.
Remember that to pass a string parameter to a stored procedure using a URL, that parameter
would occur after the Direct argument and preceded by a question mark.Image Maps
Image maps are those entities you've probably seen that allow you to click anywhere on
an image. What results occur are dependent upon exactly where on that image you actually
clicked. The coordinates of your click are passed to an imagemapping CGI which determines
where in this image you clicked and returns information based on that information; for example,
an image of a face might produce detailed information about the eye-ball assuming you clicked
on the part of the image. select 'This picture is a clickable map.<P>'
select '<A HREF="Sybernet.cgi$sp_html_map">'
select '<IMG SRC="Sybernet.cgi$sp_html_picture?' + @id + '" ISMAP>'
select '</A>'
In this example we are retrieving a GIF image stored in Sybase. The image is declared
as an image map by the ISMAP attribute. The procedure invoked by the Web
client is called sp_html_map.Search argument. The Search argument contains an X and
Y coordinate separated by a comma. Here is a simple procedure that
parses this argument and displays some relevant information to the client. create procedure sp_html_map (@coordinates varchar(10) = null)
as
declare
@X int
, @Y int
, @i int
select @i = charindex(",",@coordinates)
select @Y = convert(int,substring(@coordinates,1,@i-1)
select @X = convert(int,substring(@coordinates,@i+1,datalength(@coordinates)-@i))
select 'You selected the '
if @X < 122
select 'top '
else
select 'bottom '
if @Y < 162
select 'left.'
else
select 'right.'
return
This stored procedure simply tells you where in the picture you clicked. A more elaborate mapping
could be devised where each individual element of the picture is bound by rectangular coordinates
(or circular ones, it doesn't matter) and these coordinates along with some key are stored in a
table. A select statement with an appropriate where clause could select that key and
perform the required action.HTTP Cookies
Cookies are a method that allows a CGI (or in our case a Sybase stored procedure)
to store and retrieve state information on the client side of a connection. This section
will discuss how cookies are stored and retrieved with a stored procedure. For a complete
description of HTTP Cookies I recommend reading
Persistent Client State HTTP Cookies. create procedure show_cookies(@COOKIE varchar(255) = null)
as
select '<HTML>'
select '<BODY>'
select '<H1>Here are your cookies</H1>'
select @COOKIE
select '</BODY>'
select '</HTML>'
return
Notice that @COOKIE is declared as a VARCHAR(255) string. 255 is the maximum number of
characters that Sybase will accept in a parameter. If you attempt to set so many cookies
that this limit is exceeded, only the first 255 characters is returned. create procedure get_cookie
as
select '<HTML>'
select '<BODY>'
select '<FORM METHOD="POST" ACTION="Sybernet.cgi">'
select '<INPUT TYPE="HIDDEN" NAME="PROCEDURE" VALUE="show_cookies">'
select '<INPUT TYPE="HIDDEN" NAME="@COOKIE" VALUE="@COOKIE">'
select '<INPUT TYPE="SUBMIT" VALUE="GET COOKIES">'
select '</FORM>'
select '</BODY>'
select '</HTML>'
return
Here is a URL request in the form of a hypertext link that accomplishes the same thing:
create procedure get_cookie
as
select '<HTML>'
select '<BODY>'
select '<A HREF="Sybernet.cgi$show_cookies?@COOKIE">GET COOKIES</A>'
select '</BODY>'
select '</HTML>'
return
And finally we also want the ability to assign cookies, but the COOKIE reserved word is
read-only. To store an HTTP Cookie you must create a valid HTTP header in your stored
procedure. Here is a stored procedure that creates a valid HTTP cookie.
create procedure set_cookie
as
declare
@CRLF char(2)
select @CRLF = char(13)+char(10)
select 'HTTP/1.0 200 OK'
select @CRLF
select 'Server: WebSTAR/1.2 Sybernet/cgi'
select @CRLF
select 'Content-type: text/html'
select @CRLF
select 'Set-cookie: INVOICE=1114'
select @CRLF
select @CRLF
select '<HTML>'
select '<BODY>'
select '<H1>Thank you for your order!</H1>'
select '</BODY>'
select '</HTML>'
return
As I mentioned all cookies are returned as one long string. To actually determine the
value of all cookies in your @COOKIE parameter you need to parse this string in your
stored procedure. This string will contain zero or more NAME=VALUE pairs and NAME=VALUE
pairs are separated by semicolons. create procedure get_invoice(@COOKIE varchar(255) = null)
as
declare
@i int
, @NAME_VALUE varchar(255)
, @NAME varchar(30)
, @VALUE varchar(128)
select '<HTML>'
select '<BODY>'
while datalength(@COOKIE) > 0
begin
select @i = charindex(@COOKIE,";")
if @i > 0
begin
select @NAME_VALUE = substring(@COOKIE,1,@i-1)
select @i = @i + 1
select @COOKIE = substring(@COOKIE,@i+1,datalength(@COOKIE)-@i)
end else
begin
select @NAME_VALUE = @COOKIE
select @COOKIE = NULL
end
select @i = charindex(@NAME_VALUE,"=")
select @NAME = substring(@NAME_VALUE,1,@i-1)
select @VALUE = substring(@NAME_VALUE,@i+1,datalength(@NAME_VALUE)-@i)
if @NAME = "INVOICE"
begin
select '<H1>Here is your order:</H1>'
select '<PRE>'
select * from invoices where invoice = @VALUE
select '</PRE>'
select '</BODY>'
select '</HTML>'
return
end
end
select '<H1>Error...</H1>'
select 'Invoice not found!'
select '</BODY>'
select '</HTML>'
return
Cookies certainly can be helpful to save state information between multiple clients, but
there are other mechanisms that will accomplish the same thing. One of the easier methods
is to use "HIDDEN" fields in your form to pass this information between your clients and
your stored procedure; however, Cookies are "persistent" in the sense that the client's
browser will retain their value. You can even specify an expiration date for your
cookies which persist even if the client disconnects from Sybase and shuts-down their Mac!HTTP Headers
You have seen some examples in this guide where a stored procedure creates its own HTTP
header. We did this when we wanted to change the content-type from text/html to image/gif
and when we wanted to "redirect" a client to another URL. In general, your stored procedure
will not create HTTP headers unless you need to specify something other than the default,
but if you do, this section may be helpful understanding what happens "behind the scenes." create procedure hello_world
as
declare
@CRLF char(2)
select @CRLF = char(13)+char(10)
select 'HTTP/1.0 200 OK'
select @CRLF
select 'Server: WebSTAR/1.2 Sybernet/cgi'
select @CRLF
select 'Content-type: text/html'
select @CRLF
select @CRLF
select '<HTML>'
select '<BODY>'
select '<H1>Hello World!</H1>'
select '</BODY>'
select '</HTML>'
return
Sybernet does not scan your entire result set and does not look for more than two pairs
of HTTP headers. Only the first HTTP header--the one created by Sybernet--is stripped
when a second HTTP header is detected!Filters
Filters are independent of format and content-type. Filters are built-in
preprocessors that do something with your result set before sending them
to your browser; for example, the CGI filter sends your result sets back
to Sybase before processing your output results.The CGI Filter
This is one of the newer formatting options recently added to Sybernet. This option
will cause Sybernet to resubmit the results of your stored procedure to Sybase. create procedure bounce
as
select 'SELECT "<H1>Hello World</H1>"'
return
When FILTER=CGI, the result set produced by this procedure (SELECT "<H1>Hello World</H1>")
are intercepted by the Sybernet CGI and resent to Sybase. The result is Hello
World in big, bold text! create procedure get_table(@TABLENAME varchar(30))
as
select 'SELECT * from ' + @TABLENAME
return
In this example we are selecting a table name dynamically. This may not seem like a
big deal, except that this is something that is not allowed by a stored procedure. In
a stored procedure all objects (tables, databases, views, et al) must be hard-coded
at compile-time. The CGI filter completely eliminates this restriction. select '<TABLE WIDTH="100%">'
But when building this same expression at run-time using the CGI formatting option,
you need to separate the use of single-quotes embedded in double-quotes and vice
versa. The expression to generate this same expression using FILTER=CGI would be
similar to the following:
SELECT "select '<TABLE WIDTH=" + '"100%"' + ">'"
The last programmer to use this option was attempting to build a complex selection
condition in their WHERE clause. I first suggested fooling Sybase by doing multiple
ANDS that would return TRUE using LIKE comparisons. The idea worked fine except it
took the procedure 9 minutes to finish. With the CGI option, the selection condition
was built dynamically (specifying only those columns that required testing) and the
time to finish was reduced to a few seconds. Stunning, to say the least!Example
One of the most clever uses of this option is to insert your HTML
page into a text column (rather than sending it to the browser) and then select that
column out of the table and give that to your user instead. The user gets their report,
and you have the original safely stored away for easy retrieval. create procedure sp_html_deposit(@row_id int)
as
select 'insert safe'
, '('
, ' row_id'
, ', html'
, ')'
, 'values'
, '('
, ' ' + convert(varchar,@row_id)
, ', "'
select '<HTML>'
, '<HEAD><TITLE>Web Page</TITLE></HEAD>'
, '<BODY>'
, 'This HTML page is being inserted into a table'
, '</BODY>'
, '</HTML>'
select '")'
select '<GO>'
, 'select html from safe where row_id = ' + convert(varchar,@row_id)
, '<GO>'
return
The procedure (using FILTER=CGI and FORMAT=SUPPRESSED)
creates an insert statement, the result of which resembles the following:
insert safe
(
row_id
, html
)
values
(
123
, "<HTML><HEAD><TITLE>Web Page</TITLE></HEAD><BODY>This HTML page is being inserted into a table</BODY></HTML>"
)
It then turns around and selects the row it just inserted:
select html from safe where row_id = 123
And yes, writing stored procedures this way is not for the faint of heart. It takes a while
getting used to, and you will spend count-less hours wondering why your quotes are in the
wrong place, but the flexibility of this option makes it all worth while. Trust me!The ECHO Filter
This filter option is similar to the CGI option except that results are sent back
to your stored procedure. I added this option several months ago to solve a problem
with BLOB's which could not be handled by stored procedures. We needed the ability
to "parse" a Text field and replace certain tokens in that BLOB with other values.
The use of this option, however, is not just limited to BLOB's. create procedure echo(@ID varchar(20) = null,@timestamp varchar(26),@blob varchar(254) = null)
as
if @blob is null
begin
select Text_Column from Table_Name where id = @id
return
end
if datalength(@blob) = 254
begin
insert Scratch_Table values (@id,@timestamp,@blob)
return
end
insert Scratch_Table values (@id,@timestamp,@blob)
select '<HTML>'
select '<BODY>'
select blob from Scratch_Table
select '</BODY>'
select '</HTML>'
return
This procedure runs in three states:Filters, Formats, and Content-Types
This section attempts to explain how Filters, Formats, and Content-types interact
with each other.
Format Filter Content-type Format CGI CGI SUPPRESSED ECHO ECHO SUPPRESSED EXCEL application/excel TABULAR RTF application/rtf RTF NONE NONE SUPPRESSED SUPPRESSED TABULAR TABULAR
To filter your result sets with the CGI filter and then create an RTF document you
would specify FILTER=CGI and FORMAT=RTF.More About Content-type
When your stored procedure is concerned with delivering only a single content-type
(normally, "text/html"), things are fairly straight-forward. Things start to get
complicated when you attempt to handle many different content-types in the same
request, but with some explanation this is something you can do.