READDIR and DBI

[justin423]

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;

[marto]

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;

[Anonymous Monk]

what sort of magic is that ?

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

never see it before! what does it do?

[pryrt]

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

[LanX]

LMGTFY:

https://metacpan.org/pod/DBI#execute_array

Execute the prepared statement once for each parameter tuple (group of values) provided ... by prior calls to "bind_param_array" , ...

[justin423]

You and me both....

But it works...

[erix]

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;
"

[cavac]

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;
}

[Anonymous Monk]

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.