I tested this on a dir with 3 files and it is not inserting the
data into the table fields. The tables are related via the $id(pk in blocks and fk in vials).<br
Read more... (2 kB)
Please excuse the previous typo.

What do you want to tell me with the code you pasted? It still has the ->prepare statements inside the loop, and at a casual glance I see no difference to what you posted originally. What did you change?

I tested this on a dir with 3 files and it is not inserting the data into the table fields.

Was it working in your original version? At least I didn't read anything about any failures.

Anyway, you should set the RaiseError option on connecting to the database, see the DBI documentation.

The code you have posted above is identical to the code you posted first? Maybe its a mistake on my part.

There is a one-to-many relationship between vials(block_id) and blocks(id), but your code doesn't seem to use it in any way. If there are many rows in vials for each row in blocks, you have the opportunity to optimize away many of the INSERT followed by SELECT on the blocks table. Storing the names (pluralisation is really confusing here) and id pairs in a hash will definately speed things up.

Also, most databases with autonumbering offers a special function for retrieving the number which should perform better than doing a SELECT to find the latest inserted id. This will especially help if the names column isn't indexed.

If speed is the issue, I'd use this approach:

1. Create a surrogate temporary table with no indexes for each table you're loading.

2. Bulk load your temporary tables. (I don't know MySQL, so I'm unfamiliar with its bulk data loader.)

3. Insert new records from your temporary tables into your production tables.

   -- Only insert them if they don't exist
   INSERT INTO dev.blocks (names)
     SELECT names FROM dev.tmp_blocks
     WHERE names NOT IN (
       SELECT names from dev.blocks
     )
   INSERT INTO dev.vials (block_id, lt, well_position, gene, barcode)
     SELECT block_id, lt, well_position, gene, barcode
     FROM dev.tmp_vials
     WHERE ... clause to prevent inserting duplicates ...
   [download]

4. Drop your surrogate tables.

This way, you can let the database do the work as efficiently as possible. You'll reduce the number of transactions on the database, reduce the traffic between your server and the database server, reduce the I/O consumed by the database, etc.

There's always a better way. ;-)

I'd do something like this (untested):

my $sth1 = $dbh->prepare("INSERT INTO dev.blocks (names) VALUES(?)"); 
+     
my $sth3 = $dbh->prepare("INSERT INTO dev.vials (block_id, lt, well_po
+sition, gene, barcode) VALUES(?,?,?,?,?)");

for my $file (@files) {
  open my $fh, '<', "/home/mydir/data/test_files/$file" or die "can't 
+open $file : $!\n";
  
  while(my $line = <$fh>) {
    chomp $line;
    my ($well, $gene, $lt, $barcode, $name) = split(/\t/, $line);
    
    eval {
      $sth1->execute($name); 
    };
    if ($@) {
      die "database error 1: ", $dbh->errstr;
    }
    
    my $id = $dbh->{mysql_insertid};
    
    eval {
      $sth3->execute($id, $lt, $well, $gene, $barcode);  
    };
    if ($@) {
      die "database error 2: ", $dbh->errstr;
    }
    
  }
  close($fh);
}
[download]

Notes:

• Move the $dbh->prepare statements outside of the loop.
• Wrap the $sth->execute statements in an eval block, for error checking.
• Assuming your 'blocks' table has an auto-incrementing primary key, the $dbh->{mysql_insertid} statement saves you from having to do a select on the thing you just inserted.

After the line with the split statement, you should probably add something like this for each field you expect to read in:

unless($well) {
  die "missing value for well in file $file, line $. \n";
}
[download]

Note that the special variable $. contains the current line number of the file being read. You could also change the 'die' into a 'print' (to write errors into a log file, maybe), followed by a 'next' (to skip to the next line).

The code you posted has a lot of problems. For example, you don't put anything into the @files array before looping over it - so that loop has nothing to work on. And you can't have a variable called $file that contains the file name and also use it as the file handle. And the file handle you close should be the same as the one you open, etc.