TMPL_LOOP Array confusion

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;

Select allOpen in new window

huh the above DID return a result to the screen but it is returned ... this:

HTML::Template=HASH(0x14b0c80)

Select allOpen in new window

I suppose Ill learn about this fetchrow_hashref() method now :P

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

made sure Tie-RefHash 1.39 Is installed in PPM ... verified it too :$
I guess Im not "blessing" it ?

MySQL ODBC 3.51 Driver is not exported by Data::Dumper ... :$

fetchrow_hashref() is part of DBI.

Why did you mention Tie-RefHash?

>> 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;

Select allOpen in new window

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

Select allOpen in new window

Can be written more succinctly as:

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

Select allOpen in new window

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) 

Select allOpen in new window

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

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/

ok so this thinking to populate the key led me to

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

Select allOpen 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 !

Select allOpen in new window

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 :$

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 :$

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

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;

Select allOpen 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>

Select allOpen 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');

Select allOpen in new window

@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

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"

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?

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.

uhhh @fishmonger you ROCK !

though it took 2 days to realize I was missing this $cgi header and it would had rendered all my scripts lol go figure...