<html><head><meta http-equiv="Content-Type" content="text/html charset=us-ascii"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div>Hi, </div><div>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.</div><div>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.</div><div><div><u>error message:</u></div><div>DBD::mysql::st execute failed: Data truncated for column 'P_VALUE' at row 1 at ./test5.pl line 43.</div><div>DBD::mysql::st execute failed: Data truncated for column 'P_VALUE' at row 1 at ./test5.pl line 43.</div></div><div><br></div><div>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</div><div>thanks</div><div>Nathalie</div><div><br></div><div>>tail outtest6.txt where the script breaks</div><div><div>rs421379<span class="Apple-tab-span" style="white-space:pre"> </span>Intergenic variant<span class="Apple-tab-span" style="white-space:pre"> </span>dbSNP<span class="Apple-tab-span" style="white-space:pre"> </span>4.0500248e-004</div><div>rs429358<span class="Apple-tab-span" style="white-space:pre"> </span>Missense variant<span class="Apple-tab-span" style="white-space:pre"> </span>dbSNP<span class="Apple-tab-span" style="white-space:pre"> </span></div><div>rs429358<span class="Apple-tab-span" style="white-space:pre"> </span>Missense variant<span class="Apple-tab-span" style="white-space:pre"> </span>dbSNP<span class="Apple-tab-span" style="white-space:pre"> </span></div><div>rs429358<span class="Apple-tab-span" style="white-space:pre"> </span>Missense variant<span class="Apple-tab-span" style="white-space:pre"> </span>dbSNP<span class="Apple-tab-span" style="white-space:pre"> </span></div><div>rs429358<span class="Apple-tab-span" style="white-space:pre"> </span>Missense variant<span class="Apple-tab-span" style="white-space:pre"> </span>dbSNP<span class="Apple-tab-span" style="white-space:pre"> </span></div><div><b>rs429358<span class="Apple-tab-span" style="white-space:pre"> </span>Missense variant<span class="Apple-tab-span" style="white-space:pre"> </span>dbSNP<span class="Apple-tab-span" style="white-space:pre"> </span>NULL</b></div></div><div><b><br></b></div><div><b><br></b></div><div>######</div><div>script, note the credentials to my database are hidden you will need to test this using a in house database.</div><div><br></div><div><div>#!/usr/local/bin/perl</div><div>use strict;</div><div>use warnings;</div><div>use DBI;</div><div>use Bio::EnsEMBL::Registry;</div><div><br></div><div><br></div><div>Bio::EnsEMBL::Registry->load_registry_from_db(</div><div> -host=>"<a href="http://ensembldb.ensembl.org">ensembldb.ensembl.org</a>", -user=>"anonymous",</div><div> -port=>'5306', 'db_version' => 75,);</div><div>open (OUTFILE2, ">/outtest6.txt") or die "problem open OUTfile";</div><div>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";</div><div>print STDERR "Connection...\n";</div><div><br></div><div>my $table_creation = "create table ENSEMBL1 (</div><div>RS_ID VARCHAR(255),</div><div>CONS VARCHAR(255),</div><div>SOURCE VARCHAR(255),</div><div>P_VALUE FLOAT8</div><div>)";</div><div><br></div><div>$geno_dbh->do($table_creation);</div><div>my $sql_table = 'INSERT INTO ENSEMBL1 (RS_ID, CONS, SOURCE, P_VALUE) VALUES (?,?,?,?)';</div><div>my $geno_sthout = $geno_dbh->prepare($sql_table);</div><div><br></div><div>my $vs_adaptor = Bio::EnsEMBL::Registry->get_adaptor('human','variation','variationset');</div><div>my $vs = $vs_adaptor->fetch_by_name('All phenotype-associated variants');</div><div>my $iterator = $vs->get_Variation_Iterator();</div><div>while ($iterator->has_next()) {</div><div>my $var = $iterator->next();</div><div>my $variant=$var->name();</div><div>my @vfs = @{$var->get_all_VariationFeatures()};</div><div>my $cons=$var->display_consequence ();</div><div>my $source=$var->source;</div><div>my $p_value;</div><div>foreach my $vf (@vfs) {</div><div> my @pfs = @{$var->get_all_PhenotypeFeatures()};</div><div> foreach my $pf(@pfs) {</div><div> $p_value=$pf->p_value;</div><div>print OUTFILE2 $variant, "\t",$cons, "\t",$source, "\t", $p_value, "\n";</div><div>$geno_sthout -> execute($variant, $cons , $source, $p_value) or die $DBI::errstr;</div><div>}</div><div>}</div><div>}</div></div></body></html>