BioGeek has asked for the wisdom of the Perl Monks concerning the following question:
Hi again,
I fetch data from an external database, and I want to store it locally.
##################
# 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();
I get the error DBD::mysql::st execute failed: You have an error in your SQL syntax near 'TABLE pocus VALUES (6,152159677,152455397,'ENSG00000091831',0.0176846,1)' at line 1 at script-g3d.pl line 57, <LIST> line 1. So the fetch seems OK, but there are problems when I want to store it locally.
The lay-out of the local database is as follows:
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)
Re: DBI: error in SQL syntax.
by bradcathey (Prior) on Aug 09, 2004 at 16:28 UTC
|
| [reply] |
Re: DBI: error in SQL syntax.
by VSarkiss (Monsignor) on Aug 09, 2004 at 16:50 UTC
|
You have the right answer above: there's no TABLE keyword in an INSERT statement.
I just wanted to add a hint that may save you some grief later on: rather than depending on the implicit order of columns in your INSERT, list them explicitly:
INSERT INTO
pocus ( chr, start, stop, gene_name, score, strand )
VALUES
( ?, ?, ?, ?, ?, ? )
This way, even if the table definition changes, your statement will do what you intend. | [reply] [d/l] [select] |
Re: DBI: error in SQL syntax.
by sgifford (Prior) on Aug 09, 2004 at 16:32 UTC
|
Looks like an SQL problem. Print out the SQL statement before you run it, and try typing it manually into the mysql client, replacing the ?'s with the quoted values. That should help you track down the source of the problem.
Your mysql log may also contain what statements are being executed, which could give you further insight.
| [reply] |
Re: DBI: error in SQL syntax.
by duff (Parson) on Aug 09, 2004 at 16:28 UTC
|
Your first value is 6 but your table definition expects a string (varchar(5)). Make sure $rows[4] is treated as a string by enclosing it in double quotes (or using any other string operator on it).
Update: Ack! Ignore me, Brad has the truth of it.
| [reply] [d/l] |
Re: DBI: error in SQL syntax.
by BioGeek (Hermit) on Aug 09, 2004 at 16:56 UTC
|
You're indeed correct, dropping the TABLE solved my problem. Thanks for the quick response. | [reply] |
|
|