Oracle UDT issue with DATE vs java.sql.Timestamp

272 views
Skip to first unread message

hanky...@gmail.com

unread,
Nov 30, 2012, 7:53:50 PM11/30/12
to jooq...@googlegroups.com

Hi -

 

The Oracle database that I work with passes Arrays of UDTs that have date/time values represented as Oracle type DATE. Oracle's DATE resolves down to the second. When I use jOOQ to generate the corresponding Java class for the UDT, java.sql.Date is assigned. This class (as documented) truncates off the hours, minutes, seconds from the date/time. As such - this wont work as I need to resolve date/times down to the second.

 

To resolve this issue I applied the jOOQ generator database control of:

 

<dateAsTimestamp>true</dateAsTimestamp> 

 

That changes the generator to assign java.sql.Timestamp for my date/times.  The code runs until the jOOQ UDTs are converted to Oracle UDTs. The problem is that the Oracle type in the database expects data to be as a DATE, but the jOOQ code sets up the corresponding UDT descriptor as a TIMESTAMP rather than DATE and builds the data structure using TIMESTAMPs (probably because I told it to do so). This causes the database to kick out a SQLException to me.

 

I feel like I'm stuck in a catch-22, java.sql.Date works except for the missing hour, minute, second parts. Using the for dateAsTimestamp causes the Oracle UDTs to fail. Are there any workarounds I can put in place to help with this? Would it be possible to use a custom converter assigned to a UDT? Or is there a way to define the jOOQ  assigned data type to be the oracle.sql.DATE type? I searched and saw some discussion on this but didn’t see any solutions.

 

Thanks,

Peter

Lukas Eder

unread,
Dec 2, 2012, 4:16:39 AM12/2/12
to jooq...@googlegroups.com
Hi Peter,

Thanks for reporting this. I wasn't aware of such a "flaw" in Oracle's
handling of temporal data types within UDTs. I'm afraid that my
integration tests currently only cover the SQL DATE type, not the
TIMESTAMP type.

I'll try to reproduce your case and see what kind of solution best
matches this problem...

Could you provide me with some sample DDL to re-create the
TABLE/VARRAY of OBJECT that causes issues? Just to rule out that
there's some issue related to the array containing the UDT

Cheers
Lukas

2012/12/1 morris...@gmail.com <hanky...@gmail.com>:

Lukas Eder

unread,
Jan 2, 2013, 5:21:32 AM1/2/13
to jooq...@googlegroups.com, hanky...@gmail.com
Hi Peter,

I'm sorry for this having taken so long.

I have analysed your reported issue regarding JDBC date/timestamp data types in Oracle UDTs.
From your description, I couldn't reproduce any problem. As a matter of fact, there had already been some integration tests covering these grounds. What I could fix/implement was the fact that the <dateAsTimestamp/> flag did not affect generated array types. This has been fixed with #2073 on github trunk and will be merged downstream to 2.x versions:

Do you still encounter this problem? If yes, could you provide me with some reproducible test case and the SQLException stack trace?

Cheers
Lukas

Lukas Eder

unread,
Feb 12, 2013, 6:09:03 AM2/12/13
to jooq...@googlegroups.com, Pete Morris
Hi Pete,

(CC'ing this back to the user group, again)

I've had some time to analyse your stack trace and suggested fix. I
agree that your fix may help in your specific case. The actual problem
here is this:

There is no sensible way that a JDBC Connection can be accessed from
within a java.sql.SQLData (UDT) implementation, as the SQLData
callback methods readSQL and writeSQL receive SQLInput and SQLOutput
objects, both being oblivious of JDBC connections. More background
information about this issue can be seen here:
http://stackoverflow.com/q/11439543/521799

The ThreadLocal variable was an attempt to work around this. This has
been improved in jOOQ 3.0, where all of jOOQ's execute contexts
register a thread local Configuration that is accessible globally from
jOOQ's internals. The fix involved quite a bit of internal
refactorings, which I do not plan to merge downstream to jOOQ 2.x. Can
you reproduce this issue with jOOQ 3.0 SNAPSHOT versions?
https://oss.sonatype.org/content/repositories/snapshots/org/jooq

Cheers
Lukas

2013/1/28 Pete Morris <morris...@gmail.com>:
> Hi Lukas -
>
> I recently had a bit of time to try this fix out, sorry this took me a bit
> to get back to you too! I'm using jOOQ v2.6.1. I think this fix is in this
> version? I am receiving the error below (full stack at the very bottom)...
>
> Caused by: org.jooq.exception.DataAccessException: Cannot get a JDBC driver
> connection from configuration: null
> at org.jooq.impl.Util.getDriverConnection(Util.java:769)
> at
> org.jooq.impl.FieldTypeHelper.writeToSQLOutput(FieldTypeHelper.java:355)
> at
> org.jooq.impl.FieldTypeHelper.writeToSQLOutput(FieldTypeHelper.java:240)
> at org.jooq.impl.UDTRecordImpl.setValue(UDTRecordImpl.java:114)
>
> This seems to be related to the lack of this block:
>
> try {
> // [#1544] Set the local configuration, in case an array
> needs
> // to be serialised to SQLOutput
> LOCAL_CONFIGURATION.set(this);
> ...
> }
> finally {
> LOCAL_CONFIGURATION.remove();
> }
>
>
> Around DefaultBindContext.bindValue0(Object value, Class<?> type) throws
> SQLException():298
>
> else if (ArrayRecord.class.isAssignableFrom(type)) {
> Connection connection = getDriverConnection(this);
> ArrayRecord<?> arrayRecord = (ArrayRecord<?>) value;
> stmt.setArray(nextIndex(), on(connection).call("createARRAY",
> arrayRecord.getName(), arrayRecord.get()).<Array>get());
> }
>
> Applying that block results in:
>
> else if (ArrayRecord.class.isAssignableFrom(type))
> {
> try
> {
> // [#1544] Set the local configuration, in case an array
> needs
> // to be serialised to SQLOutput
> LOCAL_CONFIGURATION.set(this);
> Connection connection = getDriverConnection(this);
> ArrayRecord<?> arrayRecord = (ArrayRecord<?>) value;
> stmt.setArray(nextIndex(),
> on(connection).call("createARRAY", arrayRecord.getName(),
> arrayRecord.get()).
> <Array>get());
> }
> finally
> {
> LOCAL_CONFIGURATION.remove();
> }
>
> }
>
>
>
> The addition of that block (again, not sure if that is correct or not, but
> it has a null connection on the thread local configuration) seems to fix the
> null connection error and the successful processing of the Oracle array.
>
> P
>
>
> =========================================
> Full stack
> =========================================
>
> -------------------------------------------------------------------------------
> Test set: usace.cwms.db.jooq.packages.cwms_ts.ZSTORE_TS_MULTI_Test
> -------------------------------------------------------------------------------
> Tests run: 1, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 0.86 sec <<<
> FAILURE!
> test(usace.cwms.db.jooq.packages.cwms_ts.ZSTORE_TS_MULTI_Test) Time
> elapsed: 0.81 sec <<< ERROR!
> org.jooq.tools.reflect.ReflectException:
> java.lang.reflect.InvocationTargetException
> at org.jooq.tools.reflect.Reflect.on(Reflect.java:567)
> at org.jooq.tools.reflect.Reflect.call(Reflect.java:349)
> at
> org.jooq.impl.DefaultBindContext.bindValue0(DefaultBindContext.java:301)
> at
> org.jooq.impl.AbstractBindContext.bindValue(AbstractBindContext.java:139)
> at
> org.jooq.impl.AbstractBindContext.bindValues(AbstractBindContext.java:129)
> at org.jooq.impl.ArrayConstant.bind(ArrayConstant.java:81)
> at
> org.jooq.impl.AbstractBindContext.bindInternal(AbstractBindContext.java:154)
> at org.jooq.impl.AbstractBindContext.bind(AbstractBindContext.java:111)
> at org.jooq.impl.AbstractRoutine.bind(AbstractRoutine.java:353)
> 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.AbstractRoutine.executeCallableStatement(AbstractRoutine.java:308)
> at org.jooq.impl.AbstractRoutine.execute(AbstractRoutine.java:243)
> at org.jooq.impl.AbstractRoutine.execute(AbstractRoutine.java:236)
> at
> usace.cwms.db.jooq.packages.cwms_ts.ZSTORE_TS_MULTI_Test.doTest(ZSTORE_TS_MULTI_Test.java:115)
> at
> usace.cwms.db.jooq.packages.cwms_ts.ZSTORE_TS_MULTI_Test.test(ZSTORE_TS_MULTI_Test.java:50)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:601)
> at
> org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45)
> at
> org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
> at
> org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42)
> at
> org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
> at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:263)
> at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:68)
> at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:47)
> at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231)
> at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60)
> at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229)
> at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50)
> at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222)
> at org.junit.runners.ParentRunner.run(ParentRunner.java:300)
> at
> org.apache.maven.surefire.junit4.JUnit4TestSet.execute(JUnit4TestSet.java:53)
> at
> org.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:123)
> at
> org.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:104)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:601)
> at
> org.apache.maven.surefire.util.ReflectionUtils.invokeMethodWithArray(ReflectionUtils.java:164)
> at
> org.apache.maven.surefire.booter.ProviderFactory$ProviderProxy.invoke(ProviderFactory.java:110)
> at
> org.apache.maven.surefire.booter.SurefireStarter.invokeProvider(SurefireStarter.java:175)
> at
> org.apache.maven.surefire.booter.SurefireStarter.runSuitesInProcessWhenForked(SurefireStarter.java:107)
> at
> org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:68)
> Caused by: java.lang.reflect.InvocationTargetException
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:601)
> at org.jooq.tools.reflect.Reflect.on(Reflect.java:563)
> ... 45 more
> Caused by: org.jooq.exception.DataAccessException: Cannot get a JDBC driver
> connection from configuration: null
> at org.jooq.impl.Util.getDriverConnection(Util.java:769)
> at
> org.jooq.impl.FieldTypeHelper.writeToSQLOutput(FieldTypeHelper.java:355)
> at
> org.jooq.impl.FieldTypeHelper.writeToSQLOutput(FieldTypeHelper.java:240)
> at org.jooq.impl.UDTRecordImpl.setValue(UDTRecordImpl.java:114)
> at org.jooq.impl.UDTRecordImpl.writeSQL(UDTRecordImpl.java:109)
> at oracle.sql.STRUCT.toSTRUCT(STRUCT.java:498)
> at oracle.jdbc.oracore.OracleTypeADT.toDatum(OracleTypeADT.java:328)
> at
> oracle.jdbc.oracore.OracleTypeADT.toDatumArray(OracleTypeADT.java:372)
> at
> oracle.jdbc.oracore.OracleTypeUPT.toDatumArray(OracleTypeUPT.java:110)
> at oracle.sql.ArrayDescriptor.toOracleArray(ArrayDescriptor.java:1238)
> at oracle.sql.ARRAY.<init>(ARRAY.java:103)
> at
> oracle.jdbc.driver.PhysicalConnection.createARRAY(PhysicalConnection.java:8649)
> ... 50 more
>
>
>
> On Wed, Jan 2, 2013 at 11:43 AM, Pete Morris <morris...@gmail.com>
> wrote:
>>
>> Hi Lukas -
>>
>> I got pulled onto other work and had been letting this one sit on the back
>> burner. I had intended to create a test case for you but was not allocated
>> the time to do so. I'll try out your fix for this and report back to you.
>>
>> Thank you,
>> Peter
Reply all
Reply to author
Forward
0 new messages