http://www.perlmonks.org?node_id=1134481

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

I'm new to Template::Toolkit but I t hink I'll rework my actual website using it. I got a Template

[% USE oracleDB = DBI("dbi:Oracle:dbname", "username", "password") %] <html> <body> <table> <tr> <td>col1</td> <td>col2</td> <td>col3</td> </tr> [% query = oracleDB.prepare(" SELECT u.name, u.adress, u.idontknow FROM user u, anothertable t, athirdtable d WHERE u.id = t.user AND t.id = d.something ORDER BY u.name DESC") %] [% FOREACH user = query.execute( ) %] <tr> <td>[% user.u.name %]</td> <td>[% user.u.adress%]</td> <td>[% user.u.idontknow %]</td> </tr> </table> </body> </html>


The query is actual working fine, i got the amount of wors I'm expecting, but just the col td is always empty. How do i access alias-based results?

Replies are listed 'Best First'.
Re: Template::Toolkit - How do i access alias-based results?
by Your Mother (Archbishop) on Jul 13, 2015 at 13:08 UTC

    If you are new to Perl templates, you might also consider Text::Xslate. It's got a more limited feature set but it's still powerful and about a bazillion times faster than TT (which I have used and loved for 10+ years).

      Maybe I'll do later, first I wanna try to get some stuff done with TT before really going with it.

        I do like TT very much. I made the suggestion specifically because you're new to it and it has a considerable learning curve if you want to use all it has to offer. It's a mini-language more than a templating system and it's trivial to use it like PHP; AKA, the wrong way. Xslate is a step back closer to the (M)View(C) domain and it also has a learning curve. It took me quite awhile to release my death grip on TT to give Xslate a chance because of that learning investment.

Re: Template::Toolkit - How do i access alias-based results?
by Corion (Patriarch) on Jul 13, 2015 at 12:18 UTC

    What kind of datastructure does oracleDB->execute() return?

    I would assume that you don't need the u. prefix.

      Using Perl - CGI I'd do it like this:
      $hDB = _OracleConnect(); $hStatement = $hDB->prepare("MY SQL"); $hStatement->execute( $var1, $var2, $var3 ); while ( ( $ret1, $ret2, $ret3 ) = $hStatement->fetchrow_array ){ # do stuff } $hStatement->finish; $hDB->disconnect;
      Or do you want me to data dump? If yes, in Template Toolkit, or just by using Perl CGI? Because I won't know how to dump this helpful in Template Toolkit.

      Leaving the u prefix on the printings doesn't make a difference. I already tried this, just saying % user.name % but still empty.

        Yes, printing a Dumper output of the data structure you create in # do stuff or even better, the data structure that you give to Template::Toolkit would help to find out what you need to do to access the elements of the data structure.

Re: Template::Toolkit - How do i access alias-based results?
by tangent (Parson) on Jul 13, 2015 at 14:14 UTC
    Add this to the top of your template:
    [% USE Dumper %]
    Then add the dump within the loop:
    [% FOREACH user = query.execute( ) %] [% Dumper.dump( user ) %]
      Dump seems to work fine ...

      [% FOREACH user = query.execute() %] [% Dumper.dump( user ) %] <tr class="tbl_3_col_ia_tr1"> <td class="tbl_3_col_ia_td tbl_3_col_i +a_td_1 tbl_3_col_ia_text"> <span> [% user.vertragsnummer %] </span> </td> <td class="tbl_3_col_ia_td tbl_3_col_i +a_td_2 tbl_3_col_ia_text"> <span class="tbl_3_col_ia_span_mid +_l"> [% user.artikelnr %] </span> <span class="tbl_3_col_ia_span_mid +_r"> </span> </td> <td class="tbl_3_col_ia_td tbl_3_col_i +a_td_3 tbl_3_col_ia_text"> <span class=""> </span> </td> </tr> [% END %]

      Output (dump works fine, my output still is empty):
      $VAR1 = { 'DATUM' => '09-JUL-15', 'STATUS' => 'Eingelesen', 'NR' => '1 +1000530', 'NACHRICHT' => '528', 'aNR' => '8041094A' }; $VAR1 = { 'aNR +' => '8033010E', 'NR' => '11000527', 'STATUS' => 'Eingelesen', 'NACHR +ICHT' => '1559', 'DATUM' => '09-JUL-15' }; [...]

      Well, if I do user.SOMESTUFF and SOMESTUFF in Uppercase it works ...


      Update: okay, I'd like to try the same now by passing values to template. Would you be so kind and help me here too? So I know both ways to do this ...
      print "Content-type: text/html\n\n"; my $oTT = Template->new({ INCLUDE_PATH => '../templates', EVAL_PERL => 1, ABSOLUTE => 1, }) || die "$Template::ERROR\n"; #DB - Connect my $hDB = DBI->connect( "dbi:Oracle:xx", "xx", "xx", { AutoCommit => 0 + } ); my $hStatement = $hDB->prepare("MYSQL"); $hStatement->execute( ); while ( ( $iVertragsnummer, $sArtikelNr, $sVertragsStatus, $sVertragsD +atum, $iMsgID ) = $hStatement->fetchrow_array() ){ push (@aRows, ( $iVertragsnummer, $sArtikelNr, $sVertragsStatus, $s +VertragsDatum, $iMsgID )); } $hStatement->finish; $hDB->disconnect; my $sData = { 'result' => \@aRows }; $oTT->process("../templates/mytemplate.html", $sData) || die $oTT->err +or(), "\n";
      [% FOREACH line IN result %] [% Dumper.dump( line ) %] [% line.VERTRAGSNUMMER %] [% line.ARTIKELNR %] [% END %]
      I get the following output of my dump:
      $VAR1 = '11000530'; $VAR1 = '8041094A'; $VAR1 = 'Eingelesen'; [...]
      my line.vars are empty again. I think I'm doing something wrong with references in perl?
        I think I'm doing something wrong with references in perl?

        You're doing something wrong with @aRows alright - and the solution has already been given to you here

Re: Template::Toolkit - How do i access alias-based results?
by 1nickt (Canon) on Jul 13, 2015 at 23:07 UTC

    Hi Yaerox, glad to see you experimenting with TT2.

    I read most of this thread and here are some observations:

    In the first example code, once you have aliased your table as u you can just refer to it as that.

    I disagree with the monkm who recommended not using TT2 because of its complexity. It is true that it will require more time to learn, but at the end you will have a more powerful system.

    I strongly recommend not running DB perl code in your template. Sure you can, but why in the world would you? You are still obviously going to have a perl program that uses the template; that's where you should be doing the Perl work.

    An exception could be when you can retrieve a set of records with a one-line dbh call and then feed them to a TT loop. But when you are going to be processing the data or it's a multiline SQL statement, there is no way that should be in the template. That is the whole point of the template! To separate your code languages.

    Another exception could be that you retrieve the records with DBI in your Perl program and pass the record set into the Template where a Macro might process some format or something like that, or look up a string in a language library. In some cases the greater benefit is in keeping the Perl code clean.

    Typically, however, I have always tried to do every bit of processing I could in a Perl program, building up a highly complex data structure of hashes of arrays of hashes of ..... and then passing that in to the template as much intact as possible.

    HTH

    Remember: Ne dederis in spiritu molere illegitimi!

      Again, I'm a TT fan, contributor even, and I will continue to use it in personal projects but probably never again at work. The issue isn't that TT is too powerful exactly. The issue is the power makes templates harder to understand and maintain for others and encourages bad behavior in devs, exactly as you say–

      Sure you can [run DB perl code in your template.]

      I'm not quite a View purist and it sounds like you personally do things Right®. Something like Text::Xslate almost necessarily leads to simpler templates. It's also much more strict, which leads to better practices and easier maintenance. Just to say it again, TT2 really is 1,000x slower than Xslate in some use cases and there are one or two other template engines that are also in the class of zippiness. I know template speed is the last on the optimization checklist but if the difference is 500 renders per second instead of 5, and you're in front of nginx or something which is capable of handling much more, it matters.

      I don't discourage TT use out of ignorance but out of thorough familiarity.

      Thank you for your reply sir.

      Exactly what you said, is what I was thinking about all day long. I mean using SQL in Perl or on my Tempaltes do infect performance too, and I wanna go with TT because I wana separate code of all my languages. That was the reason why I asked for help doing it like this once. Now that it works, I'll go te way you described here.
Re: Template::Toolkit - How do i access alias-based results?
by monkey_boy (Priest) on Jul 13, 2015 at 14:06 UTC
    Try this:
    [% USE oracleDB = DBI("dbi:Oracle:xe", "LP", "lpdba") %] <html> <body> [% query = oracleDB.prepare(" SELECT u.name, u.adress, u.idontknow FROM user u, anothertable t, athirdtable d WHERE u.id = t.user AND t.id = d.something ORDER BY u.name DESC") %] [% USE Dumper %] <pre> [% Dumper.dump(query.execute()) %] </pre> </body> </html>



    This is not a Signature...
      Output:
      $VAR1 = bless( { 'MAX' => -1, '_STH' => bless( { '_STH' => bless( {}, 'DBI::st' ) }, 'Template::Plugin::DBI::Query' ), 'SIZE' => 0 }, 'Template::Plugin::DBI::Iterator' );
        Sorry, try this then ;) Not sure if its 'IN' or '=' in this FOR loop, try both if needed.
        [% USE oracleDB = DBI("dbi:Oracle:xe", "LP", "lpdba") %] <html> <body> [% query = oracleDB.prepare(" SELECT u.name, u.adress, u.idontknow FROM user u, anothertable t, athirdtable d WHERE u.id = t.user AND t.id = d.something ORDER BY u.name DESC") %] [% USE Dumper %] [% FOREACH res IN query.execute() %] <pre> [% Dumper.dump(res) %] </pre> [% END %] </body> </html>


        This is not a Signature...
Re: Template::Toolkit - How do i access alias-based results?
by sundialsvc4 (Abbot) on Jul 13, 2015 at 14:01 UTC

    To my way of thinking, SQL queries do not belong in templates.   I strictly think of templates as being presentation descriptions:   concerned with “how exactly to display” information which has been supplied to them, but not with the logic that supplies that information.   To me, and strictly IMHO, there is a “separation of concerns” issue here.

    Template::Toolkit has many powerful features in it, which allow you to be very selective in exactly how the presentation is done.   But, to me, they should be used only for that purpose.   Procedural, active, logic does not belong in a template.

      I understand what you're saying but this doesn't help me on my issue right now. I already did it the other way around, receive first in perl and pass as param to template, but same result anyway...

      print "Content-type: text/html\n\n"; my $oTT = Template->new({ INCLUDE_PATH => '../templates', EVAL_PERL => 1, ABSOLUTE => 1, }) || die "$Template::ERROR\n"; # DB - Connect my $hDB = DBI->connect( "dbi:Oracle:xe", "lp", "lpdba", { AutoCommit = +> 0 } ); my $hStatement = $hDB->prepare(" SELECT v.vertragsnummer, a.artikelnr, v.status, v.datum, v.nachric +ht FROM vertraege v, artikel a, benutzer b, lieferanten l, mandanten +m [...]"); $hStatement->execute( ); while ( ( $iVertragsnummer, $sArtikelNr, $sVertragsStatus, $sVertragsD +atum, $iMsgID ) = $hStatement->fetchrow_array() ){ push (@aRows, ( $iVertragsnummer, $sArtikelNr, $sVertragsStatus, $s +VertragsDatum, $iMsgID )); } $hStatement->finish; $hDB->disconnect; my $sData = { 'result' => \@aRows }; $oTT->process("../templates/mytemp.html", $sData) || die $oTT->error() +, "\n";
        Using that code your @aRows will end up like this:
        row1-iVertragsnummer row1-sArtikelNr row1-sVertragsStatus row1-sVertragsDatum row1-iMsgID row2-iVertragsnummer row2-sArtikelNr row2-sVertragsStatus row2-sVertragsDatum row2-iMsgID ...etc
        I suspect you need something like this:
        { iVertragsnummer => 'row1-iVertragsnummer', sArtikelNr => 'row1-sArtikelNr', }, { iVertragsnummer => 'row2-iVertragsnummer', sArtikelNr => 'row2-sArtikelNr', },
        So try changing to:
        while ( my $row = $hStatement->fetchrow_hashref() ) { push ( @aRows, $row ); }