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 my $sql ="SELECT Client.CodeClient, Client.NomClient,Client.Ville, ClientContrat.NumContrat FROM Client INNER JOIN ClientContrat ON (Client.CodeClient = ClientContrat.CodeClient) WHERE Client.CodeClient LIKE '%" .$search. "%' LIMIT 10"; 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 ? thanks Hugues.
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. -- David Precious ("bigpresh") <davidp@preshweb.co.uk> http://www.preshweb.co.uk/ www.preshweb.co.uk/twitter www.preshweb.co.uk/linkedin www.preshweb.co.uk/facebook www.preshweb.co.uk/cpan www.preshweb.co.uk/github
Thanks David I'm going to test asap best regards 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
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
On Fri, 31 Jan 2014 16:47:26 +0100 Hugues <hugues@max4mail.com> wrote:
Hello David query works well with $sth->fetchall_arrayref but the result with template toolkit doesn'work.
return template 'result' => { resultC => $sth->fetchall_arrayref,
}
Bah, sorry. That will return an arrayref of arrayrefs, one per DB row. Usually you'll want an arrayref of hashrefs, which is what your template code is expecting. The somewhat unintuitive syntax for that is: $sth->fetchall_arrayref({}); Passing it an empty hashref as the slice param asks for each row to be returned as a hashref. See the DBI docs for further info. -- David Precious ("bigpresh") <davidp@preshweb.co.uk> http://www.preshweb.co.uk/ www.preshweb.co.uk/twitter www.preshweb.co.uk/linkedin www.preshweb.co.uk/facebook www.preshweb.co.uk/cpan www.preshweb.co.uk/github
2014-01-31 Hugues <hugues@max4mail.com>:
resultC => $sth->fetchall_arrayref,
...
<td>[% loop.count %] - <a href="[% request.uri_base
%]/detailClient/[% item.CodeClient %] "> [% item.CodeClient %]</a>
If you ask pure arrayref (fetchall_arrayref) you can't treat your data being hashref. You have some options: - call fetchall_hashref (but order will not remain same) - call fetchall_arrayref({}) will give you every row as hashref (order remains same too) -- Wbr, Kõike hääd, Gunnar
participants (3)
-
David Precious -
Hugues -
WK