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





-H-


DBGateway Installation and Users Manual


NOTE: We would like to thank Joe Loyd, Phil Jones, and Larry Perkins for their assistance in helping us get started with DBGateway and for allowing us to use the DBGateway user's manual and documentation as source material for this appendix. Note that this documentation is also available on the CD-ROM accompanying this book.

DBGateway was developed by Computer Systems Development Corp. (CSDC) as part of the United States Army's Flexible Computer Integrated Manufacturing (FCIM) program under the direction of the Communications Electronics Command (CECOM), based at Fort Monmouth, New Jersey.


RESOURCE: You can find additional information on the FCIM program at
http://fcim.csdc.com 



DBGateway has been in use since October 1995 by FCIM organizations and members. The impetus behind its development was the need to give FCIM engineers at different geographical locations access to existing legacy databases. Because these data sources were Microsoft Access databases, DBGateway is directly geared toward that format. Additional Microsoft FoxPro read-only support was added recently in the hopes of spawning more interest in DBGateway.



You'll find the DBGateway product on the CD-ROM at the back of this book. You can use DBGateway to rapidly develop PC-based Web databases, as discussed in Chapter 24, "The Development of a PC-Based WWW Database System."


CAUTION: As with all freeware, DBGateway is released as-is without any guarantee as to its suitability for any particular task. If you choose to use the software, be sure to read the license agreement.

An Overview of DBGateway

DBGateway is a 32-bit Visual Basic application that provides Web access to Microsoft Access and FoxPro databases via the WinCGI interface. This application enables you to fully use the power and versatility of Visual Basic to develop Web database applications.

DBGateway was developed by CSDC under the United States Army's FCIM program and therefore is distributed to interested parties as freeware. It borrows heavily from (and, hopefully, complements) the work done by Bob Denny and O'Reilly & Associates' WebSite server product. For more information, go to

www.ora.com, website.ora.com, and software.ora.com.


System Requirements

To successfully use DBGateway, you must have a system that meets the following requirements:

Installation

This section describes the steps necessary to install the DBGate executable and the included sample Visitor's Book database. The Visitor's Book sample is a Microsoft Access database that implements a Web guest book. It's a simple database, but you can use it to demonstrate the basic principles of using the DBGateway software. The PhoneBook sample database also is included, which you can set up in a similar fashion. PhoneBook is a simple contact-management database with the added option of SMTP messaging for sites that have an SMTP server.

To install DBGateway, the Visitor's Book sample database, and the PhoneBook sample database, follow these steps:

1. Go to the www.microsoft.com URL and follow the links to Free Software Downloads. Download the ActiveX Internet control objects and install them on your system.

NOTE: If you are using NT 3.51, you have to get the beta version of the ActiveX objects because the final version on the Microsoft site only supports NT 4.0. You can get this beta version from CSDC's anonymous FTP server at
ftp.csdc.com/pub/Software/Utilities/ActiveX.exe



2. Unzip the DBGateway package and store it in a temporary directory on your Web server.

3. Run the included Setup.exe file and specify an installation directory.

NOTE: The setup program will not overwrite existing DLLs if you receive a copy error during the execution of Setup.exe. If you receive copy errors, note which DLLs or OCXs were indicated and check your Windows and Windows System directories to make sure that these DLL and OCX files exist on your system.
4. On your Web server, create a locally accessible directory in which you can place all your online databases. This will be your DBDATAROOT directory.

5. Place the DBGate.exe and DBGate.ini files into your designated WinCGI directory. If you do not have this directory or you do not know where it is located, consult the documentation for your Web server software. Edit the entries in the DBGate.ini file to conform to your local system setup.

6. Copy the visitors directory from the DBGateway package to the DBDATAROOT directory so that visitors is a subdirectory of DBDATAROOT.

7. Edit the visitors.ini file as necessary to conform to your local system.

8. Place the visitors.htm file somewhere in your Web document space.

9. Try out the gateway by submitting the visitors.htm document through your Web browser

10. Repeat steps 5 through 8 for the PhoneBook database, but change the name of the phonebk directory to pbdata. Use the pbdata.db database to avoid problems with associated tables.

For your own database, you must follow these two additional steps:

11. Create the HTML Query forms for accessing the database. You can use external SQL statements embedded in the HTML form, external SQL statements residing in ASCII files on the server, or the native internal Microsoft Access queries. Of course, if you have an internal query that deletes everything in the database, the last option might not be too appealing. CSDC highly recommends that you use the first or second method.

12. Create the HTML report templates if you want the results to display in formats other than TABULAR, which is the default. Note that you must enable tabular reports in the database's ini file by including the entry TabularReportAllowed=1.


Queries

DBGateway supports the following three methods for submitting databases queries:

This section describes only the first two methods because, for the initial release of the software, the third method is disabled. If CSDC gets a significant number of requests for activating the internal method, it will be enabled in a future release. This simplifies matters somewhat, because CSDC does not provide free technical support for users of DBGateway.

HTML-Embedded SQL Queries

The HTML Embedded SQL Queries method uses HTML form elements to formulate the desired SQL statement to be submitted to a specified database. Most of the parameters required for this type of query are passed as hidden HTML form elements, although this is not mandatory. The SQL parameters use an enumerated name designation so that you can divide the various SQL statement sections into different parts of the HTML form. DBGateway automatically concatenates the parts in sequential order to form the actual SQL statement. It's up to you, as the form designer, to ensure that this concatenation results in a valid SQL statement. If it doesn't, DBGateway returns an error message to the client. Unless otherwise noted, all elements are case sensitive and white-space sensitive.

The following general parameters are mandatory and normally hidden from the user:

The following SQL parameters are mandatory for DBACTION={"Query", "Show"}:

The following SQL parameters are optional for DBACTION={"Query", "Show"}:

The following parameter is mandatory for DBACTION=AddDBRecord:

The following parameters are mandatory for DBACTION=DeleteDBRecord:

The following parameters are mandatory for DBACTION=EditDBRecord:

Additional Notes The path to the Access database is formulated by DBGateway as this:

DBDATAROOT + " /" + DBPATH + DBNAME + "/" + DBNAME + ".MDB"

The path to the FoxPro databases is formulated by DBGateway as this:

DBDATAROOT + " /" + DBPATH + DBNAME + "/" + tablenames + "DBF"

Databases opened with DBACTION="Query" will be opened as read-only for safety reasons. Therefore, so-called Microsoft Action queries are not permitted.

If you receive an Invalid SQL or SQL syntax error message after you submit your form, you might find it helpful to temporarily change the DBACTION="Query" parameter to DBACTION="Show" and resubmit the form. Instead of actually submitting the query, DBGateway returns the interpreted SQL statement to you. You then can examine the statement for clues about the cause of the error.


CAUTION: Although DBACTION=AddDBRecord has been thoroughly tested, DeleteDBRecord and EditDBRecord have not. To allow Web clients to perform these actions is inherently dangerous. As of this writing, CSDC does not have any customers who require this functionality, so testing on these functions has been limited to a small number of cases. If you want to try these functions, do not use your real database. Make a copy and try it out on the copy.

DBACTION=DeleteDBRecord and DBACTION=EditDBRecord perform a search on the specified table(s) for a matching value in the key field. The action is performed on all matching records. Therefore, make sure that your criteria is unique. These actions should be used only on databases that actually use the built-in referential integrity to prevent orphaned table records.

Do not include SQL keywords such as SELECT, FROM, WHERE, and so on in the string values assigned to the SQL parameters previously described. DBGateway uses the SQL parameter names to automatically add these keywords to the appropriate places in the SQL statement for you. The following line of code illustrates incorrect usage (note the inappropriate addition of the keyword FROM in the string assigned to the value of FROM0):

<INPUT type=hidden name="FROM0" value="FROM Employees">

while the following line of code illustrates the appropriate way to specify a SQL FROM statement:

<INPUT type=hidden name="FROM0" value="Employees">

Concatenation of each enumerated SQL parameter ends at the first break in the sequential order. When debugging SQL queries, it's often best to design the query in the native database application and then cut and paste the resulting SQL statement directly into the HTML form.

Always start building a new query form with REPORTNAME="TABULAR". This enables you to check the results of the query in raw form without the added complication of report template parsing and formatting. After you debug the query, you can concentrate on making it visually appealing.

You can use the COOKIE n parameter to pass non-query-generated (static) information to the report template.

Because HTML reserves the use of the quotation mark (`) and double quotation mark (") characters, you might have to isolate parts of the SQL statement that use these characters. This might be necessary in order to prevent your browser from misreading your statement. You should implement the SQL statement

HAVING fname = "Joe"

in an HTML form element, as this example shows:

<INPUT TYPE="hidden" NAME="HAVING0" VALUE="fname = `">

<INPUT TYPE="text" NAME="HAVING1" VALUE="Joe'">

Also, because HTML reserves the use of the left (<) and right (>) angle-bracket characters, you'll need to use the following substitutions in your SQL statement:

Use This Symbol

Instead of This

LT

<

GT

>

LTE

<=

GTE

>=

NEQ

!=


Server-Side SQL Queries

The Server-Side SQL Queries method uses SQL queries that are stored with the database in ASCII text files on the server side. The only advantage of this method is that the Web client cannot "see" the query elements, because they reside on the server. Of course, this also means that you cannot change the query on-the-fly, and you cannot pass DBGateway any modifying parameters. The queries must reside in the sql subdirectory of the respective database and must conform to the following format:

QUERYTITLE=Some descriptive title string
QUERYTYPE={ "IMMEDIATEQUERY", "IMMEDIATESHOW" }
REPORTNAME = { "TABULAR", "any valid report filename
Âwithout the "rpt" extension" }
SQL=SELECT Employees.*
SQL=FROM Employees
SQL= ORDER BY Department, LastName
...

Additional Notes Setting QUERYTYPE=IMMEDIATEQUERY simply submits the query.

Setting QUERYTYPE=IMMEDIATESHOW returns the interpreted SQL statement (this can useful for debugging purposes).

You can include any number of SQL= statements. These statements are concatenated, in order, to form the submitted SQL statement.

Unless otherwise noted, all elements are case sensitive and white-space sensitive.

To access the Server-Side SQL type of query, use an HTML form with the following general parameters. These parameters are mandatory and normally hidden from the user:

Database Reports

Although using report templates is optional (you can just let DBGateway return the raw data in its native table format), these templates are where the real power of DBGateway lies. The best way to learn how to create report templates for your own database is to look at the samples included with the Visitors Book and PhoneBook databases. You can find the report templates in the rpts subdirectory of the respective database. The templates consist of the following mandatory delimited container sections. These templates can contain FIELD and/or COOKIE n tags that will be parsed out and replaced with the specified field/cookie values from the query response set and/or query form:

The following optional record count tags are supported in all sections of the report template:

The following code template illustrates how you can use optional conditional tags in any of the database report template sections to control the report formatting:

<IF OPERAND1=" operand1 " OPERAND2=" operand2" OPERATOR=" operator">
   ... HTML code used if statement is true
<ELSE>
   ... HTML code used if statement is false
</IF>

Here, operand n is any string, and operator is one of following:

= <> < > <= >=

The <ELSE> tag is optional and no nesting of the tags is allowed. As with the report section tags, the conditional tags should be isolated on separate lines.

Additional Notes

All template tags are case sensitive and white-space sensitive.

All section tags, both beginning and ending, must be present and isolated on a separate line.

Using report templates prevents Web clients from obtaining unauthorized field values by modifying the query form.

FIELD tags used in the Report Header section receive values obtained from the first record in the response set. Likewise, FIELD tags in the Report Footer section receive values from the last record.

If the name of a FIELD tag does not match any field name in the query response set, it is not parsed out. Always view the source of the returned report to look for non-parsed FIELD tags, and then modify the query or report template to correct the problem. Some browsers do not correctly ignore these tags if they are left in the returned form.

If the name of a COOKIE n tag does not match any cookie name in the report template, it is not parsed out. Always view the source of the returned report to look for non-parsed COOKIE n tags and modify the query form and/or report template to correct the problem. Some browsers do not correctly ignore these tags if they are left in the returned form.

Non-parsed FIELD and COOKIE n tags can confuse some browsers. If the returned form is garbled, it probably means that one of your FIELD/COOKIE n tags was not parsed correctly. If you received part of the form, you can check around the area where the problem first occurred to identify the problem FIELD/COOKIE n tag. Check for misspellings or incorrect case.


Secured Databases

Web access to Microsoft Access databases is controlled though the standard Web server access control and Microsoft's system.mdw file. You can use the Microsoft Access 2.0 Workgroup Administrator to create this system database and to join the workgroup. Then use Access 2.0 to set users and permissions. FoxPro access currently is limited to Web server control only.

To allow DBGateway to communicate with databases that are secured with a System database file, you must pass a user ID and password to DBGateway when submitting a form and set PublicAccessAllowed=0 in the database's .ini file. Place the System database file in the same directory as the database. In addition, if you are using the WebSite server, you must rename DBGate.exe and DBGate.ini to $DBGate.exe and $DBGate.ini, respectively, so that the WebSite server will pass these values to DBGateway.

When DBGateway receives a request (query) to a nonpublic database, it checks, in order, for the following:

1. The UID (user ID) and PWD (password) form parameters. If found, these parameters are used to log onto the system.mda or system.mdw file.

2. The user ID and password the person used to log onto the Web server. If found, the ID and password are used to log onto the system.mda or system.mdw file.

If DBGateway cannot find a valid user ID and password, the user is denied access.

Mail

DBGateway has a built-in SMTP module for use with the PhoneBook database included in the package. Unlike the rest of the DBGateway program, the SMTP module is not database independent. When the SMTP functionality is used, DBGateway expects the specified database to be structured like the included PhoneBook sample. You can add other tables and fields to your version of the database, but do not change any of the existing table or field names. The messaging capability is based on Microsoft's ActiveX SMTP control. This control is still in the beta stage of development, so if you're contemplating using the SMTP module, make sure that you monitor your Web server frequently in case it throws an exception error. Although these errors will not stop your Web service, they can slow it down by tying up resources and annoying your Web clients.

The following general parameters are mandatory and normally hidden from the user:

The following general parameters are mandatory and normally are selected by the user:


Additional Notes

DBGateway expects the list of names in the MAIL_TO parameter to be of the form FirstName LastName and/or GroupName.

Names will be parsed into one or more e-mail addresses. Therefore, all names must be contained in the specified PhoneBook, and all groups must have a least one valid member.

The DBNAME parameter should point to the specific PhoneBook database.


Examples

The best way to learn about a new software tool is by example. This section illustrates how two of the basic DBGateway functions--querying a database using SQL embedded in HTML documents and generating a report using templates--are implemented in an application. These examples also are included on the CD-ROM that accompanies this book and can serve as a starting point for developing your first DBGateway-powered Web database applications.

An HTML-Embedded SQL Query Form

Listing H.1 shows a sample query form for DBGateway. This HTML page actually contains a query to one of the CSDC PhoneBook databases. If you're familiar with SQL, you can examine the hidden form elements to understand how the query is implemented.

Listing H.1. A query form for DBGateway.

<HTML>
     <HEAD>
          <TITLE>DBGateway PhoneBook</TITLE>
     </HEAD>
     <BODY>
          <BASE TARGET="main">

          <FORM METHOD="POST"
          ACTION="/cgi-win/dbgate.exe">

               <INPUT type=hidden name=" ACTION"
               value="SubmitExternalQuery">

               <INPUT type=hidden name=" DBNAME"
               value="PhoneBk">

               <INPUT type=hidden name=" DBTYPE"
               value="ACCESS">

               <INPUT type=hidden name=" DBPATH"
               value="">

               <INPUT type=hidden name="
               QUERYNAME"
               value="EmployeeListByMatch">

               <INPUT type=hidden name=" DBACTION"
               value="Query">

               <INPUT type=hidden name="
               REPORTNAME" value="Match">

               <INPUT type=hidden name=" FIELDS0"
               value="Employees.*">

               <INPUT type=hidden name=" FROM0"
               value="Employees">

               <INPUT type=hidden name=" ORDER0"
               value="Employees, Department">

               <INPUT type=submit name="Submit"
               value=" Search For: ">

               <SELECT name= WHERE0>

                    <OPTION
                    SELECTED>LastName</OPTION>

                    <OPTION>Department</OPTION>

                    <OPTION>Title</OPTION>

               </SELECT>

               <B> = </B>

               <SELECT name=" WHERE1">

                    <OPTION
                    SELECTED>=</OPTION>

                    <OPTION
                    SELECTED>NEQ</OPTION>

                    <OPTION
                    SELECTED>LT</OPTION>

                    <OPTION
               SELECTED>GT</OPTION>

                    <OPTION
                    SELECTED>LTE</OPTION>

                    <OPTION
                    SELECTED>GTE</OPTION>

               </SELECT>

               <INPUT type=hidden name=" WHERE2"
               value="'">

               <INPUT type=text name=" WHERE3"
               value="">

               <INPUT type=hidden name=" WHERE4"
               value="'">

          </FORM>
     </BODY>
</HTML>

A Report Template

Listing H.2 shows a sample DBGateway report template. The template is an ASCII text file that is parsed prior to returning the query response to the Web client. Each report section is used and displayed as you would expect.

FIELD tags are replaced with their corresponding values for each returned record. All fields in the response set that are not specified by a FIELD tag are discarded prior to returning the response. This prevents Web clients from requesting unauthorized field information.

All FIELD tags that are included in the report template but are not in the response set are left in the returned document as unparsed.

TABULAR reports are not parsed, so if they are enabled, they allow Web clients to access all database fields.

Listing H.2. A DBGateway report template.

     < REPORTHEADER>
          <HEAD>
               <TITLE>DBGateway Member Contact
               Information</TITLE>
          </HEAD>
          <BODY>

               <P>
               <HR>
               <H1>DBGateway Member Contact Information</H1>
               <HR>
               </P>

               <TABLE BORDER>

                    <TR>

                         <TH ALIGN=CENTER>Name
                         <I>(last, first,
                         MI)</I></TH>

                         <TH
                         ALIGN=CENTER>Title</TH>

                         <TH
                         ALIGN=CENTER>Department</TH>

                         <TH
                         ALIGN=CENTER>Phone</TH>

                    </TR>

     < /REPORTHEADER>

     < GROUPHEADER NAME="LastName">
     < /GROUPHEADER>

     < RECORDHEADER NAME="RECORD1">
          <TR>
     < /RECORDHEADER>

     < RECORD>
          <TD>
               <A href="/employphotos/< FIELD
               NAME="Photograph">">

               < FIELD NAME="LastName">,

               < FIELD NAME="FirstName">

               < FIELD NAME="MI">.</A>
          </TD>

          <TD><FIELD NAME="Title"></TD>

          <IF OPERAND1="<FIELD NAME="Department">"
            OPERAND2="ACME, Inc." OPERATOR="=">
             <TD><FIELD NAME="Department"></TD>
          <ELSE>
             <TD>Road Runner & Associates</TD>
          </IF>

          <TD>< FIELD NAME="WorkPhone"></TD>
     < /RECORD>

     < RECORDFOOTER NAME="RECORD1">
          </TR>
     < /RECORDFOOTER>

     < GROUPFOOTER NAME="LastName">
     </GROUPFOOTER>

     <REPORTFOOTER>

        </TABLE BORDER>
        <P>Total number of records returned: <TotalRecordNumber></P>
        </BODY>
        </HTML>

     </REPORTFOOTER>

Troubleshooting DBGateway

Occasionally, you might experience some difficulties installing or using DBGateway. Here are some common problems/errors encountered:

Error Message:

Access violation occurred while copying the file ...\<filename> .DLL

Probable Cause: DBGateway Setup will not overwrite existing files in your system.

Solution: Rename your existing file to a recognizable name and then try reinstalling.


Error Message:

Can't load NMOCOD.dll

Probable Cause: You might not have the DLL and OCX controls registered.

Solution: Run the RegSvr32.exe program in your system directory.


Error Message:

Can't load custom control SMPTCT.OCX (error #367)

Probable Cause: You don't have the Microsoft Internet support objects loaded on your system.

Solution: Download ActiveX from www.microsoft.com.


Error Message:

This is a Windows-CGI program.

(This message may appear when you use DBGateway.)

Probable Causes:

(1) You are not running a WinCGI-compatible server.

(2) You are executing DBGateway from Windows and not from an HTML form.

(3) You are not running the required operation system.

Solutions:

(1) See system requirements.

(2) Look at the sample query in the installation and user documentation
.
(3) See system requirements.

RESOURCE: For additional information about DBGateway, contact
Computer Systems Development Corp.
3810 Concorde Parkway, Suite 2200
Chantilly, Virginia 22021
Phone: (703) 817-0604
Fax: (703) 817-0903
Or go to this URL:
http://www.csdc.com and http://fcim1.csdc.com