Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1002
  • Last Modified:

TMPL_LOOP Array confusion

Hi all
Im having confusion on melding wht my pseudo code should behave like with translating that into stable functional codebehind...

1. I have Text Template installed

I have Text Template installed

2. I have ActivePerl installed

I have ActivePerl installed

3. I am pulling queries from database

I am puling Queries
Im just not sure what Im missing to populate my SELECT columns into an array and then "map" them to a TMPL_LOOP in order to display the results ....

Please help

thanks so much

use DBI
use warnings;
my database connection info here ODBC BIND WORKS .. so skip this block...

my $customsqlquery ="SELECT NAME, USER, ID FROM MyDB.TABLE";
my $sth = $dbh->prepare ($customsqlquery);
$sth->execute();

...now heres where Im confused...
my ( $mname, $muser, $mid );
my @allrows = Sth->bind_columns(undef, \$mname, \$muser, \$mid);

$rendertemplate = HTML::Template->new(filename => 'mytemplate.tmpl');
$rendertemplate -> param(
QUERYRESULTS=>allrows
);

print $rendertemplate;
print $rendertemplate->output;

$sth->finish();
$dbh->disconnect;

Open in new window



and then in the template I have a TMPL LOOP and inside that LOOP Im trying to call our each VAR within each array row...

<TMPL_IF QUERYRESULTS>
              [indent] <TMPL_LOOP NAME=ALLROWS>[/indent]
              [indent] <TMPL_VAR NAME="mname">[/indent]
              [indent] <TMPL_VAR NAME="muser">[/indent]
              [indent] <TMPL_VAR NAME="mid">[/indent]
              [indent] </TMPL_LOOP>[/indent]

                 
<TMPL_ELSE>
no such records to return / zero matches
</TMPL_IF>

Open in new window

0
JAaron Anderson
Asked:
JAaron Anderson
  • 14
  • 6
  • 3
1 Solution
 
lwadwellCommented:
In DBI, the ->bind_columns method is a way to associate the columns in the query to variables within the script ... for when the data is returned.  It does not return any data.
If understand the problem ... you should try (untested):
use DBI;
use warnings;
my database connection info here ODBC BIND WORKS .. so skip this block...

### NOTE: synonyms added to columns to match the template vars
my $customsqlquery ="SELECT NAME as mname, USER as muser, ID as mid FROM MyDB.TABLE";
my $sth = $dbh->prepare ($customsqlquery);
$sth->execute();

### Read all of the data into an array of hashes
my $allrows; # define the variable/reference to hold the data
while ( my $row = $sth->fetchrow_hashref() ) {
    push(@{$allrows}, $row);
}

$rendertemplate = HTML::Template->new(filename => 'mytemplate.tmpl');
$rendertemplate -> param(
QUERYRESULTS=>$allrows   ### NOTE: changed to reference the array reference
);

print $rendertemplate;
print $rendertemplate->output;

$sth->finish();
$dbh->disconnect;

Open in new window

0
 
JAaron AndersonProgramming Architect @ Widener UniversityAuthor Commented:
huh the above DID return a result to the screen but it is returned ... this:
HTML::Template=HASH(0x14b0c80)

Open in new window


I suppose Ill learn about this fetchrow_hashref() method now :P
0
 
lwadwellCommented:
The fetchrow_hashref() method returns the entire row in a hash, where the column name from the SQL is the hash key and the data value is the value.

If you do
    use Data::Dumper
and do
    print Dumper(\$allrows);
it print all of the values out in an expanded form.

You may also want to read example 4 here http://www.perlmonks.org/?node_id=65642
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
JAaron AndersonProgramming Architect @ Widener UniversityAuthor Commented:
made sure Tie-RefHash 1.39 Is installed in PPM ... verified it too :$
I guess Im not "blessing" it ?
0
 
JAaron AndersonProgramming Architect @ Widener UniversityAuthor Commented:
MySQL ODBC 3.51 Driver is not exported by Data::Dumper ... :$
0
 
lwadwellCommented:
fetchrow_hashref() is part of DBI.

Why did you mention Tie-RefHash?
0
 
lwadwellCommented:
>> MySQL ODBC 3.51 Driver is not exported by Data::Dumper
Do not understand ... this is what I meant
use DBI;
use warnings;
use Data::Dumper;  #### include package

my database connection info here ODBC BIND WORKS .. so skip this block...

### NOTE: synonyms added to columns to match the template vars
my $customsqlquery ="SELECT NAME as mname, USER as muser, ID as mid FROM MyDB.TABLE";
my $sth = $dbh->prepare ($customsqlquery);
$sth->execute();

### Read all of the data into an array of hashes
my $allrows; # define the variable/reference to hold the data
while ( my $row = $sth->fetchrow_hashref() ) {
    push(@{$allrows}, $row);
}
print Dumper(\$allrows);  #### Dump variable

$rendertemplate = HTML::Template->new(filename => 'mytemplate.tmpl');
$rendertemplate -> param(
QUERYRESULTS=>$allrows   ### NOTE: changed to reference the array reference
);

print $rendertemplate;
print $rendertemplate->output;

$sth->finish();
$dbh->disconnect;

Open in new window

0
 
FishMongerCommented:
my $allrows; # define the variable/reference to hold the data
while ( my $row = $sth->fetchrow_hashref() ) {
    push(@{$allrows}, $row);
}

Open in new window

Can be written more succinctly as:
my $allrows = $sth->fetchall_hashref();

Open in new window

0
 
JAaron AndersonProgramming Architect @ Widener UniversityAuthor Commented:
ok it looks like Im REALLY close lol...
@lwadell I condensed the while loop to @FishMonger's consolidated line

now Im just getting the following error:

The headers it did return are "DBI fetchall_hashref:invalis number of arguments: got handle +0, expected handle + between 1 and 1 Usage $h->fetchall_hashref($key_field) 

Open in new window

0
 
JAaron AndersonProgramming Architect @ Widener UniversityAuthor Commented:
I tried nesting fetchall_hashref(undef,1000); thinking it needed scope... or even 2 being zero based allowing for three cells in the array one for each of my elements muser, mname, & mid hmmmm nothing workx still same error ... so Im off trying to learn about this misbehavior
0
 
FishMongerCommented:
Sorry, my fault.  I forgot that you need to specify a "key" field parameter.
fetchall_hashref syntax info

The ID field would be the most logical one to use, however, it also means that the syntax to access the data for each row will be slightly different and that difference may not work with the Tk loop.  It's been awhile since I did any Tk scripts.

For now, it might be best to use the more verbose syntax.

[EDIT]

Boy, I'm really off today

s/Tk loop/TMPL_LOOP/
0
 
JAaron AndersonProgramming Architect @ Widener UniversityAuthor Commented:
ok so this thinking to populate the key led me to

my $allrows = $sth-> fetchall_hashref('mid');

Open in new window


now Im getting... this error:
HTML::Template::param(): attempt to set parameter 'queryresults' with a scalar - parameters is not a TMPL_VAR !

Open in new window

0
 
JAaron AndersonProgramming Architect @ Widener UniversityAuthor Commented:
now the error is returning all my correct HTML WITH all my expected REAL data returned. lol  just that its in the ERROR within $VAR1 = not returned as an HTML result :$
0
 
JAaron AndersonProgramming Architect @ Widener UniversityAuthor Commented:
since I cant do the hashref()

maybe can I do the fetchrow_array ref and pus like
print $array_ref->[0], " is ", $array_ref->[1],
to be something more like
push $row


@FishMonger hmm doesnt look like I can do fetchrow_array because the error it returns is Single reference arg to param() must be a hash-ref!

Im still confused :$
0
 
JAaron AndersonProgramming Architect @ Widener UniversityAuthor Commented:
I found this and am trying to read it ... I think it answers my issues but dont understand how to implement on my own :$ http://www.perlmonks.org/?node_id=322466
0
 
lwadwellCommented:
There was nothing wrong with the original ->fetchrow_hashref solution in a while loop, other than being 'verbose'.  You can definitely do hashref.

I worked up an example script based on yours and it works fine for me.  Is below for your reference.
Perl Script:
use DBI;
use warnings;
use HTML::Template;
use Data::Dumper;

#my database connection ... use your own.
my $dbh = DBI->connect('dbi:mysql:XXXX','XXXX','XXXX') 
          or die "CONNECT ERROR: $DBI::errstr";

my $customsqlquery ="SELECT NAME as mname, USER as muser, ID as mid FROM Q_27861458";
my $sth = $dbh->prepare($customsqlquery) or die "PREPARE ERROR: $DBI::errstr";
$sth->execute() or die "EXECUTE ERROR: $DBI::errstr";

my $allrows; 
while ( my $row = $sth->fetchrow_hashref() ) {
    push(@{$allrows}, $row);
}
$sth->finish();
$dbh->disconnect;
# print Dumper(\$allrows);

$rendertemplate = HTML::Template->new(filename => 'mytemplate.tmpl');
$rendertemplate->param(
                        QUERYRESULTS=>$allrows
                      );

# print $rendertemplate;
print $rendertemplate->output;

Open in new window

HTML::Template:
<!-- mytemplate.tmpl -->
<html>
<head>
<title>Q_27861458</title>
</head>
<body>
<h1>Example HTML::Template</h1>
<table>
<!-- TMPL_LOOP NAME=QUERYRESULTS -->
   <tr>
      <td>ID</td><td><!-- TMPL_VAR NAME=mid --></td>
      <td>USER</td><td><!-- TMPL_VAR NAME=muser --></td>
      <td>NAME</td><td><!-- TMPL_VAR NAME=mname --></td>
   </tr>
<!-- /TMPL_LOOP -->
</table>
</body>
</html>

Open in new window

SQL for table:
-- ----------------------------
-- Table structure for `q_27861458`
-- ----------------------------
DROP TABLE IF EXISTS `q_27861458`;
CREATE TABLE `q_27861458` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `user` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of q_27861458
-- ----------------------------
INSERT INTO `q_27861458` VALUES ('1', 'Fred', 'fuddles');
INSERT INTO `q_27861458` VALUES ('2', 'Mary', 'Maz');
INSERT INTO `q_27861458` VALUES ('3', 'Jane', 'Flash');
INSERT INTO `q_27861458` VALUES ('4', 'Bill', 'Billy-Bob');

Open in new window

0
 
JAaron AndersonProgramming Architect @ Widener UniversityAuthor Commented:
@lwadwell Thanks for trying to help, I really appreciate all your great effort  :-)
unfortunately this is where I am stuck.
I return results to the has fine but the results never render to the text template :$

as shown in attached screen snapshot

I suppose Ill try to go explore CGI logs but Im a coder not the best server d/m/make/install/build guy :(
error-sanitized.png
0
 
JAaron AndersonProgramming Architect @ Widener UniversityAuthor Commented:
I did this I found

ause

TIP: The message "The specified CGI application misbehaved" is generic - there are multiple possible causes for this error.

This Technote specifically relates to the scenario where the cause is that the IIS Handler "CGI-exe" is enabled (for the virtual directory 'controllerbin')

Environment

Controller application server based on Windows 2008 Server.

Resolving the problem

Disable the IIS handler "CGI-exe" for the virtual directory 'controllerbin'.

Steps:

   1 Logon to the Controller application server as an administrator
   2 Launch "Internet Information Services (IIS) Manager"
   3 Select/highlight the virtual directory "controllerbin"
   4 On the right-hand side, double-click on "Handler Mappings"
   5 Right-click on "CGI-exe" and choose "Remove"
0
 
lwadwellCommented:
OK.  I step at a time.  Can you run the script and does it produce the correct output as a perl script ... not as cgi in IIS?
0
 
FishMongerCommented:
You forgot to print the http (content type) header.
print "Content-Type: text/html\n\n",
      $rendertemplate->output;

Open in new window


or my preference:
use CGI;

my $cgi = CGI->new;

...
...

print $cgi->header, $rendertemplate->output;

Open in new window

0
 
JAaron AndersonProgramming Architect @ Widener UniversityAuthor Commented:
I also did this
Those running Windows 64 bit need to now take these steps to get Perl working properly...

    Edit the file C:\Windows\System32\inetsrv\config\applicationHost.config
    Find the line:-
    <add name="Perl Script (PL)" path="*.pl" verb="*" modules="CgiModule" scriptProcessor="c:\perl\bin\perl.exe" resourceType="File" preCondition="bitness32" />
    Change scriptProcessor="c:\perl\bin\perl.exe" to scriptProcessor='c:\perl\bin\perl.exe "%s" %s'
    and remove preCondition="bitness32"
    Make sure you swap the DOUBLE quotes " around the filename path for single quotes ' like in the example above.
    Repeat this for the Perl Script (CGI).
    Now find the line:-
    <add path="c:\Perl\bin\perl.exe" allowed="true" />
    Change this to:-
    <add path='c:\Perl\bin\perl.exe "%s" %s' allowed="true" />
    Save the changes.
    Restart IIS.
0
 
JAaron AndersonProgramming Architect @ Widener UniversityAuthor Commented:
uhhh @fishmonger you ROCK !
0
 
JAaron AndersonProgramming Architect @ Widener UniversityAuthor Commented:
though it took 2 days to realize I was missing this $cgi header and it would had rendered all my scripts lol go figure...
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 14
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now