[Bioperl-l] bioperl-db: posqgresql adaptor
Hilmar Lapp
hlapp@gnf.org
Tue, 19 Nov 2002 11:51:56 -0800
Yves was right in his analysis - the bind failing is OK and is
actually caught, that's how the schema-independent adaptor figures
that the underlying schema doesn't support a particular query. It
just appears that Pg throws an exception even though
$dbh->{RaiseError} is false. So it looks like a bug in the Pg DBI
driver.
In the case in question, the unsupported query is finding comments
for SeqFeatures - note that attempting to do this is perfectly OK
from the bioperl object model standpoint because SeqFeature::Generic
does have an annotation collection which may contain comment
annotations. The BioSQL schema just happens not to support this. The
adaptor is smart in the sense that if a query fails under
circumstances that suggest it's simply not supported by the schema,
it won't even try that statement a second time if it encounters it
again.
I'm still running the DBI connection with the PrintError default of
TRUE, that's why you see the failure messages. If the messages annoy
you, the easiest way to change this is to obtain the first
persistence adaptor you can get a hold of, e.g., ($db being the
value returned from Bio::DB::BioDB->new)
my $adp = $db->get_object_adaptor("Bio::SeqI");
$adp->dbh->{PrintError} = 0;
This works because right now the connection is shared between all
adaptors. There is a way to change the parameters upfront as well.
-hilmar
On Tuesday, November 19, 2002, at 11:23 AM, Tim Henderson wrote:
> I believe this same bug exists in the mysql adaptor. It doesn't
> cause an exception to be thrown, but it does cause DBD::mysql::st
> to print a warning message when bind_param() fails. See the debug
> output below.
>
>
> -- Tim
>
>
>
> [tjh@feature tjh]$ bin/fetch_by_accession.pl --debug=2 X92703
> attempting to load adaptor class for Bio::SeqI
> attempting to load module Bio::DB::BioSQL::SeqIAdaptor
> attempting to load module Bio::DB::BioSQL::SeqAdaptor
> instantiating adaptor class Bio::DB::BioSQL::SeqAdaptor
> attempting to load adaptor class for BioNamespace
> attempting to load module Bio::DB::BioSQL::BioNamespaceAdaptor
> instantiating adaptor class Bio::DB::BioSQL::BioNamespaceAdaptor
> attempting to load driver for adaptor class Bio::DB::BioSQL::SeqAdaptor
> attempting to load driver for adaptor class
> Bio::DB::BioSQL::PrimarySeqAdaptor
> attempting to load driver for adaptor class
> Bio::DB::BioSQL::BasePersistenceAdaptor
> Using Bio::DB::BioSQL::mysql::BasePersistenceAdaptorDriver as
> driver peer for Bio::DB::BioSQL::SeqAdaptor
> preparing UK select statement: SELECT bioentry.bioentry_id,
> bioentry.display_id, bioentry.identifier, bioentry.accession,
> bioentry.description, bioentry.entry_version,
> bioentry.biodatabase_id, bioentry.taxon_id FROM bioentry WHERE
> entry_version = ? AND accession = ?
> SeqAdaptor: binding UK column 1 to "0" (version)
> SeqAdaptor: binding UK column 2 to "X92703" (accession_number)
> attempting to load adaptor class for Bio::PrimarySeq
> attempting to load module Bio::DB::BioSQL::PrimarySeqAdaptor
> instantiating adaptor class Bio::DB::BioSQL::PrimarySeqAdaptor
> attempting to load driver for adaptor class
> Bio::DB::BioSQL::BioNamespaceAdaptor
> attempting to load driver for adaptor class
> Bio::DB::BioSQL::BasePersistenceAdaptor
> Using Bio::DB::BioSQL::mysql::BasePersistenceAdaptorDriver as
> driver peer for Bio::DB::BioSQL::BioNamespaceAdaptor
> preparing PK select statement: SELECT biodatabase.biodatabase_id,
> biodatabase.name, biodatabase.authority FROM biodatabase WHERE
> biodatabase_id = ?
> BioNamespaceAdaptor: binding PK column to "1"
> attempting to load adaptor class for Bio::Species
> attempting to load module Bio::DB::BioSQL::SpeciesAdaptor
> instantiating adaptor class Bio::DB::BioSQL::SpeciesAdaptor
> attempting to load driver for adaptor class
> Bio::DB::BioSQL::SpeciesAdaptor
> attempting to load driver for adaptor class
> Bio::DB::BioSQL::BasePersistenceAdaptor
> Using Bio::DB::BioSQL::mysql::BasePersistenceAdaptorDriver as
> driver peer for Bio::DB::BioSQL::SpeciesAdaptor
> preparing PK select statement: SELECT taxon.taxon_id,
> taxon.common_name, taxon.full_lineage, taxon.ncbi_taxon_id,
> taxon.binomial, taxon.variant FROM taxon WHERE taxon_id = ?
> SpeciesAdaptor: binding PK column to "2"
> attempting to load adaptor class for Biosequence
> attempting to load module Bio::DB::BioSQL::BiosequenceAdaptor
> instantiating adaptor class Bio::DB::BioSQL::BiosequenceAdaptor
> attempting to load driver for adaptor class
> Bio::DB::BioSQL::BiosequenceAdaptor
> Using Bio::DB::BioSQL::mysql::BiosequenceAdaptorDriver as driver
> peer for Bio::DB::BioSQL::BiosequenceAdaptor
> preparing UK select statement: SELECT biosequence.bioentry_id,
> biosequence.seq_version, biosequence.division,
> biosequence.seq_length, biosequence.alphabet, NULL,
> biosequence.bioentry_id FROM biosequence WHERE bioentry_id = ?
> BiosequenceAdaptor: binding UK column 1 to "7" (primary_seq)
> attempting to load adaptor class for Bio::AnnotationCollectionI
> attempting to load module
> Bio::DB::BioSQL::AnnotationCollectionIAdaptor
> attempting to load module
> Bio::DB::BioSQL::AnnotationCollectionAdaptor
> instantiating adaptor class
> Bio::DB::BioSQL::AnnotationCollectionAdaptor
> attempting to load adaptor class for Bio::Annotation::Reference
> attempting to load module Bio::DB::BioSQL::ReferenceAdaptor
> instantiating adaptor class Bio::DB::BioSQL::ReferenceAdaptor
> attempting to load driver for adaptor class
> Bio::DB::BioSQL::ReferenceAdaptor
> attempting to load driver for adaptor class
> Bio::DB::BioSQL::BasePersistenceAdaptor
> Using Bio::DB::BioSQL::mysql::BasePersistenceAdaptorDriver as
> driver peer for Bio::DB::BioSQL::ReferenceAdaptor
> preparing SELECT ASSOC query: SELECT t2.reference_id,
> t2.reference_authors, t2.reference_title, t2.reference_location,
> t2.reference_medline, bioentry_reference.reference_start,
> bioentry_reference.reference_end FROM bioentry t1, reference t2,
> bioentry_reference WHERE t1.bioentry_id =
> bioentry_reference.bioentry_id AND t2.reference_id =
> bioentry_reference.reference_id AND t1.bioentry_id = ?
> ReferenceAdaptor: binding ASSOC column 1 to "7" (FK to Bio::Seq)
> attempting to load adaptor class for Bio::Annotation::OntologyTerm
> attempting to load module Bio::DB::BioSQL::OntologyTermAdaptor
> attempting to load adaptor class for Bio::Root::Root
> attempting to load module Bio::DB::BioSQL::RootAdaptor
> attempting to load adaptor class for Bio::Root::RootI
> attempting to load module Bio::DB::BioSQL::RootIAdaptor
> attempting to load module Bio::DB::BioSQL::RootAdaptor
> attempting to load adaptor class for Bio::AnnotationI
> attempting to load module Bio::DB::BioSQL::AnnotationIAdaptor
> attempting to load module Bio::DB::BioSQL::AnnotationAdaptor
> attempting to load adaptor class for Bio::Ontology::TermI
> attempting to load module Bio::DB::BioSQL::TermIAdaptor
> attempting to load module Bio::DB::BioSQL::TermAdaptor
> instantiating adaptor class Bio::DB::BioSQL::TermAdaptor
> attempting to load adaptor class for Bio::Ontology::Term
> attempting to load module Bio::DB::BioSQL::TermAdaptor
> attempting to load driver for adaptor class
> Bio::DB::BioSQL::TermAdaptor
> attempting to load driver for adaptor class
> Bio::DB::BioSQL::BasePersistenceAdaptor
> Using Bio::DB::BioSQL::mysql::BasePersistenceAdaptorDriver as
> driver peer for Bio::DB::BioSQL::TermAdaptor
> preparing UK select statement: SELECT
> ontology_term.ontology_term_id, ontology_term.term_identifier,
> ontology_term.term_name, ontology_term.term_definition,
> ontology_term.category_id FROM ontology_term WHERE term_name = ?
> TermAdaptor: binding UK column 1 to "Annotation Tags" (name)
> preparing SELECT ASSOC query: SELECT t2.ontology_term_id,
> t2.term_identifier, t2.term_name, t2.term_definition,
> t2.category_id FROM bioentry t1, ontology_term t2,
> bioentry_qualifier_value WHERE t1.bioentry_id =
> bioentry_qualifier_value.bioentry_id AND t2.ontology_term_id =
> bioentry_qualifier_value.ontology_term_id AND (t1.bioentry_id = ?
> AND t2.category_id != ?)
> TermAdaptor: binding ASSOC column 1 to "7" (FK to Bio::Seq)
> TermAdaptor: binding ASSOC column 2 to "5" (constraint
> Bio::Annotation::OntologyTerm::category)
> attempting to load adaptor class for Bio::Annotation::SimpleValue
> attempting to load module Bio::DB::BioSQL::SimpleValueAdaptor
> instantiating adaptor class Bio::DB::BioSQL::SimpleValueAdaptor
> attempting to load adaptor class for Bio::Ontology::Term
> attempting to load module Bio::DB::BioSQL::TermAdaptor
> attempting to load driver for adaptor class
> Bio::DB::BioSQL::SimpleValueAdaptor
> attempting to load driver for adaptor class
> Bio::DB::BioSQL::TermAdaptor
> attempting to load driver for adaptor class
> Bio::DB::BioSQL::BasePersistenceAdaptor
> Using Bio::DB::BioSQL::mysql::BasePersistenceAdaptorDriver as
> driver peer for Bio::DB::BioSQL::SimpleValueAdaptor
> preparing SELECT ASSOC query: SELECT t2.ontology_term_id,
> t2.term_name, bioentry_qualifier_value.qualifier_value,
> t2.category_id FROM bioentry t1, ontology_term t2,
> bioentry_qualifier_value WHERE t1.bioentry_id =
> bioentry_qualifier_value.bioentry_id AND t2.ontology_term_id =
> bioentry_qualifier_value.ontology_term_id AND (t1.bioentry_id = ?
> AND t2.category_id = ?)
> SimpleValueAdaptor: binding ASSOC column 1 to "7" (FK to Bio::Seq)
> SimpleValueAdaptor: binding ASSOC column 2 to "5" (constraint
> Bio::Annotation::SimpleValue::category)
> attempting to load adaptor class for Bio::Annotation::DBLink
> attempting to load module Bio::DB::BioSQL::DBLinkAdaptor
> instantiating adaptor class Bio::DB::BioSQL::DBLinkAdaptor
> attempting to load driver for adaptor class
> Bio::DB::BioSQL::DBLinkAdaptor
> attempting to load driver for adaptor class
> Bio::DB::BioSQL::BasePersistenceAdaptor
> Using Bio::DB::BioSQL::mysql::BasePersistenceAdaptorDriver as
> driver peer for Bio::DB::BioSQL::DBLinkAdaptor
> preparing SELECT ASSOC query: SELECT t2.dbxref_id, t2.dbname,
> t2.accession, t2.version FROM bioentry t1, dbxref t2,
> bioentry_dblink WHERE t1.bioentry_id = bioentry_dblink.bioentry_id
> AND t2.dbxref_id = bioentry_dblink.dbxref_id AND t1.bioentry_id = ?
> DBLinkAdaptor: binding ASSOC column 1 to "7" (FK to Bio::Seq)
> attempting to load adaptor class for Bio::Annotation::Comment
> attempting to load module Bio::DB::BioSQL::CommentAdaptor
> instantiating adaptor class Bio::DB::BioSQL::CommentAdaptor
> attempting to load driver for adaptor class
> Bio::DB::BioSQL::CommentAdaptor
> attempting to load driver for adaptor class
> Bio::DB::BioSQL::BasePersistenceAdaptor
> Using Bio::DB::BioSQL::mysql::BasePersistenceAdaptorDriver as
> driver peer for Bio::DB::BioSQL::CommentAdaptor
> preparing query: SELECT t1.comment_id, t1.comment_text,
> t1.comment_rank, t1.bioentry_id FROM comment t1 WHERE
> t1.bioentry_id = ?
> Query FIND Bio::Annotation::Comment BY Bio::Seq: binding column 1
> to "7"
> attempting to load adaptor class for Bio::SeqFeatureI
> attempting to load module Bio::DB::BioSQL::SeqFeatureIAdaptor
> attempting to load module Bio::DB::BioSQL::SeqFeatureAdaptor
> instantiating adaptor class Bio::DB::BioSQL::SeqFeatureAdaptor
> attempting to load driver for adaptor class
> Bio::DB::BioSQL::SeqFeatureAdaptor
> attempting to load driver for adaptor class
> Bio::DB::BioSQL::BasePersistenceAdaptor
> Using Bio::DB::BioSQL::mysql::BasePersistenceAdaptorDriver as
> driver peer for Bio::DB::BioSQL::SeqFeatureAdaptor
> preparing query: SELECT t1.seqfeature_id, NULL, t1.seqfeature_rank,
> t1.bioentry_id, t1.ontology_term_id, t1.seqfeature_source_id FROM
> seqfeature t1 WHERE t1.bioentry_id = ?
> Query FIND FEATURE BY SEQ: binding column 1 to "7"
> preparing PK select statement: SELECT
> ontology_term.ontology_term_id, ontology_term.term_identifier,
> ontology_term.term_name, ontology_term.term_definition,
> ontology_term.category_id FROM ontology_term WHERE
> ontology_term_id = ?
> TermAdaptor: binding PK column to "2"
> TermAdaptor: binding PK column to "1"
> TermAdaptor: binding PK column to "4"
> TermAdaptor: binding PK column to "3"
> attempting to load adaptor class for Bio::LocationI
> attempting to load module Bio::DB::BioSQL::LocationIAdaptor
> attempting to load module Bio::DB::BioSQL::LocationAdaptor
> instantiating adaptor class Bio::DB::BioSQL::LocationAdaptor
> attempting to load driver for adaptor class
> Bio::DB::BioSQL::LocationAdaptor
> attempting to load driver for adaptor class
> Bio::DB::BioSQL::BasePersistenceAdaptor
> Using Bio::DB::BioSQL::mysql::BasePersistenceAdaptorDriver as
> driver peer for Bio::DB::BioSQL::LocationAdaptor
> preparing query: SELECT t1.seqfeature_location_id, t1.seq_start,
> t1.seq_end, t1.seq_strand, t1.location_rank, t1.seqfeature_id,
> t1.dbxref_id FROM seqfeature_location t1 WHERE t1.seqfeature_id = ?
> Query FIND LOCATION BY FEATURE: binding column 1 to "18"
> preparing SELECT ASSOC query: SELECT t2.ontology_term_id,
> t2.term_identifier, t2.term_name, t2.term_definition,
> t2.category_id FROM seqfeature t1, ontology_term t2,
> seqfeature_qualifier_value WHERE t1.seqfeature_id =
> seqfeature_qualifier_value.seqfeature_id AND t2.ontology_term_id =
> seqfeature_qualifier_value.ontology_term_id AND
> (t1.seqfeature_id = ? AND t2.category_id != ?)
> TermAdaptor: binding ASSOC column 1 to "18" (FK to
> Bio::SeqFeature::Generic)
> TermAdaptor: binding ASSOC column 2 to "5" (constraint
> Bio::Annotation::OntologyTerm::category)
> preparing SELECT ASSOC query: SELECT t2.ontology_term_id,
> t2.term_name, seqfeature_qualifier_value.qualifier_value,
> t2.category_id FROM seqfeature t1, ontology_term t2,
> seqfeature_qualifier_value WHERE t1.seqfeature_id =
> seqfeature_qualifier_value.seqfeature_id AND t2.ontology_term_id =
> seqfeature_qualifier_value.ontology_term_id AND
> (t1.seqfeature_id = ? AND t2.category_id = ?)
> SimpleValueAdaptor: binding ASSOC column 1 to "18" (FK to
> Bio::SeqFeature::Generic)
> SimpleValueAdaptor: binding ASSOC column 2 to "5" (constraint
> Bio::Annotation::SimpleValue::category)
> preparing query: SELECT t1.comment_id, t1.comment_text,
> t1.comment_rank, t1.bioentry_id FROM comment t1 WHERE 1 = 1
> Query FIND Bio::Annotation::Comment BY Bio::SeqFeature::Generic:
> binding column 1 to "18"
>
> DBD::mysql::st bind_param failed: Illegal parameter number at
> /home/tjh/lib/perl5/site_perl/5.6.1/Bio/DB/BioSQL/BasePersistenceAdaptor.
> pm line 938.
>
> TermAdaptor: binding PK column to "10"
> Query FIND LOCATION BY FEATURE: binding column 1 to "20"
> TermAdaptor: binding ASSOC column 1 to "20" (FK to
> Bio::SeqFeature::Generic)
> TermAdaptor: binding ASSOC column 2 to "5" (constraint
> Bio::Annotation::OntologyTerm::category)
> SimpleValueAdaptor: binding ASSOC column 1 to "20" (FK to
> Bio::SeqFeature::Generic)
> SimpleValueAdaptor: binding ASSOC column 2 to "5" (constraint
> Bio::Annotation::SimpleValue::category)
> TermAdaptor: binding PK column to "13"
> Query FIND LOCATION BY FEATURE: binding column 1 to "19"
> TermAdaptor: binding ASSOC column 1 to "19" (FK to
> Bio::SeqFeature::Generic)
> TermAdaptor: binding ASSOC column 2 to "5" (constraint
> Bio::Annotation::OntologyTerm::category)
> SimpleValueAdaptor: binding ASSOC column 1 to "19" (FK to
> Bio::SeqFeature::Generic)
> SimpleValueAdaptor: binding ASSOC column 2 to "5" (constraint
> Bio::Annotation::SimpleValue::category)
> preparing SELECT statement: SELECT biosequence_str FROM biosequence
> WHERE bioentry_id = ?
>
> <<snip>>
>
>
--
-------------------------------------------------------------
Hilmar Lapp email: lapp at gnf.org
GNF, San Diego, Ca. 92121 phone: +1-858-812-1757
-------------------------------------------------------------