Perl Script Help

[andrew23chan]

Hi I got this error and i can't figured it out.

Can't call method "EOF" on an undefined value at N:\web_site\insp\smmc\quality\queries\dts_add.pl line 102, <DTS> line 2.

Thanks :cry:

[Rashakil Fol]

If you don't post the code that is causing this problem, people are unlikely to be able to help you.

[andrew23chan]

this is the code:
its quite long..
sorry

use Win32::ADO;
use Win32::OLE;
use Win32::OLE::Variant;

# Time Variables
  $time1 = localtime();  # Time of Start of Program
    ($tsec, $tmin, $thour, $tmday, $tmon, $tyear) = localtime();
    $tmon++;
    $tyear = 1900 + $tyear;
  $time2 = time() - (1 * 24 * 60 * 60); # Date Yesterday for Query
    ($sec, $min, $hour, $mday, $mon, $year) = localtime($time2);
        $mon++; # Increments Month by One
        $year = 1900 + $year; # Adds to Make Year Correct
        if (length($mday) < 2) {
           $mday = "0$mday";
        } 
    $DateRec = "$mon/$mday/$year";
    $SeaDate = "$mday/$mon/$year";
    $RunDate = "";

sub date_change {
    local ($param_1, $param_2) = @_;
    if ($param_2 == '1') {
  # Convert Date for Oracle Query
    $sla1 = index($param_1, "/");
    $sla2 = index($param_1, "/", $sla1+1);

        $currDay = substr($param_1, 0, $sla1);
#       $currDay = "0" . $currDay  if $currDay < 10;
        $currMon = substr($param_1, ($sla1+1), ($sla2-$sla1-1));
        if ($currMon == 1) {$cuMon = "JAN";}
        elsif ($currMon == 2) {$cuMon="FEB";}
        elsif ($currMon == 3) {$cuMon="MAR";}
        elsif ($currMon == 4) {$cuMon="APR";}
        elsif ($currMon == 5) {$cuMon="MAY";}
        elsif ($currMon == 6) {$cuMon="JUN";}
        elsif ($currMon == 7) {$cuMon="JUL";}
        elsif ($currMon == 8) {$cuMon="AUG";}
        elsif ($currMon == 9) {$cuMon="SEP";}
        elsif ($currMon == 10) {$cuMon="OCT";}
        elsif ($currMon == 11) {$cuMon="NOV";}
        elsif ($currMon == 12) {$cuMon="DEC";}
        $currYear = substr($param_1, ($sla2+1), 2);
        $currDate = "$currDay-$cuMon-$currYear"; # Formats the date so it can be queried from the Oracle Database
        }
        else {
             $currDate = "20" . join("", reverse( $param_1 =~ m#(\d{2})/(\d{2})/(\d{2})# ));
          }
        return($currDate);
}

# Open Log File to Log any Errors and Successes
  open (LogFile, ">> n:/Web_Site/insp/smmc/quality/queries/dts_Log_File.txt");
  flock (LogFile, 2);
  print LogFile "\n-------------------- $time1 --------------------\n";

# Set Variables to Count Records
  $CountRows = 0;
  $CountNLC = 0;
  $CountNDup = 0;
  $CountDup = 0;
  $CountWip = 0;
  $CountNeg = 0;
  $CountDupRec = 0;
  $Dup = "";

# Opens smmc_dts.txt file and reads records into an database.
  open (DTS, "c:/InetPub/ftproot/smmc_dts.txt") || die;

# Opens Last Recorded Date
  open(run_date, "< n:/Web_Site/insp/smmc/quality/queries/dts_run_date.txt");
  @lines = <run_date>;
  foreach $line (@lines) {
    $compare = $line;
    }
  close(run_date);

# Setup Connections to Databases
  $conn = Win32::OLE->new("ADODB.Connection");
  $conn->Open("DSN=smmcdb;UID=web_login;PWD=4website");

# Reads through and adds records to the database
while ($results = <DTS>) {
    @record = split(/\|/, $results);
    $Totalres = @record;
    $returnDate = &date_change(@record[4], 1);
    $compareDate = &date_change(@record[4], 2);
    print "returnDate: $returnDate | compareDate: $compareDate | compare: $compare \n";
    if ($compareDate gt $RunDate) {
       $RunDate = $compareDate;
       }
    if ($compare lt $compareDate) {
    for ($x=0; $x<$Totalres; $x++) {
        @record[$x] =~ s/^\s*(.*?)\s*$/$1/;
    }
    $CountRows++;
    if (@record[7] gt '0') {
    $comp = Win32::OLE->new("ADODB.Recordset");
    $sql = "SELECT ID FROM DTS WHERE (('@record[5]' Like Part_No) AND (Vendor_No = @record[1]) AND ('$returnDate' BETWEEN Implementation_Date and Cancel_Date))";
    $comp = $conn->execute($sql);
    if ($comp->EOF) {
        $CountNDup++;
     }
     else {

           $Sql = "INSERT INTO Inspection_Record_Header (Part_No, Lot, PO_No, PO_Line, Lot_Quantity, Quantity_Accepted, RCF, Product_Line, Comm_Code, Vendor_No, Vendor_Name, Date_Received, Hub, Comments, Form_Rev, Quantity_Rejected, Inspect_Time, Inspect_Scrap) VALUES ('@record[5]', 'DTS-@record[2]@record[3]', '@record[2]', '@record[3]', @record[6], @record[6], 'SMMC', '@record[8]', '@record[7]', @record[1], '@record[0]', '$returnDate', 'SMMC', 'DTS Parts Populated by dts_add.pl automatically.  Run date:  $tmon/$tmday/$tyear.', '1', 0, 0, 0)";

       $CountNLC++;

       $conn->execute($Sql);
       #print LogFile "@record[0]\n";
       #print LogFile "@record[1]\n";
       #print LogFile "@record[2]\n";
       #print LogFile "@record[3]\n";
       #print LogFile "@record[5]\n";
       #print LogFile "@record[6]\n";
       #print LogFile "@record[7]\n";
       #print LogFile "@record[8]\n";
     }
        if ($comp) {
           $comp->Close;
           }
    }
    }
    else {
         $CountNeg++;
         }
}   
    $conn->Close;
    close DTS;

#   unlink "c:/InetPub/ftproot/smmc_dts.txt";

# Records Last Date Run for Comparison

  if ($compare gt $RunDate) {
     $RunDate = $compare
     }
  open(run_date, "> n:/Web_Site/insp/smmc/quality/queries/dts_run_date.txt");
  print run_date $RunDate;
  close(run_date);


    print LogFile "There were $CountRows Rows Read from the File.\n";
    print LogFile "There were $CountNLC DTS Parts Added to the Database.\n";
    print LogFile "There were $CountNeg Negative Transactions.\n";
    print LogFile "There were $CountNDup Rows Skipped.\n";
    $time1 = localtime();
    print LogFile "\n-------------------- $time1 --------------------\n";
    flock (LogFile, 8);
    close LogFile;

[Rashakil Fol]

http://search.cpan.org/dist/Win32-OLE/lib/Win32/OLE.pm#Methods

$comp = Win32::OLE->new("ADODB.Recordset");

The 'new' method returns undef on failure; that's what's happening. Now you need to figure out why it is failing, since I don't know the particulars of this module, and you can read documentation as well as I can.

use strict; use warnings;

to avoid errors and ugliness (always).

[andrew23chan]

Hi,
Thanks for that tip.
After running it again, i got this error.. a different one.

Global symbol "$mday" requires explicit package name at N:\web_site\insp\smmc\quality\queries\dts_add.pl line 17.

Regards