[Bioperl-l] DB2 driver for BioPerl
Florian Mittag
florian.mittag at uni-tuebingen.de
Wed Jul 15 13:00:21 UTC 2009
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
More information about the Bioperl-l
mailing list