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.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.