[BioSQL-l] Problem in the SpeciesAdaptorDriver.pm

[Hilmar Lapp]

ncbi_taxon_id is only in the taxon table, *not* in the taxon_name table.

I've seen someone posting this problem too a while ago. I suspected a  
version mix problem, which as far as I can recall turned out to be  
true, but my recollection may betray me.

I'll check on this. It's possible I've messed up something in emulating  
the pure Biosql API.

	-hilmar

On Friday, November 14, 2003, at 05:36  PM, Gerben Menschaert wrote:

> Hello,
>
> We're running Biosql on Oracle (bioperl-db main cvs branch //  
> bioperl-1.2.3).
>
> When loading a genbank file we're bumping into the following error:
>
> DBD::Oracle::db prepare failed: ORA-00918: column ambiguously defined  
> (DBD ERROR: OCIStmtExecute/Describe) [for statement ``SELECT  
> taxon_name.tax_oid, NULL, NULL, taxon.ncbi_taxon_id, taxon_name.name,  
> NULL FROM taxon, taxon_name WHERE taxon.oid = taxon_name.tax_oid AND  
> name_class = ? AND ncbi_taxon_id = ?'']) at  
> /usr/lib/perl5/site_perl/5.6.1/Bio/DB/BioSQL/Oracle/ 
> SpeciesAdaptorDriver.pm line 224, <GEN0> line 49.
> Could not store AF438089: Can't call method "bind_param" on an  
> undefined value at  
> /usr/lib/perl5/site_perl/5.6.1/Bio/DB/BioSQL/BasePersistenceAdaptor.pm  
> line 943, <GEN0> line 49.
>
> This is correct since the ncbi_taxon_id is both in the taxon and  
> taxon_name table.
>
>
> To solve the problem I included the following code snippet (between  
> comment lines) in Bio::DB::BioSQL::Oracle::SpeciesAdaptorDriver (see  
> bottom) :
>
> 1) Anybody has the same problem?
> 2) The solution is not very nice, so I tried to look for the correct  
> table_name in the %object_entity_map hash in the BaseDriver.pm  
> ($self->table_name("Bio::Species"), commented out in the code below).  
> It replaced the ncbi_taxon_id correctly to taxon.ncbi_taxon_id, but it  
> also resulted in the following error further on in the code: (3) Why?)
>
> DBD::Oracle::st execute failed: ORA-00001: unique constraint  
> (BIOSQL.XAK1TAXON) violated (DBD ERROR: OCIStmtExecute) [for statement  
> ``INSERT INTO taxon (tax_oid, ncbi_taxon_id, node_rank, left_value,  
> right_value) VALUES (?, ?, ?, ?, ?)'' with params: :p4=367534,  
> :p5=367535, :p1='3405092', :p2='177435', :p3='species']) at  
> /usr/lib/perl5/site_perl/5.6.1/Bio/DB/BioSQL/Oracle/ 
> SpeciesAdaptorDriver.pm line 378, <GEN0> line 49.
> Could not store AF438089:
> ------------- EXCEPTION  -------------
> MSG: create: object (Bio::Species) failed to insert or to be found by  
> unique key
> STACK Bio::DB::BioSQL::BasePersistenceAdaptor::create  
> /usr/lib/perl5/site_perl/5.6.1/Bio/DB/BioSQL/ 
> BasePersistenceAdaptor.pm:207
> STACK Bio::DB::Persistent::PersistentObject::create  
> /usr/lib/perl5/site_perl/5.6.1/Bio/DB/Persistent/ 
> PersistentObject.pm:243
> STACK Bio::DB::BioSQL::BasePersistenceAdaptor::create  
> /usr/lib/perl5/site_perl/5.6.1/Bio/DB/BioSQL/ 
> BasePersistenceAdaptor.pm:170
> STACK Bio::DB::BioSQL::BasePersistenceAdaptor::store  
> /usr/lib/perl5/site_perl/5.6.1/Bio/DB/BioSQL/ 
> BasePersistenceAdaptor.pm:253
> STACK Bio::DB::Persistent::PersistentObject::store  
> /usr/lib/perl5/site_perl/5.6.1/Bio/DB/Persistent/ 
> PersistentObject.pm:270
> STACK (eval) /usr/local/bin/load_seqdatabase.pl:446
> STACK toplevel /usr/local/bin/load_seqdatabase.pl:429
>
> --------------------------------------
>
>
>
>
> ####Changes to SpeciesAdaptor.pm
>
> sub prepare_findbyuk_sth{
>     my ($self,$adp,$ukval_h,$fkslots) = @_;
>
>     # get the slot/attribute map
>     my $table = $self->table_name($adp);
>     my $node_table = $self->table_name("TaxonNode");
>     my $pkname = $self->primary_key_name($node_table);
>     my $fkname = $self->foreign_key_name("TaxonNode");
>     my $slotmap = $self->slot_attribute_map($table);
>     # SELECT columns
>     my @attrs = $self->_build_select_list($adp,$fkslots);
>     # WHERE clause constraints
>     my @cattrs = ();
>     foreach (keys %$ukval_h) {
>         my $col;
>         if(exists($slotmap->{$_})) {
>             $col = $slotmap->{$_};
>         }
>         push(@cattrs, $col || "NULL");
>         $self->warn("slot $_ is in unique key, but can't be mapped to  
> ".
>                     "an entity column: you won't find anything")
>             unless $col;
>     }
> ####################################################################### 
> ###
>     print STDERR "@cattrs \n";
>         for(my $i = 0; $i < @cattrs; $i++) {
>         if($cattrs[$i] =~ /ncbi_taxon_id/i){
>            # my $name_table = $self->table_name("Bio::Species");
>            # $cattrs[$i] ="$name_table.ncbi_taxon_id";
>            $cattrs[$i] = "taxon.ncbi_taxon_id";
>         }
>     }
> ####################################################################### 
> ##
>     # create the sql statement
>     my $sql = "SELECT " . join(", ", @attrs) .
>         " FROM $node_table, $table".
>         " WHERE $node_table.$pkname = $table.$fkname AND ".
>         join(" AND ", map { "$_ = ?"; } @cattrs);
>     $adp->debug("preparing UK select statement: $sql\n");
>     # prepare statement and return
>     return $adp->dbh()->prepare($sql);
> }
>
>
>
> Tanx,
> Gerben
>
> _______________________________________________
> BioSQL-l mailing list
> BioSQL-l at open-bio.org
> http://open-bio.org/mailman/listinfo/biosql-l
>
-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------