#!/usr/bin/perl -w
use strict;
use warnings;
use DBI;
################################################
# Load the files into the MySQL database       #
################################################

my $dbh = DBI->connect("DBI:mysql:dev:informatics", "mystuff", "gene77
+7" ) 
|| die "Could not connect to database:\$DBI::errstr";

# Put all of the files into an array for loading
my $count = 0;
# loop through the filehandles.treat $file as a normal
# file handle my @files
my @files;
foreach  my $file(@files)
 {
    open(my $file, "/home/mydir/data/test_files/$file");
    while(<$file>)
    {
        chomp;
        my @fields = split /\t/;
        my $well = $fields[0];
        my $gene = $fields[1];
        my $lt = $fields[2];
        my $barcode = $fields[3];
        my $name = $fields[4];
        # Insert data into tables
        my $sth1 = $dbh->prepare("INSERT INTO dev.blocks (names)
                            VALUES(?)");            
        # $name indices file are to be loaded into the 'blocks' table 
+'name' field
        $sth1->execute($name); 
        # query dev.blocks for id by passing in the $name 
        my $sth2 = $dbh->prepare("SELECT id from dev.blocks where name
+ = ?");
        $sth2->execute($name);
        # get the result from the blocks table and assign it to a vari
+able. 
        my @result = $sth2->fetchrow();
        my $id = $result[0];
        # Insert the variable into the foreign key block_id in the via
+ls table along 
        # with the other variables to be loaded into the vials table.
        my $sth3 = $dbh->prepare("INSERT INTO dev.vials (block_id, lt,
+ well_position, gene, barcode)
                            VALUES(?,?,?,?,?)");
        $sth3->execute($id,$lt, $well, $gene, $barcode);    
    }
close($ltvec_file);
$count++;
}
[download]