carrying over an array from sql into another sub-routine

erik · May 10, 2005

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 "ODIE QA
REPORT";
print " ";
print "Submitted on $dateStamp[0]
EST";

print "REMINDERS:";
print " ";
print "ODIE NAME:
$device";
print " ";
###PRINT SERIAL
if ($SERIAL_NO eq ""){
$SERIAL_NO = "NULL";
print "<body>YOUR SERIAL NUMBER IS $SERIAL_NO
$FAIL</body>";
}
elsif ($SERIAL_NO =~ "111"){

print "<body>YOUR SERIAL NUMBER IS 11111111...
$FAIL</body>";
}
else{

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

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

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

###PRINT OE

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

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

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

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

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

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

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

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

phy_addr_text
print " ";
print " ";
print "<body>LINK INFORMATION</body>";
print " ";

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

A. Sinan Unur · May 10, 2005

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>YOUR SERIAL NUMBER IS $SERIAL_NO
$FAIL</body>";
}
elsif ($SERIAL_NO =~ "111"){

print "<body>YOUR SERIAL NUMBER IS 11111111...
$FAIL</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

Database SQL problem....	2	May 31, 2006
hashing mutliple rows from sql results?	10	May 15, 2005
Only one table shows up with the information	2	Mar 29, 2023
accessing arrays in a different sub-routine	12	Mar 28, 2005
Help with Visual Lightbox: Scripts	2	May 3, 2023
how to make a tree with randomly selected html tags from an array in python?	0	Mar 10, 2013
generating html form on the fly	4	Oct 22, 2007
Web-based two-column diff with color	2	Oct 1, 2009

carrying over an array from sql into another sub-routine

erik

A. Sinan Unur

Ask a Question

Similar Threads

Members online

Forum statistics

Latest Threads