in reply to Re^4: SQL ERROR
in thread SQL ERROR

Do you care to share these lines of code with the Monastery?

CountZero

"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

Replies are listed 'Best First'.
Re^6: SQL ERROR
by WebDev (Initiate) on Nov 15, 2004 at 20:51 UTC
    Here is the code. Thanks for the help. 
    #!c:\perl\bin\perl.exe
#anniversaryweek.pl
#
######################################################################
+###############################
#
#
#########################################Header Section###############
+###############################
#
#---------------Use and Require-----------------------------
use DBI;
use Time::Local;
use strict;
use warnings;

#----------Declare variables, create objects-------------
my $dir1 = "./data";

my $dir2 = "DIRECTORY_LOCATION";

my @excludeAnn = ();

my $anniverWeek= "$dir2/anniverTEST.htm";
#$anniverWeek= "$dir2/test.htm";

my $dbh; my $sql; my $sqlH; my $sql2;
my @monMdayArray; my @monArray; my @mdayArray; my @wdayArray;

######################################################################
+###################
#
#
#############################Main Logic Section#######################
+########################
if ($ARGV[0]) {
    if ($ARGV[0] eq "/p") {
        &getDate;
        &buildQuery;
    print "Built\n";
    &dbConnect;
    print "Connected\n";
    &executeQuery;
    print "Executed\n";
    &writeOut;
    print "Written\n";
    &dbClose;
    print "Closed\n";
    &dbDisconnect;
    print "Disconnected\n";
    exit;
        } else {
        &writeHelp;
        exit;
    }
} else {
    &writeHelp;
    exit;
}

######################################################################
+###################
#
#
##############################Subroutine Section######################
+###################

#--------------------Get Current Day--------------------
sub getDate {
  
  my @argTimeArray;
  my $theLocalTime;
  
  if ($ARGV[1]) {
    @argTimeArray = split /\//, $ARGV[1];
    $theLocalTime = timelocal(0, 0, 0, $argTimeArray[1], ($argTimeArra
+y[0]-1), $argTimeArray[2]);
  } else {
    $theLocalTime = time;
  }

        (my $sec0,my $min0,my $hour0,my $mday0,my $mon0,my $year0,my $
+wday0,my $yday0,my $isdst0) = localtime($theLocalTime+86400);
        (my $sec1,my $min1,my $hour1,my $mday1,my $mon1,my $year1,my $
+wday1,my $yday1,my $isdst1) = localtime($theLocalTime+172800);
        (my $sec2,my $min2,my $hour2,my $mday2,my $mon2,my $year2,my $
+wday2,my $yday2,my $isdst2) = localtime($theLocalTime+259200);
        (my $sec3,my $min3,my $hour3,my $mday3,my $mon3,my $year3,my $
+wday3,my $yday3,my $isdst3) = localtime($theLocalTime+345600);
        (my $sec4,my $min4,my $hour4,my $mday4,my $mon4,my $year4,my $
+wday4,my $yday4,my $isdst4) = localtime($theLocalTime+432000);
        (my $sec5,my $min5,my $hour5,my $mday5,my $mon5,my $year5,my $
+wday5,my $yday5,my $isdst5) = localtime($theLocalTime+518400);
        (my $sec6,my $min6,my $hour6,my $mday6,my $mon6,my $year6,my $
+wday6,my $yday6,my $isdst6) = localtime($theLocalTime+604800);

        @monArray =  ($mon0,$mon1,$mon2,$mon3,$mon4,$mon5,$mon6);
        @mdayArray = ($mday0,$mday1,$mday2,$mday3,$mday4,$mday5,$mday6
+);
        @wdayArray = ($wday0,$wday1,$wday2,$wday3,$wday4,$wday5,$wday6
+);
        
        my $i;
        
        for ($i=0; $i<(scalar(@monArray)); $i++) {

            SWITCH: {
                    if ($monArray[$i] eq 0) {$monArray[$i]="01"; last 
+SWITCH;}
                    if ($monArray[$i] eq 1) {$monArray[$i]="02"; last 
+SWITCH;}
                    if ($monArray[$i] eq 2) {$monArray[$i]="03"; last 
+SWITCH;}
                    if ($monArray[$i] eq 3) {$monArray[$i]="04"; last 
+SWITCH;}
                    if ($monArray[$i] eq 4) {$monArray[$i]="05"; last 
+SWITCH;}
                    if ($monArray[$i] eq 5) {$monArray[$i]="06"; last 
+SWITCH;}
                    if ($monArray[$i] eq 6) {$monArray[$i]="07"; last 
+SWITCH;}
                    if ($monArray[$i] eq 7) {$monArray[$i]="08"; last 
+SWITCH;}
                    if ($monArray[$i] eq 8) {$monArray[$i]="09"; last 
+SWITCH;}
                    if ($monArray[$i] eq 9) {$monArray[$i]="10"; last 
+SWITCH;}
                    if ($monArray[$i] eq 10) {$monArray[$i]="11"; last
+ SWITCH;}
                    if ($monArray[$i] eq 11) {$monArray[$i]="12"; last
+ SWITCH;}
            }
        }
        
        for ($i=0; $i<(scalar(@mdayArray)); $i++) {
          if (length $mdayArray[$i] eq 1) {$mdayArray[$i] = "0".$mdayA
+rray[$i];}
        }
        
        for ($i=0; $i<(scalar(@mdayArray)); $i++) {
          $monMdayArray[$i] = $monArray[$i]."/".$mdayArray[$i];
        }
        
        for ($i=0; $i<(scalar(@wdayArray)); $i++) {
            SWITCH: {
                    if ($wdayArray[$i] eq 0) {$wdayArray[$i]="Sunday";
+ last SWITCH;}
                    if ($wdayArray[$i] eq 1) {$wdayArray[$i]="Monday";
+ last SWITCH;}
                    if ($wdayArray[$i] eq 2) {$wdayArray[$i]="Tuesday"
+; last SWITCH;}
                    if ($wdayArray[$i] eq 3) {$wdayArray[$i]="Wednesda
+y"; last SWITCH;}
                    if ($wdayArray[$i] eq 4) {$wdayArray[$i]="Thursday
+"; last SWITCH;}
                    if ($wdayArray[$i] eq 5) {$wdayArray[$i]="Friday";
+ last SWITCH;}
                    if ($wdayArray[$i] eq 6) {$wdayArray[$i]="Saturday
+"; last SWITCH;}
            }
        }
        
}

#-----------End of Calulation------------------#


#--------------------Build Query----------------------------
sub buildQuery {
        $sql = "SELECT facility, department, name, soc, anniversary, y
+ears "
        ."FROM EMPFILE "
        ."WHERE ((anniversary LIKE '".($monArray[0]."/".$mdayArray[0])
+."') OR "
        ."(anniversary LIKE '".($monArray[1]."/".$mdayArray[1])."') OR
+ "
        ."(anniversary LIKE '".($monArray[2]."/".$mdayArray[2])."') OR
+ "
        ."(anniversary LIKE '".($monArray[3]."/".$mdayArray[3])."') OR
+ "
        ."(anniversary LIKE '".($monArray[4]."/".$mdayArray[4])."') OR
+ "
        ."(anniversary LIKE '".($monArray[5]."/".$mdayArray[5])."') OR
+ "
        ."(anniversary LIKE '".($monArray[6]."/".$mdayArray[6])."')) "
        ."ORDER BY facility, years DESC";       
}

#--------------------Connect to Database--------------------
sub dbConnect {
        $dbh = DBI->connect("DBI:CSV:f_dir=$dir1;csv_sep_char=\011");
        $dbh->{'RaiseError'} =0;
        $@ = '';
}

#--------------------Execute Query--------------------------
sub executeQuery {
    
        eval{$sqlH = $dbh->prepare($sql);};
        if ($@) {print "Database Error: " . $dbh->errstr();exit;}
        print "Prepared\n";
        eval{$sqlH->execute();};
        #eval{$sqlH->execute($monArray[1], $mdayArray[1], $monArray[2]
+, $mdayArray[2], $monArray[3], $mdayArray[3], 
        #                                        $monArray[4], $mdayAr
+ray[4], $monArray[5], $mdayArray[5], $monArray[6], $mdayArray[6]);};
        if ($@) {print "Database Error: " . $dbh->errstr();exit;}
}

#--------------------Case Conversion------------------------
sub caseConvert {
    my $j;
    my $returnStr;
    for ($j=0; $j<@_; $j++) {
        $_[$j] = lc ($_[$j]);
        $_[$j] = ucfirst ($_[$j]);
        if ($j ne 0) {
            $returnStr = $returnStr." ".$_[$j];
        } else {
            $returnStr = $_[$j];
        }
    }
    return $returnStr;
}


#-------------------Bulk Write------------------------------
sub writeOut {

  open (NEWOUTFILE, ">$anniverWeek")||die "Error: ".$!;

    print NEWOUTFILE "<link rel=\"stylesheet\" href=\"/css/styles.css\
+">\n";
    
    print NEWOUTFILE "<table width=\"100%\" cellspacing=\"2\" cellpadd
+ing=\"2\">\n"
        ."<tr><td class=\"defaultFont\">\n"
        ."<div align=\"center\"><a href=\"#\" onClick=\"window.close()
+\">Close Window</a>\n"
        ."</div></font></tr></td></table>\n";

    print NEWOUTFILE "<table align=\"center\" border=\"1\" bordercolor
+=\"#CCCCCC\" cellspacing=\"0\" cellpadding=\"2\">\n"
               ."  <tr>\n"
               ."  <th colspan=\"4\" bgcolor=\"#006633\"><font class=\
+"defaultFont\" color=\"#FFFFFF\">"
               #."Anniversarys For This Week \n"
               ."Service Anniversaries For $monMdayArray[0] through $m
+onMdayArray[6]\n"
               ."</font></th>\n"
               ."  </tr>\n";    
    
    print NEWOUTFILE "  <tr align=\"left\">\n"
               ."    <td><font class=\"defaultFont\"><b>Name</b></font
+></td>\n"
               ."    <td><font class=\"defaultFont\"><b>Dept</b></font
+></td>\n"
               ."    <td align=\"right\"><font class=\"defaultFont\"><
+b>Years</b></font></td>\n"
               ."    <td align=\"right\"><font class=\"defaultFont\"><
+b>Weekday</b></font></td>\n"
               ."  </tr>";

  my $row;
  my $facilPrev;
  #my $facilTemp;

  WRITEBDAY: while ($row = $sqlH->fetchrow_hashref) {
    my $facilTemp = $row->{facility};
    my $deptTemp  = $row->{department};
    my $nameTemp  = $row->{name};
    my $socTemp   = $row->{soc};
    my $anniversary = $row->{anniversary};
    my $yearsTemp = $row->{years};
    $yearsTemp = $yearsTemp+1;

   
    foreach my $excludeAnnTemp (@excludeAnn) {
      if ($excludeAnnTemp eq $socTemp) {next WRITEBDAY;}
    }
    
    my @facilArrayTemp = split / /, $facilTemp;
    my @deptArrayTemp = split / /, $deptTemp;
    my @nameArrayTemp = split / /, $nameTemp;

    $facilTemp = &caseConvert(@facilArrayTemp);
    $deptTemp = &caseConvert(@deptArrayTemp);
    $nameTemp = &caseConvert(@nameArrayTemp);
    
    my $i;
    my $j;
    
    for ($i=0; $i<(scalar(@monMdayArray)); $i++) {
      if ($anniversary eq $monMdayArray[$i]) {$j=$i;}
    }

    if ($facilPrev ne $facilTemp) {
        if ($facilPrev) {print NEWOUTFILE "<tr><td colspan=\"4\">&nbsp
+;</td></tr>\n";}
        $facilPrev = $facilTemp;
        print NEWOUTFILE "  <tr>\n"
                        ."    <td nowrap colspan=\"4\"><font class=\"d
+efaultFont\"><u>$facilTemp</u></font></td>\n"
                        ."  </tr>\n";
    }

    print NEWOUTFILE "  <tr>\n"
                    ."    <td nowrap><font class=\"defaultFont\">$name
+Temp</font></td>\n"
                    ."    <td nowrap><font class=\"defaultFont\">$dept
+Temp</font></td>\n"
                    ."    <td align=\"right\"><font class=\"defaultFon
+t\">$yearsTemp</font></td>\n"
                    ."    <td align=\"right\"><font class=\"defaultFon
+t\">$wdayArray[$j]</font></td>\n"
                    ."  </tr>\n";
    }
         
     #print NEWOUTFILE "</td></tr>\n";        

      print NEWOUTFILE "</table>\n";
   close NEWOUTFILE;
}


#-------------------Close Database Connection---------------
sub dbClose {
        $sqlH->finish();
}

#-------------------Close Database Connection---------------
sub dbDisconnect {
        $dbh->disconnect();
}

    [download]
[reply]
[d/l]
      Unless I have counted wrong, line 213 is   WRITEBDAY: while ($row = $sqlH->fetchrow_hashref) {

      I don't see how you can get a Use of uninitialized value in string ne at Test.pl line 213. error in this statement.

      May be I am looking at the wrong statement.

      CountZero

      "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[reply]
[d/l]

In Section Seekers of Perl Wisdom