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

Hi there,
first of all, I am not a mysql goeroe, and know just a little bit about perl DBI.
Now that said, here is my problem.
I have this piece of code: 
use strict;
use DBI;
my $o = DBI -> connect("foo","bar","you wish");

my $dir="/A_Directory";
opendir(DIR, $dir) or die $!;

foreach ( readdir DIR ) {
    next if /^\.{1,2}$/;
    my $file=$dir."/".$_;
    my $query;
    $query="insert into search ( title ) values ( '$file' )";
    print "$query: ",$o->do($query),"\n";
    $query="update search set body = LOAD_FILE('$file') where title = 
+'$file'";
    print "$query: ",$o->do($query),"\n";
    }
closedir(DIR);

$o->disconnect;

[download]
The mysql table search is setup: 
title -> varchar(200) primary key not null
body  -> text
fulltext on title and body
Now, the title is inserted OK, but the body is never inserted. (I also tried to put it all in one sql statement, but got the same problem).

But, when I copy/paste the stdout in the mysql CLI, it works fine.
Am I missing something?

note: perl 561, MySQL 3.23.47
---------------------------
Dr. Mark Ceulemans
Senior Consultant
IT Masters, Belgium

Replies are listed 'Best First'.
Re: load_file and mysql
by gmax (Abbot) on Feb 07, 2002 at 10:42 UTC
    You know, of course, that the LOAD_FILE function only works if the file is in the server's host. If the file is not in the same host, then you should read the file with your script and insert all the values with a query.
    # untested

my $query = qq{ INSERT INTO search VALUES (? ?)};
my $sth = $dbh->prepare($query);

# get the list of filenames into an array, or just loop
# though them as you wish. This is just an example

for my $filename (@files) {
    local $/ = undef; # will read the file at once
    open FILE, "< $filename";
    my $text = <FILE>;
    close FILE;
    $sth->execute( $filename, $text);
}

    [download]
    This example will only work if the size of your file is less than max_allowed_packet. I have a similar routine in my database and I have had no problems inserting big fields. The maximum allowed is 16 MB, but I never needed anything more than 3 MB. 
     _  _ _  _  
(_|| | |(_|><
 _|

    [download]
[reply]
[d/l]
[select]
Re: load_file and mysql
by davis (Vicar) on Feb 07, 2002 at 09:34 UTC
    A thought occurred to me: does $file contain the full path to the file?, if it doesn't, then perhaps MySQL can't find the file you're looking for.
    The reason it works in the CLI could be that you're running the CLI from the correct directory
    Hope this helps
    davis
    Is this going out live?
    No, Homer, very few cartoons are broadcast live - it's a terrible strain on the animator's wrist
[reply]
      Hi Davis,
      Thanks for your quick resonse
      If you look at the dir variable, you'll see a / in front of it. And the load_file always requires a fully qualified pathname.
      ---------------------------
      Dr. Mark Ceulemans
      Senior Consultant
      IT Masters, Belgium
[reply]
        Hi there,
        The call to readdir will not return the full path - here's an example I've just tried:
        #!/usr/bin/perl -w

use strict;
my $dir = "/scratch";
opendir(DIR, $dir)
        or die "Unable to opendir $dir: $!\n";
foreach my $file (readdir DIR) {
        print "file is at: $file\n";
}
closedir(DIR);

        [download]
        That code will only print the file's paths relative to /scratch - in order to get the fully qualified path, I'd need to prepend the value of $dir.
        Note that this may still be barking up the wrong tree ;-)
        davis
        Is this going out live?
        No, Homer, very few cartoons are broadcast live - it's a terrible strain on the animator's wrist
[reply]
[d/l]
      Hi Davis,
      Thanks for your quick response
      If you look at the dir variable, you'll see a / in front of it. And the load_file always requires a fully qualified pathname.
      ---------------------------
      Dr. Mark Ceulemans
      Senior Consultant
      IT Masters, Belgium
[reply]
Re: load_file and mysql
by tachyon (Chancellor) on Feb 07, 2002 at 09:59 UTC

    Here are some basic DBI hints: error checking, ?placeholders?, cached queries and commiting changes.

    Could I recommend this excellent article to you: A short guide to DBI by our own dominus. It should get you on track in no time.

    cheers

    tachyon

    s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print

[reply]
Re: load_file and mysql
by Ryszard (Priest) on Feb 07, 2002 at 10:04 UTC
    I havent had any experience with MySQL specifically using DBI, however in my oracle and postgres travels i always set raiseerror=>1 and check the errstr after the statement.

    This doesnt directly help you, but there may be something there you're not seeing.

    I also recommend the use of placeholders in your statements as the db engine doesnt have to parse the entire statement each time, rather just pull it out of its cache, just saves a bit of overhead. In fact its so easy to use, i dont know why people dont. (Unless their db engine doesnt support if of course.. )

[reply]
      Hi Ryszard,
      I changed (on popular demand :-) ) the code to:
      foreach ( readdir DIR ) {
    next if /^\.{1,2}$/;
    my $file=$dir."/".$_;
    my $query;
    my $h;
    $file=$o->quote($file);
    $query='insert into search ( title ) values ( ? )';
    $h=$o->prepare($query);
    print "$query: ",$h->execute($file),"\n";
        print $h->errstr() if $h->err();
    $query='update search set body=LOAD_FILE(?) where title = ? '  ;
    $h=$o->prepare($query);
           print "$query: ",$h->execute($file),"\n";
        print $h->errstr() if $h->err();
    }

closedir(DIR);

      [download]
      and the problem remains.
      ps: If I remove LOAD_FILE and just insert ? (placeholder), it works fine. I guess DBI has a problem with the LOAD_FILE part.
      ---------------------------
      Dr. Mark Ceulemans
      Senior Consultant
      IT Masters, Belgium
[reply]
[d/l]

Back to Seekers of Perl Wisdom