carrying over an array from sql into another sub-routine


E

erik

I am ultimately trying to print out the @link array in the
db_print_report sub-routine. I can print out each scalar element of the
array in seperate sub-routines but I cannot print the entire array set.
I tried to initialize the array in the beginning of script with "my
@link;" and that did not help. I am sure this is a scope issue but why
can I print the scalar elements of the array but not the entire array?
Can someone help me out?

p.s. everything else is working as expected, except for the piece I
mentioned.


#!/usr/local/bin/perl

#########################################################
# Global Variables #
#########################################################
use DBI;
use CGI(":standard");
my $date=`date +%x_%r`;
my $device = param("device");
my $datetemp = `date +%x_%r`;
my @dateStamp = split('\n',$datetemp);
my $green = "#33CC33";
my $red = "#FF0000";
my $OK = " ............................. OK";
my $FAIL = " ............................. FAIL";
my @link;

#DNS check for host
my $temp = gethostbyname($device);
my $deviceip = join(".",unpack('C4',$temp));
#########################################################
# Connect to Database and run SQL query #
#########################################################
sub db_connect
{
$ENV{'ORACLE_SID'} = 'xxxxx';
$ENV{'ORACLE_HOME'} = '/oracle/';
# Connect to the Database
$dbh = DBI->connect("dbi:Oracle:host=xxxx;sid=xxxx", "xxxxx", "xxxxx",
{ AutoCommit => 0 } );

# check connect to DB
$dbh or (print <<EOD
Error: Can't connect to database
EOD
and die $DBI::errstr);
}
################################################################
# SERIAL and OE NUMBER QUERY #
################################################################
#I GRAB CUST_ID HERE FOR QUERIES LATER
sub db_serial_query{

my $serial_sql = qq( SELECT SERIAL_NO, ADDRESS_449,CUST_ID,OE_NUMBER
from DEVICE where DEVICE_ID= '$device');

my $sth = $dbh->prepare($serial_sql);
#Execute SQL Statement
$sth->execute or die "DBI::execute : $!";

while (@serial = $sth->fetchrow_array) {
($SERIAL_NO,$ADDRESS_449,$CUST_ID,$OE_NUMBER) = @serial;
}
$sth->finish();
}


################################################################
# CONTACT NAME QUERY #
################################################################


sub db_contact_query{

my $contact_sql = qq( select PRIORITY,FIRSTNAME,LASTNAME from CONTACT
where CONTACT_ID='$device');

my $sth = $dbh->prepare($contact_sql);
#Execute SQL Statement
$sth->execute or die "DBI::execute : $!";

while (@contact = $sth->fetchrow_array) {
($PRIORITY,$FIRSTNAME,$LASTNAME) = @contact;
}
$sth->finish();
}

################################################################
# LINK QUERY #
################################################################
sub db_link_query{

my $link_sql = qq( select
STATUS,SERVICE_TYPE,FR_NET_TYPE,BANDWIDTH,CKT_ID,FMS_NO,LINK_DESC,LINK_TYPE,LINK_ENCAPS,FMS_CKT_STUS_CD,T1_FRAME_LINE_ENCODING
from link where router_id_a='$device');

my $sth = $dbh->prepare($link_sql);
#Execute SQL Statement
$sth->execute or die "DBI::execute : $!";
while (@link = $sth->fetchrow_array) {

($STATUS,$SERVICE_TYPE,$FR_NET_TYPE,$BANDWIDTH,$CKT_ID,$FMS_NO,$LINK_DESC,$LINK_TYPE,$LINK_ENCAPS,$FMS_CKT_STUS_CD,$T1_FRAME_LINE_ENCODING
)= @link; #<--THIS IS WHAT I WANT PRINT IN LAST SUB-ROUTINE!!
}
$sth->finish();
}

#################################################################
# Ensure Device is found in DNS #
#################################################################
sub check_dns
{
( $deviceip ? ( $DNS_RESULTS = "OK" ) : ( $DNS_RESULTS = "FAIL" ));

}#end sub

###############################################################
# PRINT ALL RESULTS #
###############################################################
sub db_print_report{



print "Content-type: text/html\n\n";
print "<html><head><title>ODIE QA REPORT</title></head>";
print "<body bgcolor=#343434 text=$green>";
print "<p align=center><b><font color=#FFFFFF size=6>ODIE QA
REPORT</font></b>";
print "<br>";
print "<p align=center><font color=#FFFFFF>Submitted on $dateStamp[0]
EST</font></p>";

print "<p align=center><font color=#0099FF>REMINDERS:</font>";
print "<br>";
print "<p align=center><b><font color=#FFFFFF size=3>ODIE NAME:
$device</font></p></b>";
print "<br>";
###PRINT SERIAL
if ($SERIAL_NO eq ""){
$SERIAL_NO = "NULL";
print "<body><font color=$red>YOUR SERIAL NUMBER IS <b> $SERIAL_NO
$FAIL</b></font></body>";
}
elsif ($SERIAL_NO =~ "111"){

print "<body><font color=$red>YOUR SERIAL NUMBER IS 11111111...<b>
$FAIL</b></font></body>";
}
else{

print "<body>YOUR SERIAL NUMBER IS: <b> $SERIAL_NO
$OK</b></font></body>";
}
print "<br>";
###PRINT 449

if ($ADDRESS_449 eq ""){
$ADDRESS_449 = "NULL";
print "<body><font color=$red>YOUR 449 ADDRESS IS <b>$ADDRESS_449
$FAIL</b></font></body>";
}
else{

print "<body>YOUR 449 ADDRESS IS <b>$ADDRESS_449
$OK</b></font></body>";
}
print "<br>";

###PRINT OE

if ($OE_NUMBER eq ""){
$OE_NUMBER = "NULL";
print "<body><font color=$red>YOUR MNS OE IS <b>$OE_NUMBER
$FAIL</b></font></body>";
}
else{

print "<body>YOUR MNS OE IS <b>$OE_NUMBER $OK</b></font></body>";
}
print "<br>";

###PRINT FOR DNS
if ($DNS_RESULTS eq "FAIL"){
print "<body><font color=$red>YOUR ODIE NAME IS NOT IN DNS<b>
$FAIL</b></font></body>";
}
else{

print "<body>YOUR ODIE NAME IS IN DNS <b>$OK</b></font></body>";
}
print "<br>";

###PRINT Contact Info
if ($FIRSTNAME eq "" or $LASTNAME eq ""){
$FIRSTNAME = "NULL";
$LASTNAME = "NULL";
print "<body><font color=$red>YOUR PRIMARY CUSTOMER CONTACT NAME IS:
<b>$FIRSTNAME $LASTNAME $FAIL</b></font></body>";
}
else{

print "<body>YOUR PRIMARY CUSTOMER CONTACT NAME IS: <b> $FIRSTNAME
$LASTNAME $OK</b></font></body>";
}
print "<br>";
print "<br>";



######################PRINT CONTACT TABLE######################
#I USED A HERE DOCUMENT IN THIS SECTION TO PRESERVE THE SPACES IN HTML
print <<phy_addr_text;

<body><b>PHYSICAL SITE ADDRESS INFORMATION</b></font></body>
<table width="50%" border="1" height="171">
<tr>
<td>BUILDING: &nbsp; $BUILDING</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>ROOM: &nbsp; $ROOM</td>
<td>FLOOR: &nbsp; $FLOOR</td>
</tr>
<tr>
<td>STREET: &nbsp; $STREET</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>CITY: &nbsp; $CITY</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>STATE: &nbsp; $STATE</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>ZIPCODE: &nbsp; $ZIPCODE</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>COUNTRY: &nbsp; $COUNTRY</td>
<td>&nbsp;</td>
</tr>
</table>

phy_addr_text
print "<br>";
print "<br>";
print "<body><b>LINK INFORMATION</b></font></body>";
print "<br>";

print "<body>link info: @link </body>";
print "</html>";
}#end sub
#########################################################
# Database Disconnect #
#########################################################

sub db_disconnect{
$dbh->disconnect();
}

#########################################################
# Main Driver #
#########################################################
db_connect();
db_serial_query();
db_contact_query();
db_link_query();
check_dns();
db_print_report();
db_disconnect();
 
Ad

Advertisements

A

A. Sinan Unur

I am ultimately trying to print out the @link array in the
db_print_report sub-routine. I can print out each scalar element of
the array in seperate sub-routines but I cannot print the entire array
set. I tried to initialize the array in the beginning of script with
"my @link;" and that did not help. I am sure this is a scope issue but
why can I print the scalar elements of the array but not the entire
array? Can someone help me out?

It looks like you still haven't read the posting guidelines (see Message-
ID: <[email protected]>)

Do you really expect everyone to go through all the lines of code below
to figure out where things go wrong.
#!/usr/local/bin/perl

use strict;
use warnings;

missing.

Indeed, running this with strict enabled will give you some interesting
information:

AMong others:

Global symbol "$dbh" requires explicit package name at t.pl line 30.
Global symbol "$dbh" requires explicit package name at t.pl line 34.
Global symbol "$dbh" requires explicit package name at t.pl line 48.

More on this later.
my $date=`date +%x_%r`;

There is no need to spawn an external process for something Perl can
handle with no problem.

....
my $datetemp = `date +%x_%r`;

There is no need to spawn another external process.
my @dateStamp = split('\n',$datetemp);

Hmmmmm ... I remember making these same comments some time ago.

If it made sense to spawn an external process, you would should have used
chomp to remove the "\n" from the output from that program. The use of
split here is confusing.

On the other hand, you could just use

perldoc -f localtime

and not spawn two, *two*, external processes.

my $green = "#33CC33";
my $red = "#FF0000";
my $OK = " ............................. OK";
my $FAIL = " ............................. FAIL";
my @link;

#DNS check for host
my $temp = gethostbyname($device);
my $deviceip = join(".",unpack('C4',$temp));

You suffer from premature declaration syndrome. Always declare variables
in the smallest applicable scope.
sub db_connect
{
$ENV{'ORACLE_SID'} = 'xxxxx';
$ENV{'ORACLE_HOME'} = '/oracle/';
# Connect to the Database
$dbh = DBI->connect("dbi:Oracle:host=xxxx;sid=xxxx", "xxxxx", "xxxxx",
{ AutoCommit => 0 } );

Oh, you have now magically created a global variable $dbh. At the very
least, you could have declared $dbh in file scope.

Your code is too hard to follow, and possibly ridden with many other
bugs.
sub db_serial_query{
....

while (@serial = $sth->fetchrow_array) {
($SERIAL_NO,$ADDRESS_449,$CUST_ID,$OE_NUMBER) = @serial;

Again, you hurry up and declare $green at file scope, but have no problem
with having all sorts of global variables springing into existence all
over the place.
sub db_print_report{



print "Content-type: text/html\n\n";
print "<html><head><title>ODIE QA REPORT</title></head>";
print "<body bgcolor=#343434 text=$green>";

Interesting. Why is $green no special?
print "<body><font color=$red>YOUR SERIAL NUMBER IS <b> $SERIAL_NO
$FAIL</b></font></body>";
}
elsif ($SERIAL_NO =~ "111"){

print "<body><font color=$red>YOUR SERIAL NUMBER IS 11111111...<b>
$FAIL</b></font></body>";
}

Please indent properly if you want others to be able read your code.

I am so confused now, I can't even remember the question.

Sinan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top