Hello  David
query works well with  $sth->fetchall_arrayref  but the result with template toolkit doesn'work.

return template 'result' => {
                                  resultC      => $sth->fetchall_arrayref,
}


data are here on we page, I can see with DUMP

[% USE Dumper %]
[% Dumper.dump(resultC) %]

$VAR1 = [ [ '0003908', 'MME xxxx ANGELE', 'TOULON SUR ALLIER', '7248' ], [ '00923', 'DR CHLIER VICOA', 'RUEIL MALMAISON', 'AC0605' ],
...
...
...
RRAOLIN', '121420171' ] ];


I use


[%  FOREACH item IN resultC %]
        <tr>
                <td>[% loop.count %] - <a href="[% request.uri_base %]/detailClient/[% item.CodeClient %] "> [% item.CodeClient %]</a> </td>
                <td>                   <a href="[% request.uri_base %]/detailClient/[% item.CodeClient %] "> [% item.NomClient %] </a>  </td>
                <td>[% item.Ville %]</td>
                <td>[% item.Adresse %]</td>
        </tr>
                [%  END %]



loop.count works, there all lines but other fields are empty

my $sql =qq/SELECT Client.CodeClient , Client.NomClient,Client.Ville, ClientContrat.NumContrat FROM Client INNER JOIN  ClientContrat
                                  ON (Client.CodeClient = ClientContrat.CodeClient)
                                  WHERE Client.CodeClient LIKE ? LIMIT 10/;


do you have a idea ?


I try [% item.table.field %] without success

bye
Hugues.

Le 28/01/2014 13:13, David Precious a écrit :
On Tue, 28 Jan 2014 09:34:02 +0100
Hugues <hugues@max4mail.com> wrote:


Hello
I use dancer 1 with with mysql

@resultCC  = database('db')->quick_select('ClientContrat',
{ NumContrat => { like => "$search%" } }, { limit => 10 } );

I send @resultCC to my template tt and it's work well

I would like to pass complex query

<snipped SQL> 

I do not find solution to use current mysql connection , and 
quick_select ?  ( it 's ok only if I create new connection with , 
connect, prepare execute ....)
what is the best way to do that ?

The database() keyword provided by Dancer::Plugin::Database gives you a
DBI connection handle (well, a subclass of one), so you can use it just
as you'd use DBI for more complex stuff, e.g.

my $sth = database('db')->prepare($sql);
$sth->execute;

template 'foo', { clients => $sth->fetchall_arrayref }

Also, in your example you had:


... WHERE Client.CodeClient LIKE '%" .$search. "%' LIMIT 10";

Customary Bobby Tables warning - if you're going to interpolate a
variable directly, use e.g. database->quote($search) to make sure it's
quoted appropriately.  Otherwise, what if $search contained e.g. "foo%';
TRUNCATE TABLE Client; --"?  Don't leave yourself open to SQL
injection, it's a very common mistake.




-- 
Salutations