in reply to Re: HTML::Template-Displaying DB Records
in thread HTML::Template-Displaying DB Records

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
-yes, I do use them but could not use them here because I needed to use the quote function-to ensure I got the single quotes around the values used in "LIKE" -the query didn't work otherwise-and it is advisable not to use place binders with the quote function-that's what DBI documentation says.
Please help me solve this problem.

Replies are listed 'Best First'.
Re: Re: HTML::Template-Displaying DB Records-Still struggling
by perrin (Chancellor) on May 25, 2004 at 20:27 UTC
    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]
      Thanks. I tried Data::Dumper and here's the output: 
      <br>
use Data:: Dumper;
<br>
print Dumper $dbh;   <i>###This displays $VAR1 = bless( {}, 'DBI::db' 
+); -does this mean DB connection was established? (do need an answer 
+here)</i>
<br>
print $rows; <i>###This displays ARRAY(0x1ed0ad8)</i>
<br>
print Dumper $rows; <i>### This displays $VAR1 = []; which means the a
+rray reference is empty-but why?</i>
<br>

      [download]
      I also tried fetchrow_hashref(): 
      my $rows=[];
    $sth = $dbh->prepare( $SQL );
    $sth->execute();
    
    push @{$rows}, $_ while $_ = $sth->fetchrow_hashref();
    
    print Dumper $dbh; #Prints $VAR1 = bless( {}, 'DBI::db' ); 
    print Dumper $sth;#Prints $VAR1 = bless( {}, 'DBI::st' ); 
    print Dumper $rows; #Prints $VAR1 = [];

      [download]
      Same problem. Surprisingly, if you look at the code in my previous posts, I used selectcol_arrayref successfully in the retrieveFormData function so the DB connection seems to be fine. What could possibly be wrong?
[reply]
[d/l]
[select]
        First, I would chasnge that while $_ = $sth->fetchrow_hashref() into a simple for loop. It's just a little confusing right now, and hard to tell if there may be some strange scoping issue going on. Don't use $_ for this.

        Then I would break it down some more. Try running just this section of code in a separate script (after providing a $dbh of course).

        You should pass the RaiseError option to DBI, because you are not checking return values for errors here. You should also use bind variables. I'm not sure where you got the idea that bind variables don't work with LIKE queries. They work just fine, and will make your code more robust.

[reply]
[d/l]

In Section Seekers of Perl Wisdom