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

This code doesn't work, but I have to think there is an easier way to read a file directory into a database, but I just don't see it.
my $SQL= "INSERT INTO DIRECTORY_LIST(FILENAME) VALUES ('$filename')"; my $sth= $dbh->prepare($SQL) or die "Prepare".$dbh->errstr; opendir my $dh, $dir or die "Could not open '$dir' for reading: $!\n"; $sth-> execute() or die "".$dbh->errstr; while (my $row = readdir $dh) { if ($filename eq '.' or $filename eq '..'){ next; my ($filename)=($row->[0]); } print "$filename\n"; } print "\n"; closedir $dh; $dbh->disconnect;

Replies are listed 'Best First'.
by marto (Cardinal) on Feb 26, 2024 at 17:26 UTC

    Something like:

    my $path = '/home/marto/code/'; # open dir opendir(my $dh, $path) || die "Can't opendir $path $!"; # grep, testing for files my @files= grep { -f "$path/$_" } readdir($dh); closedir $dh; # connect to sqlite db my $dbh = DBI->connect('dbi:SQLite:dbname=filelist.sqlite','',''); # prepare insert my $sth = $dbh->prepare('insert into files(filename) values(?)'); # bind params $sth->bind_param_array(1, \@files); # execute $sth->execute_array({ ArrayTupleStatus => \my @tuple}) or die $sth->er +rstr;
      what sort of magic is that ?
      $sth->execute_array({ ArrayTupleStatus => \my @tuple}) or die $sth->er
      never see it before! what does it do?

        what sort of magic is that ?

        $sth->execute_array({ ArrayTupleStatus => \my @tuple}) or die $sth->errstr;

        It depends on which portion of that line seems like "magic" to you.

        • LanX answered with the execute_array description.
        • ArrayTupleStatus is described in the DBI bind_param_array and execute_array documentation.
        • \my @tuple syntax is shown in the examples of that DBI documentation. The concept of creating an array and immediately getting a reference to that array is described in perlref: Assigning-to-References, and is essentially shorthand for calling my @tuple; in one line and then using \@tuple in the execute_array() call to pass the reference to the function. (This is my best guess as to what you would consider the "magic" portion of that line of code.)
        • or die $sth->errstr is the typical way to raise an error with DBI, unless you use RaiseError

        You and me both....

        But it works...

by erix (Prior) on Feb 26, 2024 at 20:07 UTC

    Maybe bulkloading is easier; most DBs have a way to bulkload, for instance postgres:

    ls -1v *.txt | psql -c " create table if not exists t(filename text); copy t from stdin; "
by cavac (Parson) on Feb 27, 2024 at 12:41 UTC

    Reading directories is a bit more complicated than just opendir/readdir/closedir, especially if you want to recurse into subdirectories and follow common conventions like ignoring files that start with a dot (Linux/Unix "hidden" files).

    There are modules like File::Find that can do a lot of the magic for you. But if you want do more complicated stuff, you may or may not have to roll your own. For example, handling files as you see them (a tactic i use in my own frameworks, especialy when i need to do a lot of processing per file or need complicated search rules) or deciding if you want to read certain directories depending on config options in a database or something like that.

    Here's a simple example code of how to recursively iterate through subdirectories. Note that i only have one directory handle open at any one time, to avoid running out of open file handles (Let a bunch of Microsoft Office users get access to a shared drive for a decade and you get directory structures deeper than the Mariana Trench.)

    #!/usr/bin/env perl use v5.36; use strict; use warnings; use Data::Dumper; use Carp; use English; my $dirfname = "."; # Start with working directory my @files = findFiles($dirfname); print Dumper(\@files); exit(0); sub findFiles($basedir) { my @fnames; my @dnames; opendir(my $dfh, $basedir) or croak($ERRNO); while((my $fname = readdir $dfh)) { if($fname =~ /^\./) { # Ignore "hidden" files next; } my $fullname = $basedir . '/' . $fname; if(-d $fullname) { push @dnames, $fullname; } elsif(-f $fullname) { push @fnames, $fullname; } else { # It is something else, like a softlink. Ignore it for now } } closedir $dfh; # Only recurse AFTER closing the current dir, this way we won't ex +ceed filehandle maximums foreach my $dname (@dnames) { push @fnames, findFiles($dname); } return @fnames; }

    PerlMonks XP is useless? Not anymore: XPD - Do more with your PerlMonks XP
by Anonymous Monk on Feb 26, 2024 at 21:11 UTC
    This can't possibly work because there is no execute inside the loop.

    Looks like copy and paste from someone who can't code and wants to pretend he did some effort.