IEEM 230
Industrial Data systems
Basically, there are three things that need to be understood in order to allow Database transactions using a CGI. In this lab, you will get familiar with one way of doing each of these three.
You have some information on a web site. You have some more information in a database (in our case, SYBASE). When a user connects to your web-site, they can see all the information on the web-site (web server). But what if they want to see some information which you store on SYBASE ?
First of all, they must tell you what information they need to get from the DB server !
This is done by use of a form. Forms allow several different methods of getting some input from a user. These include selection from ampng several choices (using radio buttons), text inputs, etc. Examples of the most popular form input methods are shown in this example. If you want to write a form with similar choices, you can easily include the <FORM>... </FORM> structure inside any HTML document. If you look at the document source for the example, you can see how to write your own forms.
The PERL script that gets executed to generate the output for this form is shown here.
Every form must have a "SUBMIT" button. When the visitor to your web site presses the "SUBMIT" button, they send some information to your web server. It is now your job to read this information, and act accordingly. So how do you read this information ?
Each FORM also indicates a CGI-program that must be executed when the FORM is submitted. As you know, the CGI program must be placed in the correct directory, for the web browser to allow it to be executed. In our case, we follow the CCST guidelines about where to put our cgi programs.
You can write the CGI program in any programming language you like. Since we are all experts in using PERL, we shall use that language. It is the job of the CGI program to read the inputs sent to your server by the visitor. The format in which the data is received by your CGI program depends on the METHOD of submission you specify inside your <FORM>... statement.
If you indicate: METHOD = "GET", then the data is received as part of a UNIX environment variable, called QUERY_STRING.
How do you read what's in this variable ?
In PERL, you have several ways of
doing this. You could use the system function, with the argument
"printenv QUERY_STRING" . Remember that in UNIX, you can get the value
of any environment variable by using the function printenv. Alternatively, you
can use the PERL function to read an environment variable:
$InputString = $ENV{'QUERY_STRING'};
Which reads the value of the environment variable 'QUERY_STRING' into a perl character string variable called InputString.
But what if you had indicated: METHOD = "POST" in your FROM ?
In this case, the inputs form the user are sent to the standard input, just as if they were being typed by the user at your keyboard. You can directly use the read (STDIN, ...) function of perl to read the inputs.
Simple !! But you have to be careful: you don't know how much data the user is sending you (especially if they are using a variable length text record in the FORM. Therefore, it is important for your CGI program to know when to stop reading infomration from the STDIN. This problem is solvable, since when the FORM gets SUBMITted, the visitor's browser also send the total length of the entire submitted data to you, in an environemnt variable called CONTENT_LENGTH . You should first read the value of this variable, and then read the inputs from STDIN unitl you have read CONTENT_LENGTH characters.
As with most programs, all this can be made easy to learn if we have an example. In fact, here are PERL subroutines which can help you to read and parse the data from FORMS of either type !
And finally, if we can use the user infomration to link with the SYBASE
database, we are done !
PERL can link to SYBASE by using a special set of
functions, which are provided to you through a special program called
sybperl . SYBPERL contains all of perl, plus SYBASE linking functions.
The following example will show you how SYBPERL can be used to:
(a) Connect to a SYBASE server;
(b) Send some SQL commands to the SYBASE server;
(c) How to receive the output from the SQL command;
and finally
(d) How to compose it all into a nice, HTML format that can be displayed as the result of submitting the form, to the visitor of your web-site.
First a form to get some infomration.
When you submit the form, a CGI-script similar to this one gets executed.
Notes:
1. Note that if you are connecting to SYBASE, your CGI program must be written
in sybperl , not perl. The first line is:
#!/usr/local/sybase/sybperl
2. Within sybperl, you can use all perl functions, as well as the methods shown to connect to Sybase, and run queries in Sybase.
3. Notice that a new type of data structure is used by the subroutine ReadParse . It is an associative list, or a hash list, which is like a 2-column array. The keyword of the first column has the value given by the second column. This is a very nice way to refer to inputs that are identified by names (such as SID, age, etc).
4. My CGI-script assumes the existence of a table (in my account) of a table called ajtest which has the following attributes: name, age, sid. You must, of course, replace this with your own table names and attribute names.