hi Lukas, Thanks for the updates! I'm not in a hurry (we work with HSQLDB), so i'll wait for 3.2.2.
cheers. stan. On Saturday, December 7, 2013 10:42:47 AM UTC+1, Lukas Eder wrote: > > In the mean time, I can confirm a fix for #2152, which will be implemented > in jOOQ 3.3.0 and 3.2.2. The solution is to bind all java.sql.Types.BOOLEAN > bind values through > > stmt.setObject(index, null); > > This will delegate the type inferring to ojdbc, which is pretty good at > that. > I'm expecting 3.2.2 to be released by mid-December > > Cheers > Lukas > > > 2013/12/5 Lukas Eder <[email protected] <javascript:>> > >> Btw: if there is a solution for #2152, it will certainly be merged to >> 3.2.2 >> >> Cheers >> Lukas >> >> >> 2013/12/5 Lukas Eder <[email protected] <javascript:>> >> >>> Hi Stan, >>> >>> Sorry for the delay. It has been a busy week with the Java2Days >>> conference... >>> Thanks for the additional info. I now recognise the issue as it was back >>> then :-) I think the best way to work around this problem is not to force >>> the type to a SQL BOOLEAN, since ojdbc seems incabable to map a Java >>> Boolean null to a SQL NUMBER NULL. >>> >>> The only workaround that I can see right now is to write your own >>> Converter<Byte, Boolean>, and force that converter onto all relevant >>> columns. Can you try this? >>> >>> public class OracleBooleanConverter implements Converter<Byte, Boolean>{ >>> >>> @Override >>> public Boolean from(Byte t) { >>> return t == null >>> ? null >>> : t == (byte) 0 >>> ? false >>> : true; >>> } >>> >>> @Override >>> public Byte to(Boolean u) { >>> return u == null >>> ? null >>> : u >>> ? Byte.valueOf((byte) 1) >>> : Byte.valueOf((byte) 0); >>> } >>> >>> @Override >>> public Class<Byte> fromType() { >>> return Byte.class; >>> } >>> >>> @Override >>> public Class<Boolean> toType() { >>> return Boolean.class; >>> } >>> } >>> >>> >>> Cheers >>> Lukas >>> >>> 2013/12/2 Stanislas Nanchen <[email protected] <javascript:>> >>> >>>> Hi Lukas, >>>> >>>> Sorry :) >>>> >>>> I have a table with a column "...._FLAG" of oracle type number(1), >>>> hsqldb type boolean. >>>> I also have the following rule : >>>> forcedType { >>>> name 'Boolean' >>>> expressions '.*_FLAG' >>>> } >>>> The records are correctly generated with Boolean types. >>>> On HsqlDB, everything works fine; on Oracle, i get an >>>> org.jooq.exception.DataAccessException : SQL [null]; Invalid column type: >>>> 16 >>>> when i insert a record with a 'null' in one of those column, i.e. the >>>> translation to number(1) does not happen. >>>> >>>> https://github.com/jOOQ/jOOQ/issues/2152 is scheduled for 3.3.0. Do >>>> you have a workaround inbetween? like maybe a custom converter? >>>> >>>> cheers. stan. >>>> >>>> On Monday, December 2, 2013 9:05:13 AM UTC+1, Lukas Eder wrote: >>>> >>>>> Hi Stan, >>>>> >>>>> Can you elaborate what "the same problem" is for you? That thread >>>>> isn't so new. Chances are that there are better workarounds in the mean >>>>> time... >>>>> >>>>> Cheers, >>>>> Lukas >>>>> ------------------------------ >>>>> Von: Stanislas Nanchen >>>>> Gesendet: 02.12.2013 08:57 >>>>> An: [email protected] >>>>> >>>>> Betreff: Re: customType/forcedType by database vendor? >>>>> >>>>> 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. >>>>> >>>>> On Friday, February 1, 2013 4:34:58 PM UTC+1, Lukas Eder wrote: >>>>>> >>>>>> 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 <[email protected]>: >>>>>> > 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 >>>>>> > >>>>>> > >>>>>> > On 30 January 2013 13:29, Lukas Eder <[email protected]> wrote: >>>>>> >> >>>>>> >> 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 <[email protected]> >>>>>> >>> >>>>>> >>> 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 >>>>>> >>> >>>>>> >>> >>>>>> >>> On 28 January 2013 13:13, Ben Hood <[email protected]> wrote: >>>>>> >>>> >>>>>> >>>> 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 >>>>>> >>>> >>>>>> >>>> On Sunday, 27 January 2013 at 17:27, Witold Szczerba wrote: >>>>>> >>>> >>>>>> >>>> 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 >>>>>> >>>> >>>>>> >>>> -- >>>>>> >>>> >>>>>> >>>> >>>>>> >>>> >>>>>> >>>> >>>>>> >>>> -- >>>>>> >>>> >>>>>> >>>> >>>>>> >>> >>>>>> >>> >>>>>> >>> -- >>>>>> >>> >>>>>> >>> >>>>>> >> >>>>>> >> >>>>>> >> -- >>>>>> >> 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 [email protected]. >>>>>> >> For more options, visit https://groups.google.com/groups/opt_out. >>>>>> >> >>>>>> >> >>>>>> > >>>>>> > >>>>>> > -- >>>>>> > 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 [email protected]. >>>>>> > For more options, visit https://groups.google.com/groups/opt_out. >>>>>> > >>>>>> > >>>>>> >>>>> -- >>>>> 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 [email protected]. >>>>> For more options, visit https://groups.google.com/groups/opt_out. >>>>> >>>> -- >>>> 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 [email protected] <javascript:>. >>>> For more options, visit https://groups.google.com/groups/opt_out. >>>> >>> >>> >> > -- 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 [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
