[Bioperl-l] DB2 driver for BioPerl

[Florian Mittag]

Hi Jonathan,

thanks for your quick answer.

On Thursday 02 July 2009 15:23, Jonathan Crabtree wrote:
> Just based on what's in your e-mail, it looks as though BioSQL *wants* a
> NULL value to come back as the 6th column of every row in the result of
> that query.  So by removing it you run the risk that BioSQL is going to
> retrieve the wrong values from the query result, at least for those columns
> after the 6th (and assuming all the columns are retrieved by position--it's
> not entirely clear if this is the case.) 

Well, what made me suspicious was that the returned columns were exactly the 
ones from the term table plus the NULL column. One way to verify this would 
be to look whether the same thing happens with other tables as well.

> I'd be inclined to throw in a 
> test here to see if the backend is DB2 and, if so, substitute the
> appropriate syntax instead of "NULL".  I'm not sure what that syntax is,
> but a bit of web searching suggests that you might be able to select the
> value from a dummy table (this might be more difficult because it would
> require non-local code changes -- this method is only for the select list)
> or use a function called "nullif" with appropriately-chosen arguments. 
> Another comment I saw suggested that using "NULL" was OK but it has to be
> coerced/typecast into the right type.

Yeah, this was what I've found, too, but I couldn't figure out what was the 
right type to cast to.


Unfortunately, now that the database is filled (hopefully correctly), the 
script gives me a different error message and I don't know if it is because 
of a change I made or because the database is not empty. Originally, I was 
struggling with Hibernate and I'm back to it again (damn CLOBs...), so I am 
happy to have a seemingly correct database to work with.

I'm pretty confident that I can write a working DB2 driver for BioPerl, but 
for that I should start from scratch instead of copying the MySQL one and 
modifying it until all error messages disappear. And this would take far too 
much time, if I'm doing this by trial and error.

Is there any developers guide that would help to find out what methods I have 
to override to implement database specific stuff?


Thanks,
Florian

> On Thu, Jul 2, 2009 at 5:28 AM, Florian Mittag
> <florian.mittag at uni-tuebingen.de> wrote: 
> > Hi!
> >
> > I previously posted a message on the BioSQL mailinglist regarding a
> > BioSQL schema for DB2 and we are several steps closer to completion now.
> >
> > We were able to adapt the "load_ncbi_taxonomy.pl" script from BioSQL to
> > fill
> > our DB2 database with taxonomy data, but loading the gene ontology with
> > BioPerl's "load_ontology.pl" is somewhat harder.
> >
> > We created the Package Bio::DB::BioSQL::DB2 and copy-pasted the contents
> > of the Oracle package into it. Then we changed the (what we thought)
> > appropriate
> > methods whenever we encountered an error, but now we are a bit
> > frustrated.
> >
> > We execute the command:
> > perl load_ontology.pl --driver DB2 --dbname bioseqdb --dbuser user
> > --dbpass passwd --namespace "Gene Ontology"
> > --format obo --debug gene_ontology.1_2.obo
> >
> > It first ran a few minutes processing the file and then died after the
> > following SQL-command was prepared and executed:
> >
> > "SELECT term.term_id, term.identifier, term.name, term.definition,
> > term.is_obsolete, NULL, term.ontology_id FROM term WHERE identifier = ?"
> >
> > I don't know if the "NULL" column is supposed to be there, but DB2
> > doesn't like it. After ours of digging into the code, I gave up and
> > simply commented
> > out the line that added the NULL column in
> > Bio::DB::BioSQL::BaseDriver::_build_select_list
> >
> >        ...
> >        if((! $attr) || (! $entitymap->{$tbl}) ||
> >           $dont_select_attrs->{$tbl .".". $attr}) {
> > #            push(@attrs, "NULL");
> >        } else {
> >        ...
> >
> > The script completed with a few warnings, like:
> > "no adaptor found for class Bio::Annotation::TypeManager"
> > or
> > "-------------------- WARNING ---------------------
> > MSG: PMID:15012271 exists in the dblink of _default"
> >
> > so we don't know, if it really worked. Since removing this one line will
> > probably break compatibility with other databases, it is not a real
> > solution
> > and we would appreciate any hints pointing us to the real cause.
> >
> >
> > We would really like to contribute to the BioPerl project by adding DB2
> > support, but we need some help here, since none of us has experience with
> > either Perl or BioPerl ;-)
> >
> >
> > Keep up the good work!
> >
> > Regards,
> > Florian
> >
> >
> >
> > --
> > Dipl. Inf. Florian Mittag
> > Universität Tuebingen
> > WSI-RA, Sand 1
> > 72076 Tuebingen, Germany
> > Phone: +49 7071 / 29 78985  Fax: +49 7071 / 29 5091
> >
> > _______________________________________________
> > Bioperl-l mailing list
> > Bioperl-l at lists.open-bio.org
> > http://lists.open-bio.org/mailman/listinfo/bioperl-l
>
> _______________________________________________
> Bioperl-l mailing list
> Bioperl-l at lists.open-bio.org
> http://lists.open-bio.org/mailman/listinfo/bioperl-l

-- 
Dipl. Inf. Florian Mittag
Universität Tuebingen
WSI-RA, Sand 1
72076 Tuebingen, Germany
Phone: +49 7071 / 29 78985  Fax: +49 7071 / 29 5091