Building Web Database Applications Using Novell Script for NetWare

Articles and Tips: article

BRAD NICHOLES
Senior Software Engineer
Novell Platform Group
Novonyx Engineering

01 Aug 1999

Describes three scripting technologies Novell Script for NetWare (NSN), Novell Script Pages (NSP), and the NetWare Data Object (NDO) and explains how you can use them to build dynamic Web pages that are connected to a database.

Introduction
Novell Script for NetWare
Novell ScriptPages
NetWare Data Object
Database Web Applications
Real-World Database Application
Built-in Debugger
Conclusion

Introduction

With thousands of new users logging on every day, there is no denying that the Internet has gained popularity beyond anyone's imagination. No other medium comes close to providing instant access to such a large audience. As a result, people are using the Internet as a means to communicate, sell products, and share data. The Internet has become an easy and convenient way to locate information about everything from local movie listings to new car prices to searching for intelligent alien life forms.

Where does all of this information come from? Primarily from years of study and research by millions of people all over the world. As the information is gathered, it has to be stored on a computer file system in a data store, which can be anything from a simple text file to a full-blown relational database. But just gathering and storing the information is not enough. Once you have the information, how do you put it on the Internet to make it available to the millions of interested people throughout the world? How are the millions of Internet Web servers going to connect to the various data stores, extract the data, and present it in a usable manner? The answer lies in scripting. (A script is a list of commands that can be executed automatically without user interaction. Novell Script for NetWare can interact with HTML code to help you produce dynamic Web pages.)

This article describes three scripting technologies Novell Script for NetWare (NSN), Novell Script Pages (NSP), and the NetWare Data Object (NDO) and explains how you can use them to build dynamic Web pages that are connected to a database.

Novell Script for NetWare

NSN is a new scripting tool from Novell. It is a follow-on product to NetBasic v6.0, which Novell purchased along with NetBasic v7.0 from HiTecSoft Inc. in 1998. Though both NetBasic v6.0 and v7.0 are forms of the BASIC language, their syntax is dramatically different. Also, unlike v6.0, NetBasic v7.0 is compatible with VBScript. For this reason, Novell split v7.0 off as its own product and changed the name to Novell Script for NetWare.

In its early years, Visual Basic (VB) was viewed as a simple language that could teach beginning programmers and non-programmers how to write simple Windows applications. Since then, it has been used increasingly in the professional development community to rapidly create and deploy powerful applications. A subset of Visual Basic, VBScript, is also used to create macros and to extend the functionality of Microsoft Office applications. Additionally, VBScript is being used as a Web scripting language.

Microsoft first introduced VBScript as a server-side scripting language when they announced their Active Server Pages (ASP) technology. ASP provides the Web developer with the ability to embed VBScript directly into HTML pages. This allows both HTML and VBScript to do what each does best. HTML displays static content while VBScript processes data, makes decisions, and presents dynamic content. Combining HTML with script code allows the Web developer to create powerful dynamic Web applications. Now, with the introduction of NSN, the NetWare platform can also take advantage of this powerful and exciting new technology.

Like VBScript, NSN is not just a Web scripting technology you can also use it to extend the functionality of NetWare itself. NSN includes a set of UCX components that can be used in scripts to access various NetWare services such as NDS. This helps in automating tasks on NetWare by writing NSN scripts. For example, scripts can perform routine tasks such as adding a large number of user accounts to NDS or searching the file system at set intervals for specific files.

NSN scripts can be invoked in various ways. Scripts can be invoked through the powerful NSN Shell, which runs on NetWare console. The commands such as "Dir," "CD," "Copy," or "Del," which can be executed from the shell, are themselves NSN scripts. Scripts can also be invoked directly from the server console or they can be invoked through a request from a browser. This article will focus on browser requests and explains how you can use NSN to create dynamic Web applications.

Novell ScriptPages

As mentioned earlier, script code and HTML can be combined to create powerful Web applications. Novell Script Pages is the NetWare technology that allows Web developers to do just that. The following table describes the five NSP objects that let you take advantage of Web server services from within a script.

Object	Description
Application	Stores shared information that can be consumed by all users of a given application.
Request	Retrieves the values that the browser passed to the server during an HTTP request.
Response	Sends output to the browser.
Server	Allows access to the Web server services and attributes.
Session	Stores information about a particular user-session.

Using these objects, the Web developer can do the following:

Store and retrieve information about the Web application
Store and retrieve information about a specific user session
Retrieve data sent by the browser to the Web server
Access server variables generated by the Web server
Send data back to the browser
Redirect the browser to a different location
Access information about the Web server
Access information about the browser
Control caching and page expiration
Send, receive, and manage cookies

Using a Web authoring tool makes developing NSP applications easy. You can develop NSP pages using any standard HTML authoring tool. The NSP research development for this article was done using Microsoft FrontPage 98. An authoring tool allows you to quickly and easily build the static content of a page, then insert the script tags for your script code. NSP supports two methods for embedding script code into your HTML code: <SCRIPT> tags and <%> tags.

<SCRIPT> Tags

The first method is through the standard <SCRIPT>...</SCRIPT> tags. The following example shows embedded script code using these tags.

<script language="NScript">

response.write "Hello World"

</script>

Because NSN can only be run on the server, there is no need to use the RUNAT keyword to indicate whether the script should be run on the server or the client unless you are also mixing in client-side JavaScript or VBScript. If you were to embed client-side JavaScript or VBScript along with NSN, the RUNAT tag would be required to tell the ScriptPages parser to ignore the JavaScript or VBScript and send it on to the client as is. The client-side code can then be executed by the browser just as if it were not part of an NSP page.

<%> Tags

The second method for embedding script code into HTML is by surrounding the code with the <%...%> tags. These tags also tell the ScriptPages parser that everything between them should be treated as script rather than HTML. Unlike the <SCRIPT> tag, the <%> tag assumes that the script code is server-side Novell Script. These tags effectively evaluate expressions and send the results to the client browser. The following example shows embedded script code using these tags.

<head>
<title>Report List for <%=FirstName & " " & LastName%>&nbsp; - Novell Expense 
Reporter</title>
</head>

In this example, the contents of the variables "FirstName" and "LastName" are concatenated together and sent to the browser to be displayed in the title bar. You can also use the "%" tags to perform a quick calculation and send the result back to the browser or store it in a server variable to be used later.

Figure 1: NSP Object Model This diagram sows the objects that make up Novell Script Pages.

NetWare Data Object

The NDO lets you connect script code to a database by providing a common object-oriented interface into a variety of database engines. Underneath, NDO provides native connections to Oracle 8, Oracle 7, and SQL Integrator, as well as a connection to any ODBC-compliant database that is running on NetWare or a remote Windows machine. The NDO interface is comprised of the following objects.

Object	Description
Connection	Represents an open connection to a data source.
Command	Defines a specific command that will be execute against a data source.
Recordset	Represents the entire set of records from a base table or the results of an SQL request.
Parameters	Collection that contains all the Parameter objects of a Command object.
Parameter	Represents a parameter or argument associated with a Command object based on a parameterized query or stored procedure.
Fields	Collection that contains all the Field objects of a Recordset object.
Field	Represents a column of data with a common data type.

Using these objects, the Web developer can do the following:

Connect to a database
Execute standard SQL statements
Execute parameterized SQL statements
Retrieve row data
Retrieve attributes about the database engine
Retrieve attributes about the database schema

Because NDO presents a common object-oriented interface to all of these database engines, you can write your script code once, then run it against different databases by simply changing the connection string. Also, NDO is implemented as a Universal Component Extension (UCX) component, which means it is invoked through the Universal Component System (UCS). The UCS technology allows components like NDO to be consumed not only by NSN, but by other scripting languages like Perl and JavaScript, as well. This means that the same object-oriented interface that connects to a database from NSN is also exposed to Perl and JavaScript.

Figure 2: Universal Component System The UCS technology allows components like NDO to be consumed not only by NSN, but by other scripting languages such as PERL and JavaScript as well. This means that the same object oriented interface that connects to a database from NSN is also exposed to PERL and JavaScript.

If you are a multi-lingual programmer, or if you are more comfortable using Perl or JavaScript, you no longer need to deal with different database interfaces just because you are using a different scripting language. You can use the same database interface regardless of which scripting language you choose.

Figure 3: NDO Object Model This diagram shows the objects that comprise the NetWare Data Object.

Database Web Applications

Where does the database live? While different architectures answer this question different ways, the architecture that we are interested in for a Web-based application is three-tiered. A three-tiered architecture consists of three different parts: the User interface, the Business logic, and the Database.

Figure 4: Three-tiered Architecture This architecture consists of three different parts: the User Interface, the Business Logic, and the Database.

All three parts could reside on the same physical machine, but in the real world, they typically live on separate computers in a networked environment. By separating these elements into three parts, maintenance can be done at any level without affecting the others. For example, the user could upgrade or change browsers without the business logic or the database's knowledge. A new feature could be added to the application through the business logic without requiring the user interface or the database to change. This type of architecture relieves a lot of the update and maintenance headaches that are common in the client-server or single-user architectures.

User Interface

The user interface is responsible both for presenting the information to the user and taking input from the user and passing it to the business logic. The user interface can consist of all of the elements that an Internet browser is capable of presenting, such as banners, graphics, text, entry fields, check boxes, and radio buttons

In a Web environment the user interface application is usually an Internet browser that communicates with the business logic through the HTTP protocol and a Web server. Using a browser as the user interface not only makes the interaction between the user and the application simple, it also allows the user to access the application from any client machine that is connected to the Internet. There is no need to install special client application software in order to take full advantage of the Web-based application.

Business Logic

The business logic is the brain of the Web application itself. It is the part of the application that makes all of the decisions. It is where NSN and NSP live. The Web application can be built by using the technologies introduced earlier. By taking advantage of Novell Script Pages, you can design your application using any standard Web-authoring tool, then embed NSN code into your HTML page to make decisions and provide dynamic content. This brings your application to life. No longer will your Web pages look and act the same for every user who accesses them. Now they change according to the criteria that you specify in your script code. The user now sees information that pertains specifically to him rather than information that is generalized for anyone. Also, through NSN and NDO, your application can connect to a database and provide the third tier in the three-tiered architecture: the database.

Database

The third tier, or the database, is where all of your dynamic content resides. It provides your users with live data that can change each time it is viewed. It also provides a data store where additional data can be saved and used later.

Real-World Database Web Application

So how can all of these technologies be brought together in a three-tiered architecture to produce a real-world application? Let's take a look at a simple Web based expense reporting application. This application is called the Novell Expense Reporter and was implemented using Microsoft FrontPage 98. It takes advantage of Novell Script for NetWare, Novell ScriptPages, the NetWare Data Object, and the Netscape Enterprise Server running on NetWare 5. The database can be interchangeable between Oracle 8, SQL Integrator, or Microsoft Access running on a Windows NT machine.

When the user first accesses the Novell Expense Reporter application, he simply enters a URL from a Web browser. The first page in the application asks him to identify himself by supplying a user name and password, which are authenticated through NDS.

Figure 5: Novell Expense Reporter Login Page?The information entered on the login page is authenticated through NDS before the user is allowed to continue.

Once the user has been identified, a query is submitted to the database to retrieve the user's personal information. If the information is not found, the user is determined to be a new user and the browser is redirected to a new user page. On this page, the user is prompted to supply additional information such as his cost code, department, and currency type. After this information has been submitted and stored in the database, the browser is again redirected to show the expense report list page. If the user information had already existed in the database, the browser would have been sent directly to the expense report list page and all of the existing expense reports for the user would have been displayed.

Figure 6: Expense Report List Page?Shows a list of all of the expense reports that belong to a specific user. The expense report data is retrieved from the database as the page is created.

Expense Report List Page#151;Shows a list of all of the expense reports that belong to a specific user. The expense report data is retrieved from the database as the page is created.

This page is made up of both static content and dynamic content. The static content includes the Novell banners and graphics, buttons and the column headers. The dynamic content is the data that is displayed in each row of the table. This data was retrieved from a database and formatted by NSN before the page was sent to the browser. All of the script code for this page was embedded directly into the HTML code, then executed through NSP on the server before the page was sent to the browser. If the user were to view the source for this page from the client, all he would see is the raw HTML code. All of the script code was executed at the server and only the resulting HTML was sent to the browser.

Figure 7: Embedded Novell Script Code?The FrontPage 98 HTML view of the report list page shows embedded Novell Script which makes a connection to the database and queries it for information.

Let's take a little closer look at how this page was built. First, all of the static content for the page was laid out visually in FrontPage. <SCRIPT>...</SCRIPT> tags were then inserted into the page by selecting INSERT->ADVANCED->SCRIPT... from the FrontPage editor menu. Finally all of the script was embedded into the HTML code. The script can be embedded either through the Script window or it can be inserted directly by selecting the HTML view in the editor.

Figure 8: Script Window?Using FrontPage 98, script code can be inserted into the HTML by using the script window.

Proceeding through the script code that produced the dynamic content inside the table, it first creates an NDODB.CONNECTION object by calling the CreateObject() function.

set CN = CreateObject ("UCX:NDODB.CONNECTION")
CN.Open ("DBTYPE=1;DATABASE=ExpenseRpt, '', '' ")

This returns a reference to the Connection object that will be used to query the database. A query is defined that selects the expense report data for the current user. The query is sent to the database by calling the Execute() method of the Connection object.

SelQuery = "Select    empid,reportnum,datecreated,periodending,purpose,foreigncurrency,costcenter,
   totalamount,currencytype,submitted,paid from ExpReports where EmpID = ' " &
   EmployeeID & " 'Order by DateCreated Desc"

set RS = CN.Execute (SelQuery, lRec)

The Execute() method returns a Recordset object that contains all of the data which resulted from the query. The next statement checks the Err object to make sure that no errors happened during the execution of the query. A "Do While" loop is entered so that each row of data contained in the Recordset can be formatted and sent to the browser.

do while not RS.EOF
   set FLDS = RS.Fields
   
   response.write '<tr>'
   ReportNumber = FLDS.Item(2).value

The Fields collection of the Recordset is extracted by accessing the Fields property and then used throughout the rest of the code to extract the values for each field. Because each expense report is made up of several receipts, a second query is sent to the database that calculates the sum of all of the receipts for each expense report.

SelTotal = "select sum(totalamount) from receipts where reportnum = ' " & 
   ReportNumber & " ' "
   
set RSTotal = CN.Execute (SelTotal, lRec)
if not RSTotal.eof then
   Total = RSTotal.Fields.Item(1).Value
else
   Total = 0.00
endif
response.write '<td width="8%">$' & CCur(Total) & ' </td>'
RSTotal.Close

The query returns another Recordset object and the total of all of the receipts for the expense report is extracted and sent to the browser. Other decisions are made and output is sent to the browser based on different database values. The Recordset is finally checked for an End of File condition and once all of the rows in the Recordset have been processed, the code exits the "Do While" loop.

RS.MoveNext
   loop
    
   RS.Close
endif

CN.Close

Finally, the connection needs to be cleaned up. The Close() method of the connection object is called and NSN releases all of the objects. All this happens at the time the Web page is requested from the browser. The HTML code is sent to the browser as is and only the HTML results of the executed script are sent.

?
set CN = CreateObject ("UCX:NDODB.CONNECTION")
CN.Open ("DBTYPE=1;DATABASE=ExpenseRpt, '', '' ")

?

SelQuery = "Select    empid,reportnum,datecreated,periodending,purpose,foreigncurrency,costcenter,
totalamount,currency   type,submitted,paid from ExpReports where EmpID = ' " & EmployeeID & " ' 
Order by DateCreated    Desc"

set RS = CN.Execute (SelQuery, lRec)

if err.number = 0 then
   do while not RS.EOF
      set FLDS = RS.Fields

      response.write '<tr>'
      ReportNumber = FLDS.Item(2).value

      response.write '<td width="3%"><a href="http://' & LocalIP &
          '/sp/ScriptPages/expenserpt/reportdetails.htm?ReportNumber=' & ReportNumber &
'          ">*</a></td>'
      response.write '<td width="10%">' & FLD.value & '  </td>'
      response.write '<td width="13%">' & formatdatetime(cdate(FLDS.Item(3).value),
vblongdate) &          ' </td>'
      response.write '<td width="13%">' & formatdatetime(cdate(FLDS.Item(4).value),
vbshortdate) &          ' </td>'
response.write '<td width="47%">' & FLDS.Item(5).value & ' </td>'

      SelTotal = "select sum(totalamount) from receipts where reportnum = ' " &
ReportNumber & " ' "

      set RSTotal = CN.Execute (SelTotal, lRec)
      if not RSTotal.eof then
         Total = RSTotal.Fields.Item(1).Value
      else
         Total = 0.00
      endif
      response.write '<td width="8%">$' & CCur(Total) & ' </td>'
      RSTotal.Close

      if FLDS.Item(10).Value = 0 then
         Submitted = "No"
      else
         Submitted = "Yes"
      endif
      response.write '<td width="8%">' & Submitted & ' </td>'

      if FLDS.Item(11).Value = 0 then
         Paid = "No"
      else
         Paid = "Yes"
      endif
      response.write '<td width="5%">' & Paid & ' </td>'
      response.write '</tr>'

      RS.MoveNext
   loop

   RS.Close
endif

CN.Close

You can run the Novell Expense Reporter application from any client machine that is connected to the Internet. It not only allows a user to view his expense reports, it also lets him add new information or update the existing information. The beauty of this type of application is that the user does not have to rely on special software being installed on the client machine. He could relax in a hotel room and add all of his expenses as they occur at the end of each day. When he returns to the office, his expense report is already complete and all that is left to do is submit it to accounting.

Built-in Debugger

Another convenience of Novell Script for NetWare is the built-in debugger. Most developers make mistakes along the way that causes the program to function improperly. This is where debuggers come in handy. The debugger lets you step through each line of code and examine variables to make sure the code is functioning properly. The debugger is invoked by inserting the statement "Debug.On" anywhere in the script code. It can even be invoked from within the HTML code by surrounding the statement with the <%...%> tags.

Once the debugger has been started, execution of the script is stopped and a window appears on the NetWare console. The contents of the console window shows the current execution line being highlighted and allows you to scroll through the entire script. By pressing the F2 and F3 keys, you can view all of the local or global variables along with their values. To exit the debugger, insert the statement "Debug.Off." Inserting these statements at various points within the script code provides break points so the code and variables can be examined.

Figure 9: Novell Script Debugger?The debugger lets you step through each line of code and examine variables to make sure the code is functioning properly.

Conclusion

Scripting has become a popular means by which Web developers produce dynamic Web pages. The script code in these dynamic Web pages can be executed either by the client or the server. Typically, code that is executed on the client simply enhances the user interface; while code that is executed on the server defines the application. Novell Script for NetWare, Novell Script Pages, and the NetWare Data Object give Web developers on the NetWare platform the ability to create powerful Web applications.

The three-tiered architecture that separates the user interface, business logic, and database allows for easier maintenance and upgrades without the headaches. Because Novell Script for NetWare is 100 percent VBScript-compatible, developers who are already familiar with it will have no learning curve to develop and deploy applications on the NetWare platform. NSN also allows many existing Web-based applications to be moved to NetWare to take advantage of the scalability and performance with little or no porting effort.

Through these technologies, gigabytes of information that is currently stored in databases all over the world can be exposed to the Internet. Companies can provide information about themselves and customers can evaluate and purchase their products without leaving their homes. Employees can conduct business and stay in contact while away from the office. While the Internet is providing a means through which all of this can be accomplished, Web developers who take advantage of state-of-the-art technologies like NSN, NSP, and NDO provide the way. They are the ones creating the powerful applications that the entire world is using to share information.

The best part is that NSN, NSP, and NDO are all available now and are ready to be used. You can download them for free from the Novell DeveloperNet Website at http://developer.novell.com.

* Originally published in Novell AppNotes

Disclaimer

The origin of this information may be internal or external to Novell. While Novell makes all reasonable efforts to verify this information, Novell does not make explicit or implied claims to its validity.