[Bioperl-l] DB2 driver for BioPerl

[Jonathan Crabtree]

Hi Florian,

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.)  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.

Jonathan

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
>