#!c:\Perl\Perl5.00402\bin\perl.exe

	# You change the line above!  And by the way
	# you should call this script as
	# http://localhost/cgi-bin/dbi_test.cgi?tableName=[insertYourTableNameHere]
	# Also, please note that this script is not
	# as parsimonious as I could make it.  It is meant only
	# to show examples of design for the DBI tutorial.
	# If you were to use this code in real life, you
	# should clean it up.

use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser);

	# You change the following 4 lines

$thisScript = "dbi_test.cgi";
$dbName     = "DBI:ODBC:MyCompany";
$dbUserName = "";
$dbPassword = "";

	# You change the table definitions to suit
	# your own tables.  And then leave the rest 
	# alone.  That is all the customization 
	# that need be done.

%tableDefinitions = ("Employees" => ["EmployeeID", "LastName", "FirstName"],
 		     "Customers" => ["CustomerID", "FirstName", "LastName", "Email"]);

@administrativeParameters = ("requestType", "tableName");

$dataIn       = new CGI;
$requestType  = $dataIn->param('requestType');
$tableName    = $dataIn->param('tableName');

@tableNames = keys(%tableDefinitions);
@tableColumns = getColumnNamesForTable($tableName, \%tableDefinitions);

$dbh = &connectToDB();

if ($requestType eq "")
	{
	print &getHTMLPageHeader("Database Search", "FFFFFF", "000000");
	print &getFormHeader("POST", $thisScript);
	print &getSearchForm();
	print &getSearchFormFooter();
	print &getPageFooter();
	&closeDBConnection();
	exit;
	}

elsif ($requestType eq "Change Table")
	{
	print &getHTMLPageHeader("Database Search: Table Chnaged to $tableName", "FFFFFF", "000000");
	print &getFormHeader("POST", $thisScript);
	print &getSearchForm();
	print &getSearchFormFooter();
	print &getPageFooter();
	&closeDBConnection();
	exit;
	}

elsif ($requestType eq "Submit Search")
	{
	print &getHTMLPageHeader("Database Search Results", "FFFFFF", "000000");
	print &getFormHeader("POST", $thisScript);
	&executeSelect();
	print &getFormattedSearchResults();
	print &getSearchForm();
	print &getSearchFormFooter();
	print &getPageFooter();
	&closeDBConnection();
	exit;
	}

elsif ($requestType eq "Delete Selected")
	{
	print &getHTMLPageHeader("Database Search Results: Post Deletion", "FFFFFF", "000000");
	print &getFormHeader("POST", $thisScript);
	&executeDelete();
	&executeSelect();
	print &getFormattedSearchResults();
	print &getSearchForm();
	print &getSearchFormFooter();
	print &getPageFooter();
	&closeDBConnection();
	exit;
	}

elsif ($requestType eq "Perform Insert")
	{
	print &getHTMLPageHeader("Database Search Results: Post Insertion", "FFFFFF", "000000");
	print &getFormHeader("POST", $thisScript);
	&executeInsert();
	&executeSelect();
	print &getFormattedSearchResults();
	print &getSearchForm();
	print &getSearchFormFooter();
	print &getPageFooter();
	&closeDBConnection();
	exit;
	}

elsif ($requestType eq "Modify Selected")
	{
	print &getHTMLPageHeader("Database Search Results: Post Update", "FFFFFF", "000000");
	print &getFormHeader("POST", $thisScript);
	&executeUpdate();
	&executeSelect();
	print &getFormattedSearchResults();
	print &getSearchForm();
	print &getSearchFormFooter();
	print &getPageFooter();
	&closeDBConnection();
	exit;
	}

#################################################################
#				executeSelect()			#
#################################################################

sub executeSelect
	{
	my ($whereClause, $html);

	foreach $param ($dataIn->param())
		{
		if (!isItAnAdminParameter($param) &&
		    $dataIn->param($param) ne "" &&
		    $dataIn->param($param) ne "on")
			{
			if ($dataIn->param($param) =~ /[a-zA-Z]/)
				{
				$whereClause .= qq! $param LIKE '%!; 
				$whereClause .= $dataIn->param($param);
				$whereClause .= qq!%' AND!;
				}
			else
				{
				$whereClause .= qq! $param = !; 
				$whereClause .= $dataIn->param($param);
				$whereClause .= qq! AND!;
				}
			}
		}
				# Remove trailing "AND"

		chop $whereClause;
		chop $whereClause;
		chop $whereClause;

	if ($whereClause eq "")
		{
		$sqlStatement= qq!SELECT * FROM $tableName;!;
		}
	else
		{
		$sqlStatement= qq!SELECT * FROM $tableName WHERE $whereClause;!;
		}

	$dataObject = &executeSQLStatement($sqlStatement);
	@dbRows = &getDBRows($dataObject);
	}

#################################################################
#				executeDelete()			#
#################################################################

sub executeDelete
	{
	my ($whereClause, $html);

	foreach $param ($dataIn->param())
		{
		if (!isItAnAdminParameter($param) &&
		    $dataIn->param($param) ne "")
			{
			@columnValues = split(/\|/, $param);
			for ($i=0;$i<@columnValues;$i++)
				{
				if (@columnValues[$i] =~ /[a-zA-Z]/)
					{
					$whereClause = qq! @tableColumns[$i] = '@columnValues[$i]' !; 
					push (@whereClauseFragments, $whereClause);
					}
				else
					{
					$whereClause = qq! @tableColumns[$i] = @columnValues[$i] !; 
					push (@whereClauseFragments, $whereClause);
					}
				}
			}
		}

	$joinString = "AND";
	$whereClause = join($joinString, @whereClauseFragments);
	$sqlStatement= qq!DELETE FROM $tableName WHERE $whereClause;!;
	$dataObject = &executeSQLStatement($sqlStatement);
	}

#################################################################
#				executeInsert()			#
#################################################################

sub executeInsert
	{
	my ($insertClause, $html);

	$insertClause = "(";
	$insertClause .= join(",", @tableColumns);
	$insertClause .= ") VALUES (";

	foreach $param ($dataIn->param())
		{
		if (!isItAnAdminParameter($param) &&
		    $dataIn->param($param) ne "")
			{
			if ( $dataIn->param($param) =~ /[a-zA-Z]/)
				{
				$insertClause .= qq!'!;
				$insertClause .= $dataIn->param($param);
				$insertClause .= "',";
				}
			else
				{
				$insertClause .= $dataIn->param($param);
				$insertClause .= ",";
				}		
			}
		}
	chop $insertClause;
	$insertClause .= ");";
	$sqlStatement= qq!INSERT INTO $tableName $insertClause!;
	$dataObject = &executeSQLStatement($sqlStatement);
	}

#################################################################
#				executeUpdate()			#
#################################################################

sub executeUpdate
	{
	my ($setClause, $whereClause, $html);

	foreach $param ($dataIn->param())
		{
		if (!isItAnAdminParameter($param) &&
		    $dataIn->param($param) ne "")
			{
			@columnValues = split(/\|/, $param);

			if (@columnValues <= 1)
				{
				if ($dataIn->param($param) =~ /[a-zA-Z]/)
					{
					$setClause .= "$param = '";
					$setClause .= $dataIn->param($param);
					$setClause .= "',";
					}
				else
					{
					$setClause .= "$param = ";
					$setClause .= $dataIn->param($param);
					$setClause .= ",";
					}
				}
			else
				{
				for ($i=0;$i<@columnValues;$i++)
					{
					if (@columnValues[$i] =~ /[a-zA-Z]/)
						{
						$whereClause = qq! @tableColumns[$i] = '@columnValues[$i]' !; 
						push (@whereClauseFragments, $whereClause);
						}
					else
						{
						$whereClause = qq! @tableColumns[$i] = @columnValues[$i] !; 
						push (@whereClauseFragments, $whereClause);
						}
					}
				}
			}
		}
	chop $setClause;
	$joinString = "AND";
	$whereClause = join($joinString, @whereClauseFragments);

	if ($whereClause eq "")
		{
		print "You must select a row to modify!";
		exit;
		}

	$sqlStatement= qq!UPDATE $tableName SET $setClause WHERE $whereClause!;
	$dataObject = &executeSQLStatement($sqlStatement);
	}

#################################################################
#			getFormattedSearchResults()		#
#################################################################

sub getFormattedSearchResults
	{
	$html = qq!
	<P>
	<CENTER>
	<TABLE BORDER = "1">
	<TR>
	<TH>Select</TH>!;

	foreach $column (@tableColumns)
 		{ 
		$html .= qq!
		<TH>$column</TH>!;
		}
	$html .= qq!
	</TR>!;

	foreach $rowReference (@dbRows)
		{
		foreach $columnReference (@$rowReference)
			{
			$nameValue = "";
			$columnHtml = "";
			foreach $column (@$columnReference)
				{
				$columnHtml .= qq!<TD>$column</TD>\n!;
				$nameValue .= qq!$column\|!;
				}
			$columnHtml .= qq!</TR>!;
			chop $nameHtml;
			$html .= qq!<TR><TD><INPUT TYPE = "RADIO" NAME = "$nameValue"></TD>!; 
			$html .= $columnHtml;
			}
		}

	$html .= qq!
	</TABLE>
	</CENTER>
	<P>!;
	return $html;
	}

#################################################################
#			connectToDB()				#
#################################################################

sub connectToDB
	{
	return (DBI->connect($dbName, $dbUserName, $dbPassword));
	}

#################################################################
#			closeDBConnection()			#
#################################################################

sub closeDBConnection
	{
	$dbh->disconnect();
	}

#################################################################
#			executeSQLStatement()			#
#################################################################

sub executeSQLStatement
	{
	my ($sql) = shift;
	$dataObject = $dbh->prepare($sql);
	$dataObject->execute() || die $dataObject->errstr;
	return $dataObject;
	}

################################################################
#			getDBRows()				#
#################################################################

sub getDBRows
	{
	my ($dataObject) = shift;
	return $dataObject->fetchall_arrayref();
	}

sub getColumnNamesForTable
	{
	my ($tableName) = shift;
	my ($tableDefinitionsReference) = shift;
	my (@columns, %tableDefinitions);
	%tableDefinitions = %$tableDefinitionsReference;
	foreach $table (@tableNames)
		{
		if ($table eq $tableName)
			{
			$columnRefs = (%tableDefinitions->{$table});
			foreach $column (@$columnRefs)
				{
				push(@columns, $column);
				}
			}
		}
	return @columns;
	}

sub getHTMLPageHeader
	{
	my ($title) = shift;
	my ($bgColor) = shift;
	my ($textColor) = shift;
	my ($backgroundImage) = shift;
	return qq!
	<HTML>
	<HEAD>
	<TITLE>$title</TITLE>
	</HEAD>
	<BODY BGCOLOR = "$bgColor" TEXT = "$textColor">!;
	}

sub getFormHeader
	{
	my ($methodType) = shift;
	my ($actionType) = shift;
	return qq!
	<FORM METHOD = "$methodType" ACTION = "$actionType">!;
	}

sub getSearchForm
	{
	my ($html);
	$html = qq!
	<P>
	<CENTER>
	<TABLE BORDER = "1">

	<TR>
	<TH>Table</TH>
	<TD><SELECT NAME = "tableName">!;

	foreach $table (@tableNames)
		{
		if ($table eq $tableName)
			{
			$html .= qq!<OPTION SELECTED>$table!;
			}
		else
			{
			$html .= qq!<OPTION>$table!;
			}
		}

	$html .= qq!
	</SELECT>
	</TD>
	<TD><INPUT TYPE = "SUBMIT" NAME = "requestType" VALUE = "Change Table"></TD>
	</TR>
	<TR>
	<TD COLSPAN = "3">&nbsp</TD>
	</TR>
	<TR>
	<TH>Column Name</TH>
	<TH COLSPAN = "2">Input One or More Search Criteria</TH>
	</TR>!;

	foreach $column (@tableColumns)
 		{ 
		$html .= qq!
		<TR>
		<TH>$column</TH>
		<TD COLSPAN = "2"><INPUT TYPE = "TEXT" SIZE = "40" NAME = "$column"></TD>
		</TR>!;
		}

	$html .= qq!
	</TABLE>
	</CENTER>
	<P>!;
	return $html;
	}

sub getSearchFormFooter
	{
	if ($requestType eq "" || $requestType eq "Change Table")
		{
		return qq!
		<P>
		<CENTER>
		<INPUT TYPE = "HIDDEN" NAME = "tableName" VALUE = "$tableName">
		<INPUT TYPE = "SUBMIT" NAME = "requestType" VALUE = "Submit Search" >
		</CENTER>
		<P>
		</FORM>!;
		}
	else
		{
		return qq!
		<P>
		<CENTER>
		<INPUT TYPE = "HIDDEN" NAME = "tableName" VALUE = "$tableName">
		<INPUT TYPE = "SUBMIT" NAME = "requestType" VALUE = "Delete Selected">
		<INPUT TYPE = "SUBMIT" NAME = "requestType" VALUE = "Modify Selected">
		<INPUT TYPE = "SUBMIT" NAME = "requestType" VALUE = "Perform Insert">
		<INPUT TYPE = "SUBMIT" NAME = "requestType" VALUE = "Submit Search">
		</CENTER>
		<P>
		</FORM>!;
		}
	}

sub getPageFooter
	{
	return qq!
	</BODY>
	</HTML>!;
	}

sub isItAnAdminParameter
	{
	my ($param) = shift;
	foreach $parameter (@administrativeParameters)
		{
		if ($param eq $parameter)
			{
			return 1;
			}
		}
	return 0;
	}