<html>
<head>
<meta content="text/html; charset=windows-1252"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">Hi Nathalie,<br>
<br>
The issue with the p-value is due to an issue with our import
pipeline of the NHGRI data. It affects less than 20 entries.<br>
Basically these entries don't have p-values.<br>
We apologize for this issue and this will be fixed on the next
Ensembl release (78).<br>
<br>
If you want to avoid the error on you import script, you can
replace the line:<br>
$p_value=$pf->p_value;<br>
by <br>
$p_value = ($pf->p_value eq "NULL") ? undef : $pf->value;<br>
<br>
Best regards,<br>
<pre class="moz-signature" cols="72">Laurent</pre>
On 14/10/2014 17:29, Nathalie Conte wrote:<br>
</div>
<blockquote
cite="mid:20141014162952.F3D3513349D_43D4F80B@hx-mx2.ebi.ac.uk"
type="cite">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<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 moz-do-not-send="true"
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>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
Dev mailing list <a class="moz-txt-link-abbreviated" href="mailto:Dev@ensembl.org">Dev@ensembl.org</a>
Posting guidelines and subscribe/unsubscribe info: <a class="moz-txt-link-freetext" href="http://lists.ensembl.org/mailman/listinfo/dev">http://lists.ensembl.org/mailman/listinfo/dev</a>
Ensembl Blog: <a class="moz-txt-link-freetext" href="http://www.ensembl.info/">http://www.ensembl.info/</a>
</pre>
</blockquote>
<br>
</body>
</html>