In part one of this article, Data
Views, I described several techniques using
server-side JavaScript and HTML tables for
presenting data in an interactive format that allows users to customize
their views of the data. Of course, there
are two sides to every story, and this one is no different. Part
one described some helpful ways in which to build
views of data, that is outputting the data into an HTML page.
Server-side JavaScript -- part of the LiveWire development
tool and an intrinsic part of the Netscape Enterprise Server -- also offers
some very useful ways for inputting data back into a database. In
fact, you'll find that the plot thickens when I describe ways to create
forms capable of both input and output, which I'll
do in this part of the article. I hope you'll come away with a few
more JavaScript tricks to put up your sleeve.
Push and Pull
In part one, I discussed constructing views on data sets with HTML tables
and hyperlinks. Once we construct the view, we still
have to decide what to do with the records we spent so much time making
accessible. Many times, simply displaying a record will be sufficient once it's found.Other
times, users will want to "drill down" and find a specific
record for the purpose of modifying its data.
In the wide world of the Web, HTML forms do the dirty work of data entry.
HTML forms have long been used to obtain new data from users. Yet in most
traditional client/server database applications, forms
not only let users enter new data into blank fields, they also display
data to users and let them update that data. We
want our Web-based applications to be able to deliver these same features,
and, fortunately, that's one of the most important ways you can use server-side
JavaScript.
Those of you familiar with database-aware rapid application development
(RAD) tools, such as Borland Delphi or Symantec Visual Cafe Pro, should
recognize the concept of using forms and data-aware controls for both input
and output over the Web. With server-side
JavaScript, development is not yet quite so
"rapid," so we must do the work of turning ordinary HTML controls
-- such as text fields and select lists -- into data-aware controls. Given
server-side JavaScript's capabilities for
dynamically generating HTML, replicating this behavior with HTML forms
should be straightforward. Let's take a look at a few examples and see
just how easy it is to design HTML forms that not only pull
data from a database, but push data
back into it, as well.
Is it Input or Output?
Let's start with a simple example of a
one-way form that takes user input and stores it in a database, shown in
Example 1. Imagine an HTML form designed as part of an intranet data-entry
application designed for entering employee data. Once the user clicks a
submit button on the form, the data is passed to the server and stored
in a database table called Employee. Figure
1, "Form Handler Structure," shows
the pages involved and the flow of data.
Example 1
<!-- Employee form data sent to a form handler called "employee_handler.html" -->
<FORM NAME="employeeForm" ACTION=/old?u=http%3A%2F%2Fdeveloper.iplanet.com%2Fviewsource%2F%26quot%3Bemployee_handler.html%26quot%3B&y=1999 METHOD=POST>
<!-- Social Security Number -->
<INPUT TYPE="text" NAME="ssn" SIZE=9>
<!-- First Name -->
<INPUT TYPE="text" NAME="firstName" SIZE=20>
<!-- Last Name -->
<INPUT TYPE="text" NAME="lastName" SIZE=40>
The page indicated by the ACTION attribute of the <form>
tag is called a form handler. For our purposes, a form handler -- in this
case employee_handler.html -- is an HTML page containing server-side
JavaScript used to process the data from the form. With
server-side JavaScript, data values submitted from a form are available
to the form handler as properties of the Request object. The form handler
for the employeeForm.html form from Example 1
will look something like the code in Example 2.
Example 2
<SERVER>
// Connect to "myDatabase" on the Informix database server "myDBServer"
database.connect ("INFORMIX", "myDBServer", "myUsername", "myPassword", "myDatabase");
// Create updatable cursor on the Employee table
empCursor = database.cursor("select * from EMPLOYEE", true);
// Assign form field values to cursor values to prepare
// for insertion into the Employee table.
empCursor.ssn = request.ssn;
empCursor.fName = request.firstName;
empCursor.lName = request.lastName;
// Be sure to escape incoming data from textarea field
// in case any carriage returns or other special
// characters were entered.
empCursor.jobDesc = escape(request.jobDescription);
// Insert the record into the database
empCursor.insertRow("EMPLOYEE");
empCursor.close();
// Call a custom server-side function defined elsewhere that
// will generate HTML and serve up to show user results
GenerateHTMLResults();
</SERVER>
Examples 1 and 2 provide a basis for illustrating a few techniques.
Let's first take a look at how we might change the input form to show existing
data for an employee and allow users to update that data, rather than simply
insert data for new customers. To keep things simple, I'm going to make
two assumptions: First, the user has arrived at our input form (see
Example 1) by entering a social security number (SSN)
or selecting a social security number from a list earlier in the process.
Second, this social security number has been stored in the Client object
earlier in the process as the property client.ssn.
Because the social security number is now a unique identifier for an
existing record in the Employee table, and its value never changes, I want
to display this value and not let users modify it. I'll use a server-side
write statement to display the social security number and remove the
corresponding text input element so that users can see but not modify that
field.
In order to put data into the HTML form text elements, we'll have to
add some server-side JavaScript to our form. We will do this in two ways
-- using <server> tags as well as by using back quotes (`), which
tell the server to emit HTML. (See the LiveWire Developer's Guide,
chapter
three, pp. 75-77.) First, in Example 3 let's add code to the input
form -- employeeForm.html -- that will retrieve data on the employee
from the database.
Example 3
<SERVER>
// Connect to "myDatabase" on the Informix database server "myDBServer"
database.connect ("INFORMIX", "myDBServer", "myUsername", "myPassword", "myDatabase");
// Establish a cursor on an existing employee record
cursor = database.cursor ("select * from EMPLOYEE where ssn='" + client.ssn + "'");
// Advance the cursor to point to the data record
cursor.next();
// Write out the employee's social security number
write ("Employee SSN: " + client.ssn);
The code in Example 3 opens a cursor on the Employee table within the
HTML page containing our form. Below, we'll add data from the table to
the input fields. Notice that back quotes (`) are used inside the HTML
tags instead of <server> tags. Server tags could be used to write
out the the entire HTML tag, but back quotes are more convenient
for dynamically writing data inside HTML tags, and they make the
code a bit easier to read. As the developer's guide shows, back quotes
instruct the server to emit HTML, so we
don't have to usewrite statements to output HTML
from within them. The new code is shown in
bold in Example 4.
Note: Boldface indicates code changes from
Example 3.
We've added very little code, and yet the
form elements will now be filled in with whatever data that's presented
by the database cursor's result set. However, we're not quite finished.
What will happen when a field in the database
contains a null value? In other words, what will be written in a text box
if a particular field is simply null in the database? One might think that
the field will simply be left blank -- which is what
we'd like. However, that's not what happens. Instead,
if a field in the database has a null value,
when the server renders and serves up the HTML page to the client, the
text string 'null' will appear in the text or textarea input
field.
This ugly little user interface issue can be avoided by adding some
safety code that checks if the database
field is null before assigning any
value to the input field seen by the user. To do this, we could
use an if statement, but there's another conditional construct
in JavaScript that will perform the same function in shorthand, if you
will: the ? operator.
The ? operator takes the following form:
expression ? value1 : value2
If expression evaluates to true, the statement equates
to value1; otherwise, it equates to value2. It's simply
shorthand for an "if-then-else" clause. So, to ensure that the
text string 'null' doesn't appear in any fields in
our form, the code from Example 4 needs to
be modified as shown in Example 5.
Example 5
<!-- First Name -->
<INPUT TYPE="text" NAME="firstName" SIZE=20 `VALUE=(cursor.fName != null)
? cursor.fName : ""`>
<!-- Last Name -->
<INPUT TYPE="text" NAME="lastName" SIZE=40 `VALUE=(cursor.lName != null)
? cursor.lName : ""`>
<!-- Job Description -->
<TEXTAREA NAME="jobDescription" ROWS=4 COLS=40>
<SERVER>
if (cursor.jobDescription != null)
write(unescape(cursor.jobDesc));
</SERVER>
</TEXTAREA>
At this point, I should explain why I used <server>
tags for the text-area field instead of the conditional operator. Text-area
fields are a little different than single-line text input fields. Unlike
a simple input field like a text field, a
text-area field is a container, which means that it has an opening tag
-- <textarea> -- and must also have a matching closing tag -- </textarea>.
Any text appearing in between these tags will appear in the text area box.
Because back quotes are only used inside HTML tags (not inside containers),
we have to use <server> tags to output the data between the opening
and closing tags of the text-area field. Since we have to use <server>
tags anyway, we can use a simple if statement to do the work,
rather than the shorthand conditional operator, since it's a bit easier
to write.
The code shown in Example 5 is all that's needed
on the HTML form to change it from a one-way, submission-only form into
a two-way, display-and-update form. Next, the form handler needs to be
modified a bit to perform an update instead of an insert. Example 6 shows
the necessary modifications we need to make to the form handler.
Example 6
<SERVER>
// Connect to "myDatabase" on the Informix database server "myDBServer"
database.connect ("INFORMIX", "myDBServer", "myUsername", "myPassword", "myDatabase");
// Create updatable cursor on the Employee table and
// select the record associated with the social security number
// stored in the client object.
empCursor = database.cursor("select * from EMPLOYEE where ssn='" + client.ssn + "'", true);
// Advance the cursor to the selected record
empCursor.next();
// Assign form field values to cursor values to prepare
// for update into the Employee table.
empCursor.fName = request.firstName;
empCursor.lName = request.lastName;
// Be sure to escape incoming data from textarea field
// in case any carriage returns or other special
// characters were entered.
empCursor.jobDesc = escape(request.jobDescription);
// Update the record in the database
empCursor.updateRow("EMPLOYEE");
empCursor.close();
// Redirect to a page that reports the operation has completed
redirect("confirmation.html");
</SERVER>
Let's analyze the changes made in Example 6. We
altered the cursor construction to select the record associated
with the social security number stored in the client object, just as we
did on the HTML input form (shown in Example
3). Next, we advanced the cursor
to select the record we'll be updating. In contrast, when inserting a record,
it's not necessary to advance the cursor to an existing row since an entirely
new row is being inserted. It's no longer necessary to insert the social
security number since it already exists in the table as a unique identifier
and is no longer updatable on the HTML form. Finally, the insertRow
statement has been changed to a call to the updateRow method.
The form will now not only pass data into the database, it will also display
current data from the database as well in the text and text-area
fields within the form.
It's Elemental
As I mentioned in the introduction, RAD tools on the market today make
the creation of data-aware forms seem like a snap. Many of the tools come
with form elements, such as text boxes, select lists, and list boxes, that
are already data-aware and simply need to be hooked up to the appropriate
data source. Unfortunately, things aren't nearly that simple with HTML
and server-side JavaScript. Yet, the situation is far from grim. I've already
shown how simple text and text-area fields in an
HTML form can be transformed into data-aware elements; next I'll demonstrate
how the same thing can be done with more complex elements, such as select
and list boxes.
One of the most useful pieces of LiveWire application code I've written
over the past several months is a server-side JavaScript function that
dynamically populates an HTML select box. I found that I often needed to
provide users with a select box that contains values from a lookup table,
such as a list of credit card types accepted by a merchant doing business
on the Internet. Such data may change over time, and it may be required
in several forms in an application. If the data were hard-coded into the
HTML forms, anytime it changed I'd have to
change the HTML in every form on which this lookup data appeared. Instead,
the data could be more conveniently stored
in a lookup table in a database, and the select boxes
could then be dynamically generated with JavaScript. That way any
time the data changes, I'd only have to update
the data in a single lookup table, and every instance of the HTML select
box will be automatically updated.
For those unfamiliar with HTML select boxes, two examples of working
select boxes are shown below. Go ahead and click on them -- they
won't bite. There are two distinct types of select boxes. A
select --or drop-down -- box contains only one visible row unless it's
clicked on, such as the box shown in the illustration "Drop-Down Box."
In contrast, a list box, as shown in "List Box," shows a list
of multiple items. Example 7 illustrates the HTML code necessary to create
a select box.
Drop-Down
Box
List Box
Example 7
<SELECT NAME="myList" SIZE=1>
<OPTION VALUE="DINERSCLUB">Diner's Club
<OPTION VALUE="MASTERCARD">MasterCard
<OPTION VALUE="VISA">Visa
</SELECT>
Each item in the select box is listed as
an <option> tag contained within <select> tags. The SIZE
parameter of the <select> tag determines if
the HTML page will render a one-row drop-down
box or a multiline scrolling list box. If
the SIZE parameter equals 1, the page uses a drop-down box; if
it's greater than 1, the page uses a scrolling list box with a number of
rows equal to that number. Note that each option can have both a
value and descriptive text, which can be different. In Example 7, DINERSCLUB
is the value and "Diner's Club" is the more readable
descriptive label. Providing descriptive labels helps users identify the
choices instead of making them choose from among
values that aren't very human-friendly. We'll take advantage of
such descriptive labels in the example below.
Before I show a code sample illustrating how to create drop-down boxes
dynamically, I want to outline some of the features we'll
need in the function, which I call DynamicSelect.
First of all, we'd like the function to be reusable for any lookup table,
not just one for a list of credit card types. Second, we'd like the function
to be somewhat flexible in terms of how it displays
lookup data. For instance, we may want the data to be displayed
in a scrolling list box instead of a drop-down box.
In order for the function to be reusable, we need to decide upon a common
format for the lookup tables. Our lookup tables will have two fields; I
describe them here in the order in which they appear in the table:
lookupValue holds the actual value (for example, DINERSCLUB)
that we are interested in passing back to the database when the user makes
a selection.
lookupLabel holds a string that contains descriptive text
(for example, "Diner's Club") that will actually be
displayed to the user in the drop-down box.
The lookupLabel column is optional;
it allows the display of a descriptive label associated with the actual
value to be submitted with the form, in case the actual value is stored
in an unpleasant format. If lookupLabel is blank or has a null
value in the database, lookupValue will be used as both the value
and label by default.
To achieve the second goal of flexibility, the
DynamicSelect function should have parameters that allow some
of the attributes of the HTML select box to
be set dynamically. The complete function is shown
in Example 8. To help you understand it, in the next section I'll
construct the function piece by piece, and I'll explain what each segment
of code does as I go.
Example 8
function DynamicSelect( listname, size, cursorObj )
{
// Variables to hold the value and text label for an option in the list box.
var value, label;
// Construct the SELECT tag
write("<SELECT NAME=\"" + listname + "\" SIZE=" + size + ">");
// The while loop writes out the options array by dynamically constructing
// each option statement based upon the two fields in the table.
while(cursorObj.next()) {
// If the value field is null, give it an empty string ("") for a value
// otherwise, assign the actual value from the lookupValue column.
if ( ((cursorObj.lookupValue + "") == "null") || (cursorObj.lookupValue == "") )
value = "";
else
value = cursorObj.lookupValue;
// If the lookupText field is null or empty, substitute the lookupValue
// column for the text to be displayed as the default.
if ((cursorObj.lookupLabel == null) || (cursorObj.lookupLabel == ""))
label = value + ""; // convert to a string
else
label = cursorObj.lookupLabel + ""; // make sure it's a string
Let's first consider the first part of the code
in Example 8, as follows:
function DynamicSelect( listname, size, cursorObj )
{
// Variables to hold the value and text label for an option
// in the list box.
var value, label;
The function has the following three parameters:
listname: the name of the HTML select
box we're creating.
size: equivalent to the size parameter of the HTML <select>
tag. If equal to 1, the box will be a drop-down box. If greater than 1,
it will be a scrolling list box with the number of visible rows equal to
size.
cursorObj: a cursor object that is created before calling
this function. The cursor is created outside of the function so the function
can be reused with any lookup table having a similar structure.
Two local variables are declared as temporary storage for the lookup
value and label. Next, we will want to construct the actual <select>
tag that will start the definition of the list box. In
the following code, the listname and size parameters
from the function are filled in for the similar parameters for the <select>
tag:
// Construct the SELECT tag
write("<SELECT NAME=\"" + listname + "\" SIZE=" + size + ">");
Next, we must loop through each record in the table and construct each
<option> tag, as shown in the following:
// The while loop writes out the options array by dynamically constructing
// each option statement based upon the two fields in the table.
while(cursorObj.next()) {
// If the value field is null, give it an empty string ("") for a value
// otherwise, assign the actual value from the lookupValue column.
if ( ((cursorObj.lookupValue + "") == "null") || (cursorObj.lookupValue == "") )
value = "";
else
value = cursorObj.lookupValue;
// If the lookupText field is null or empty, substitute the lookupValue
// column for the text to be displayed as the default.
if ((cursorObj.lookupLabel == null) || (cursorObj.lookupLabel == ""))
label = value + ""; // convert to a string
else
label = cursorObj.lookupLabel + ""; // make sure it's a string
The code shown above performs a test to
see if the optional field, lookupLabel, contains a value for this
record. If it doesn't, the value for lookupValue
is substituted by default. Next, we'll complete
the code for the function, as follows:
// Construct the OPTION tag with the values calculated above
write("<OPTION VALUE=\"" + value + "\" >" + label + "\n");
} // END while loop
//Close the SELECT block
write("</SELECT>\n");
} // END FUNCTION DynamicSelect()
The final lines of code close the loop, and after the loop exits
we add the required closing tag for the select box. When the function is called with the appropriate parameters, the
server will render an HTML select box
-- which may be a drop-down or scrolling list box -- and fill
it with data from a lookup table. In case
you missed it the first time, Example 8 contains
the complete code listing for the DynamicSelect function.
Now that the function is defined, let's examine how the function is
called from within an HTML form in Example 9.
Example 9
<!-- This form gathers purchase information from a customer and passes it on to the form handler "handle_purchase.html" -->
<!-- Full name for billing purposes -->
<INPUT TYPE="text" NAME="fullName" SIZE=20>
<!-- Credit card number -->
<INPUT TYPE="text" NAME="cardNumber" SIZE=20>
<!-- Accepted credit cards -->
<SERVER>
// Establish a cursor on the lookup table containing card types,
// CARD_LOOKUP
cardCursor = database.cursor("select * from CARD_LOOKUP");
DynamicSelect("cardType", 1, cardCursor);
</SERVER>
</FORM>
There are a few things to note in the example. First, it
assumes a database connection has already been established, either previously
on this page or in the initial page of the application. Next, recall that
the DynamicSelect function has three parameters:
name of the select box, size, and a cursor object. A cursor object on the
lookup table we're using must be established
prior to the call to DynamicSelect. This requirement may seem
odd at first, but it allows us to decide
just what query the select box will be based on.
Otherwise, the function could only be used for a single lookup table, rather
than any number of different lookup tables with similar schemas. As a final
note, you'll notice that we haven't performed
a cursor.next() after creating the cursor. This is because the
DynamicSelect function uses a loop that performs the initial -- and
all subsequent -- cursor.next() calls for us. (See Example 8.)
Adding Power to Database Applications
There are quite a few more possibilities for associating HTML form elements
with data from a database. We can also program forms
that dynamically construct radio buttons and check boxes based upon
SQL queries. I encourage you to experiment when designing your HTML forms.
I think you'll find that there are many ways in which your form elements
can be constructed as data-aware components, providing your users with
a more realistic and powerful way to view and manipulate data.
JavaScript is a simple language in many ways; even
though it's simple, it provides us with great flexibility and freedom
in designing useful, reusable data-aware code. The fundamental database
connectivity objects and methods included with server-side JavaScript provide
an ample basis for creating advanced solutions to solve real-world problems.
Connecting data with hyperlinks, constructing HTML tables for data output,
and using HTML forms for input and output are all important concepts to
understand for adding real power to real world database applications with
server-side JavaScript.
(Technical reviewers for this article were Netscape engineers Irene
Wand, Rich Yaker, and Rand McKinney.)
View Source wants your feedback! Write to us and let us
know what you think of this article.
J.J. Kuslich
is an Internet/intranet consultant working for the software consulting
firm Application Methods, Inc.
in Seattle. He's been working with LiveWire for the past year and helped
develop the "Video" sample application for LiveWire and the Starter
Applications for Netscape LivePayment. His interests include hiking, camping,
and playing guitar rather poorly (while pretending he's playing brilliantly
on stage with his favorite band, Rush).
Asked whether or not he considers himself an "empowered proactive
knowledge worker," he responds: "Did you just say something in
manager-speak? I shall have to slap you now." Realizing that he is
nonviolent by nature, he recommends instead we all read more Dilbert.
