LiveWire Web-to-Database Solutions, Part 1

William is the executive director of Global Internet Solutions and author of several books, including Web Publishing Unleashed, FrontPage 97 Unleashed, Peter Norton's Guide to Java Programming, and Netscape ONE Developer's Guide. He can be contacted at [email protected].

Enabling advanced server-side solutions is what LiveWire is all about, and if you need a complete web-to-database connectivity and management solution, LiveWire may be just what you are looking for. LiveWire is an all-in-one web site management and application development tool suite from Netscape.

Sure, the LiveWire tool suite with its WYSIWYG web-site manager, web-page editor, and application manager is great for creating and managing web sites and intranets, but as a database developer, you want to know how you can use LiveWire for web-to-database solutions. This is where the LiveWire server extensions come into the picture. Using the server extensions, you can build powerful database management applications that run within the context of a web browser.

Introducing LiveWire

LiveWire is designed to work with Netscape clients, Netscape servers, and ODBC-compliant databases. Your Netscape client can be any version of the Netscape Navigator browser, including Netscape Navigator 3.0 or Netscape Communicator. Netscape recommends that you use LiveWire with its SuiteSpot servers. Still, as long as you have version 2.0 or higher of any Netscape web server, including FastTrack and Enterprise, you can run LiveWire applications.

Currently, LiveWire is available in two editions: standard and professional. The key difference between them is that the professional edition adds the Informix SQL server and Crystal Reports. If you plan on purchasing Netscape SuiteSpot, you don't need to purchase LiveWire separately. The LiveWire professional edition is included in the SuiteSpot package.

Ideally, you will install LiveWire on a development machine and deploy your finished and tested LiveWire applications to your web server. Because versions of LiveWire are available for UNIX, Windows 95/NT, and Macintosh platforms, your development machine can be just about any workstation. However, keep in mind that to run applications after you build them, the development machine must be running a Netscape server process. If the development machine isn't running Netscape HTTP, you must deploy the application to your web server for testing and evaluation.

Working With LiveWire

When you create applications to access your databases, you will rely heavily on the LiveWire Site Manager. Site Manager is the main interface to everything LiveWire has to offer. You will use Site Manager to generate web sites based on templates, to manage your web site or intranet, and to compile your LiveWire applications.

LiveWire applications combine HTML and server-side JavaScript objects. The server-side JavaScript objects provide an extensive framework for working with databases, accessing files, and tracking the state of clients, applications, and servers.

After you enter all the necessary HTML markup and JavaScript statements, you use the build feature of Site Manager to create server-ready files for the application. Your application's server-ready files are in an intermediate format that the server parses before passing the data to a client. This parsed data is in the form of standard HTML and client-side JavaScript statements. (For more information, see the accompanying textbox entitled "How Does JavaScript Fit Into the Picture?")

Once you create server-ready files for an application, you will use the Application Manager to install, configure, and run the application. The Application Manager also features access control and debugging mechanisms. After you build an application, you must add it to the manager, then you must start the application, which tells the server the application is available to users.

Creating LiveWire Applications

All applications you create in LiveWire leverage the simplicity of HTML, the versatility of JavaScript, and the power of the web's distributed environment. To create a LiveWire application, you start with source files that are displayed in a web browser as web pages. The hypertext linking and redirection capabilities of the web mean that LiveWire applications can have any number of source files associated with them. It is the structure of these files that defines the interface for your application.

LiveWire source files can be written in HTML, JavaScript, or a combination of HTML and JavaScript. When you combine HTML and server-side JavaScript, you must identify the server-side source code with the <SERVER> tag (see Example 1). All statements placed between the begin and end <SERVER> tags are preparsed into a server-ready format at compile time, which is when you build the application in the Site Manager.

Although LiveWire applications are preparsed, your web server will always parse the data again before sending it to a client. This final parsing allows you to run LiveWire applications on any operating system that has a JavaScript-compliant browser (including Macintosh, Windows 95/NT, and UNIX).

As stated earlier, LiveWire is designed to be used with Netscape clients. Still, if you've written scripts in JavaScript, you know that Microsoft's Internet Explorer supports a version of JavaScript called JScript. JScript and JavaScript are not 100-percent compatible, which means that some LiveWire applications will not work properly in Internet Explorer.

Keep in mind that Microsoft and Netscape are discussing a standardized version of JavaScript that will bring the features of JScript and JavaScript into a single mainstream specification.

The LiveWire Object Framework

When you install LiveWire, the server extensions and server-side object framework are installed on your web server. The server extensions are responsible for translating your server-ready files that contain LiveWire object references into usable content.

The LiveWire object framework consists of the server-side objects listed in Table 1. The most important object for database transactions and management is the database object.

The database Object

LiveWire is the intermediary between client browsers, your web server, and the database server. When a client browser initiates a database transaction, your LiveWire application (which is running on the web server) receives the request, processes it into a form acceptable to the database server, then makes a request to the database server. After processing the request, the database server returns a response to the LiveWire application and, in turn, the application formats the response and sends it to the client.

The object that makes this interaction possible is the database object. As with most JavaScript objects, the database object has methods associated with it. Methods used to connect to a database server and report errors are shown in Table 2. Methods used to handle queries, transactions, and cursors are listed in Table 3.

LiveWire uses the methods of the database object to create generic instructions that can be passed to any ODBC-compliant database. Before the instructions are passed to the database server, LiveWire translates them into a series of SQL statements.

Data Type Conversion

Although most databases support many different data types, JavaScript and LiveWire support only a limited number of data types. As a result, LiveWire must convert incoming data from your database to data types supported by JavaScript, and must convert outgoing data to data types supported by your database server.

Table 4 summarizes the data types supported by LiveWire. This table is a matrix that shows how data types are converted. As you can see, LiveWire converts raw data, binary data or byte data to JavaScript Binary Large Objects (BLObs). BLObs are used to store and retrieve multimedia files such as JPEG images, MPEG video files, or AIFF sound files.

LiveWire converts all strings to JavaScript strings. For most databases, this means any fields containing characters, character strings, or text entries are converted to JavaScript strings.

Similarly, LiveWire converts all date and time entries to JavaScript dates. Because the format of a date or time entry in LiveWire may not be exactly as it was on the database server, you should experiment with the formatting before you try to extract information from date strings.

LiveWire converts all numeric values to JavaScript numbers. In JavaScript, floating point numbers are stored as double-precision floating point values. Double-precision floating point values offer 64-bit precision. JavaScript does not support packed decimal notation, which may cause some loss of precision during type conversion. If your database uses packed decimal notation, be sure to check the results of computations before reinserting values into the database. You may need to build a function to correct the precision problem.

Connecting to the Database

Establishing a connection to the database server is an essential step before any application can manipulate a database. Before you make a connection to a database, you should:

• Examine the level of security and control necessary to protect the integrity of the database.
• Consider the types of tasks users will be performing with the application.

Armed with the security level of the database and a keen understanding of what the application will be used for, you can determine whether the application should establish the database connection automatically based on default settings or rely on the user to initiate the connection. For example, you may want to automatically establish the connection if the application itself is password protected for individual users and users can only perform database queries. On the other hand, if users are allowed to update or delete database records, you may want them to fill in the proper database settings, which would include a user name and password for authentication.

To establish a database connection, you will use the database.connect() method. Example 2 is the general syntax for this method, where dbtype is the type of database server, servername is the name of the database server, username is a user ID for the database, password is a user password for the database, and dbname is the name of a database on the server.

The database type parameter is always INFORMIX, SYBASE, ORACLE, or ODBC. If your database server is from a specific vendor, you should use the specific vendor's name in the first parameter. Otherwise, you should use the generic database identifier, ODBC.

The database name parameter is used to identify the database server process to which you want to connect. This name is usually assigned when the database server is installed. For a generic ODBC database, you will want to use the name of the database service.

The user name and password parameters should match those of a valid user within the database. On some relational database management systems, the database user name must match the login name for the operating system. If your database does not require a password, use an empty string ("").

For databases that support multiple databases on a single server, such as Sybase, you must set the server name parameter. You will use the named database for all transactions during a specific connection. To use a different database after you have already established a connection, you should close the current connection, then establish a new connection. If your database server doesn't support multiple databases, use an empty string ("").

Before connecting to a database, you should always lock the project object. Locking the project object ensures that multiple clients using the application won't try to establish a connection to the database at the same time. Once the connection is established, you should unlock the project object, which allows other clients to make a connection. Example 3 illustrates locking and unlocking.

LiveWire translates the database.connect() method parameters into a series of SQL statements, then passes them to the database server. For the Sybase server in Example 3, this means that the server would receive commands similar to the following:

isql -UADMIN -PSYSADMIN
use personnel_db
go

Using HTML and LiveWire, you can build a web-based front end for connecting to a database. To do this, you can create a web page with an HTML form that allows users to enter settings for the database connection. Although a simple form, such as Listing One would do the job, your goal should be to make the application easy to use. Figure 1 shows this example in a browser.

Listing Two presents a better way to create the database connection page. When this web page is loaded into a browser for the first time, default values are assigned to properties of the client object. The form elements used in this page change the default values to values you enter. From then on, when the page is loaded, the fields show the current value of their related client property. This example is shown in Figure 2.

When the form is submitted, the user's browser retrieves a page called "dbconnect.html." As depicted in Example 4, this page uses the values passed in the form to make the connection to the database.

To verify that a connection has been established, you will use the connected() method. If the connected() method returns True, the connection was established successfully. Otherwise, a connection was not established and it is likely that the server will report an error.

When an error occurs, you will want to redirect the user's browser to a page within the application that handles the error. In Example 5, browsers are redirected to a page called dberror.html when an error occurs.

Putting the actual dbconnect.html page together for the application requires a few extra steps and a strong understanding of how LiveWire works. The key LiveWire concept is that all input fields of a form are translated into similarly named properties of a request object when a form is submitted. This means that when the form shown in Listing Two is submitted, a request object is created with five properties:

• request.dbtype.
• request.server.
• request.id.
• request.password.
• request.database.

By assigning these properties to like-named properties of a client object, you can maintain the values across multiple requests from a particular client. Because the values are maintained, the next time the form is loaded, the current values of the properties can be displayed as the default text for the input fields (see Figure 3).

Listing Three presents the source for dbconnect.html. As you examine this listing, note how the database connection is established, as well as what happens when errors occur. By assigning the error codes and messages to properties of the client object, you can maintain the errors, then display them on an error page.

The error page is reached by redirecting the user's browser to a new page; in this case, dberror.html. If no error occurs, the user's browser is redirected to the start page for the application, which is called dbhome.html.

Coming Up

As you can see, LiveWire includes powerful tools and frameworks for programming server-side functions, especially when it comes to database connectivity and management. After reviewing LiveWire basics, we jumped into some pretty advanced material and we're well on our way to creating a complete web-based database management application. In my next column, I'll continue to develop this application as I examine how to handle database queries, updates, and more with LiveWire.

DDJ

Listing One

<HTML>
<HEAD>
<TITLE> Enter Settings for the Database Connection </TITLE>
</HEAD>

<H2 ALIGN=CENTER>Enter Settings for the Database Connection</H2>

<FORM METHOD="post" ACTION="dbconnect.html">
<INPUT TYPE="text" NAME="client.settings" SIZE="60">
<INPUT TYPE="submit" VALUE="Establish Connection">
</FORM>

</BODY>
</HTML>

Back to Article

Listing Two

<HTML>
<HEAD>
<TITLE> Enter Settings for the Database Connection </TITLE>
</HEAD>

<BODY BGCOLOR="#FFFFFF">

<SERVER>
if(client.server == null)
  client.server = "server_name_null";
if(client.id == null)
   client.id = "user_id_null";
if(client.password == null)
   client.password = "password_null";
if(client.database == null)
   client.database = "database_null";
</SERVER>

<DIV ALIGN=CENTER>
<H3>Enter Settings for the Database Connection </H3>

<FORM METHOD="post" ACTION="dbconnect.html">

<TABLE BORDER=0>
<TR>
<TH ALIGN=LEFT>Database Type:
<TD><SELECT NAME="dbtype" SIZE="1">
<OPTION `(client.dbtype == "ORACLE")   ? "selected" : ""`> ORACLE
<OPTION `(client.dbtype == "SYBASE") ? "selected" : ""`> SYBASE
</SELECT>
</TR>
<TR>
<TH ALIGN=LEFT>Server Name:
<TD><SELECT NAME="server" SIZE="1">
<OPTION `(client.server == "twiggie") ? "selected" : ""`> twiggie
<OPTION `(client.server == "pixi")   ? "selected" : ""`> pixi
</SELECT>
</TR>
<TR>
<TH ALIGN=LEFT>User Name:
<TD><INPUT TYPE="text" NAME="id" VALUE=`client.id` SIZE="20">
</TR>
<TR>
<TH ALIGN=LEFT>User Password:
<TD><INPUT TYPE="password" NAME="password" VALUE=`client.password` SIZE="20">
</TR>
<TR>
<TH ALIGN=LEFT>Database Name:
<TD><SELECT NAME="database" SIZE="1">
<OPTION `(client.database == "personnel_db") ? "selected" : ""`> personnel_db
<OPTION `(client.database == "finance_db")   ? "selected" : ""`> finance_db
<OPTION `(client.database == "hr_db") ? "selected" : ""`> hr_db
<OPTION `(client.database == "records_db") ? "selected" : ""`> records_db
</SELECT>
</TR>
</TABLE>

<INPUT TYPE="submit" VALUE="Establish Connection Using These Settings">
</FORM>

</DIV>
</BODY>
</HTML>

Back to Article

Listing Three

<HTML>
<HEAD>
<TITLE>Connecting to Database Server . . . </TITLE>
</HEAD>

<BODY BGCOLOR="#FFFFFF">

<SERVER>

client.dbtype = request.dbtype;
client.server = request.server;
client.id = request.id;
client.password = request.password;
client.database = request.database;

project.lock();
database.connect(client.dbtype,
                 client.server,
                 client.id,
                 client.password,
                 client.database)

if(!database.connected())
{
   client.majorCode = database.majorErrorCode();
   client.majorMsg = database.majorErrorMessage();
   client.minorCode = database.minorErrorCode();
   client.minorMsg = database.minorErrorMessage();

  redirect("dberror.html");
}

</SERVER>

<H1 ALIGN=CENTER>Database Connection Established</H1>

<SERVER>
redirect("dbhome.html")
</SERVER>

</BODY>
</HTML>

Back to Article