Derby casting madness – the sequel
Join the DZone community and get the full member experience.
Join For FreeI have recently blogged about the general bind variable casting madness in SQL:
http://lukaseder.wordpress.com/2011/08/31/rdbms-bind-variable-casting-madness/
So this is the sequel of the above story, purely dedicated to Derby with its “conversion table from hell“. One of jOOQ’s goals is to make SQL as compatible as possible across various databases, in a way that you can re-use the same SQL on various environments. For instance:
- Use Derby to develop your database
- Use DB2 for production
While I personally discourage such set-ups, I know that many developers prefer that, especially when it comes to run fast-running integration tests. And the above coupling of Derby and DB2 is an especially good one, as Derby is quite similar to DB2. See also this Stack Overflow Question:
http://stackoverflow.com/questions/4419684/portable-schema-between-derby-and-db2
But back to casting. In order to make casting as compatible as possible, jOOQ generates casting SQL according to the following rules:
Casting NUMERIC to VARCHAR
Interestingly, this is not supported, but casting to CHAR is. So jOOQ generates:
-- When 123 is inlined: trim(cast(cast(123 as char(38)) as varchar(32672))) -- When 123 is bound as a variable trim(cast(cast(cast(? as int) as char(38)) as varchar(32672)))
Casting CHAR/VARCHAR to DOUBLE/FLOAT/REAL
Again, this isn’t supported for some reason. So jOOQ generates:
-- When 123.0 is inlined: cast(cast('123.0' as decimal) as float) -- When 123.0 is bound as a variable cast(cast(cast(? as varchar(32672)) as decimal) as float)
Casting NUMERIC to BOOLEAN
This can’t be expressed simply with a CAST clause. A CASE .. WHEN clause is rendered instead, by jOOQ (note that Derby doesn’t support the simple CASE clause either…):
case when cast(? as int) = 0 then false when cast(? as int) is null then null else true end
Casting CHAR/VARCHAR to BOOLEAN
The Derby documentation claims that this should work, but I’ve experienced quite some trouble. Derby seems to accept only SQL standard boolean literals and rejects values such as ’0′, ’1′, etc… Most databases accept ’0′, ’1′ as boolean string values as well. So jOOQ simulates the following
case when cast(? as varchar(32672)) = '0' then false when lower(cast(? as varchar(32672))) = 'false' then false when lower(cast(? as varchar(32672))) = 'f' then false when cast(? as varchar(32672)) is null then null else true end
Casting other pairs
Luckily, all other common types of casts seem to work as expected, also in the Derby database.
From http://lukaseder.wordpress.com/2011/10/29/derby-casting-madness-the-sequel/
Opinions expressed by DZone contributors are their own.
Comments