thisisperl has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to display retrived DB records in a template (using <TMPL_LOOP>). I have run the query returned by my script in MS Access and it returns a record. However, as part of the script, both from the command line and browser, the retrieved record is not displayed in the template. Other stuff in the template is displayed but not the retrived record. Here's the code with explanations in parenthesis:
Script 
#! 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 Ti
+cket 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 i
+n the search form. Has no bearing on my problem but I still left it h
+ere 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 Cust
+omerListTable 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, Rema
+rks, Priority, ETD ";
    my $temp2 = "FROM TSIssuesTable ";
    my $temp3 = "WHERE ";
    my $temp4 = "Customer LIKE $quotedString1 AND Priority LIKE $quote
+dString2 AND TicketNo LIKE $quotedString3 AND DateSubmission LIKE $qu
+otedString4";
    
        
    $SQL = $temp1.$temp2.$temp3.$temp4;
    print $SQL; (Flow is fine till here. This statement prints - SELEC
+T TicketNo, Customer, UserName, DateSubmission, Via, Issues, SupportT
+ype, Description, Detail, AssignTo, Status, Remarks, Priority, ETD FR
+OM 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; <font color="red">(FLow is fine here too-this stateme
+nt prints ARRAY(0x1ed0bb0) )</font>
    
    
    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

[download]
The template: 
<html>

<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-12
+52">
<title>Search</title>
<link rel="stylesheet" type="text/css" href="./ss.css">
<script language="JavaScript" type="text/javascript" src="../TechSuppo
+rtScripts.js"></script>
</head>

<body>
(Including the search bar. This is succesfully displayed in the output
+ after the script is run)
<!-- TMPL_INCLUDE NAME = "SearchTechSupport.tmpl" -->

<p><b>Results</b></p>
<table border="1" >
(The header row for results-also displayed in the output-You can skip 
+thru this)
  <tr>
    <td>
    <b><font>Ticket #</font></b></td>
    <td>
    <font><b>Reseller</b></font></td>
    <td>
    <b><font>End User Name</font></b></td>
    <td>
    <font><b>Reported On</b></font></td>
    <td>
    <font><b>Reported By</b></font></td>
    <td>
    <font><b>Product</b></font></td>
    <td>
    <font><b>Support Type</b></font></td>
    <td>
    <font><b>Brief Description</b></font></td>
    <td>
    <font><b>Details</b></font></td>
    <td>
    <font><b>Assigned To</b></font></td>
    <td>
    <font><b>Issue Status</b></font></td>
    <td>
    <font><b>Remarks</b></font></td>
    <td>
    <font><b>Priority</b></font></td>
    <td>
    <font><b>Closure Date</b></font></td>
  </tr>
(Problem here-this row -for displaying DB-retrieved data is not displa
+yed)
  <!-- TMPL_LOOP NAME=ROWS -->
  <tr>
    <td>
    <!-- TMPL_VAR NAME=TicketNo --></td>
    <td>
    <!-- TMPL_VAR NAME=Customer --></td>
    <td>
    <!-- TMPL_VAR NAME=UserName --></td>
    <td>
    <!-- TMPL_VAR NAME=DateSubmission --></td>
    <td>
    <!-- TMPL_VAR NAME=Via --></td>
    <td>
    <!-- TMPL_VAR NAME=Issues --></td>
    <td>
    <!-- TMPL_VAR NAME=SupportType --></td>
    <td>
    <!-- TMPL_VAR NAME=Description --></td>
    <td>
    <!-- TMPL_VAR NAME=Detail --></td>
    <td>
    <!-- TMPL_VAR NAME=AssignTo --></td>
    <td>
    <!-- TMPL_VAR NAME=Status --></td>
    <td>
    <!-- TMPL_VAR NAME=Remarks --></td>
    <td>
    <!-- TMPL_VAR NAME=Priority --></td>
    <td>
    <!-- TMPL_VAR NAME=ETD --></td>
  </tr>
  <!-- /TMPL_LOOP -->
</table>
</body>
</html>

[download]
I am posting all of my code bcoz I have been on this problem for sometime-tried another forum but have not got a response-so I thought I might as well provide all details.

Replies are listed 'Best First'.
Re: HTML::Template-Displaying DB Records
by perrin (Chancellor) on May 25, 2004 at 04:13 UTC
    Simplify. You seem to have debugged it down to one section of code involving a database fetch. Take that, run it on its own, and debug it until it works. The use of selectall_arrayref with the Columns => {} attribute looks suspicious to me. I would fiddle with that.

    You didn't ask for general comments, but there are at least a couple of things in this script that will jump out at any experienced coder. The first is the lack of "use strict" at the top. That is a must. The second is that you are not using bind parameters with your SQL statements. You really should, since it is simpler and safer than all of the quoting you have right now. Kudos for using a template though. That's a step in the right direction.

[reply]
      Alright.... I know my template code is fine. I tried: 
      my $rows=[
    #            {
    #                TicketNo => 1,
    #                Customer => "Foo",
    #                UserName => "Foo",
    #                DateSubmission =>"Foo",
    #                Via =>"Foo",
    #                Medium =>"Foo",
    #                Issues =>"Foo",
    #                SupportType =>"Foo",
    #                Description =>"Foo",
    #                Detail =>"Foo",
    ##                AssignTo =>"Foo",
    #                Status =>"Foo",
    #                Remarks =>"Foo",
    #                Priority =>"Foo",
    #                ETD =>"Foo",
    #                ClosedDate =>"Foo",
    #                ClosedName =>"Foo"
    ##            },
    ##            {
    #                TicketNo => 2,
    #                Customer => "Blah",
    #                UserName => "Blah",
    #                DateSubmission => "Blah",
    #                Via => "Blah",
    #                Medium => "Blah",
    #                Issues => "Blah",
    #                SupportType => "Blah",
    #                Description => "Blah",
    #                Detail => "Blah",
    #                AssignTo => "Blah",
    #                Status => "Blah",
    #                Remarks => "Blah",
    #                Priority => "Blah",
    #                ETD => "Blah",
    #                ClosedDate => "Blah",
    #                ClosedName => "Blah"
    #            }
    #                
    #        ];
    
    #$template->param(ROWS => $rows || []);

      [download]
      And the rows displayed in the template. The problems seems to be with selectall_arrayref. But when I print $rows, an address is displayed-I just think $rows is empty or something. I cannot for the life of me figure out how to test selectall_arrayref. And yes I tried: 
      my $rows = $dbh->selectall_arrayref("SELECT TicketNo, Customer, UserNa
+me, DateSubmission, Via, Medium, Issues, SupportType, Description, De
+tail, AssignTo, Status, Remarks, Priority, ETD, ClosedDate, ClosedNam
+e FROM TSIssuesTable WHERE Customer LIKE $quotedString1 AND Priority 
+LIKE $quotedString2 AND TicketNo LIKE $quotedString3 AND DateSubmissi
+on LIKE $quotedString4");

      [download]
      Removed the COlumns=>{}-But it makes no difference. What can I do to debug further? How can I check whether selectall_arrayref returns something (besides how I am doing it now). And I must mention here that I am relatively new to perl-about 2-3 months old. About place binders
      Read more... (299 Bytes)
      Please help me solve this problem.
[reply]
[d/l]
[select]
        Here is how you examine a complex data structure: 
        use Data::Dumper;

print Dumper $rows;

        [download]
        That will show you what's in it.

        Honestly, I have never used the convenience functionns in DBI like selectall_arrayref, so I don't know if you are using it right. Why don't you try going one step lower and calling prepare, execute, and fetchall_arrayref separately?

[reply]
[d/l]
Re: HTML::Template-Displaying DB Records
by Hagbone (Monk) on May 25, 2004 at 01:06 UTC
    First off, let me qualify what follows with this: I'm a DBI/HTML::TEMPLATE rookie, so be sure to take my suggestion accordingly. At this point, I've noticed no one else has responded, so I thought I'd offer what may be ??? the problem. In your code: 
    my $rows = $dbh->selectall_arrayref($SQL,{Columns =>{}}) ;

print $rows; <font color="red">(FLow is fine here too-this stateme
+nt prints ARRAY(0x1ed0bb0) )</font>

foreach (@$rows) {
    print "I say $_\n"; (Problem here-no output on screen.)
    }

    [download]
    I have a program that contains code similar to what you show, but the difference I noticed is instead of: 
    foreach (@$rows) {

    [download]
    my program uses .... 
    foreach (@{$rows}) {

    [download]
    {note the additional curly brackets} ... could that be it? It's easy enough to try. And if it doesn't work, it seems to me that your problem lies with DBI, not HTML::TEMPLATE, given where things seems to crap out.

    Hagbone

[reply]
[d/l]
[select]
      Nope, those are actually just syntax variations. They do the same thing.
[reply]

Back to Seekers of Perl Wisdom