[ensembl-dev] variation API-Data truncated for column error
Nathalie Conte
nconte at ebi.ac.uk
Tue Oct 14 17:29:55 BST 2014
Hi,
i have this script below, which fetch from ensembl APi, RS-ID, consequence source and pvalue and write these to a mysql database table and a txt file.
This script will print the RS-ID, consequence source and pvalue in a file without any issue (outtest6.txt) but not with the table in the database, it will break at some point and throw an error message.
error message:
DBD::mysql::st execute failed: Data truncated for column 'P_VALUE' at row 1 at ./test5.pl line 43.
DBD::mysql::st execute failed: Data truncated for column 'P_VALUE' at row 1 at ./test5.pl line 43.
it will break at a line where the pvalue is written as "NULL" (string is NULL not empty), see below - I am not sure why this is happening but it could be linked to the datatype of the p_value retrieved from ensembl as in most case this is either a numeric value or nothing, but not "NULL". Any help or tips appreciated
thanks
Nathalie
>tail outtest6.txt where the script breaks
rs421379 Intergenic variant dbSNP 4.0500248e-004
rs429358 Missense variant dbSNP
rs429358 Missense variant dbSNP
rs429358 Missense variant dbSNP
rs429358 Missense variant dbSNP
rs429358 Missense variant dbSNP NULL
######
script, note the credentials to my database are hidden you will need to test this using a in house database.
#!/usr/local/bin/perl
use strict;
use warnings;
use DBI;
use Bio::EnsEMBL::Registry;
Bio::EnsEMBL::Registry->load_registry_from_db(
-host=>"ensembldb.ensembl.org", -user=>"anonymous",
-port=>'5306', 'db_version' => 75,);
open (OUTFILE2, ">/outtest6.txt") or die "problem open OUTfile";
my $geno_dbh = DBI->connect( 'dbi:mysql:dbname=x;host=mysql-mi-dev;port=x', 'x', 'x', {InactiveDestroy => 1, RaiseError => 1, PrintError => 1} ) || die "Database connection not made: $DBI::errstr";
print STDERR "Connection...\n";
my $table_creation = "create table ENSEMBL1 (
RS_ID VARCHAR(255),
CONS VARCHAR(255),
SOURCE VARCHAR(255),
P_VALUE FLOAT8
)";
$geno_dbh->do($table_creation);
my $sql_table = 'INSERT INTO ENSEMBL1 (RS_ID, CONS, SOURCE, P_VALUE) VALUES (?,?,?,?)';
my $geno_sthout = $geno_dbh->prepare($sql_table);
my $vs_adaptor = Bio::EnsEMBL::Registry->get_adaptor('human','variation','variationset');
my $vs = $vs_adaptor->fetch_by_name('All phenotype-associated variants');
my $iterator = $vs->get_Variation_Iterator();
while ($iterator->has_next()) {
my $var = $iterator->next();
my $variant=$var->name();
my @vfs = @{$var->get_all_VariationFeatures()};
my $cons=$var->display_consequence ();
my $source=$var->source;
my $p_value;
foreach my $vf (@vfs) {
my @pfs = @{$var->get_all_PhenotypeFeatures()};
foreach my $pf(@pfs) {
$p_value=$pf->p_value;
print OUTFILE2 $variant, "\t",$cons, "\t",$source, "\t", $p_value, "\n";
$geno_sthout -> execute($variant, $cons , $source, $p_value) or die $DBI::errstr;
}
}
}
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.ensembl.org/pipermail/dev_ensembl.org/attachments/20141014/56b161ff/attachment.html>
More information about the Dev
mailing list