##################
# Make connection with Ensembl, fetch information and store locally
#################
use strict;
use warnings;
use DBI;

my $datasource_ensembl = "DBI:mysql:ensembl_mart_23_1:ensembldb.ensemb
+l.org";
my $datasource_local = "DBI:mysql:Jeroen:localhost";
my $dbh_ensembl = ();
my $dbh_local   = ();
my $sth_ensembl = ();
my $sth_local   = ();     # database and statement handles
my @rows;         # to get results or queries
my $row_count;    # to see how much we get back
my $username_ensembl = "anonymous";
my $username_local   = "jeroen";
my $password_ensembl = "";
my $password_local = "*****";
my $marker   = 'marker_list.txt';

# connect to database:
$dbh_ensembl = DBI->connect( $datasource_ensembl, $username_ensembl, $
+password_ensembl, { RaiseError => 1 } );
$dbh_local = DBI->connect( $datasource_local, $username_local, $passwo
+rd_local, { RaiseError => 1 } );

open( LIST,  "<$marker" )         or die("Couldn't open file $marker: 
+$!.\n");
open( ERROR, ">error_pocus.txt" ) or die $!;

while (<LIST>) {
    my ( $ensemblID, $score_pocus ) = split( /\t/, $_ );

    # select all sequence entries:

    $row_count = 0;
    $sth_ensembl       = $dbh_ensembl->prepare(
        qq{
         SELECT gene_stable_id, gene_chrom_start, gene_chrom_end, chro
+m_strand, chr_name
         FROM hsapiens_gene_ensembl__gene__main
         WHERE gene_stable_id = "$ensemblID"
    }
    );
    $sth_ensembl->execute();

    # read results:
    while ( @rows = $sth_ensembl->fetchrow_array() ) {
        $row_count++;
        $score_pocus = $score_pocus * 1000;
        my $strand_pocus = ( $rows[3] == 1 ) ? '+' : '-';
        printf "chr%-2g %-9d %-9d %-16s %-12g %3s\n",$rows[4], $rows[1
+], $rows[2],
          $ensemblID, $score_pocus, $rows[3];



    #store results locally
    $sth_local = $dbh_local->prepare("INSERT INTO TABLE pocus VALUES (
+?,?,?,?,?,?)");

        $sth_local->execute($rows[4], $rows[1], $rows[2], $ensemblID, 
+$score_pocus, $rows[3]);

    }

    unless ($row_count) {
        print ERROR
"The gene $ensemblID  doesn't appear to be placed on the current assem
+bly.\n";
    }

}

# tidy up:
close(LIST);
$sth_ensembl->finish();
$sth_local->finish();
$dbh_ensembl->disconnect();
$dbh_local->disconnect();
[download]

mysql> DESCRIBE pocus;
+-----------+---------------------+------+-----+---------+-------+
| Field     | Type                | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| chr       | varchar(5)          | YES  |     | NULL    |       |
| start     | int(10) unsigned    | YES  |     | NULL    |       |
| stop      | int(10) unsigned    | YES  |     | NULL    |       |
| gene_name | varchar(15)         | YES  |     | NULL    |       |
| score     | float(6,3) unsigned | YES  |     | NULL    |       |
| strand    | char(1)             | YES  |     | NULL    |       |
+-----------+---------------------+------+-----+---------+-------+

6 rows in set (0.00 sec)
[download]