Template::Toolkit, DBI and mysql % wildcards

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

I have a query that I am trying to use with Template Tookit. Here is my code:

#!perl
use Template::Plugin::DBI;

$query = <<"EOQ";
select user
from logs
where user not like 'adm%'
EOQ

$tt = <<"EOT";

[% USE DBI('dbi:mysql:logs','','') %]
[% FOREACH item = DBI.query('$query') %]
  [% item.user %]
[% END %]

EOT

my $template = Template->new;
$template->process(\$tt)
    or die $template->error();
[download]

I am getting the following error when trying to use this code:

file error - parse error - input text line 12-25: unexpected token (%'
+)
[download]

I also tried this:

[% TAGS [- -] %]
[- USE DBI('dbi:mysql:logs','','') -]
[- FOREACH item = DBI.query('$query') -]
  [- item.user -]
[- END -]
[download]

The error was the same.

Any ideas on how to fix this?

Update: Fixed the query to be the same as the one that gives the error; it was a transcription error.

Comment on Template::Toolkit, DBI and mysql % wildcards Select or Download Code

Replies are listed 'Best First'.
Re: Template::Toolkit, DBI and mysql % wildcards by holli (Abbot) on Mar 14, 2006 at 20:49 UTC
Don't try to put in a variable by fiddling with the template. TT has a method to do so. It's called the stash. `#!perl use Template::Plugin::DBI; $query = <<"EOQ"; select user from logs where user not like 'adm%' EOQ $tt = <<"EOT"; [% USE DBI('dbi:mysql:logs','','') %] [% FOREACH item = DBI.query(query) %] [% item.user %] [% END %] EOT my $template = Template->new; $template->process(\$tt, {query => $query}) or die $template->error();` [download] This solution becomes quite obvious when one reads the docs. holli, /regexed monk/	[reply] [d/l]
Re^2: Template::Toolkit, DBI and mysql % wildcards by bfdi533 (Friar) on Mar 14, 2006 at 21:14 UTC
Thank you very much for the assistance!! That was exactly what I was looking for. I had read the manuals but there are so many parts of them that I did not know where to start to find the answer to my question.	[reply]
Re^3: Template::Toolkit, DBI and mysql % wildcards by holli (Abbot) on Mar 15, 2006 at 14:42 UTC
See here for a PDF version of the docs. There you got all the docs in 3 single documents (tutorial, manual, reference). holli, /regexed monk/	[reply] [d/l]
Re: Template::Toolkit, DBI and mysql % wildcards by jeffa (Bishop) on Mar 14, 2006 at 20:57 UTC
Why don't you simply move the query out of the TT template? `my $dbh = DBI->connect( ... ); my $items = $dbh->selectall_arrayref(q{ select user from logs where user not like 'adm%' }, {Slice=>{}}); my $template = Template->new or die Template->error; $template->process(\*DATA, {items => $items}) or die $template->error; __DATA__ [% FOREACH item = items %] [% item.user %] [% END %]` [download] UPDATE: I should have checked before posting, holli gave the same answer first. holli++ jeffa L-LL-L--L-LL-L--L-LL-L-- -R--R-RR-R--R-RR-R--R-RR B--B--B--B--B--B--B--B-- H---H---H---H---H---H--- (the triplet paradiddle with high-hat)	[reply] [d/l]
Re^2: Template::Toolkit, DBI and mysql % wildcards by holli (Abbot) on Mar 14, 2006 at 21:07 UTC
Yes in this case. But I often use it the DBI plugin like `[% USE DBI('dbi:mysql:logs','','') %] [% SET dbh = dbi.prepare(query) %] [% FOREACH user = user_list %] [% FOREACH item = dbh.execute(user) %] [% item.user %] [% END %] [% END %]` [download] code: `$template->process(\$tt, {query => $query, user_list => [foo, bar]) or + die $template->error(); [download]` [download] That's quite handy. Also when you put all the database stuff inside the templates, you can easily switch between databases, without touching the perl code. holli, /regexed monk/	[reply] [d/l] [select]
Re^2: Template::Toolkit, DBI and mysql % wildcards by bfdi533 (Friar) on Mar 14, 2006 at 21:13 UTC
I tried that first but did not know enough about getting the results into a hash do it this way. I will say your code for later use; very helpful!	[reply]
Re: Template::Toolkit, DBI and mysql % wildcards by Roy Johnson (Monsignor) on Mar 14, 2006 at 20:16 UTC
I'm probably just displaying my ignorance of TT, but I'd look for a percent sign followed by a single quote. The code you included doesn't have it, but that's what the error message says. Caution: Contents may have been coded under pressure.	[reply]
Re^2: Template::Toolkit, DBI and mysql % wildcards by bfdi533 (Friar) on Mar 14, 2006 at 20:34 UTC
I updated the SQL query to be the same as the code producing the error with a 'adm%' in the query rather than '%adm'. However, that is the query I need to run and cannot, for the life of me, figure out how to get the template to allow the valid MySQL wildcard character in the query.	[reply]
Re: Template::Toolkit, DBI and mysql % wildcards by pboin (Deacon) on Mar 14, 2006 at 20:57 UTC
One thing you could think about would be to change the START_TAG and END_TAG options, so that the percent sign is no longer a token that Template::Toolkit is looking for. Also, is the above code working for you? I had to "use Template;" in order to get it to run...	[reply]
Re^2: Template::Toolkit, DBI and mysql % wildcards by bfdi533 (Friar) on Mar 14, 2006 at 21:11 UTC
I did that. If you notice my post under "I also tried this ...". Same error regardless of the fact that the % was no longer the TAG delimiter.	[reply]