E
erik
I am doing an sql query that can sometimes return multiple rows of
data. For example I might get 3 rows of results, and in field $STATUS
one row might be an O for operational and another row might be F for
failed. I am looking to check one row at a time for certain
information. Is there a way to put a key:value pair on each ROW, and
then call that row when needed and get all the values from my sql
query. How can I label each row and get all the data in it? I could put
a counter in here and unshift it into an array but I don't think I
would be able to get all the data that is in that row.
I am not sure if hashing will work becuase I don't know if you can have
multiple value's per key. I hope I am wrong.
################################################################
# LINK QUERY #
################################################################
sub db_link_query{
my $link_sql = qq( select link.status, link.service_type,
link.bandwidth, physica
l_link.pln, router_interface.router_id,
router_interface.router_if_id,link.fms_ck
t_stus_cd, link.fms_no, link.fr_net_addr, physical_link.ckt_id,
physical_link.alt
_ckt_id,physical_link.alt_crr_id, physical_link.lec_trbl_num,
link.link_type,link
..link_encaps,physical_link.dial_line_type,link.OPL_XCPT_REAS_TXT,
physical_link.T
RPT_TYP_CD from router_interface, link, physical_link, device where
physical_li
nk.link_id = link.link_id and router_interface.router_id =
device.device_id and d
evice.device_id = '$device' and router_interface.router_id =
link.router_id_a and
router_interface.router_if_id = link.router_if_id_a order by
router_interface.r
outer_id ASC);
my $sth = $dbh->prepare($link_sql);
#Execute SQL Statement
$sth->execute or die "DBI::execute : $!";
print "Content-type: text/html\n\n";
print "<html><head><title>DEVICE QA REPORT for $device</title></head>";
print "<body bgcolor=#dddddd text=$green>";
print "<p align=center><b><font color=#000000 size=6>DEVICE QA
REPORT</font></b>";
print "<br>";
print "<p align=center><font color=#000000>Submitted on $dateStamp[0]
EST</font><
/p>";
print "<br>";
while (@link = $sth->fetchrow_array) {
($STATUS,$SERVICE_TYPE,$BANDWIDTH,$PLN,$ROUTER_ID,$
ROUTER_INTERFACE,$FMS_CKT_ST
ATUS,$FMS_NO,$FR_NET_ADDR ,$CKT_ID, $ALT_CKT_ID, $ALT_CRR_ID,
$LEC_TRBL_NUM,$LINK
_TYPE,$LINK_ENCAPS,$DIAL_LINE_TYPE,$OPL_XCPT_REAS_TXT,$TRPT_TYP_CD
)=@link;
print <<link_contents;
<table width="92%" border="1" height="65">
<tr align="center" valign="middle">
<td width="5%" height="81"><font
color="$black"><b>Status</b></font></td>
<td width="5%" height="81"><font color="$black"><b>Service
Type</td>
<td width="5%" height="81"><font
color="$black"><b>Bandwidth</b></td>
<td width="11%" height="81"><font color="$black"><b>PL</b></td>
<td width="10%" height="81"><font color="$black"><b>Router
IF</b></td>
<td width="10%" height="81"><font color="$black"><b>FMS Ckt
STATUS</b></td>
<td width="7%" height="81"><font color="$black"><b>FMS NO.</b></td>
(MORE OF THE SAME, SNIPPED, BUT I PRINT ALL THE ROWS IN A NICE HTML
TABLE)
</tr>
link_contents
}
$sth->finish();
print "</table>";
}
data. For example I might get 3 rows of results, and in field $STATUS
one row might be an O for operational and another row might be F for
failed. I am looking to check one row at a time for certain
information. Is there a way to put a key:value pair on each ROW, and
then call that row when needed and get all the values from my sql
query. How can I label each row and get all the data in it? I could put
a counter in here and unshift it into an array but I don't think I
would be able to get all the data that is in that row.
I am not sure if hashing will work becuase I don't know if you can have
multiple value's per key. I hope I am wrong.
################################################################
# LINK QUERY #
################################################################
sub db_link_query{
my $link_sql = qq( select link.status, link.service_type,
link.bandwidth, physica
l_link.pln, router_interface.router_id,
router_interface.router_if_id,link.fms_ck
t_stus_cd, link.fms_no, link.fr_net_addr, physical_link.ckt_id,
physical_link.alt
_ckt_id,physical_link.alt_crr_id, physical_link.lec_trbl_num,
link.link_type,link
..link_encaps,physical_link.dial_line_type,link.OPL_XCPT_REAS_TXT,
physical_link.T
RPT_TYP_CD from router_interface, link, physical_link, device where
physical_li
nk.link_id = link.link_id and router_interface.router_id =
device.device_id and d
evice.device_id = '$device' and router_interface.router_id =
link.router_id_a and
router_interface.router_if_id = link.router_if_id_a order by
router_interface.r
outer_id ASC);
my $sth = $dbh->prepare($link_sql);
#Execute SQL Statement
$sth->execute or die "DBI::execute : $!";
print "Content-type: text/html\n\n";
print "<html><head><title>DEVICE QA REPORT for $device</title></head>";
print "<body bgcolor=#dddddd text=$green>";
print "<p align=center><b><font color=#000000 size=6>DEVICE QA
REPORT</font></b>";
print "<br>";
print "<p align=center><font color=#000000>Submitted on $dateStamp[0]
EST</font><
/p>";
print "<br>";
print "<br>";print said:</b>";
</b>";
while (@link = $sth->fetchrow_array) {
($STATUS,$SERVICE_TYPE,$BANDWIDTH,$PLN,$ROUTER_ID,$
ROUTER_INTERFACE,$FMS_CKT_ST
ATUS,$FMS_NO,$FR_NET_ADDR ,$CKT_ID, $ALT_CKT_ID, $ALT_CRR_ID,
$LEC_TRBL_NUM,$LINK
_TYPE,$LINK_ENCAPS,$DIAL_LINE_TYPE,$OPL_XCPT_REAS_TXT,$TRPT_TYP_CD
)=@link;
print <<link_contents;
<table width="92%" border="1" height="65">
<tr align="center" valign="middle">
<td width="5%" height="81"><font
color="$black"><b>Status</b></font></td>
<td width="5%" height="81"><font color="$black"><b>Service
Type</td>
<td width="5%" height="81"><font
color="$black"><b>Bandwidth</b></td>
<td width="11%" height="81"><font color="$black"><b>PL</b></td>
<td width="10%" height="81"><font color="$black"><b>Router
IF</b></td>
<td width="10%" height="81"><font color="$black"><b>FMS Ckt
STATUS</b></td>
<td width="7%" height="81"><font color="$black"><b>FMS NO.</b></td>
(MORE OF THE SAME, SNIPPED, BUT I PRINT ALL THE ROWS IN A NICE HTML
TABLE)
</tr>
link_contents
}
$sth->finish();
print "</table>";
}