In a previous article in View Source, JavaScript on the Server, Paul Dreyfus introduced you to the concept of using server-side JavaScript—part of the Netscape LiveWire development tool—to develop Internet and intranet applications, particularly for connectivity with relational databases. As a software consultant, I've used LiveWire and server-side JavaScript to develop a variety of specific Web-based database access solutions. Since there's so much demand for knowledge about how to develop database-enabled Web sites, I thought I'd share some of my experiences using and building upon the simple database constructs from server-side JavaScript. I think you'll find that these constructs open a wide world of possibilities for Web-based applications.

In this two-part article, I'll demonstrate a few fundamental techniques you can use to create interactive database-access applications using server-side JavaScript. Part one will deal with creating flexible "views" on databases. For you die-hard SQL experts, I'm not speaking of strict SQL views, but rather simply creating result sets and displaying them to the user in a useful format. I'll demonstrate some useful ways to display and organize data from databases on HTML pages. In part two, I'll describe how to create "live" input forms that let users edit existing data instead of merely entering information into blank input forms. I'll present techniques in each section that go beyond the simple examples presented in the Netscape LiveWire Developer's Guide.

A basic understanding of HTML, SQL, and JavaScript and its database access objects will help you understand the techniques I describe. However, if you're not very familiar with these topics, don't worry. Some of the code samples presented here may pass you by, but the underlying concepts will still help you on your way to designing database applications. For those readers who aren't familiar with the JavaScript objects that provide database access, I'll start by giving a brief overview of them. For a complete description of all of their capabilities, I encourage you to refer to the LiveWire Developer's Guide.

Brief Overview of Database Access with JavaScript

LiveWire supports connectivity to Informix, Oracle, Sybase, and any other relational databases that accord with the ODBC standard for Windows NT-based systems. Developers access databases with the Netscape Enterprise and FastTrack servers through the server-side JavaScript database object, which has several methods for performing operations on a database. Among the more commonly used methods of the database object are the following:

• connect for establishing a connection to the database with a specific set of user privileges
• execute for executing pass-through SQL statements
• cursor for establishing a cursor object on a database
• SQLTable for printing a simple HTML table based upon a SQL query
• transaction control and error reporting methods

In LiveWire, cursors are primarily used for conducting queries and performing operations on the returned data; you should familiarize yourself with how they work (if you haven't already). Essentially, a cursor is a pointer to a set of records—a result set—that results from an SQL query. Calling the database.cursor() method returns a cursor object that points to the result set from an SQL query. Cursors provide a uniform interface to database operations such as inserting, updating, and deleting rows from a table, as well as navigating a result set. They do all this without having to know the specifics of the database management system (DBMS) they're connected to. In a corporate environment where several types of databases—such as Oracle, SQL Server, and so on—may exist, JavaScript cursor objects give developers a tremendous advantage.

As I just mentioned, calling the database.cursor method will establish a cursor containing a result set from an SQL query. The cursor method actually creates a cursor object in JavaScript that represents the cursor, and as such the cursor object has its own set of methods. Performing operations on a cursor is as simple as calling methods and referring to properties, just as with other JavaScript objects.

Some of the most widely used properties and methods of the cursor object are the following:

• the cursorColumn property is an array that represents a particular column in the result set. Columns may be accessed as elements of an array or by column name
• insertRow, updateRow, and deleteRow methods are used for inserting, updating, and deleting rows in the result set
• the next method allows navigation through the result set

Example 1 is an introductory example that demonstrates how to use the objects and methods I just mentioned. The code sample illustrates connecting to a database, writing some values from a table called Customers to the screen in HTML, then updating two other fields and writing them back to the Customers table. Just about any database application in JavaScript will contain some or all of the fundamental methods and properties referenced in Example 1.

// Connect the database MYDATABASE on server MYSERVER
database.connect("INFORMIX", "myServer", "informix", "informix", "MyDatabase");

// Establish an updatable cursor on a sub-set of the CUSTOMERS table

custCursor = database.cursor("select * from CUSTOMERS where LASTNAME=‘Andreessen'", true);

// One next() must be called initially whenever a cursor is established
custCursor.next();

// Write some data values, firstName and address, to the screen
write("Name: " + custCursor.firstName + "<BR>");
write("Address: " + custCursor.address + "<BR>");

// Set some data values and update the row in the CUSTOMERS table
custCursor.income = custCursor.income * 10;
custCursor.description = "CTO of Netscape";
custCursor.updateRow("CUSTOMERS");

// Close the cursor and disconnect from the database
custCursor.close();
database.disconnect();

Please refer to chapter six of the LiveWire Developer's Guide for specific information on each of the objects, methods, and properties mentioned in Example 1.

The Fine Line Between Input and Output

HTML tables are commonly used to organize output from database tables or queries, because they're a natural construct for visually organizing and displaying data from relational database tables. Yet there are times when there is either too much data to display cleanly on a single screen, or you aren't quite sure what data the user wants to see. Fortunately, HTML was designed for just such situations. You can take advantage of HTML hyperlinks to give users different views of a data set depending on the data they're already viewing. In other words, part of one query's output can become input to a new query that will display a more specific view of the data set chosen.

For example, let's say we're working with a database that stores information about a company's employees and departments in two tables, Employee and Department. The Department table contains a department number (the primary key), manager, location and budget information. The Employee table contains each employee's social security number, name, address, number of dependents, salary, and department number. The table has a composite key consisting of the social security number—SSN—and the department number—deptNumber. The table is linked to the Department table through the deptNumber field, as shown in Figure1.

We've designed some simple HTML reports that output the contents of each table upon request. However, suppose a user would like to see the list of all employees in a particular department. What's the best way to create this association and allow users to perform such a look-up operation? One option would be to make an HTML form that allows ad hoc queries on each table, construct a query engine, perform error checks on all of the form input, format the output, and so on. While such a utility may be useful, it will take hours to design, build, and test. Instead, we can take advantage of the reports we already have to let our users obtain the information they want. Surely the solution that leaves us time enough for a few games of air hockey is the right one, so let's delve into modifying those HTML reports.

First, let's take a look at how one of the standard reports might be constructed from data in the Department table. The page shown in Example 2 will display information on any given department. No employee or other information has been related to department information at this point.

Example 2

<HTML>
<HEAD>
<TITLE>Department Data</TITLE>

<SERVER>
       // Connect the database COMPANYDATABASE on server MYSERVER
      database.connect("INFORMIX", "myServer", "informix", "informix", "CompanyDatabase");
</SERVER>

</HEAD>
<BODY>

<TABLE BORDER=2>
<TR>
   <TH>Department No.</TH>
   <TH>Manager</TH>
   <TH>Location</TH>
   <TH>Annual Budget</TH>
</TR>

<SERVER>
      // Establish a cursor on the Department table
      deptCursor = database.cursor("select * from DEPARTMENT");
      

      // Iterate across every record in the result set
      // and write out selected fields in a table row.
      // Note that next() returns true as long as there are more rows
      while ( deptCursor.next() )
      {
            write("<TR>");
            write("<TD>" + deptCursor.deptNumber + "</TD>");
            write("<TD>" + deptCursor.manager + "</TD>");
            write("<TD>" + deptCursor.location + "</TD>");
            write("<TD>" + deptCursor.budget + "</TD>");
            write("</TR>");
       } deptCursor.close();
</SERVER>

</TABLE>

</BODY>
</HTML>

You'll notice I didn't use the database.SQLTable method to simply print out all of the fields in the table at once. The SQLTable method has its uses in certain situations, but it's very limited and inflexible for formatting and manipulating the output. I 'll want to reformat the data displayed in this report, so I've chosen to use a more "manual" method for constructing the HTML table.

To accomplish my goal of performing a lookup, I need to find a way to allow users to use the information presented on the Departments report to find more detailed information about that department, namely its employees. Yet, all that's on my report is some HTML text that just sits there staring at the users, allowing them no interactivity. Fortunately, HTML has an excellent and way to add interaction to a Web page—hyperlinks! We can use them here to take users to a report displaying employees that work in a selected department. Figure 2 illustrates the desired interaction:

To add this interactivity to the Departments report, I 'll add a hyperlink to the deptNumber field that will jump to the report for employees. By itself, such a link will simply jump to the Employees report, showing all employees regardless of their department. I need to add code that passes some information to that report when the user clicks the link, narrowing the query to only employees in a particular department. To do so, I 'll use URL encoding to pass along the department name so the results will be limited to only those in the selected department.

URL encoding is a way of adding variable names and associated values, referred to as "name-value pairs," to the URL for the link. In general, such links look like the following:

<A HREF="/old?u=http%3A%2F%2Fdeveloper.iplanet.com%2Fviewsource%2FmyUrl.html%3Fvar1%3Dvalue1%26var2%3Dvalue2%26...&y=1999">my link</A>

In the line just shown, var1=value1 is an example of a name-value pair; each name-value pair is separated by an ampersand (&) character. On the server, this name-value data is available as properties of the request object in the destination page, in this case myUrl.html. For more detailed information on URL encoding and the request object, see chapter eight of the LiveWire Developer's Guide.

To add the data to the URL for the Department report, I'll have to modify the first line of HTML column output that above was merely printing out the department number, as shown in Example 3.

Example 3

write("<TD> <A HREF=/old?u=http%3A%2F%2Fdeveloper.iplanet.com%2Fviewsource%2F%C2%91employees.html%3Fdept%3D&y=1999" +
            escape(deptCursor.deptNumber) +
            "‘>" + deptCursor.deptNumber + "</A> </TD>");

For instance, for a department number of '37', the code in Example 3 will generate the following HTML output:

<TD> <A HREF=/old?u=http%3A%2F%2Fdeveloper.iplanet.com%2Fviewsource%2F%C2%91employees.html%3Fdept%3D37&y=1999'>37</A> </TD>

If the user clicks the link, the department number would be available to employees.html as the property request.dept with a value of "37". The code for this operation may appear a bit intimidating at first, so let's examine it line by line.

The first line of code in Example 3 writes out the HTML column tag <TD> and the first part of the URL in the anchor tag. The second line URL-encodes, or "escapes," the value to be placed in the URL as the value for dept. Encoding the value is necessary when the value passed contains spaces, punctuation or other nonalphanumeric values. Finally, the third line of Example 3 writes the department number that will appear to users as the link they can click; it then closes the anchor and column tags, completing that column of the HTML table.

We need to make one final modification, this time to the Employees report, and make use of the data passed via URL encoding to narrow the report to only employees indicated by the deptNumber parameter. Imagine that employees.html originally used a simple database.SQLTable method to display employee data, which would look like the code shown in Example 4.

Example 4

<HTML>
<HEAD><TITLE>Employee Data</TITLE>
</HEAD>
<BODY>

<SERVER>
      database.SQLTable("select * from EMPLOYEE");
</SERVER>

</BODY>
</HTML>

Altering this statement to account for the data we will be passing to it is a simple matter. With server-side JavaScript, data passed via URL-encoded parameters are available to the application in the request object. When a user clicks on a link from the departments page, employees.html will have the department number available as request.dept. We can now modify the above query with the code shown in Example 5.

Example 5

<SERVER>

var selectionString = "";

// If the value is not null, select employees from a particular
// department
if (request.dept != null)
      selectionString = " where department=" + request.dept;

database.SQLTable("select * from EMPLOYEE" + selectionString);

</SERVER>

The code in Example 5 will display a list of employees from a particular department if the department number is provided in the URL. Otherwise it will display all employees as it did before the changes were made.

The ultimate result of the code changes we've made to the departments and employees pages will be an application that displays data on departments where every department number will be a link to a list of employees in that department. When the user clicks a department number, the application presents a list of employees for that department and only that department. If no department number is specified in the URL, all employees will be listed. With little more than a half dozen new lines of code, we've transformed simple HTML reports into dynamic, hyperlinked documents that perform database queries and display the results on the fly.

The Sorted Details

The example above shows a useful way of connecting data and HTML links for the purposes of displaying data so that users can control how they view data. The example can be taken a bit further to let users sort the result sets by any column simply by clicking on the column name at the top of the HTML table. The code in Example 6 shows how you'd begin to modify the code from Example 2 to implement this "sort by column name" feature. (Modifications to the code shown in Example 2 are indicated in boldface.)

Example 6

<TABLE BORDER=2>
     <TR>
        <TH><A HREF="/old?u=http%3A%2F%2Fdeveloper.iplanet.com%2Fviewsource%2Fdepartment.html%3Forderby%3DDEPTNUMBER&y=1999">Department No.</A></TH>         
        <TH><A HREF="/old?u=http%3A%2F%2Fdeveloper.iplanet.com%2Fviewsource%2Fdepartment.html%3Forderby%3DMANAGER&y=1999">Manager</A></TH>
        <TH><A HREF="/old?u=http%3A%2F%2Fdeveloper.iplanet.com%2Fviewsource%2Fdepartment.html%3Forderby%3DLOCATION&y=1999">Location</A></TH>
        <TH><A HREF="/old?u=http%3A%2F%2Fdeveloper.iplanet.com%2Fviewsource%2Fdepartment.html%3Forderby%3DBUDGET&y=1999">Annual Budget</A></TH>      
     </TR>

     <SERVER>
           var orderString = "";

           // If the parameter passed in via URL encoding is not null
           // add an "order by" clause for the field indicated
           //
           if (request.orderby != null)
                 orderString = "order by " + request.orderby;

           // Establish a cursor on the Department table with an ordered result set
           deptCursor = database.cursor("select * from DEPARTMENT " + orderString);

Note: Boldface used to indicate code changes from Example 2.

Now, with the additional code displayed in Example 6, whenever a user clicks on a column name, the same page—department.html—will be requested again; this time, it will include a parameter included in the URL that tells the server to construct the cursor and order the result set by the column selected. It's important to note that the value of the orderby parameter in Example 6 contains a string that exactly matches the column name as it's defined in the Department database. For the case presented, the URL parameter and corresponding column name must match exactly, since it is used directly in the SQL "order by" statement, or the query will not work properly.

"Drilling Down" For Useful Views of Data Sets

In addition to the modifications illustrated above, we could add even more useful features to our application. We could limit the number of fields displayed from the Employee table so that the user doesn't have to scroll to see every data field for a particular employee's data. We could make the application show only three or four columns of information on the initial page, such as social security number, name, and supervisor. We could then create a dynamic link on each employee's name that, when it's clicked, would take the user to a detailed report containing data from all the fields for a single employee. This "drill-down" technique is very useful for large data sets and data sets with large numbers of fields.

As I've shown, there are many ways in which URL-encoding and hyperlinks can help turn an application that had little or no interactivity into one that gives users a great deal of control over what data they'll see, and how it will be presented to them. Using hyperlinks to turn output into input also reduces development time, and therefore reduces project costs. These techniques show ways to create interesting and interactive presentations of database information. In part two, I'll discuss different ways to insert and update information with server-side JavaScript and HTML forms, and I'll show how forms themselves can be constructed based upon data pulled from a database.

(Special thanks to Paul Dreyfus and Mike Plumley for their help in putting together this article. Technical reviewers for this article were Netscape engineers Rich Yaker and Rand McKinney.)

FURTHER READING

Live Wire Data Sheet
The Live Wire Developers Guide
Technical Notes for Live Wire 1.x
Technical Notes for Live Wire Pro 1.x
Beyond Data Basics: Writing JavaScript Database Applications
JavaScript on the Server

DEVEDGE MEMBERS RESOURCES

DevEdge LiveWire Newsgroup
DevEdge LiveWire FAQ
An overview of Netscape ONE Technologies

BOOKS FROM AMAZON.COM

LiveWire books

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.

(2.97)

---

Related Readings:

• Frequently Asked Questions: Find the answers to your questions on Internet technologies.
• Documentation: A library of technical information.
• Developer Central: Collections of developer resources organized by Internet technology areas.

Any sample code included above is provided for your use on an "AS IS" basis, under the Netscape License Agreement - Terms of Use