Platinum Edition Using HTML 4, XML, and Java 1.2
(Publisher: Macmillan Computer Publishing)
Author(s): Eric Ladd
ISBN: 078971759x
Publication Date: 11/01/98

CHAPTER 29
Custom Database Query Scripts

by Melissa Niles

In this chapter

Understanding Database Design 722

Why Access a Database? 722

Database Access Limitations 722

Creating and Using Flat File Databases 724

DBM Databases 726

Relational Databases 730

Debugging Database Applications 733

Understanding Database Design

In this chapter, you’ll take a look at three kinds of databases: flat file, DBM, and SQL. You’ll build a different database using each of these databases so that you can see the differences among the three methods of storing information. The three methods used here require little or no money to use and build. Nearly everyone will be able to work with databases, and most database applications—free or commercial—work with the same basic principles.

The most difficult and daunting task is how to go about designing your database to store and retrieve information. What would happen if you wanted to upgrade your database or if you needed to insert additional information (fields or tables, for example) to your database?

When requesting information that derives from a database, you set into motion quite a few steps to complete that request:

1.  Your Web server receives the request from the visitor to your site, then sends that information on to your CGI script.

2.  The CGI script acts as the main gateway tying two very different systems together. The CGI script performs the actual query, receives the results from the database, formulates a proper reply, and sends it off to the Web server.

3.  The Web server, in turn, sends it to the person visiting your site.

Why Access a Database?

Most likely, your organization already has an existing database in which it has stored information about its products, its customers, and other aspects of business life. Some of this information you might want to allow your customers to see, or you might even want to make the information in the database available to your workers stationed away from the office. If so, you would have to create HTML documents that contain all this information several times, which, if you’re part of a large organization, can be a tedious task. Integrating the Web with your databases can save you tremendous amounts of time in the long run, especially when it comes to maintaining that information. As the database changes, your Web pages change.

Another good reason to use the World Wide Web to access your database is that any Web browser that supports forms can access information from the database—no matter which platform is being used.

Database Access Limitations

Consider the following events:

1.  Person one accesses the database for editing.

2.  Next, person two comes along and does the same thing.

3.  Person one makes changes and saves that information to the database.

4.  Person two saves information as well, possibly writing over what person one just saved.

5.  A short time later, person one is wondering what happened to his or her data.

The browser and the server are stateless in relation to each other. Except in certain instances with certain products, the browser makes a request, the server processes the query and sends the result back to the browser, and the connection is closed. This creates a problem with databases because a connection to a database is usually constant. Normally, someone accesses the database, which keeps a connection open, locking a record if any editing is performed, and closes the connection only when the person is finished. Accessing a database doesn’t work the same way when you’re using a CGI script.

---

NOTE:  Application Programming Interfaces (APIs) have been created to alleviate the problem with stateless connections. You have to use proprietary software to utilize these APIs, but they are well worth it. Active Server Pages using ODBC and NSAPI are two examples.

Accessing a database using a Java client/server application can also eliminate the problems associated with stateless connections.

---

Two ways exist to handle the problem described previously. The first method involves keeping track of all entries with a time stamp. This will enable both entries to be maintained by the database, without the possibility of either person’s entry being overwritten.

Another solution is to only provide information from the database and not allow someone on the Web to edit, remove, or insert information to the database. Although this limits some of the possibilities for having the database on the Web, it also alleviates some of the security problems.

---

NOTE:  With the exception of ODBC for NT and a few other proprietary methods, no official standard exists that you can use to connect to a database. If you create a script to access one type of database, that same script won’t necessarily work on a different database—even if the query used was the same. Because of this, you may be required to learn a lot about each database application that you come across.

---

---

Security Issues

The major problem with having those on the Web accessing your database is that your CGI script is trusted by your database program. That is, your database has to accept commands from your CGI script, and your CGI script needs to perform queries based upon what you want to provide to those on the Web. This can lead to problems if someone with ill intentions gains access to a script and is able to edit your database.

In addition, most databases require the use of a password. Because your CGI script stores user information in the database as well as retrieves information from the database, your script needs to have the password to access your database. You need to ensure that your script cannot be read by others within your organization and outside your organization.

---