Hello Monks

I have a REALLY strange display problem I can only assume is a bug in Perl Templates (very unlikly, I know!). If you stay with me I will explain what is happening:

I have a small MySQL database with a table being queried. Below is the function to query the database, the file that calls the function and the template file displaying the data. The first set displays the data correctly. The second set, queries the same database and the same table, the only thing that is different is the where clause. Yet the data is displayed incorrectly.

Working function, file and template

#Calling file
#!/usr/bin/perl
use strict;
use Template;
use CGI;
use CGI::Session ( '-ip_match' );
use DBI;
use Data::Dumper;
require "class.pm";
my $cgi = new CGI;

my @cases = show_cases_home($library_id,$users_id);

my $template = Template->new();
my $file = 'templates/home.tt';
my $vars = {
  
     cases          =>  @cases
};

$template->process($file,$vars)
   || die $template->error(), "\n";

# SUB ROUTINE IN CLASS FILE

sub show_cases_home {
    my $dbh = new_dbh();
    my @cases;
    my $library_id = $_[0];
    my $assigned_to = $_[1];
    #my $query = 'SELECT c.*, DATE_FORMAT(c.added_date, "%d/%m/%Y"), b
+.firstname, b.surname from cases c, borrower b
    #WHERE c.library_id=? AND c.assigned_to = ? and c.borrower_id = b.
+borrower_id order by added_date desc';
    my $query = 'SELECT c.caseid,c.status,c.case_header,DATE_FORMAT(c.
+added_date, "%d/%m/%Y"), b.firstname, b.surname 
                 FROM cases c
                 LEFT JOIN borrower b USING (borrower_id)
                 WHERE c.library_id=? AND c.assigned_to =?
                 AND c.status = "open"
                 order by added_date desc';   
    my $sth = $dbh->prepare($query);
    $sth->execute($library_id,$assigned_to);
    while (my @row = $sth->fetchrow_array()) {
    push @cases, \@row;
    }
    return \@cases;
}

# TEMPLATE FILE SHOWING ALL CORRECTLY
[% INCLUDE templates/header_auth.inc %]
[% FOREACH wah IN cases %]
            <td><a href="show_case.pl?caseid=[% wah.0 %]">[% wah.0 %]<
+/a></td><td>[% wah.3 %]</td><td>[% wah.2 %]</td><td>[% wah.4 %] [% wa
+h.5 %]</td><td>[% wah.1 %]</td>
        </tr>
[% END %]

# END of WORKING FILES
[download]

The next set will use the same class file, the same dbh connection and the same table. However the data, specifically data with accents e.g "Séan" don't show correctly. Note: both template files are using the same header (UTF8) calling the same database (using the same dbh call with UTF8 enabled) and the same table

use strict;
use Template;
use CGI;
use CGI::Session ( '-ip_match' );
use DBI;
use Data::Dumper;
use Sphinx::Search;
require "class.pm";
my $cgi = new CGI;
my $dbh = new_dbh();
my $caseid = $cgi->param("caseid");
my $users_id = "31";
my @lines = word_test($caseid,$users_id);

# Sub routine

sub word_test {
    my $dbh = new_dbh();
    my @cases;
    my $caseid = $_[0];
    my $assigned_to = $_[1];
    my $query = 'SELECT caseid,status,case_header,DATE_FORMAT(added_da
+te, "%d/%m/%Y")
                 FROM cases
                 where caseid = ? and assigned_to = ?
                 order by added_date desc';   
    my $sth = $dbh->prepare($query);
    $sth->execute($caseid,$assigned_to);
    while (my @row = $sth->fetchrow_array()) {
    push @cases, \@row;
    }
    return \@cases;
}

# TEMPLATE FILE
[% INCLUDE templates/header_auth.inc %]

[% FOREACH row IN lines %]
"[% row.0 %] [% row.2 %] "
<br />
[% END %]

# END FILE
[download]

If I remove the caseid from the function the data seems to return and display correctly.
I have spent hours on this today and I am hoping it is a stupid problem that some will spot straight away!

Anyone!?

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.