> DBI::db=HASH(0x8b8e210)->disconnect invalidates 1 active statement
> handle (either destroy statement handles or call finish on them before
> disconnecting) at
> /usr/local/lib/perl5/site_perl/5.005/Template/Plugin/DBI.pm line 280.
> Template::Plugin::DBI::DESTROY('Template::Plugin::DBI=HASH(0x8b4809c)')
> [snip]
> From Template::Plugin::DBI manual i read that dbi disconnect i made
> when it gets out of scope, maybe this is the problem?
You have uncovered a small bug in Plugin/DBI.pm. If you don't
loop over all the elements returned by DBI.query(), then
sth->finish doesn't get called. The DESTROY method in
Plugin/DBI.pm correctly disconnects from the database, and the
DESTROY method in Template::Plugin::DBI::Query correctly calls
sth->finish. However, if you are in the middle of a query and
the DBI handle goes out of context, Template::Plugin::DBI::DESTROY
gets called before Template::Plugin::DBI::Query::DESTROY. The fix
is to delete $self->{ _STH } in Template::Plugin::DBI::DESTROY,
so that sth->finish gets called before dhb->disconnect.
#------------------------------------------------------------------------
# DESTROY
#
# Called automatically when the plugin object goes out of scope to
# disconnect the database handle cleanly
#------------------------------------------------------------------------
sub DESTROY {
my $self = shift;
+ delete($self->{ _STH }); # first DESTROY any query
$self->{ _DBH }->disconnect() if $self->{ _DBH };
}
Anyhow, you shouldn't come across this bug in the first place.
You must always exhaust all the items returned by DBI.query
using a loop.
Unfortunately the Iterator::get_all method doesn't work on the
iterator returned by DBI.query. Plugin/DBI.pm should probably
provide its own get_all method.
> P.S. dbi stuff in menu.html looks like this:
>
> [% item = DBI.query('SELECT * FROM table LIMIT 1') %]
> [% item.body %]
This needs to be:
[% FOREACH item = DBI.query('SELECT * FROM table LIMIT 1') %]
[% item.body %]
[% END %]
> Hmm, and now i remember one other problem too. Everywhere in DBI
> examples used only FOREACH to show query results. But let's say i get 3
> answers for query and i want to show only second? How is this done?
> Something like: [% result.header[1] %], where [1] is second element...
> Well, in EmbPerl it's something like [+ $$result[1][0] +], but in TT no
> idea.
If there was a working get_all method in Plugin/DBI.pm, you could write
item = DBI.query('SELECT ...').get_all.1;
or if you could apply numerical array indices to iterators, you could write
item = DBI.query('SELECT ...').1;
...but neither of these currently work. Instead, you have to do this:
[% FOREACH item = DBI.query('SELECT ...') %]
[% NEXT IF index != 1 %]
Second record is: $item.fields etc.
[% END %]
or do a manual get_all like this:
[% result = []; result.push(item) FOREACH item = DBI.query('SELECT ...'); %]
Second record is: $result.1.fields etc.
Craig
