On Thu, Nov 15, 2001 at 11:22:37PM -0800, Craig Barratt wrote:
> It's a bug. The DBI plugin keeps the last statement handle (sth)
> around in _STH (actually a Template::Plugin::DBI::Query object). The
> new (inner) query overwrite _STH, which causes the first (outer)
> Template::Plugin::DBI::Query's DESTROY method to be called, which
> calls sth->finish on the outer loop. You get two outer loops because
> the Interator object caches the next value.
Well caught! I've got a test in t/dbi.t which tests for this and passes.
Of course, my outer loop only has 2 records and so passes because of the
iterator lookahead. When I increase the outer loop to 3 records, it all
falls apart as predicted.
> Pending an actual fix, one workaround is to exhaust the iterator
> on the first query by appending a get_all:
>
> [% FOREACH tour = DBI.query( "SELECT * FROM tour_name
> ORDER BY tour_name_id DESC" ).get_all %]
Or use separate prepare/execute:
[% outer_query = DBI.prepare('SELECT * FROM tour_name');
inner_query = DBI.prepare('SELECT * FROM tour_event WHERE tour = ?');
FOREACH tour = outer_query.execute();
FOREACH event = inner_query.execute(tour.id);
...
END;
END
%]
Or better yet, like Perrin says, write it in Perl and hide it away.
[% USE tourguide;
FOREACH tour = tourguide.tours;
FOREACH event = tourguide.events(tour.id);
...
END;
END
%]
Of course, while I'm all in favour of well abstracted templates with a
clear separation of HTML, SQL, Perl, etc., there are times when being
pragmatic pays off. Sometimes a quick embedded SQL query, or a chunk of
Perl code embedded right there in the document where you can see it can
be the best thing to get a simple job done quick.
It's like knowing when to use 'goto'. Mostly harmful, but not always. :-)
A
