[Bioperl-l] DB2 driver for BioPerl

[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