customType/forcedType by database vendor?

[Witold Szczerba]

Hi,
I am migrating from H2 to Oracle (H2 was used in early dev to kick start the project, customer forces us to use Oracle). There are few problems, one of which is the Oracle does not support boolean types, so following manual section:

Custom data types and type conversion
http://www.jooq.org/doc/2.6/manual/code-generation/custom-data-types/

Snippets available: https://gist.github.com/4649307

My question is: is it possible to enable that converter only when operating on Oracle and ignore it otherwise?

Regards,
Witold Szczerba

[Ben Hood]

Hey Witold,

JOOQ _should_ handle this seamlessly - our app runs on H2 and Oracle without the need to do any custom conversion. AFAIK, the JOOQ factory reads the dialect at runtime and issues the appropriate SQL transparently, as long as your boolean encoding is vaguely sane (e.g. something like 1,0, true, false). Have you been seeing issues with this?

Cheers,

Ben

--
 
 

[Witold Szczerba]

Hi,

I am using code generator and without applying my converter the generated code looks like this:

(class names simplified)

public class Estate extends UpdatableTableImpl<EstateRecord> {
[...]
public final TableField<EstateRecord, Byte> DEVELOPED = createField("DEVELOPED", SQLDataType.TINYINT, this);

//with converter applied:

public final TableField<EstateRecord, Boolean> DEVELOPED = createField("DEVELOPED", SQLDataType.TINYINT.asConvertedDataType(new OracleBooleanConverter()), this);
[...]
}

I do believe it could handle boolean type conversion on the fly (in Oracle the column type is NUMBER(1,0), but my case is about code generator.

Regards,
Witold Szczerba

--
 
 

[Lukas Eder]

This issue was recently encountered on Stack Overflow:

http://stackoverflow.com/q/14123209/521799

You can force a set of fields matched by a regex (not a data type, such as NUMBER(1, 0)) to the SQL BOOLEAN data type. This will then work for both H2 and Oracle

Cheers

Lukas

2013/1/28 Witold Szczerba <pljos...@gmail.com>

--
 
 

[Witold Szczerba]

Hi,
I have just checked the solution with no custom converter, used the built-in BOOLEAN.

It does not work well with Oracle (don't know about other databases). The problem is with null values:

org.jooq.exception.DataAccessException: SQL [null]; Invalid column type: 16
    at org.jooq.impl.Util.translate(Util.java:649)
    at org.jooq.impl.AbstractBindContext.bindValue(AbstractBindContext.java:142)
    at org.jooq.impl.Val.bind(Val.java:544)
    at org.jooq.impl.AbstractBindContext.bindInternal(AbstractBindContext.java:154)
    at org.jooq.impl.AbstractBindContext.bind(AbstractBindContext.java:111)
    at org.jooq.impl.AbstractBindContext.bind(AbstractBindContext.java:71)
    at org.jooq.impl.FieldMapForInsert.bind(FieldMapForInsert.java:135)
    at org.jooq.impl.AbstractBindContext.bindInternal(AbstractBindContext.java:154)
    at org.jooq.impl.AbstractBindContext.bind(AbstractBindContext.java:111)
    at org.jooq.impl.AbstractBindContext.bind(AbstractBindContext.java:71)
    at org.jooq.impl.FieldMapsForInsert.bind(FieldMapsForInsert.java:135)
    at org.jooq.impl.AbstractBindContext.bindInternal(AbstractBindContext.java:154)
    at org.jooq.impl.AbstractBindContext.bind(AbstractBindContext.java:111)
    at org.jooq.impl.InsertQueryImpl.bindInsert(InsertQueryImpl.java:360)
    at org.jooq.impl.InsertQueryImpl.bind(InsertQueryImpl.java:330)
    at org.jooq.impl.AbstractBindContext.bindInternal(AbstractBindContext.java:154)
    at org.jooq.impl.AbstractBindContext.bind(AbstractBindContext.java:111)
    at org.jooq.impl.Factory.bind(Factory.java:736)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:254)
    at org.jooq.impl.TableRecordImpl.storeInsert(TableRecordImpl.java:189)
    at org.jooq.impl.TableRecordImpl.storeUsing(TableRecordImpl.java:150)
    at org.jooq.impl.UpdatableRecordImpl.store(UpdatableRecordImpl.java:78)

The offending code:

AbstractBindContext:
    @Override
    public final BindContext bindValue(Object value, Class<?> type) {
        try {
            return bindValue0(value, type);
        }
        catch (SQLException e) {
            throw Util.translate(null, e);
        }
    }

where,

value is null
type is java.lang.Boolean

When switched back to my converter, the null value of Boolean (SQL Types.BOOLEAN (16) gets replaced by null value of TINYINT (-6) and Oracle JDBC driver handles it correctly.

I am using driver: ojdbc6.jar
Implementation-Vendor: Oracle Corporation
Implementation-Title: JDBC
Implementation-Version: 11.2.0.2.0

Regards,
Witold Szczerba

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Lukas Eder]

Hello Witold,

Yes, I can reproduce this. Thanks for reporting. This reminds me why I
haven't documented this feature yet: It is still in a somewhat
experimental state since it was implemented. As a reminder, I have
registered #2152 for this:
https://github.com/jOOQ/jOOQ/issues/2152

On the other hand, this shows that maybe re-writing SQL data types
(without converters) doesn't work reliably at all. jOOQ needs some
sort of type information, when binding variables through the JDBC API.
If BOOLEAN is the only information available to jOOQ, it cannot guess
that TINYINT (or NUMBER) would be a more suitable type. In other
words, this information is lost during the code generation process. So
maybe, converters are the only correct way to handle booleans here...?

I'll have to further investigate this issue. The correct handling of
SQL NULL through 14 dialects and 14 JDBC drivers is quite a pain ;-)

Cheers
Lukas



2013/1/31 Witold Szczerba <pljos...@gmail.com>:

[Durchholz, Joachim]

> On the other hand, this shows that maybe re-writing SQL data types
> (without converters) doesn't work reliably at all. jOOQ needs some
> sort of type information, when binding variables through the JDBC
> API.
> If BOOLEAN is the only information available to jOOQ, it cannot
> guess that TINYINT (or NUMBER) would be a more suitable type.

In fact there's a whole lot of reasonable encodings.
It can be a character data type with an encoding of Y/N (or J/N in German shops, O/N in French shops, 0/1 in C-influenced shops, etc.)
For numbers, some encode it as 0/1, others as 0/-1.
Not all encodings are reasonable, but all are in use :-)

[Lukas Eder]

> In fact there's a whole lot of reasonable encodings.
> It can be a character data type with an encoding of Y/N (or J/N in German shops, O/N in French shops, 0/1 in C-influenced shops, etc.)
> For numbers, some encode it as 0/1, others as 0/-1.
> Not all encodings are reasonable, but all are in use :-)

These are application-specific encodings, and I dare say they're all "unreasonable". The JDBC specification says:

If the designated column has a datatype of CHAR or VARCHAR and contains a "0" or has a datatype of BIT, TINYINT, SMALLINT, INTEGER or BIGINT and contains a 0, a value of false is returned. If the designated column has a datatype of CHAR or VARCHAR and contains a "1" or has a datatype of BIT, TINYINT, SMALLINT, INTEGER or BIGINT and contains a 1, a value of true is returned.

Taken from http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#getBoolean(int)

Also, the SQL standard mentions bit values of 0 and 1:

              Note: BOOLEAN is the predefined enumeration type STANDARD.BOOLEAN
              with values (FALSE, TRUE). The equivalences

                  BOOLEAN'POS(FALSE)  0
                  BOOLEAN'POS(TRUE)  1

              define the correspondence between the bit values of 0 and 1
              and the Boolean values of false and true, respectively.

Taken from http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt (I didn't find any such reference in a later version of the standard, in a quick document scan).

Chosing any other encoding for boolean values in a Java / SQL application will probably just cause headaches without adding value. If your legacy database does have such encodings, you can still use jOOQ's converter feature

Cheers
Lukas

[Durchholz, Joachim]

Heh. I wasn't aware that JDBC proposes a standard mapping.
Though who's that new-fangled "JDBC" to establish a standard in database design ;-P

More seriously, since Jooq's mission is "database first", JDBC conventions are probably not an authoritative source for DB encoding conventions.

I agree that in the absence of a standard encoding, you'd always need a Jooq converter, so a standard encoding could be useful.
On the other hand, standard conventions like that have been eating incredible amounts of debugging time whenever they did not do exactly the right thing, so I'm generally not too happy about them. That's a design trade-off, of course, and YMMV.

Regards,
Jo

[Lukas Eder]

> More seriously, since Jooq's mission is "database first", JDBC conventions are probably not an authoritative source for DB encoding conventions.

I trust that the JDBC specification was made by an expert group based
on well-informed "database-first" decisions. In particular, this case
is not a convention but behaviour by specification. JDBC drivers are
to adhere to this specification. Hence the abstraction provided by
JDBC over any underlying database is trustworthy enough for jOOQ to
rely on - unless there is some specification ambiguity that I have now
overlooked...?

[Witold Szczerba]

So, the bottom line is the Oracle JDBC driver does not care about specification. Trying to set a number(1,0) field with a null value as boolean throws an exception and there is not much we can do (excluding registering an issue at oracle).
As far as I remember, projects like Hibernate or Toplink Essentials (followed by Eclipselink) do translate booleans to numbers before touching JDBC, but maybe we should double check it.

Regards,
Witold Szczerba
---
Sent from my mobile phone.

[Lukas Eder]

> So, the bottom line is the Oracle JDBC driver does not care about
> specification. Trying to set a number(1,0) field with a null value as
> boolean throws an exception and there is not much we can do (excluding
> registering an issue at oracle).

In this particular case, yes, Oracle doesn't properly implement PreparedStatement.setNull(index, Types.BOOLEAN) if the underlying data type is better described by Types.TINYINT. However, It is possible to let the driver "guess" the appropriate SQL type: PreparedStatement.setObject(index, null). The problem is that this change is quite delicate and would cause other parts of jOOQ to break.

> As far as I remember, projects like Hibernate or Toplink Essentials
> (followed by Eclipselink) do translate booleans to numbers before touching
> JDBC, but maybe we should double check it.

Yes, as long as jOOQ has the relevant information (e.g. when using converters), it knows that it really has to bind a "CAST(NULL AS NUMBER)", not "CAST(NULL AS BOOLEAN)". Forcing types to BOOLEAN without converters leads to jOOQ being ignorant of that fact.

The "easiest" way of being database-agnostic in this case seems to be not to use the SQL BOOLEAN type in other databases, and model boolean values the same way across databases. Other users on this list may have made different experiences. Maybe Aaron Digulla has some insight?

@Aaron: How do you guys deal with the SQL BOOLEAN type and jOOQ across your many supported database dialects?

Cheers

[Durchholz, Joachim]

>> More seriously, since Jooq's mission is "database first",
>> JDBC conventions are probably not an authoritative source
>> for DB encoding conventions.
>
> I trust that the JDBC specification was made by an expert
> group based on well-informed "database-first" decisions.

Given the large number of project for "a better JDBC" (including Jooq), I'd say those people were better database than Java experts.
And this is an interface issue: how to interpret attempts to retrieve a Boolean from a field that isn't boolean on the database side.

> In particular, this case is not a convention but behaviour
> by specification. JDBC drivers are to adhere to this
> specification.

I doubt that any driver fully implements any JDBC spec.
(ojdbc doesn't anway.)

Still people have to work with them, so the spec isn't the final word on this kind of issue.

> Hence the abstraction provided by JDBC over any underlying
> database is trustworthy enough for jOOQ to rely on - unless
> there is some specification ambiguity that I have now
> overlooked...?

It's JDBC's take on what to do if trying to retrieve a Boolean where the database delivers something non-Boolean.
That doesn't mean that it should be used by default. I wouldn't want to, for various reasons (I like to have representation nailed down, and I'd expect more drivers to have forgotten to implement this special conversion rule, particularly if the database does have a boolean type, so if cross-database portability is an issue I'd want to short-circuit this potential source of bugs from the outset).

That said, of course I could just nail down everything using a converter, so it's not a showstopper either way :-)

Regards,
Jo

[Lukas Eder]

[Forwarding this mail to the group for reference...]

---------- Forwarded message ----------
From: Aaron Digulla <adig...@gmail.com>
Date: 2013/3/4
Subject: Re: customType/forcedType by database vendor?
To: Lukas Eder <lukas...@gmail.com>


2013/2/5 Lukas Eder <lukas...@gmail.com>:

> @Aaron: How do you guys deal with the SQL BOOLEAN type and jOOQ across your
> many supported database dialects?

We use NUMERIC/NUMBER/DECIMAL everywhere (date/time types, boolean and
numbers) and our own converters.

This works pretty well. Things like searching a timestamp column for a
certain a day of week is just a division/modulo operation. It also
keeps over-eager database optimizers in check and the data is dead
easy to migrate from one database platform to the other.

Regards,

[Stanislas Nanchen]

Hi Lukas,

I have the very same problem. We need 'null' values for booleans for override flags.

Do you have a workaround before 3.3?

cheers. stan.