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.
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."
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.
To successfully use DBGateway, you must have a system that meets the following requirements:
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:
ftp.csdc.com/pub/Software/Utilities/ActiveX.exe
For your own database, you must follow these two additional steps:
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.
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.
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:
|
|
|
LT |
< |
|
GT |
> |
|
LTE |
<= |
|
GTE |
>= |
|
NEQ |
!= |
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:
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.
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.
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:
If DBGateway cannot find a valid user ID and password, the user is denied access.
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:
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.
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.
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>
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>
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:
Solutions:
http://www.csdc.com and http://fcim1.csdc.com/PRE>
