sdyates has asked for the wisdom of the Perl Monks concerning the following question:

I have made progress from yesterday. I can now read a file in to a database. I am using an IIS log as an example. However, when the log file has fields with values of '0' or ' 0 ', perl reads this as being at the end of the file and stops. By changing all occurences of 0 to an O, will allow the script to complete properly. 
if ($UserFile) {
    print start_html();
    # reads the log file and creates a SQL string. The string holds th
+e data to create each column 
    # in the table...

# This part reads one line of the file and creats a column in a table 
+for each space-separated item.
    CreateTable();
    $dbh = open_dbi();

    my $sth = $dbh->prepare("CREATE TABLE $TableName ($SQLString)") or
+ err_trap("Cannot prepare the load_category_names query");
    $sth->execute or err_trap("Cannot execute the SQL query to CREATE 
+TABLE");

    open IN, "$UserFile";
    $z = 0;
    $B = "\'";
    Delimiter();

# I believe this is the problem, but not sure how to fix...
    while (<IN>) {
        $TheRec = $_;    
        @NewItem = ();
        $SQLString = "\'";
        $z = 0;
        chomp $TheRec;
        
        @NewItem = split(/$FileDelimiter/, $TheRec);        #split the
+ line in records by space
        while ($NewItem[$z]) {
            $SQLString = $SQLString . $NewItem[$z] . "\',\'";
            $z=$z + 1;
        }
        # removes the trailing comma from the end of the line
        $LineLength = length($SQLString);
        $LineLength = $LineLength -2;
        $SQLString = substr($SQLString,0,$LineLength);
         my $sth = $dbh->prepare("INSERT INTO $TableName VALUES($SQLSt
+ring)") or err_trap("Cannot prepare the load_category_names query");
        $sth->execute or err_trap("Cannot execute the SQL query to LOA
+D DATA INFILE");
    }
    close_dbi($dbh);
}


# In this section, I had the same problem, but I added if ($NewItem[$C
+olumnNumber] eq 0) {$NewItem[$ColumnNumber] = O}; and the problem was
+ resolved--all required columns were created...

sub CreateTable {
        $zzz=1;
        @NewItem = ();
        open LOGFILE, "$UserFile" or die "Cannot open file $UserFile";
        Delimiter();
        while (<LOGFILE>) {
            $TheRec = $_;
            chomp($TheRec);
        }
        $A =();
        @NewItem = split(/$FileDelimiter/, $TheRec);        #split the
+ line in records by space

        # sql code for each column is formed here
        $ColumnNumber=0;
        if ($NewItem[$ColumnNumber] eq 0) {$NewItem[$ColumnNumber] = O
+};
        while ($NewItem[$ColumnNumber]) {
            $SQLString = $SQLString . "$ColumnName[$ColumnNumber] CHAR
+(255),";
            $ColumnNumber = $ColumnNumber +1;
        }
        # removes the trailing comma from the end of the line
        $LineLength = length($SQLString);
        $LineLength = $LineLength -1;
        $SQLString = substr($SQLString,0,$LineLength);
}

[download]
Please help--thanks...

Replies are listed 'Best First'.
Re: while(INFILE) terminating before finishing file.
by jwest (Friar) on Aug 23, 2001 at 22:32 UTC
    Instead of using while(<INFILE>)... use while(defined($_ = <INFILE>))...
    This will let the result of <INFILE> be false, while keeping it true for defined values.

    Hope this helps!

    --jwest

    Update: changed to code that works ;) 
    -><- -><- -><- -><- -><-
All things are Perfect
    To every last Flaw
    And bound in accord
         With Eris's Law
 - HBT; The Book of Advice, 1:7
[reply]
[d/l]
[select]
      Well, that worked very well, unfortunately, it created another problem, the $TheRec = $_; is now empty. Therefore, the entire table, while populated with 1374 lines, is empty. What's the best way to go about this? Thanks again for you help--I was going nowhere quick :)
[reply]
        Well, unless you're sure that $TheRec is left empty, in which case we should start there, I'd look at your other while() loops. For example, the next one: while ($NewItem[$z]) { ... } will fail similarly to your old while (<IN>) loop. To rectify this one, for instance, you might need to switch to something more like while(defined($NewItem[$z])) or somesuch.

        Although there's nothing really wrong with the way you expressed this, by the way, it is more idiomatic to write the loop like this:

          # Itterate over the contents of the @NewItem list
  # For each item in the list, set $item to the value
  # (equivalent to $NewItem[$z] in your code)
  foreach my $item (@NewItem) {
    # And add this value in like you did before.
    $SQLString = $SQLString . $item . "\',\'";
  }

        [download]

        And even that's a little more loose that it could be.

        At any rate, go back and refactor your code, looking for more places to stick watch for the defined-but-false condition. And be sure that $TheRec is actually coming back blank.

        Hope this helps!
        --jwest

        -><- -><- -><- -><- -><-
All things are Perfect
    To every last Flaw
    And bound in accord
         With Eris's Law
 - HBT; The Book of Advice, 1:7
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom