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)

Replies are listed 'Best First'.
Re: DBI: error in SQL syntax.
by bradcathey (Prior) on Aug 09, 2004 at 16:28 UTC
    Drop the word "TABLE" so, "INSERT INTO pocus".

    —Brad
    "Don't ever take a fence down until you know the reason it was put up. " G. K. Chesterton
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.

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.

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.

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.