#! C:/perl/bin -w
use diagnostics;
use CGI;
use HTML::Template;
use DBI;
#Creating Object
$query=new CGI;
$template= HTML::Template->new(filename => 'TechSupportResults.tmpl');
#Setting content type
print $query->header('text/html');
#Retrieving values for query
(A user can search on CustomerName, Date of Submission, Priority or Ticket No. If any value is not selected, the default value is "*" )
$customer = $query->param('CustomerList');
$createMonth = $query->param('ProblemMonth');
$createDay = $query->param('ProblemDay');
$createYear = $query->param('ProblemYear');
$date = $createMonth."/".$createDay."/".$createYear;
$priority = $query->param('Priority');
$ticket = $query->param('TicketNo');
retrieveFormData();
fetchData();
displayResults();
(You can ignore this function. This populates the CUStomer drop down in the search form. Has no bearing on my problem but I still left it here just in case..)
sub retrieveFormData
{
my $data_string = "dbi:ODBC:TSCenter";
my $username = "";
my $password = "";
connectDB( $data_string , $username , $password );
my $arrayRef = $dbh->selectcol_arrayref('SELECT Customer FROM CustomerListTable ORDER BY Customer');
my @loop_data = ();
foreach $eachCustomer (@{$arrayRef})
{
my %customers = (
CUSTOMER => $eachCustomer,
);
push (@loop_data, \%customers);
}
$template -> param(CUSTOMERLIST => \@loop_data);
} # of retrieveFormData
(This is where the problem is....specific comments included.)
sub fetchData
{
my $SQL;
my $quotedString1 = $dbh->quote("$customer");
my $quotedString2 = $dbh->quote("$priority");
my $quotedString3 = $dbh->quote("$ticket");
my $quotedString4 = $dbh->quote("$date");
my $temp1 = "SELECT TicketNo, Customer, UserName, DateSubmission, Via, Issues, SupportType, Description, Detail, AssignTo, Status, Remarks, Priority, ETD ";
my $temp2 = "FROM TSIssuesTable ";
my $temp3 = "WHERE ";
my $temp4 = "Customer LIKE $quotedString1 AND Priority LIKE $quotedString2 AND TicketNo LIKE $quotedString3 AND DateSubmission LIKE $quotedString4";
$SQL = $temp1.$temp2.$temp3.$temp4;
print $SQL; (Flow is fine till here. This statement prints - SELECT TicketNo, Customer, UserName, DateSubmission, Via, Issues, SupportType, Description, Detail, AssignTo, Status, Remarks, Priority, ETD FROM TSIssuesTable WHERE Customer LIKE 'Support Concepts' AND Priority LIKE '*' AND TicketNo LIKE '*' AND DateSubmission LIKE '*/*/*'-When I copy and run this query in MS Access a record is returned.)
my $rows = $dbh->selectall_arrayref($SQL,{Columns =>{}}) ;
print $rows; (FLow is fine here too-this statement prints ARRAY(0x1ed0bb0) )
foreach (@$rows)
{
print "I say $_\n"; (Problem here-no output on screen.)
}
$template->param(ROWS => $rows || []);
disconnectDB();
} # of fetchData
sub displayResults
{
#Printing information
print $template->output;
} # of displayResultScreen
sub connectDB
{
my $data_string = $_[0];
my $username = $_[1];
my $password = $_[2];
$dbh = DBI->connect( $data_string , $username , $password ) or die "Could not connect to database";
}# of connectDB
sub disconnectDB
{
$dbh->disconnect();
}# of disconnectDB
####
Search
(Including the search bar. This is succesfully displayed in the output after the script is run)
Results
(The header row for results-also displayed in the output-You can skip thru this)
Ticket #
Reseller
End User Name
Reported On
Reported By
Product
Support Type
Brief Description
Details
Assigned To
Issue Status
Remarks
Priority
Closure Date
(Problem here-this row -for displaying DB-retrieved data is not displayed)