Simple table question?

Hi, I have a working DBI::Oracle script that I am now trying to get
CGI.pm to HTML-ize the output of, My problem is in the table statement
how can I get the output of the while statement to be in the table? I
have just about beat myself silly trying to figure this out.

SCRIPT:

#!/usr/bin/perl
#
use strict;
use DBI;
use CGI qw(:standard escapeHTML);

#
# Set some environment variables
#
$ENV{ORACLE_HOME} =3D '/opt/oracle/9i';
my ($count, $dbh, $host, $pass, $port, $r_au, $sql, $sid, $sth, $user,
@row);

#
# my declarations
#
$host =3D 'host=3DHOSTNAME';
$pass =3D 'PASSWORD';
$port =3D 'port=3DPORT';
$sid  =3D 'sid=3DSID';
$user =3D 'USER';

$dbh =3D DBI->connect("dbi:Oracle:$host;$sid;$port", $user, $pass,
{ RaiseError =3D> 1, AutoCommit =3D> 0}
) || die "Database connection not made:
$DBI::errstr";

$sql =3D qq{ select r.au_number, count(distinct r.subj_id)
from t_request r, t_certificate c
where r.req_status_id=3D9  and
(r.req_id=3Dc.req_id) and
c.cert_status_id=3D1
group by au_number };

$sth =3D $dbh->prepare($sql);
$sth->execute();
$sth->bind_columns(undef, \$r_au, \$count);

print header(), start_html("BEST CertMan results");

print h1("Results"),
table({-border=3D>'1', -width=3D>'20%'},
Tr({-align=3D>'LEFT', -VALIGN=3D>'TOP'},
th({-width=3D>'30%', -bgcolor=3D>'#CCCCCC'}, =
"AU"),
th({-bgcolor=3D>'#AAAAAA', =
-fontcolor=3D>'#FFFFFF'},
"Certs"),
),

while (@row =3D $sth->fetchrow_array())
{
Tr( td( \@row ));
}

);

$sth->finish();

# Disconnect from Oracle
$dbh->disconnect;

print "<a href=3D\"../\">Return</a>\n";

print end_html();

Thanks, that fixed it once I changed the

us CGI qw(:standard escapeHTML);

to

use CGI qw/:standard *table/;

-----Original Message-----
From: Shaun Fryer [mailto:sfryer@sourcery.ca]=20
Sent: Monday, January 03, 2005 3:18 PM
To: Fontenot, Ward P.
Subject: Re: Simple table question?

On Mon, Jan 03, 2005 at 04:06:42PM -0600, Ward.P.Fontenot@wellsfargo.com
wrote:
> Hi, I have a working DBI::Oracle script that I am now trying to get
> CGI.pm to HTML-ize the output of, My problem is in the table statement
> how can I get the output of the while statement to be in the table? I
> have just about beat myself silly trying to figure this out.

You must open the table tag with start_table(). It's in the docs, albeit
a bit obscured by the amount of info.

print h1("Results"),
start_table({-border=3D>'1', -width=3D>'20%'}),
Tr({-align=3D>'LEFT', -VALIGN=3D>'TOP'},
th({-width=3D>'30%', -bgcolor=3D>'#CCCCCC'}, "AU"),
th({-bgcolor=3D>'#AAAAAA', -fontcolor=3D>'#FFFFFF'}, "Certs"),
);
while (@row =3D $sth->fetchrow_array()) {
print Tr( td( \@row ));
}
print end_table();

Off the top of my head ... that should do it.

--=20
 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D
Shaun Fryer
 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D
http://sourcery.ca/
ph: 416-544-9461
 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D

On Mon, 3 Jan 2005 16:06:42 -0600, Ward.P.Fontenot@wellsfargo.com
>
>         while (@row = $sth->fetchrow_array())
>         {
>                         Tr( td( \@row ));
>         }
>
> );
>
> $sth->finish();

There are a couple of things going on here...fetchrow_array returns a
normal array--unlike selectall_arrayref, which returns a reference--so
you need @row, not \@row.  The logic also gets a little more complex
than I usually have luck getting CGI.pm built-in functions to deal
with.  For instance, you've put the closing paren on the table before
the while loop, so your Tr(td(..is hanging in space.  print and
old-fashioned HTML are probably going to be your friends here.

I would do something like this:

<snippet>

print "HTML for your table headers" ;

while (@row = $sth->fetchrow_array) {
print "<tr>", map {"<td>$_ </td>"} @row, "</tr>\n"
# or:
# print "<tr>", map {defined $_ ? "<td>$_ </td>" :
"<td>(null)</td>"} @row, "</tr>\n" ;
}

print "</table>\n" ;

</snippet>


HTH,

--jay

On Mon, Jan 03, 2005 at 04:06:42PM -0600, Ward.P.Fontenot@wellsfargo.com wrote:
> Hi, I have a working DBI::Oracle script that I am now trying to get
> CGI.pm to HTML-ize the output of, My problem is in the table statement
> how can I get the output of the while statement to be in the table? I
> have just about beat myself silly trying to figure this out.

You must open the table tag with start_table(). It's in the docs, albeit
a bit obscured by the amount of info.

print h1("Results"),
start_table({-border=>'1', -width=>'20%'}),
Tr({-align=>'LEFT', -VALIGN=>'TOP'},
th({-width=>'30%', -bgcolor=>'#CCCCCC'}, "AU"),
th({-bgcolor=>'#AAAAAA', -fontcolor=>'#FFFFFF'}, "Certs"),
);
while (@row = $sth->fetchrow_array()) {
print Tr( td( \@row ));
}
print end_table();

Off the top of my head ... that should do it.

--
=====================
Shaun Fryer
=====================
http://sourcery.ca/
ph: 416-544-9461
=====================

Shaun Fryer wrote:
> On Mon, Jan 03, 2005 at 04:06:42PM -0600, Ward.P.Fontenot@wellsfargo.com w
rote:
> 
>
>
> You must open the table tag with start_table(). It's in the docs, albeit
> a bit obscured by the amount of info.
>
> print h1("Results"),
>     start_table({-border=>'1', -width=>'20%'}),
>     Tr({-align=>'LEFT', -VALIGN=>'TOP'},
>         th({-width=>'30%', -bgcolor=>'#CCCCCC'}, "AU"),
>         th({-bgcolor=>'#AAAAAA', -fontcolor=>'#FFFFFF'}, "Certs"),
>     );
>     while (@row = $sth->fetchrow_array()) {
>         print Tr( td( \@row ));
>     }
> print end_table();
>
> Off the top of my head ... that should do it.
>

note that his use of \@row there is a convenient shortcut for another
CGI.pm trick : passing an arraryref to one of the subs will span it
across the contents.

print Tr( td( [ qw{ cat dog bird fish } ] ));

results in

<tr>
<td>cat</td>>
<td>dog</td>
etc..
</tr>

--
Scott R. Godin
Laughing Dragon Services
www.webdragon.net

Ward P Fontenot wrote:
> Hi, I have a working DBI::Oracle script that I am now trying to get
> CGI.pm to HTML-ize the output of, My problem is in the table statement
> how can I get the output of the while statement to be in the table? I
> have just about beat myself silly trying to figure this out.
>
> SCRIPT:
>
> #!/usr/bin/perl
> #
> use strict;
> use DBI;
> use CGI qw(:standard escapeHTML);
>
> #
> # Set some environment variables
> #
> $ENV{ORACLE_HOME} = '/opt/oracle/9i';
> my ($count, $dbh, $host, $pass, $port, $r_au, $sql, $sid, $sth, $user,
> @row);
>
> #
> # my declarations
> #
> $host = 'host=HOSTNAME';
> $pass = 'PASSWORD';
> $port = 'port=PORT';
> $sid  = 'sid=SID';
> $user = 'USER';
>
> $dbh = DBI->connect("dbi:Oracle:$host;$sid;$port", $user, $pass,
>                         { RaiseError => 1, AutoCommit => 0}
>                         ) || die "Database connection not made:
> $DBI::errstr";
>
> $sql = qq{ select r.au_number, count(distinct r.subj_id)
>               from t_request r, t_certificate c
>               where r.req_status_id=9  and
>                 (r.req_id=c.req_id) and
>                 c.cert_status_id=1
>               group by au_number };
>
> $sth = $dbh->prepare($sql);
> $sth->execute();
> $sth->bind_columns(undef, \$r_au, \$count);
>
> print header(), start_html("BEST CertMan results");
>
> print h1("Results"),
>         table({-border=>'1', -width=>'20%'},
>                 Tr({-align=>'LEFT', -VALIGN=>'TOP'},
>                         th({-width=>'30%', -bgcolor=>'#CCCCCC'}, "AU"),
>                         th({-bgcolor=>'#AAAAAA', -fontcolor=>'#FFFFFF'},
> "Certs"),
>                 ),
>
>         while (@row = $sth->fetchrow_array())
>         {
>                         Tr( td( \@row ));
>         }
>
> );
>
> $sth->finish();
>
> # Disconnect from Oracle
> $dbh->disconnect;
>
> print "<a href=\"../\">Return</a>\n";
>
> print end_html();
>

I've done things like this myself. You might find the shortcut I used to
bind the columns interesting. Here's a sub from a recent project that
creates a table within a form.

sub existing_images ($)
{
my $item_id = shift;
my $dbh = new_connect($database, $dbi_user, $dbi_pass);
my $sth = $dbh->prepare("SELECT image_id, image_filename,
image_type FROM $image_table WHERE item_id = ? ORDER BY image_type DESC");
$sth->execute($item_id);
my $rows = $sth->rows;
unless ($rows)
{
return h3("No Images currently associated with this item");
}
my @table;
push @table, (
start_form(), hidden(-name=>'item_id', -value=>$item_id),
start_table({-border=>1}), Tr( th("Image Filename"), th("Image
Type"), th("Remove Image"))
);

my ($id, $filename, $type);
$sth->bind_columns( \($id, $filename, $type) );

while ($sth->fetch)
{
push @table, ( Tr(
td( a({-href=>$filename, -target=>'_new'}, $filename)),
td({-align=>'center'}, $type),
td({-align=>'center'}, checkbox(-name=>'removeimage',
-value=>$id, -label=>''))
) );
}

push @table, end_table();
push @table, (
p("Be certain of the images you have selected for deletion;
this action is ", b("not undo-able"), "."),
p("Should you wish, you may click the image filenames above to
preview each image before making your decision."),
p("Note that this only removes the selected images from the
database entry, and does ", b("not"), " delete the files from the
directory itself.")
);
push @table, ( p({-align=>'center'}, submit(-name=>'delete',
-label=>'Remove Selected Images') ), end_form() );
return @table;
}

then somewhere in the middle of the other html output I need merely do

print existing_images($requested_itemid);

--
Scott R. Godin
Laughing Dragon Services
www.webdragon.net