As you learned in Chapter 19, Cold Fusion is a great tool for many database-oriented CGI tasks, especially for non-programmers. C, C++, and Perl are also popular CGI languages, but they all have a high entry fee in terms of programming experience. And
when it comes to CGI, they "don't do Windows." Nor do they have an ODBC database interface. CGI PerForm, discussed in the previous chapter, does have a database interface, but it is a proprietary one (very powerful, but not yet ODBC-compatible).
If it sounds as if we are heaping on the criticism to prepare you for the next pitch, here it comes. To put it simply, you have to give Visual Basic a try before you can appreciate its awesome potential. Not only is it a rapid development environment,
but it supplies all of the features just discussed that are missing from other CGI alternatives.
Nothing in life is free. The tradeoff for having the power to custom develop your CGI applications is, of course, that it takes time to develop your own programs. Tools such as Cold Fusion and CGI PerForm can accomplish most database-oriented CGI tasks
without programming.
Visual Basic includes very powerful capabilities for database, file, and string manipulation. And these features just happen to have a high correlation with a "dream" language for CGI. Once you become familiar with VB, you'll see that this
list is just the tip of the iceberg of its capabilities. For example, did you know that VB Professional will let you send and receive e-mail on the LAN using MAPI (the Mail API)? Did you know that you can use it to send a fax? VB programs are even used to
control factory automation processes. Imagine using a Web form to automatically initiate a fax transmission, beep your pager, or build a customized pizza.
Pundits claim that VB is slow, but this just isn't true. Well, of course, it is slower than a compiled language, but the point is that this usually doesn't matter. You see, as soon as you identify the segment of your code that cries out to
be optimized (evidence suggests that this is often less than one percent), and assuming that your customers don't already have more important things for you to be working on instead, you can rewrite that function in C and put it in a DLL. This way, you get
to retain VB for the things it does best, namely user interface construction and database connectivity, while tuning only the sections of the program that really need the boost. Isn't it better to get it working first and optimize it later?
Many Windows Web servers don't support WinCGI. Standard CGI requires a shell interface, and VB doesn't build that kind of console-mode application. This is unfortunate, but thanks to the infinite malleability of software, it's not insurmountable. We
have devised a clever kludge (yes, we believe in the power of positive criticism) so that you can take advantage of CGI with just about any Windows-based Web server and still take advantage of the database power of VB.
We are going to develop a C++ program (more like "extended C" because it doesn't really use any object-oriented features) to parse the form data into a temporary file before it invokes the Visual Basic application. The VB program will run
invisibly while it reads the temporary file passed in by the C++ program and writes the information to the Access database.
If you read the WinCGI specification in Appendix I, you'll see that the method adopted here is very comparable in terms of efficiency. WinCGI involves file I/O to write the environment variables to WIN.INI and to write the form data to a temporary file.
(Speaking of efficiency, see the sidebar in the following section titled "The Efficiency of CGI.")
There are four pieces to this puzzle: you need an HTML form that will invoke the CGI application, the CGI application written in C++ that will handle the environment variables and pass the form data to the Visual Basic program, the Visual Basic program
itself, and an Access database to store the data. Using Visual Basic, you could just as easily connect to a FoxPro database, or any database for which an ODBC driver is available.
What does this application do when it is all done? Frankly, not much more than what you have seen in the previous chapter, except that now form data will be saved in an Access database. This opens the door to future customizations in the Windows
environment. For example, you could run other programs in Access or Visual Basic to process the collection of form data by sending e-mail in a batch fashion. If you run the second-stage programs offline (perhaps from another workstation) or on a daily or
weekly basis, you can minimize the hit on system resources while the Web server is running. In other words, consider tuning the Web server to perform only the quick acquisition of form data, and save the data processing for later.
In order not to introduce too much complexity all at once, we are going to recycle the customer feedback form from Chapter 13. It presents a simple form interface on the Web to enable a customer to complement (or complain) about your products or
services. You'll recall that the feedback form in Chapter 13 processed the data by mailing it to the attention of a predetermined company representative. In this chapter, you'll insert the form data into a table in an Access database. Each record you
insert includes a timestamp so that you can use a SQL query (in a separate program such as Access) to determine which records have been added within a given date range.
The main goal of this section is to show you the possibilities of CGI with VB. The sample application that you are going to build can easily be extended in any number of ways. With HTML, VB, ODBC, and the Windows API at your disposal, you are limited
only by your imagination.
The Efficiency of CGI
Listing 23.1 is the file feedbck2.htm from the \vb directory on the CD. It is a modified version of feedback.htm from Chapter 13. Notice in the <FORM> tag that this version uses the POST method to invoke a CGI application on the server named
cgi_cpp.exe. Also notice the names of the input fields; you will be checking for those in the VB program.
<HEAD> <TITLE>Suggestions and Comments</TITLE> </HEAD> <BODY> <form action="http://localhost/cgi-bin/cgi_cpp.exe" method="POST"> <H1>Your Comments, Questions and Feedback!</H1> Please enter your Name: <BR><INPUT TYPE=text NAME="name" SIZE = 40 MAXLENGTH=40> <BR>Email address: <BR><INPUT TYPE=text NAME="email" SIZE = 40 MAXLENGTH=40> <P> Enter your comments, questions and/or suggestions in the space below:<BR> <TEXTAREA NAME=comments ROWS=12 COLS=60 MAXLENGTH=3000></TEXTAREA> <P> <input type="submit" value="Submit Comments"> </FORM> </BODY> </HTML>
Take another look at the <FORM> tag in Listing 23.1. Notice that the action URL indicates "localhost." 127.0.0.1 is defined to be the loopback address for IP, meaning that this special IP address is always valid to refer to the current
machine. The HOSTS file in your Windows directory should indicate that localhost is an alias for 127.0.0.1. This makes a convenient way to check your CGI systems on your server, even if you aren't connected to the Internet.
The program developed here is called cgi_cpp.cpp. It has been modified from savedata.c, which was presented in Chapter 22, "Power CGI Programming with C/C++." You recall that savedate.exe can be used to parse the contents of an HTML form and
save them in a text file named savedata.HFO. Our purpose for modifying the program in this chapter is to get it to parse the data slightly differently before handing it off to the Visual Basic program.
One way in which it is modified is that it creates a small temporary file each time it is invoked, rather than inserting the data into one file, which increases in size. See Listing 23.2 for the code. Most of it will look very familiar to you by now. In
order to save trees, we have trimmed out three functions in Listing 23.2 and marked the location with a comment. You have already seen the functions strcvrt, TwoHex2Int, and urlDecode in Chapters 11 and 22, and they remain unchanged in this program. The
file cgi_cpp.cpp appears in its entirety in the \cgi\vb directory on the CD.
/************************************************************************
* File: cgi_cpp.cpp
*
* Description: CGI program for use with HTTPS.
* Parses form data and invokes Visual Basic application.
*
* Assumes it is invoked from a form. This script writes the form data
* to a temporary file and then invokes the Visual Basic application
* which serves as the interface to the database.
* Ensure that you compile this script as an NT console mode app.
* This program is a modified version of the source code that comes with
* EMWAC HTTPS.
*
* October, 1995
* By: Scott Zimmerman and Christopher L. T. Brown
*
************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <ctype.h>
#include <io.h>
#include <time.h>
#define DATA_PATH "c:\\temp" // Location of temp file for form data
char InputBuffer[4096]; // Maximum amount of data user may enter
/*** the function void strcvrt(char *cStr, char cOld, char cNew) appears here ***/
/*** the function static int TwoHex2Int(char *pC) appears here ***/
/*** the function void urlDecode(char *p) appears here ***/
/* Parse out and store field=value items into the temp file.
** DON'T use strtok here because it is ALREADY used by caller.
*/
void StoreField(FILE *f, char *Item)
{
char *p;
p = strchr(Item, '=');
*p++ = '\0';
urlDecode(Item);
urlDecode(p);
strcvrt(p, '\n', ' ');
/* Get rid of those nasty +'s */
strcvrt(p, '+', ' ');
fprintf(f, "%s=%s\n", Item, p);
}
int main(void)
{
int ContentLength, x, i;
char *p, *URL, *whocalledme;
char datebuf[9], timebuf[9];
char FileName[_MAX_PATH];
char cmdbuf[_MAX_PATH + 30];
FILE *f;
// Turn buffering off for stdin
setvbuf(stdin, NULL, _IONBF, 0);
// Tell the client what we're going to send back
printf("Content-type: text/html\n\n");
// Uses a kludgy IPC method to pass form data to VB
for (i = 0; i <= 9999; i++)
{
// Make a new filename
sprintf(FileName, "%s\\CGI%d.HFO", DATA_PATH, i);
// If the file exists, try again. Doesn't handle errors!
if(access(FileName, 0) == -1)
break;
}
// Open the file
f = fopen(FileName, "a");
// Check if open succeeds
if(f == NULL)
{
printf("<HEAD><TITLE>Error: cannot open file</TITLE></HEAD>\n");
printf("<BODY><H1>Error: cannot open file</H1>\n");
printf("The file %s could not be opened.\n",FileName);
printf("</BODY>\n");
exit(0);
}
// Write to the file the URL which posted the form data
whocalledme = getenv("REMOTE_ADDR");
fprintf(f, "URL=%s\n", whocalledme);
// Write to the file the date/time of this hit
_strdate(datebuf);
_strtime(timebuf);
fprintf(f, "Date=%s\n", datebuf);
fprintf(f, "Time=%s\n", timebuf);
// Get the length of the client input data
p = getenv("CONTENT_LENGTH");
if(p != NULL)
ContentLength = atoi(p);
else
ContentLength = 0;
// Avoid buffer overflow -- better to allocate dynamically
if(ContentLength > sizeof(InputBuffer) -1)
ContentLength = sizeof(InputBuffer) -1;
// Get the data from the client (assumes POST method)
i = 0;
while(i < ContentLength)
{
x = fgetc(stdin);
if(x == EOF)
break;
InputBuffer[i++] = x;
}
InputBuffer[i] = '\0';
ContentLength = i;
p = getenv("CONTENT_TYPE");
if(p == NULL)
{
fclose(f);
return(0);
}
if(strcmp(p, "application/x-www-form-urlencoded") == 0)
{
// Parse the data
p = strtok(InputBuffer, "&");
while(p != NULL)
{
// Write the field/value pair to the temp file
StoreField(f, p);
p = strtok(NULL, "&");
}
}
else
// Write the whole data to file
fprintf(f, "Input = %s\n", InputBuffer);
// Confirm to client
if(!ferror(f))
{
// What url called me
URL = getenv("HTTP_REFERER");
printf("<HEAD><TITLE>Submitted OK</TITLE></HEAD>\n");
printf("<BODY><h2>Your information has been accepted.");
printf(" Thank You!</h2>\n");
printf("<h3><A href=\"%s\">Return</a></h3></BODY>\n", URL);
}
else
{
// What url called me
URL = getenv("HTTP_REFERER");
printf("<HEAD><TITLE>Server file I/O error</TITLE></HEAD>\n");
printf("<BODY><h2>Your information could not be accepted\n");
printf("due to a file I/O error at the server.</h2>\n");
printf("<h3><A href=\"%s\">Return</a></h3></BODY>\n", URL);
}
// Close the file.
fclose(f);
// Invoke the Visual Basic program to update the database
sprintf(cmdbuf, "start cgi_vb.exe %s", FileName);
system(cmdbuf);
return(0);
}
The only thing that has significantly changed from the previously presented version of this program in Chapter 22 is the main() function. One of the first things that main() does is to invent a temporary filename. It tries to use CGI0.HFO. If that
filename exists, it will increment the number and try CGI1.HFO. This algorithm is very inefficient and doesn't check for errors, but it serves the purpose so you can focus on the interesting stuff.
The first item written to the temporary file is the URL from the REMOTE_ADDR environment variable. This tracks the client. The date a time follow that, on separate lines. All fields are on lines by themselves, and each field name is separated from its
corresponding data by an equals sign. You need to keep these things in mind when you write the VB program. The StoreField function has also been modified from the savedata version in Chapter 22 so that all lines are formatted similarly, with no spaces
surrounding the equals signs.
Unlike savedata.c, this program ignores some error-checking, and it blindly assumes that REQUEST_METHOD is POST.
The interesting thing about the code is that two output files are being written simultaneously. Remember, the CGI application is supposed to send some HTML output back to the browser so that the user won't get stranded on the Web. This is achieved by
the calls to printf, which write to stdout. The HTTP server will pick up the stdout data, apply the HTTP protocol, and send it back to the client.
Meanwhile, you still have to write the form data from the client into the temporary file before you launch the VB program. That is achieved by the fprintf calls.
At the end of the program, cgi_vb.exe is launched through the system() call in the C standard library.
Now you need the VB program to pick up the form data and insert it into the database. This process will execute fairly quickly, so you really don't need a user interface. In fact, you want the program to quit as soon as it's finished—with no user
involvement at all. Remember, this program is only going to run on the server. You can set the Visible property on the main form to False. You could accomplish the same thing by placing the code from Form_Load into Sub Main in a .BAS file and getting rid
of the .FRM file altogether. In fact, that would be slightly more efficient because it avoids the overhead of VB loading the form.
See Listing 23.3 for the code. This is the file cgi_vb.frm in the \cgi\vb directory on the CD. The VB 4.0 project file is cgi_vb.vbp.
This program was developed using the Visual Basic 4.0 Enterprise Edition. The program will also work "as is" with the 4.0 Professional Edition. With very minor changes, the program could be made to work
with the Visual Basic 3.0 Professional Edition.
Private Function ParseField(szText As String) As String
Dim k As Integer
k = InStr(szText, "=")
' Return the substring following the equals sign
ParseField = Mid$(szText, k + 1)
End Function
Private Sub Form_Load()
Dim szURL As String
Dim szDate As String
Dim szTime As String
Dim szName As String
Dim szEmail As String
Dim szComments As String
Dim db As Database
Dim rs As Recordset
' Open the temporary file with form data and read it into memory
Open Command$ For Input As #1
Line Input #1, szURL
Line Input #1, szDate
Line Input #1, szTime
Line Input #1, szName
Line Input #1, szEmail
Line Input #1, szComments
' Close the temporary file and delete it
Close #1
Kill Command$
' Open the database and the table
Set db = OpenDatabase(App.Path & "\cgi_vb.mdb")
Set rs = db.OpenRecordset("table1", dbOpenTable)
' Add a new record to the table. Counter field is
' initialized automatically by Jet 3.0.
rs.AddNew
rs!When = ParseField(szDate) & " " & ParseField(szTime)
rs!URL = ParseField(szURL)
rs!Name = ParseField(szName)
rs!Email = ParseField(szEmail)
rs!Comments = ParseField(szComments)
' Update the table, close everything and quit
rs.Update
rs.Close
db.Close
End
End Sub
Almost all of the code is in the Form_Load event. The project only includes one form. The command$ statement is used to retrieve the name of the temporary file passed in by the C++ CGI program.
Note that it assumes the order of the fields in the text file written by the C++ program. This isn't robust, but again, that isn't our point. If you want to modify the HTML feedback form, you will also have to modify the VB code. The C++ should be able
to survive without modification if new fields are added to the HTML file, the VB code, and the database.
The temporary input file from the C++ CGI program is deleted as soon as we are through reading it. Then the database is opened, and a recordset is created using the dbOpenTable parameter. This will permit you to write to the table. The table is called
table1 (until you change it).
The fields in the table correspond to the data you capture on the form, as well as the URL of the client and the date/time the submission was made to the server. For each field, call the ParseField function to retrieve the substring following the equals
sign.
If you designate a key field to use AutoIncrement in Data Manager, the Jet 3.0 engine will automatically take care of incrementing it for you. Therefore, you don't need to supply the value of the key field between the calls to AddNew and Update. See
Figure 23.1, which shows the Edit Field dialog in Data Manager 32. Note the check box that is labeled "Counter." Only one such field per table should have that value turned on. It is only available for Long Integer type fields.
The database is named cgi_vb.mdb. We assume that it lives in the same directory as the Visual Basic program itself. By the way, depending on your server, the C++ program probably needs to live in the cgi-bin directory. The VB program is not invoked with
a full path name, so it too should probably go in the cgi-bin directory.
The database is also in the \cgi\vb directory on the CD. We suggest that you copy it and the rest of the \cgi\vb directory to a location on your hard drive where you can experiment with it.
You can open the database with Access 7.0 or the VisData and Data Manager utility programs that come with the VB 4.0 Professional and Enterprise editions. This chapter won't go into the steps to create the database because that information is readily
available in the product documentation for Access and Data Manager.
Field Name |
Data Type |
|
Counter |
Long (primary key, required unique) |
|
URL |
Text, length 40 |
|
When |
DateTime 8-byte variant datatype |
|
Name |
Text, length 40 |
|
|
Text, length 40 |
|
|
|
The field named Counter is the key field and is also marked as a Counter type in Data Manager. This means that you don't need to calculate unique values for it, because VB will do this automatically when you execute rs.Update. See Figure 23.2 for the
Edit Index dialog showing the fields of our CGI table.
Figure 23.2. The Edit Index dialog in Data Manager 32 showing the primary key field.
The program lacks several important error-checking features. For example, no check is made to ensure that the supplied text does not exceed the field sizes. The field sizes in the database should match the MAX attribute of the TEXT input fields in the
HTML file. The VB program should probably be written to truncate longer text just in case the two sizes should ever become mismatched. As it stands now, the VB program would crash if the input text exceeded the database field size.
Most database tasks can be accomplished with just four simple SQL statements:
What follows are several short and sweet examples of each type of statement. In each case, it is not important to type in uppercase, but the SQL keywords are shown in uppercase for readability. The terms table1, field1, and field2 represent placeholders
for the actual object names that you would use in your database.
Here are examples of the SELECT statement:
The asterisk wildcard character is specific to the Access dialect of SQL. Standard SQL uses the percent (%) character instead.
Now that you know several varieties of SELECT, the DELETE statement is easy. Here is an example:
DELETE FROM table1 WHERE field1 = 24
This example deletes all rows from the selected table that match the optional WHERE clause.
The UPDATE statement is a little trickier. It can be used to modify one or more rows of a selected table.
UPDATE table1 SET field1 = 99 WHERE field1 = 44
This statement will change the data in the field1 column for all rows that currently have a field1 value of 44. Other field=value pairs may be included as long as they are separated by commas.
The INSERT statement is also a little different from the others. Whereas the other statements operate on existing data, INSERT is used to add a new record:
INSERT INTO table1 (field1, field2) VALUES (99, 'abc')
Assuming that field1 and field2 are the only required fields, this statement will create a new row in the selected table. It is very important that the order of the values matches the order of the selected column names. For example, 99 will be placed in
field1 and 'abc' will be placed in field2.
Unless you've been under a rock on the Internet, you can't have gone the past few months without having heard about Java, HotJava, or JavaScript. The next chapter is going to show you why Java is so important to the future of the Web. It will also develop a sample Java application that you can take advantage of on your Web site today.
