[BioSQL-l] Problem in the SpeciesAdaptorDriver.pm

[Gerben Menschaert]

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