and yep : with constant 1 and 0 instead on '1' and '0', oracle transforms 'FLAG = 0' into 'TO_NUMBER(FLAG)=0' and therefore do not use the index on FLAG.
On Tuesday, December 15, 2015 at 11:29:22 AM UTC+1, Stanislas Nanchen wrote: > > Hi Lukas! > > Thanks for the quick answer! > I will look at the custom binding solution and will keep you updated. The > default binding class does a lot of work and so I am not so inclined to > replace it :) > > Yet, as the booleans are so widely differently encoded, it would be maybe > a good idea to allow a 'BooleanEncoding' > in the settings that allows defining the encoding; (one could even provide > some typical encodings like, 0/1, 'y'/'n', 't'/'f', '1'/'0', etc...) > > cheers. stan. > > > On Tuesday, December 15, 2015 at 10:33:20 AM UTC+1, Lukas Eder wrote: >> >> Hi Stan, >> >> Interesting point about the implicit conversion problems. I suspect the >> effect is similar as when binding a java.sql.Timestamp to an Oracle DATE >> column, when instead of truncating the bind variable to fit the DATE type, >> the DATE column is widened via an INTERNAL_FUNCTION, preventing index >> usage. Is that what you're experiencing? >> >> I've blogged about the above case here: >> >> http://blog.jooq.org/2014/12/22/are-you-binding-your-oracle-dates-correctly-i-bet-you-arent/ >> >> The easiest way to circumvent that is by using a custom data type >> binding, just as for the above problem: >> >> http://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings >> >> With an org.jooq.Binding, you can override the DefaultBinding behaviour >> and bind a String instead, and cast that to CHAR(1) in the generated SQL: >> CAST(? AS CHAR(1)). >> >> It would be interesting to see if we should pursue this on the jOOQ side >> and provide something out of the box... What do you think? >> >> Cheers, >> Lukas >> >> 2015-12-15 9:07 GMT+01:00 Stanislas Nanchen <[email protected]>: >> >>> Hi everyone, >>> >>> We have a database on Oracle that uses char(1) to encode boolean values. >>> Unfortunately, Jooq inlines boolean values for Oracle with the numbres 1 >>> and 0 (hardcoded in the class DefaultBinding, private method toSQL). >>> To avoid implicit conversion problems, we would like to have boolean >>> encoded as '1' and '0'. >>> >>> Is there a way to tell Jooq to encode the booleans differently? >>> >>> Thanks! >>> Stan. >>> >>> -- >>> 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/d/optout. >>> >> >> -- 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/d/optout.
