SQL Zone is brought to you in partnership with:

I am the founder and CEO of Data Geekery GmbH, located in Zurich, Switzerland. With our company, we have been selling database products and services around Java and SQL since 2013. Ever since my Master's studies at EPFL in 2006, I have been fascinated by the interaction of Java and SQL. Most of this experience I have obtained in the Swiss E-Banking field through various variants (JDBC, Hibernate, mostly with Oracle). I am happy to share this knowledge at various conferences, JUGs, in-house presentations and on our blog. Lukas is a DZone MVB and is not an employee of DZone and has posted 255 posts at DZone. You can read more from them at their website. View Full User Profile

Derby casting madness – the sequel

11.09.2011
| 3650 views |
  • submit to reddit

I 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/

Published at DZone with permission of Lukas Eder, author and DZone MVB.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Tags: