IEEM 230

Industrial Data systems

 

CGI's: How to connect to a Database ?

 

In this lab, we look at how to connect the internet browser (e.g. Internet Explorer, or Netscape Navigator) to a Database system. In our case, we shall use the most popular architecture for such connectivity: we shall make the web server to be the same computer as our Database client. Thus, we need two things.
  1. A mechanism for data tranfers between our web client computer and the web server.
    Method we shall use: FORMs and CGI programs
  2. How to connect our CGI programs to the Database server, and to exchange information with the DB server. "Exchange" here means:
    1. The ability to log into our Database server account;
    2. The ability to send an SQL query to the DB server; and
    3. To receive the result of our query (a table) back into our CGI program.
    Method we shall use: Perl function library called sybperl

PERL can link to SYBASE by using a special set of functions, which are provided to you through 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 information. Note that this form cannot execute. You may copy its source into your public_html directory, and modify the "ACTION=..." portion to make it work.

Next, you need to write a SYBPERL script NOTE: the script is in sybperl, not perl which is the CGI program. 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 use 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. My CGI-script assumes the existence of a table (in my account) of a table called EMPLOYEE which has the following attributes: Lname and SSN. There may be more attributes, of course! You must replace these names with your own table names and attribute names.

Exercise

1. Write forms and scripts to manage the Works_on data.

Make a web-page with three links as follows. Each link will lead to a web-form whose details are described below.
(i) Report work assignment of Employee.
The form takes one input: the SSN of the empoyee, and reports back a list with the employee's projects and the number of hours per week they spend on it.
(ii) Assign employee to project.
The form takes three inputs: SSN, Project Number and Hours. It creates a new record in the Works_ON table according to the data submitted by the form.
(iii) Remove employee from project. [this question may be submitted to TA by email]
This form will take one input: The employee's SSN. It uses the SSN to generate a new form, with a bullet list of all projects (and hours) that this employee works. Based on the selection in the bullet-list, another script is executed, which will remove the selected project record of this employee from the Works_ON table.