Copying Files to MySQL

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

I have a script which basically (among other things) I want to read the contents of some XML files into a table in MySQL. I'm using DBI.pm to interact with MySQL. Everything else is working fine: I'm able to read the files, parse them a bit for other things, but at the end of the script, when I try to reopen the files to copy their total contents to MySQL, the contents aren't saved. Below is the sub-routine which I wrote to handle this part:

sub xmlfiles_to_mysql {
  my $xmlfile_contents;
    
  while(my ($file_id,$xml_file) = each(%xml_files) ) {
    
     open(XML_FILE, "./xml_files/$xml_file");

     while (<XML_FILE>) { 
        chomp;
        $xmlfile_contents .= $_;
     }
     my $sql_stmnt = "REPLACE INTO xml_files
                      (file_id, xml_file)
                      VALUES(?,?)";
     my $sth = $dbh->prepare($sql_stmnt);
     $sth->execute($file_id,$xmlfile_contents);
     $sth->finish();

     close XML_FILE;
  }
}
[download]

The hash %xml_files is set up outside the sub-routine and seems to work alright. The table in MySQL have the names of the xml filse in the first column. They have NULL for the xml_file column. That column is a BLOB column.

I'm getting this error message for each file read:

readline() on closed filehandle XML_FILE at xml_processing.pl line 275.

Obviously the file is closing before it's finished. But I don't see how. Any thoughts on what I'm doing wrong or in the wrong order? I know it's probably simple, but I just don't see it. Thanks in advance for any help.

-Spenser

That's Spenser, with an "s" like the detective.

Comment on Copying Files to MySQL Select or Download Code

Replies are listed 'Best First'.
Re: Copying Files to MySQL by Fletch (Bishop) on Apr 20, 2007 at 17:42 UTC
More likely is that it's not opening for some reason but since you don't check the return value of your open call you don't know why. `open( XML_FILE, "./xml_files/$xml_file") or die "Can't open './xml_files/$xml_file': $!\n";` [download] Update: Gah, dropped a bang there. (friedo noticed as well)	[reply] [d/l]
Re^2: Copying Files to MySQL by johngg (Canon) on Apr 20, 2007 at 20:30 UTC
Perhaps you meant `$!` rather than a bare `$` at the end of the `die`? Cheers, JohnGG	[reply] [d/l] [select]
Re: Copying Files to MySQL by wjw (Priest) on Apr 20, 2007 at 20:37 UTC
I am wondering about the "xml_processing.pl line 275". What does line 275 say? Is that line number within the code you included above? One thing you might want to do is close that file handle as soon as your done reading the file into $xmlfile_contents. You don't need the open file handle after that point anyway. Thanks to naikonta for pointing out the missing italics tag... :-) My bad... ...the majority is always wrong, and always the last to know about it...	[reply]
Re: Copying Files to MySQL by Cody Pendant (Prior) on Apr 21, 2007 at 13:05 UTC
Along with "not checking what happens when you open()" you're also not checking what happens when you do your SQL execute. `$sth->execute($file_id,$xmlfile_contents) \|\| die $sth->errstr;` is what you want, I think. But it's probably going to be answered by checking the open call. What's your working directory, for a start? ($_='kkvvttuu bbooppuuiiffss qqffssmm iibbddllffss') =~y~b-v~a-z~s; print	[reply] [d/l]