DB2 driver for BioPerl

[Florian Mittag]

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@...
http://lists.open-bio.org/mailman/listinfo/bioperl-l

[Jonathan Crabtree-4]

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@...> 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@...
> http://lists.open-bio.org/mailman/listinfo/bioperl-l
>

_______________________________________________
Bioperl-l mailing list
Bioperl-l@...
http://lists.open-bio.org/mailman/listinfo/bioperl-l

[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@...> 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@...
> > http://lists.open-bio.org/mailman/listinfo/bioperl-l
>
> _______________________________________________
> Bioperl-l mailing list
> Bioperl-l@...
> 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

_______________________________________________
Bioperl-l mailing list
Bioperl-l@...
http://lists.open-bio.org/mailman/listinfo/bioperl-l

[Jonathan Crabtree-4]

Hi Florian,

On Thu, Jul 2, 2009 at 10:52 AM, Florian Mittag
<florian.mittag@...> wrote:
> 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.

Others may disagree, but I think it's fairly clear (from just looking
at the subroutine you mentioned) that the inclusion of the NULL value
is most definitely deliberate; note that it is only done if $entitymap
doesn't have a value for the table in question, and $entitymap is
described as follows:

A reference to a hash table mapping entity names to aliases (if
omitted, aliases will not be used, and SELECT columns can only be
from one table)

So I suspect what we're seeing here is a select in which aliases
_aren't_ being used and therefore the order of the returned values is
significant, and the NULL value is needed to keep everything in the
right order for whatever piece of code is reading the result.  But
never having worked much with BioSQL I don't know where you'd go to
find the type information needed to determine what type the NULL value
needs to be coerced into...

Jonathan
_______________________________________________
Bioperl-l mailing list
Bioperl-l@...
http://lists.open-bio.org/mailman/listinfo/bioperl-l

[Hilmar Lapp]

Hi Florian:

On Jul 2, 2009, at 11:28 AM, Florian Mittag 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.

Good to hear!

> We were able to adapt the "load_ncbi_taxonomy.pl" script from BioSQL  
> to fill
> our DB2 database with taxonomy data

Would you mind posting to the BioSQL list which changes you had to  
make to make the script work with DB2?

More generally, is there some kind of comprehensive documentation on  
what is different in DB2 from standard SQL92? The  
load_ncbi_taxonomy.pl script should in principle work with any SQL92-
compliant RDBMS ... Have you found that not to be the case (which  
would be a bug), or is DB2 in some ways not SQL92-compliant?

> , but loading the gene ontology with BioPerl's "load_ontology.pl" is  
> somewhat harder.

The ontology as well as the sequence loader are really just front-ends  
to the Bioperl-db object-relational mappers (ORMs). So I would start  
there, rather than looking at errors the script does or does not throw  
(you don't want to run all combinations of command line parameters  
that would exercise each and every feature of the script).

In order to create DB2 driver support in Bioperl-db, you need to add  
two things. First, you need to create a module Bio/DB/DBI/DB2.pm that  
overrides the methods from base.pm according to DB2. The fact that you  
didn't report any errors about that module not having been found  
suggests that you've done this already.

The second step is as you say to create a package Bio/DB/BioSQL/DB2  
with at least BasePersistenceAdaptorDriver.pm as module in it, and  
starting with a copy of the existing ones is indeed the best way to  
get started on this. Unless you also created the DB2 database DDL  
scripts from the Oracle ones, I wouldn't necessarily copy from Oracle  
though, but maybe rather from Pg. And rather than looking for errors  
of one of the scripts, I'd just go systematically through the files  
and make sure the SQL in there is DB2 compliant.

> [...]
> 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  
> = ?"

Could you post the full error message? It is rather difficult to  
diagnose what's going on w/o the error message and stack trace.

I'd be surprised BTW if DB2 were indeed offended by the NULL in the  
above statement - I'm pretty sure that "SELECT NULL FROM  
sometable" (or "SELECT 1 FROM sometable") is standard SQL. Are you  
sure that if you execute such a statement at a SQL prompt it results  
in an error?

Since I can hardly believe that DB2 doesn't support selecting  
constants (NULL is as much a constant as 1 is), maybe what it wants  
though is aliasing the column. So if

SELECT NULL FROM bioentry;

yields an error, does

SELECT NULL AS colAlias FROM bioentry;

work fine?

> I don't know if the "NULL" column is supposed to be there

It is. The code in BaseDriver.pm that you were looking at should not  
need to be modified. (Rather, DB2/BasePersistenceAdaptorDriver.pm is  
supposed to override any method that needs to be adapted to DB2.) The  
way the ORM works is by trying to map all properties of a BioPerl  
object that are persistent to a column of a table in the database. If  
it can't map a property (for whatever reason) its value is simply  
always undef (or NULL in SQL). I.e., NULL columns are the placeholder  
for a column that failed to be mapped to a property. You can't simply  
remove them or all subsequent columns are shifted.

Hth,
        -hilmar
--
===========================================================
: Hilmar Lapp  -:-  Durham, NC  -:-  hlapp at gmx dot net :
===========================================================



_______________________________________________
Bioperl-l mailing list
Bioperl-l@...
http://lists.open-bio.org/mailman/listinfo/bioperl-l

[Florian Mittag]

Hi!

On Saturday 04 July 2009 12:39, Hilmar Lapp wrote:
> On Jul 2, 2009, at 11:28 AM, Florian Mittag wrote:
> > We were able to adapt the "load_ncbi_taxonomy.pl" script from BioSQL
> > to fill
> > our DB2 database with taxonomy data
>
> Would you mind posting to the BioSQL list which changes you had to
> make to make the script work with DB2?

No problem, I will post the diff sometime this week, since there are a few
changes not necessary anymore, e.g., the new DB2 Express-C version 9.7
supports the "TRUNCATE TABLE" command, which it previously didn't.


> More generally, is there some kind of comprehensive documentation on
> what is different in DB2 from standard SQL92? The
> load_ncbi_taxonomy.pl script should in principle work with any SQL92-
> compliant RDBMS ... Have you found that not to be the case (which
> would be a bug), or is DB2 in some ways not SQL92-compliant?

I don't know, I haven't looked for this kind of documentation, but the two
things that annoyed me most were:
1) DB2 doesn't support UNIQUE on columns that allow for NULL values.
Solution: create triggers that ensure UNIQUEness and create an INDEX.

2) Columns of type CLOB do not allow to be compared through "=", but only
through "LIKE", which leads to problems with BioJava's Hibernate queries.
Solution: currently none

I want to discuss these problems in more detail on the other mailinglists,
since they do not really belong here.

> > , but loading the gene ontology with BioPerl's "load_ontology.pl" is
> > somewhat harder.
>
> The ontology as well as the sequence loader are really just front-ends
> to the Bioperl-db object-relational mappers (ORMs). So I would start
> there, rather than looking at errors the script does or does not throw
> (you don't want to run all combinations of command line parameters
> that would exercise each and every feature of the script).
>
> In order to create DB2 driver support in Bioperl-db, you need to add
> two things. First, you need to create a module Bio/DB/DBI/DB2.pm that
> overrides the methods from base.pm according to DB2. The fact that you
> didn't report any errors about that module not having been found
> suggests that you've done this already.

Correct ;-)


> The second step is as you say to create a package Bio/DB/BioSQL/DB2
> with at least BasePersistenceAdaptorDriver.pm as module in it, and
> starting with a copy of the existing ones is indeed the best way to
> get started on this. Unless you also created the DB2 database DDL
> scripts from the Oracle ones, I wouldn't necessarily copy from Oracle
> though, but maybe rather from Pg. And rather than looking for errors
> of one of the scripts, I'd just go systematically through the files
> and make sure the SQL in there is DB2 compliant.

Okay, I'll do that, but that will take some time and I'll probably turn to
this mailings for further assistance with more specific questions.

> > [...]
> > 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
> > = ?"
>
> Could you post the full error message? It is rather difficult to
> diagnose what's going on w/o the error message and stack trace.

Right now, unfortunately not, because this error message won't appear again.
I'm not sure is this is because of the database now containing data or
because of some other changes I've made, but I will see this in the process
of rewriting the DDL scripts.

> I'd be surprised BTW if DB2 were indeed offended by the NULL in the
> above statement - I'm pretty sure that "SELECT NULL FROM
> sometable" (or "SELECT 1 FROM sometable") is standard SQL. Are you
> sure that if you execute such a statement at a SQL prompt it results
> in an error?
>
> Since I can hardly believe that DB2 doesn't support selecting
> constants (NULL is as much a constant as 1 is), maybe what it wants
> though is aliasing the column. So if
>
> SELECT NULL FROM bioentry;
>
> yields an error, does
>
> SELECT NULL AS colAlias FROM bioentry;
>
> work fine?

Well, it is like this with version 9.5 of DB2 Express-C:

SELECT NULL FROM bioentry;

yields:
 SQL0206N  "NULL" is not valid in the context where it is used.  
SQLSTATE=42703 SQLCODE=-206

But if I do:

SELECT cast(NULL AS VARCHAR(255)) FROM bioentry;

it returns the correct result without error. Thew new version 9.7 claims to
have changed this behavior, so that the first query would run fine, but I
didn't have time to test the new version, yet.

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.wn.doc/doc/i0054263.html

>
> > I don't know if the "NULL" column is supposed to be there
>
> It is. The code in BaseDriver.pm that you were looking at should not
> need to be modified. (Rather, DB2/BasePersistenceAdaptorDriver.pm is
> supposed to override any method that needs to be adapted to DB2.) The
> way the ORM works is by trying to map all properties of a BioPerl
> object that are persistent to a column of a table in the database. If
> it can't map a property (for whatever reason) its value is simply
> always undef (or NULL in SQL). I.e., NULL columns are the placeholder
> for a column that failed to be mapped to a property. You can't simply
> remove them or all subsequent columns are shifted.

It ran fine without the NULL column, but that isn't necessarily a sign of
correctness. My problem was that (as stated above) the old version of DB2
requires you to cast the NULL value to a data type, which I wasn't able to
determine from the code. With the new version, it should work, so I'll have
to rerun my tests again and see if the problem is still there.


I will keep you updated on the Perl issues and hope to have some useful
results by the end of the week. And I hope you excuse me for posting things
here that are hardly related to BioPerl, but the some problems are a complex
entanglement of issues with BioSQL, BioPerl and BioJava, so it's hard to
decide where to post it ;-)


Regards,
 Florian
_______________________________________________
Bioperl-l mailing list
Bioperl-l@...
http://lists.open-bio.org/mailman/listinfo/bioperl-l

[Peter-329]

On BioPerl-l, July 2009, Florian Mittag wrote:
> ...
> Okay, I'll do that, but that will take some time and I'll probably turn to
> this mailings for further assistance with more specific questions.
> ...
> I will keep you updated on the Perl issues and hope to have some useful
> results by the end of the week. And I hope you excuse me for posting things
> here that are hardly related to BioPerl, but the some problems are a complex
> entanglement of issues with BioSQL, BioPerl and BioJava, so it's hard to
> decide where to post it ;-)

You may want to cross post some things (e.g. the hibernate issue
to BioSQL and BioJava lists). I've CC'd this reply to BioSQL-l for
example. I think some guidance from Hilmar on this etiquette would
help ;)

I would not expect BioJava people to follow BioPerl-l for example.
(Although here I am as a Biopython person keeping an eye on
BioPerl-l sometimes).

I assume (hope?) that people from all the Bio* projects with BioSQL
bindings will be following the BioSQL-l mailing list - so for anything
clearly cross project like the schemas themselves, at very least
please CC the BioSQL-l mailing list.

Peter
(Biopython)
_______________________________________________
Bioperl-l mailing list
Bioperl-l@...
http://lists.open-bio.org/mailman/listinfo/bioperl-l

[Florian Mittag]

Hi!

So, I have finally installed the new version 9.7 of DB2, but I am a bit
disappointed. First of all, the TRUNCATE TABLE command only works when it is
the first command in a transaction, so I will skip this one for now.

Secondly, they only changed the way "NULL" is interpreted in a SELECT
statement, when there is a column name "NULL". But it still is not allowed to
have untyped NULLs in a select statement.

On Monday 06 July 2009 18:08, Florian Mittag wrote:

> On Saturday 04 July 2009 12:39, Hilmar Lapp wrote:
> > I'd be surprised BTW if DB2 were indeed offended by the NULL in the
> > above statement - I'm pretty sure that "SELECT NULL FROM
> > sometable" (or "SELECT 1 FROM sometable") is standard SQL. Are you
> > sure that if you execute such a statement at a SQL prompt it results
> > in an error?
> >
> > Since I can hardly believe that DB2 doesn't support selecting
> > constants (NULL is as much a constant as 1 is), maybe what it wants
> > though is aliasing the column. So if
> >
> > SELECT NULL FROM bioentry;
> >
> > yields an error, does
> >
> > SELECT NULL AS colAlias FROM bioentry;
> >
> > work fine?
>
> Well, it is like this with version 9.5 of DB2 Express-C:
>
> SELECT NULL FROM bioentry;
>
> yields:
>  SQL0206N  "NULL" is not valid in the context where it is used.
> SQLSTATE=42703 SQLCODE=-206
>
> But if I do:
>
> SELECT cast(NULL AS VARCHAR(255)) FROM bioentry;
>
> [...]
>
> It ran fine without the NULL column, but that isn't necessarily a sign of
> correctness. My problem was that (as stated above) the old version of DB2
> requires you to cast the NULL value to a data type, which I wasn't able to
> determine from the code. With the new version, it should work, so I'll have
> to rerun my tests again and see if the problem is still there.

You convinced me that the NULL column is supposed to be there, so I found
another workaround around line 1273 in BaseDriver.pm:

        if((! $attr) || (! $entitymap->{$tbl}) ||
           $dont_select_attrs->{$tbl .".". $attr}) {
            #push(@attrs, "NULL");
            push(@attrs, "cast(NULL as VARCHAR(255))");
        } else {

Since I don't know how to determine the datatype of the column that is set to
NULL, I simply chose VARCHAR and tested it. And it worked! (BTW: The column
set to NULL is named "rank" in the case below.)

But as before, it gives me a bunch of Warnings. The other messages between the
warning are debug messages I inserted myself and they show which SQL commands
are to be executed. The following output is only the end of a nearly endless
stream of warnings similar to those.


-------------------- WARNING ---------------------
MSG: GOC:mah exists in the dblink of _default
---------------------------------------------------
SELECT UK Bio::DB::BioSQL::TermAdaptoridentifier
identifier : GO:0034679
SELECT term.term_id, term.identifier, term.name, term.definition,
term.is_obsolete, cast(NULL as VARCHAR(255)), term.ontology_id FROM term
WHERE identifier = ?

-------------------- WARNING ---------------------
MSG: PMID:12297042 exists in the dblink of _default
---------------------------------------------------
SELECT UK Bio::DB::BioSQL::TermAdaptoridentifier
identifier : GO:0070505
SELECT term.term_id, term.identifier, term.name, term.definition,
term.is_obsolete, cast(NULL as VARCHAR(255)), term.ontology_id FROM term
WHERE identifier = ?

-------------------- WARNING ---------------------
MSG: GOC:mah exists in the dblink of _default
---------------------------------------------------

-------------------- WARNING ---------------------
MSG: GOC:rph exists in the dblink of _default
---------------------------------------------------

-------------------- WARNING ---------------------
MSG: PMID:12930826 exists in the dblink of _default
---------------------------------------------------

-------------------- WARNING ---------------------
MSG: PMID:15012271 exists in the dblink of _default
---------------------------------------------------


Should I be worried?

For now, I'll continue with my actual work on our program, so it is possible
that some problems will turn up later.


Regards,
   Florian
_______________________________________________
Bioperl-l mailing list
Bioperl-l@...
http://lists.open-bio.org/mailman/listinfo/bioperl-l

[Florian Mittag]

Hi!

I just noticed, that we didn't solve this problem completely.

On Wednesday, 15. July 2009 15:00, Florian Mittag wrote:

> > Well, it is like this with version 9.5 of DB2 Express-C:
> >
> > SELECT NULL FROM bioentry;
> >
> > yields:
> >  SQL0206N  "NULL" is not valid in the context where it is used.
> > SQLSTATE=42703 SQLCODE=-206
> >
> > But if I do:
> >
> > SELECT cast(NULL AS VARCHAR(255)) FROM bioentry;
> >
> > [...]
> >
> > It ran fine without the NULL column, but that isn't necessarily a sign of
> > correctness. My problem was that (as stated above) the old version of DB2
> > requires you to cast the NULL value to a data type, which I wasn't able
> > to determine from the code. With the new version, it should work, so I'll
> > have to rerun my tests again and see if the problem is still there.
>
> You convinced me that the NULL column is supposed to be there, so I found
> another workaround around line 1273 in BaseDriver.pm:
>
>         if((! $attr) || (! $entitymap->{$tbl}) ||
>            $dont_select_attrs->{$tbl .".". $attr}) {
>             #push(@attrs, "NULL");
>             push(@attrs, "cast(NULL as VARCHAR(255))");
>         } else {
>
> Since I don't know how to determine the datatype of the column that is set
> to NULL, I simply chose VARCHAR and tested it. And it worked! (BTW: The
> column set to NULL is named "rank" in the case below.)

Although this solution works, it is not the best, because it breaks
compatibility with all other database types, e.g., MySQL. Is there a way to
change the "NULL" to "cast(NULL as VARCHAR(255))" only when the driver is
DB2?

- Florian
_______________________________________________
Bioperl-l mailing list
Bioperl-l@...
http://lists.open-bio.org/mailman/listinfo/bioperl-l

[Hilmar Lapp]

On Aug 6, 2009, at 5:38 AM, Florian Mittag wrote:

> Is there a way to change the "NULL" to "cast(NULL as VARCHAR(255))"  
> only when the driver is DB2?


Not yet, but that's the solution I had in mind, i.e., introducing a  
method in the Bio::DB::DBI::* (driver-specific) classes that returns  
whatever NULL as a SELECT field should be represented as. What will be  
very hard or nearly impossible to do is to cast to the actual type of  
the column, so if simply using VARCHAR(255) does the trick for DB2  
that'd be great. <sigh/>

BTW you did check that simply aliasing the column does not fix the  
problem for DB2, right? I.e., "SELECT NULL AS col1 FROM bioentry" will  
throw an error, right?

        -hilmar
--
===========================================================
: Hilmar Lapp  -:-  Durham, NC  -:-  hlapp at gmx dot net :
===========================================================



_______________________________________________
Bioperl-l mailing list
Bioperl-l@...
http://lists.open-bio.org/mailman/listinfo/bioperl-l

[Florian Mittag]

On Thursday, 6. August 2009 15:42, Hilmar Lapp wrote:
> On Aug 6, 2009, at 5:38 AM, Florian Mittag wrote:
> > Is there a way to change the "NULL" to "cast(NULL as VARCHAR(255))"
> > only when the driver is DB2?
>
> Not yet, but that's the solution I had in mind, i.e., introducing a
> method in the Bio::DB::DBI::* (driver-specific) classes that returns
> whatever NULL as a SELECT field should be represented as.

Sounds like a good idea!


> What will be
> very hard or nearly impossible to do is to cast to the actual type of
> the column, so if simply using VARCHAR(255) does the trick for DB2
> that'd be great. <sigh/>

Surprisingly, it does. At least, I haven't noticed any problems if the target
data type is for example an integer. With all the trouble I have with DB2, I
didn't expect this.


> BTW you did check that simply aliasing the column does not fix the
> problem for DB2, right? I.e., "SELECT NULL AS col1 FROM bioentry" will
> throw an error, right?

Yepp:

SELECT term.term_id, term.identifier, term.name, term.definition,
term.is_obsolete, NULL AS col1, term.ontology_id FROM term WHERE identifier
= ?

[IBM][CLI Driver][DB2/LINUX] SQL0418N  A statement contains a use of an
untyped parameter marker or a null value that is not valid.


- Florian
_______________________________________________
Bioperl-l mailing list
Bioperl-l@...
http://lists.open-bio.org/mailman/listinfo/bioperl-l