[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