Skip to main content.

Web Based Programming Tutorials

Homepage | Forum - Join the forum to discuss anything related to programming! | Programming Resources

Web Database Developer's Guide with Visual Basic 5

World Wide Web Database Developer's Guide with Visual Basic 5



-E-

Web Database Development Frequently
Asked Questions (FAQs)

This appendix contains some of the most frequently asked questions by users and developers new to Web database development. This is not an all-encompassing list of questions. If you're interested in adding database access to your Web, however, you'll find that these questions provide most of the basic answers. Here's a list of the questions answered in this appendix:


What is a Web database?

In the recent past, the definition of Web databases included primarily large HTML document repositories that existed at various Websites. Many of these sites allowed reasonably comprehensive searches of these documents via search engines such as WAIS. More recently, however, the view of a Web database has converged with the traditional notion of a database management system (typically, relational) that includes APIs, a search engine, DBA tools, a query language, and so on.



Like any regular database management system, a Web database is a data store or information repository that can be accessed via a query language or programming API. Unlike conventional database systems, however, access to Web databases typically is not achieved by typing instructions at a command line or by using interfaces that are custom designed for use on a specific computer platform.

Web databases are databases accessed via other Web applications--specifically, forms developed using standard (almost) HTML. Using facilities available in HTML, applications programs on the Web server are accessed through a server-side mechanism known as the Common Gateway Interface (CGI). This interface enables you to create applications that integrate database functionality and provide access to organizational data repositories on behalf of Web clients (a user and browser). Applications can be designed solely for the purpose of querying a database and returning specific information. Also, the application can incorporate information pulled from a database for use as part of a larger application.

Why should I use a Web database?

An organization might want to use databases in its Web applications for several reasons:

How does an intranet DB application differ
from an Internet DB application?

Functionally, intranet database applications and Internet database applications are the same. In fact, intranet applications simply are subsets of Internet or Web applications; they both implement the same technologies to accomplish their goals. The primary difference between an intranet and an Internet database application is who has access to the database applications. A common misconception about intranets is that they are physically located in a single geographical area (for example, internal to a corporate office building) and are not connected to the Internet. In reality, intranets typically are not physically disconnected from the Internet, and they are not bound by geography. Once again, the definition begins and ends with who can access the data.

What database-access methods can be integrated
with Web applications?

Many methods exist for providing database access to your Web applications. Three of the most common methods are using HTML embedded-SQL extensions, codeless interfaces, and custom CGI programs.

HTML-Embedded SQL Extensions

Many database systems provide a mechanism for embedding SQL statements directly within HTML files passed to the Web client by the Web server. When a user submits a query, the HTML file is passed to a CGI program that parses the SQL statements and passes these on to the database system. Results of the SQL query are retrieved by the CGI program, reformatted to HTML, and returned to the requesting client. This mechanism is typical of many gateway products, as well as shareware databases that often support only a subset of the functionality of commercial DBMSs. Additionally, the developer often is limited in his capability to modify the CGI program doing the actual database access and formatting of results.

Codeless Interfaces

Essentially, codeless types of access methods consist of software toolsets (often, Perl scripts) that work with developer-defined template files. These files specify various views into the database and how extracted data should be manipulated and formatted when returned to the requesting client. Programs in the software toolset use these template files to automatically generate HTML forms. These forms, when submitted by the user, are processed by CGI programs that query the database and format the results in accordance with the predefined templates.

Most of the toolsets available today are somewhat manual in nature. In the very near future, however, visual programming tools that implement drag-and-drop technology for building forms interfaces, specifying database queries, and detailing the format of results sent to the user should become more prevalent.


Custom CGI Programs

Customized CGI programs are gateway programs written specifically to accept and process Web client requests for a service provided by the Web server. Typically, these requests are submitted by users in the form of input they've entered in an HTML form. CGI programs designed for database access parse this user input, use it to formulate queries, connect to the database and submit the query, accumulate results, possibly manipulate these results or perform some other specialized processing, and dynamically create HTML documents that are sent back to the Web client. Custom CGI programs have the advantage of generally giving you access to the full range of functionality provided by database APIs or ODBC.


How can database results be used in a Web application?

The use of database query results is somewhat dependent on the type of access method used. Many of the available tools, such as gateway products or codeless interfaces (those that implement template files) provide reasonably strong data-formatting and report-generation capabilities. Often, this is all that the application and users require.

But what about cases where you want to do some additional processing on the data? Suppose that you need to perform some statistical processing on the returned data. What if you want to generate charts, maps, or graphs? How about using the results to populate a spreadsheet? These are instances in which custom CGI programming still offers an advantage over Web- application development tools. One of the primary strengths of CGI programs is the capability to extend the capabilities of the Web server not only by adding access to a database, but by providing the capability to integrate other applications as well. So, you now can develop applications that retrieve data from a database and then do some form of value-added processing on that data.

What types of Web applications can use databases?

Virtually any Web application that would benefit from having access to data and information stored in a database can use a database, as long as the Web-application development environment supports the type of database access required. Some typical business examples of Web applications that use databases include online banking and securities trading, online stores, shopping malls and catalogs, customer services, product information, online technical support, real estate advertising, recruiting, and specialty product sales.

Intranet Web database applications also abound. Organizations have found that database-based Web applications can integrate many of the functions of corporate MIS systems and groupware products. The resultant proliferation of information sharing, improved operating efficiencies, and improved employee productivity have paid for the cost of development several times over.

Does the DBMS need to reside on the same CPU as the Web server?

No. Thanks to client/server database systems, CGI database client applications can reside on the Web server, whereas the database server being accessed can be located virtually anywhere, as long as network access between the database client and server programs exists.

Additionally, many databases, such as Microsoft's Access desktop database system, provide the capability to link to external databases and data sources through ODBC. A Web server residing on a Windows 95 PC, for example, could use cgi-bin, cgi-win, or database gateway programs to access the Access database. The database, in turn, could be configured with links to an external database. This, in effect, provides another method for a Web server to access remote databases.

Can I use desktop databases to create Web database applications?

Yes. Virtually any database that has an Application Programming Interface (API) can be accessed from CGI programs and various gateway products. This is tremendously useful to organizations with legacy databases and information repositories that want to make this information accessible to internal employees and/or external users. Because the database and query models already exist for legacy databases, often all that is required is to develop the interface. (Often, this is just a matter of developing HTML documents, with perhaps the addition of some HTML tag extensions that support SQL query development within HTML.)

Already, gateways are being developed for desktop databases such as Microsoft Access and FoxPro. CGI programs also are commonly being developed in languages such as Visual Basic and Visual C++ and are using database APIs or ODBC functions.

With the availability of very inexpensive yet highly capable PC Web servers, the use of desktop databases is likely to increase for intranet database applications within small companies and organizations to support a variety of project informational needs.

How is CGI used with Web database applications?

CGI provides the means to pass user-specified information (typically, input to a form) to a program that uses that input to create database queries, as well as SQL statements to add, delete, or modify records. CGI simply serves as a mechanism to allow communication between a Web client and CGI database applications programs on a Web Server.

What CGI languages can I use for database development?

CGI database programs and scripts contain the code that accepts data from the Web server (most often passed on from its Web clients) and uses the data as the basis for forming a query (typically SQL) to a database. Additionally, these CGI programs generally are responsible for capturing database query results, formatting the results, and generating the appropriate HTML necessary to present these results to the user.

Note that any language supported on the Web server host machine that also supports access to the database(s) via a database API or ODBC can be used as a CGI development platform. Strictly speaking, however, CGI languages must meet three requirements:

Potential CGI languages for database applications include (but are not limited to) compiled and interpreted languages, such as C, C++, FORTRAN, Perl, UNIX shells, Awk, Expect, and Tcl (and some variants that support Sybase and other database access), as well as some of the newer Perl extensions for client/server database access--for example, Sybperl and Oraperl.

What security concerns exist with Web DB applications?
How can I address these concerns?

As with any application that accesses a database--and especially those that provide access from unknown users such as the WWW community--security issues are always a major concern. Depending on the level of access your application will need (query, insert, update, or delete), you'll need to consider how to adequately handle access privileges for your database gateway programs.

One way to handle access privileges is to control application access at the document level using specially created database user accounts. For database-access documents that require only query capabilities, for example, you can set up a database user account with read-only privileges. Then, depending on the access level the document requires, you can specify a special access account to handle the transaction. For ODBC database resources, this is as simple as creating a different ODBC data source for each level of access.

Another way to handle access privileges is to use a temporary table to hold database transactions (inserts, updates, and deletes) for batch processing. Using a queuing system such as this gives database administrators the capability to review transactions before processing them.

How are query results from a Web database
application presented to a user?

The presentation of query results depends on the access method used. Using database gateway products and codeless interfaces often requires you to use template or report files to define the structure and layout of results presented to the user. Although many of these methods enable you to nicely format presentations for the user, they typically don't provide the degree of flexibility offered by custom CGI programs.

CGI programs, on the other hand, enable you to generate comprehensive HTML presentations from database query results on-the-fly. You have ultimate control over tasks such as these:

These presentations also can include a complex mixture of data types, including text, graphics, images, dynamically generated hypertext links, and other MIME data types such as audio and video.

Does a Web client need to know SQL to use
a Web database application?

No. This is one of the primary benefits of Web databases. Users of Web database applications typically don't need to know a query language or the underlying database table structures and relationships in order to submit a query to the database.

Users generally interact with database applications via HTML forms developed for them by the application developers. User input to these forms then is used to formulate a query that is sent to the database by a CGI program or database gateway program. All the user needs to know how to do is use a mouse. In fact, users often don't even know that information presented to them as a result of form input is derived from a database at all.

What role do HTML and forms play in Web database applications?

HTML forms provide the standard interface by which users interact with a Web database application. Typically, an HTML form includes user-input objects such as checkboxes, radio buttons, scrollable menus, and text-input boxes. These input elements enable the user to make selections or provide input that ultimately is used by CGI programs or database gateways to formulate SQL queries to a database when the form is submitted to the Web server. This input can be used to fill in one or more parts of a standard SQL query, such as a table; fields in a SELECT statement; or values required to generate WHERE, ORDER BY, GROUP BY, or COMPUTE clauses. Well-designed forms abstract the database details from the user simply by providing the input objects and range of choices appropriate for each.

HTML forms can be used as an interface to a variety of Web applications, including these:

Does HTML support embedded SQL statements?

Not directly. Numerous commercial and freely available gateway products do enable you to embed SQL or variants directly into HTML documents, however. When submitted, these HTML documents, form input, or hidden variables are parsed, and the gateway handles the details of interpreting the embedded-SQL statements, formulating a query, contacting the database, and collating query results for presentation to the user.

What are Microsoft Internet Database Connector (.idc) files?

Microsoft Internet Information Server includes a dynamic link library interface called the Internet Database Connector (IDC). The IDC uses specially formatted .idc documents to identify key database access parameters, such as the ODBC resource name, the SQL statement to execute, the Hypertext Extension template file to post results to, the user name to use when accessing the data source, and any required parameters. Here is a sample .idc file:

Datasource: pubs2
Username: admin
Template: employee.htx
RequiredParameters: EmployeeId
SQLStatement:
+SELECT FirstName, LastName
+ FROM Employees
+ WHERE EmployeeID = %EmployeeID%

What are Microsoft Hypertext Extension (.htx) files?

Hypertext Extension files are used by Microsoft IIS to identify the specially formatted HTML documents that will include special tags used to handle such items as looping constructs for multirow results and if-then-else control blocks. Microsoft IIS references .htx files within an accompanying IDC file, as shown in the previous question. Here is a sample .htx file:

<html>
<title>Employee Lookup</title>
<BODY>
<%begindetail%>
<%if CurrentRecord EQ 0 %>
<STRONG>Employee:</STRONG> <%FirstName%> <%LastName%></h4>
<%endif%>
<%enddetail%>
<%if CurrentRecord EQ 0 %>
<h4>No employee records found.</h4>
<%else%>
<%endif%>
</body>
</html>

Can I use scripting languages such as Perl to access my database?

Yes, but there might be limitations, depending on the database you'll be accessing. If your database platform supports command-line access through interface programs such as Oracle's SQL*Plus or Sybase's iSQL, for example, you can launch the interface program from within any scripting language. Additionally, Perl has been ported to support several database platforms, such as Sybase (Sybperl) and Oracle (Oraperl). These Perl interfaces access their respective databases via special Perl subroutines.

Here is some sample code to access a Sybase database via a Sybperl environment:

$dbh->dbcmd("select FirstName, LastName");
$dbh->dbcmd("from Employees");
$dbh->dbsqlexec;
$status = $dbh->dbresults;
while (@row = $dbh->dbnextrow) {
   print "Employee Name: @row[0] @row[1]<BR>\n");
}

What is VBScript and how does it differ from Visual Basic?

VBScript represents the low end of the scale of the Microsoft VB family, which also includes Visual Basic for Applications (VBA) as well as Visual Basic 5. VBScript is a subset of VBA and VB, and it was designed to provide client-side scripting capabilities within Microsoft's Internet Explorer Web browser. The capability to perform client-side processing opens up an entire new realm in Internet applications development. VBScript provides the capability to perform client-side functions such as validating user input. It also enables you to develop highly interactive user interfaces complete with embedded ActiveX controls.

Although VBScript originally was a language of modest capabilities, it quickly is maturing into a full-fledged language that provides many of the niceties found in other common and popular interpreted scripting languages, such as Perl. As of this writing, VBScript only has native support within Internet Explorer. Plug-ins are available, such as that developed by NCompass, which enables you to use VBScript and ActiveX within Netscape browsers, too. Microsoft also has indicated that Internet Explorer and VBScript soon will make their way to UNIX platforms as well.

I want to decrease the traffic on my Web server because
of the amount of incomplete requests or requests
with improper information; what can I do?

You can do two things. First, the forms for your application should provide the user with some visual clue that certain fields must be completely filled out in order for the request to be handled properly when submitted. A common idiom is to mark required fields with an asterisk (*) character.

Second, you should start taking advantage of the functionality provided by VBScript to perform validation of user input on the client browser. With VBScript, you can validate that users not only supplied input to required fields, but that input to all fields meets certain criteria that you establish. Your scripts can perform range checking, for example, to make certain that a user enters reasonable numeric and text values in appropriate fields. The level of sophistication you use in checking user input is limited only by your needs and imagination.

If users supply incorrect or inappropriate information, your scripts can prompt them with a MsgBox dialog box (or by using the alert method). You even can use VBScript to set the cursor focus to the offending field automatically--another nice way to make things easier for your users.