Using Perl CGI with MS-Access on IIS

Though I was working on Perl for the past 1.5 years I was unaware of a few simple permission settings that may be required on a Windows machine with IIS to ensure that a Perl CGI program interacting with MS-Access Database with the help of DBI package perfectly works.

Let us make a test case. We will require two files -

1. main.html - a simple html form
2. update.pl - Perl script that would be called once you submit the html form. This script would update the data into an MS-Access database - emp.mdb

main.html

<form id="form1" name="form1" method="post" action="update.pl">
<p>Employee Id:
<input name="emp_id" type="text" id="emp_id" />
</p>
<p>Employee Name:
<input name="emp_name" type="text" id="emp_name" />
</p>
<p>E-mail:
<input name="mail_id" type="text" id="mail_id" />
</p>
<p>
<input type="submit" name="Submit" value="Update" />
</p>
</form>

The html form as you can make out is a small data entry form with three text fields for filling up the details like employee id, name, email address and once you enter all these details, you click on the submit button.

update.pl

use CGI qw(:standard);
use DBI;

sub validEmpId
{
print "Validating Employee id";
if($emp_id !~ m/\d*/)
{
return false;
}
else{
return true;
}

}
sub validMailId
{
print "validating mail id";
if($mail_id =~ m/\w+\@\w+\.com/)
{
return true;;
}
else
{
return false;
}
}
$emp_id = param("emp_id");
$emp_name = param("emp_name");
$mail_id = param("mail_id");

if($emp_id =~ m/[a-z A-Z]/)
{
exit;
}
elsif($mail_id =~ m/\w+\@\w+\.com/){
##store it in the database
$dbh = DBI->connect("DBI:ODBC:emp");
$sth = $dbh->prepare("Insert into emp_data(Id,Name,Mail_ID) values($emp_id,'$emp_name','$mail_id')");
$sth->execute();
$sth->finish();
$dbh->disconnect();
}
else{
exit;
}

The data entered through the html form, is passed on to the update.pl Perl script which inserts the data into the database. You could also use the sub-routines of the program for some validations like ensuring the Employee ID is a number and the mail ID is in a correct format etc., and then insert the data into the MS-Access database.

Settings:

1. The html file, the Perl script file and the emp.mdb (MS Access database) resides in a folder.
2. Appropriate DBI/ODBC Perl packages are available.
3. Appropriate ODBC DSN (Data Source Name) called “emp” is created , connecting to the MS-Access Database called “emp.mdb”, which has a table called emp_data which is supposed to store the entered employee details. (Done using the “Data Sources(ODBC)” option under the Control Panel -› Administrator Tools)
4. Also created a virtual directory on the IIS and linked to the physical directory where the html and the Perl script resides (Eg.: A virtual directory called “ss” was created which was linking to the actual physical directory where the html, Perl and the mdb files were residing) (Done using the “Internet Information Services” option under the Control Panel -› Administrator Tools)
   

How the program is supposed to behave: Once the main.html file is accessed through the browser, the Data Entry Form is to be shown. Once all the data is entered into the text fields and form submitted, the data is supposed to be passed to the Perl script file (update.pl) which would in turn update the data into the corresponding database.

How was it actually behaving: The form shows up properly, but when the data was entered and submitted, it throws up an error, like:

CGI Error

The specified CGI application misbehaved by not returning a complete set of HTTP headers. The headers it did return are:
DBI connect('emp','',...) failed: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL-IM002)(DBD: db_login/SQLConnect err=-1) at C:\mini-project\store.pl line 40
Can't call method "prepare" on an undefined value at C:\mini-project\store.pl line 41.

Solution:

• The physical directory where you store all your files and then link it up with the virtual directory in IIS has to have some specific permissions settings. User called IUSR_, IWAM_ have to be given full permissions (at least read and write). (right click on the folder -> properties -> security tab -> add -> advanced -> find now -> choose the user earlier mentioned and give them full/appropriate read and write access)
• Then in the data source name that we create in the ODBC, we need to create it as "system DSN" instead of "user DSN" and then give enough login and password details if required. For MS Access the default user is “admin” and password is NULL password.