Sorry Nelson, i might have missed few details, but could you send me the
XML equivalent of the query ?
On 27/09/2011 15:20, [email protected] wrote:
Hi Junjun& Syed,
I still have problems with this earlier reported query. So I have tried to
tweak a few parameters (listed below) as I generated error logs, hopefully
these will be more useful to diagnosing the problem.
You correctly pointed out the query involves a join between two datasets
linked by exportables and importables each of which has 2
attributes/filters (based on ensembl gene and uniprot id attributes).
This query works with an old version of our database but it throws the SQL
syntax error with the new version of the database.
I have tried to perform a few tests and saved the error_log files. I hope
these will shed more light.
You had mentioned that something might be missing before the 'AND' and
this reminded me that there possibly might be a NULL value on one of the
tables that could trigger an ERROR (I have encountered this before, and I
got a patch from Damian on the FilterList.pm module to fix such a bug, he
later committed this to the BioMart code). Since this mart is on a
different server from the one that had the patch, I thought I would try
testing with the patch as well.
Like Syed had asked, the query in question is sent to the server via
webservice.
Some information about the error_logs from each test:
=================================================
These are logs for the query when its not working
=================================================
About error_log1: http://pastebin.com/3CKRzTvc
1. Deleted the two_variable_id exportable/importables both on EWAS and
Pathway datasets.
2. Used new version db (v38_mart).
3. Query of interest not working.
4. Used FilterList.pm module that was altered by Damian.
About error_log2: http://pastebin.com/ekVihAn9
1. Deleted the two_variable_id exportable/importables both on EWAS and
Pathway datasets.
2. Used new version db (v38_mart).
3. Query of interest not working.
4. Used original FilterList.pm module.
About error_log3: http://pastebin.com/ZLagmDQh
1. Has two_variable_id exportable/importables both on EWAS and Pathway
datasets.
2. Used new version db (v38_mart).
3. Query of interest not working.
4. Used FilterList.pm module that was altered by Damian.
==========================================
These are logs for the query when it works
==========================================
About error_log4: http://pastebin.com/prAgP3vS
1. Has two_variable_id exportable/importables both on EWAS and Pathway
datasets.
2. Used old version db (v35_mart).
3. Query of interest Works.
4. Used original FilterList.pm module.
About error_log5: http://pastebin.com/gS8HghkT
1. Has two_variable_id exportable/importables both on EWAS and Pathway
datasets.
2. Used old version db (v35_mart).
3. Query of interest Works.
4. Used FilterList.pm module that was altered by Damian.
Thanks,
Nelson
This SQL doesn't look correct, something is missing right before the 'AND'
for the part that is joined with 'OR', with the exception of the last item
as below:
((pathway__referencepeptidesequence__dm.referencedatabase_uniprot =
'P49327') AND (pathway__referencednasequence__dm.referencedatabase_ensembl
= 'ENSG00000169710'))
It looks like the original query involves join between two datasets.
Usually that is not a problem when the link defined as an
exportable/importable pair, in which exportable contains one attribute
(eg, ensembl_gene) and importable contains one filter (eg, ensembl_gene).
The piece of SQL statement shown above makes me thinking that the link you
defined seemed to have two attributes and two filters (ensembl gene and
uniprot id) for its exportable and importable.
Although it is legitimate to create such a link, but I am afraid it is not
going to work in most of the cases. It works only when *one* pair of the
values is exported from one dataset and imported to the other dataset. The
'band' filter works exactly like this in Ensembl gene mart, user specifies
*one* band to a hidden dataset which exports *one* pair (ie, one row) of
'chromosome' and 'coordinate' value (eg, 1, 10000303), and ensembl_gene
dataset imports this pair and stick it in to SQL that queries against it's
own db.
The confirm this, you can double check the exportable/importable settings
in your configuration.
Hope this helps. Let us know if you have any further questions.
Best regards,
Junjun
On 11-09-26 9:47 AM, "[email protected]"<[email protected]> wrote:
Hi,
I am getting an error from one of my queries along the lines of SQL
syntax
being the cause, am not sure what the real problem is though. I set the
log4perl.conf to debug mode and I got back a more elaborate error report
http://pastebin.com/9rqKBfWR
Anyone know what the problem is?
Thanks,
Nelson
Serious Error: Error during query execution: You have an error in your
SQL
syntax; check the manual that corresponds to your MySQL server version
for
the right syntax to use near 'AND
(pathway__referencednasequence__dm.referencedatabase_ensembl =
'ENSG00000166' at line 1
ERROR: caught BioMart::Exception::Database: Error during query execution:
You have an error in your SQL syntax; check the manual that corresponds
to
your MySQL server version for the right syntax to use near 'AND
(pathway__referencednasequence__dm.referencedatabase_ensembl =
'ENSG00000166' at line 1
_______________________________________________
Users mailing list
[email protected]
https://lists.biomart.org/mailman/listinfo/users
_______________________________________________
Users mailing list
[email protected]
https://lists.biomart.org/mailman/listinfo/users
_______________________________________________
Users mailing list
[email protected]
https://lists.biomart.org/mailman/listinfo/users
