Eclipse Community Forums: EclipseLink » Unable to retrieve TIMESTAMPTZ from Oracle

Help

Home

Home » Eclipse Projects » EclipseLink » Unable to retrieve TIMESTAMPTZ from Oracle

Show: Today's Messages :: Show Polls :: Message Navigator

Unable to retrieve TIMESTAMPTZ from Oracle [message #1831381]

Tue, 18 August 2020 18:01

Aaron Carnes

Messages: 7
Registered: August 2020
Location: Seattle, WA

Junior Member

I am using Eclipselink 2.7.6p1 that comes with Payara, Oracle 12c, Payara 5.2020.4 and I get a conversion exception when running a query.

The exception thrown is:

Exception Description: The object [oracle.sql.TIMESTAMPTZ@cef6f7b], of class [class oracle.sql.TIMESTAMPTZ], from mapping [org.eclipse.persistence.mappings.DirectToFieldMapping[runStartTime-->ASR_RUN.RUN_START_TIME]] with descriptor [RelationalDescriptor(<packagename_removed>.ASRRun --> [DatabaseTable(ASR_RUN)])], could not be converted to [class java.util.Date].
	at org.eclipse.persistence.exceptions.ConversionException.couldNotBeConverted(ConversionException.java:81)
	at org.eclipse.persistence.internal.helper.ConversionManager.convertObjectToUtilDate(ConversionManager.java:1025)
	at org.eclipse.persistence.internal.helper.ConversionManager.convertObject(ConversionManager.java:109)
	at org.eclipse.persistence.internal.databaseaccess.DatasourcePlatform.convertObject(DatasourcePlatform.java:226)
	at org.eclipse.persistence.mappings.converters.TypeConversionConverter.convertDataValueToObjectValue(TypeConversionConverter.java:125)
	at org.eclipse.persistence.mappings.foundation.AbstractDirectMapping.getObjectValue(AbstractDirectMapping.java:605)
	at org.eclipse.persistence.queries.ReportQueryResult.processItemFromMapping(ReportQueryResult.java:186)
	at org.eclipse.persistence.queries.ReportQueryResult.processItem(ReportQueryResult.java:226)

The Entity definition is:
```
@Column(name = "RUN_START_TIME")
@Temporal(TemporalType.TIMESTAMP)
private Date runStartTime;
@Column(name = "RUN_STOP_TIME")
@Temporal(TemporalType.TIMESTAMP)
private Date runStopTime;
```
The persistence.xml file has the following properties:

    
<properties>
      <property name="eclipselink.logging.level" value="FINE"/>
      <property name="eclipselink.target-database" value="org.eclipse.persistence.platform.database.Oracle12Platform"/>
      <property name="eclipselink.target-server" value="Glassfish"/>
    </properties>

And I have added the following dependencies to my POM.xml:

 
                <dependency>
			<groupId>org.eclipse.persistence</groupId>
			<artifactId>org.eclipse.persistence.oracle</artifactId>
			<version>2.7.6</version>
		</dependency>
               <dependency>
                         <groupId>com.oracle.ojdbc</groupId>
                        <artifactId>ojdbc10</artifactId>
                        <version>19.3.0.0</version>
                </dependency>
		<dependency>
			<groupId>javax.persistence</groupId>
			<artifactId>javax.persistence-api</artifactId>
			<version>2.2</version>
		</dependency>
		<dependency>
			<groupId>org.eclipse.persistence</groupId>
			<artifactId>eclipselink</artifactId>
			<version>2.7.6</version>
		</dependency>

I also added ojdbc10.jar to the payara domain's lib directory as suggested by a colleague.

I have tried changing the java type to various other types such as java.time.OffsetDateTime. It is almost as if it can't find the convert functions for the db data type.

Does anybody have any suggestions on what I could be doing wrong? I have seen a couple of other threads with regards to this but most don't have solutions/answers and the others seem to suggest what I have defined should be sufficient. I have this same code base working against MS SQL as well.

I'm still not sure what could be misconfigured.

[Updated on: Wed, 19 August 2020 15:06]

Report message to a moderator

Re: Unable to retrieve TIMESTAMPTZ from Oracle [message #1831423 is a reply to message #1831381]

Wed, 19 August 2020 13:29

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

MySql isn't using a column of type oracle.sql.TIMESTAMPTZ, so the driver isn't returning that class to convert into a java.util.Date defined within your entity. EclipseLink can handle the conversion, but you need to use the proper DatabasePlatform target class appropriate for your database. In this case, try using Oracle12Platform but it depends on the driver being used. Anything over Oracle9Platform should contain the logic for Oracle specific TIMESTAMPTZ handling.

See https://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/p_target_database.htm for specifying the DB platform.

Report message to a moderator

Re: Unable to retrieve TIMESTAMPTZ from Oracle [message #1831427 is a reply to message #1831423]

Wed, 19 August 2020 14:54

Aaron Carnes

Messages: 7
Registered: August 2020
Location: Seattle, WA

Junior Member

Thanks for the reply. I added some additional information to the original post. Do you see anything I am missing?

[Updated on: Wed, 19 August 2020 15:24]

Report message to a moderator

Re: Unable to retrieve TIMESTAMPTZ from Oracle [message #1831489 is a reply to message #1831381]

Thu, 20 August 2020 16:53

Aaron Carnes

Messages: 7
Registered: August 2020
Location: Seattle, WA

Junior Member

I have removed the use of the Date fields from the code and executed the queries and everything works fine. This is purely an issue with the conversion of TIMESTAMPTZ to java.util.Date and as I mentioned before, it seems as though something is not configured properly or perhaps the incorrect library is being used. Does this sound like a familiar problem to anybody? Is there some way to verify that the libraries I am using will allow the conversion?

Any help would be greatly appreciated. I am spinning my wheels and not sure how to proceed. I am also considering alternatives to move forward. I was thinking about making a wrapper that uses jdbc for oracle if necessary. Does anybody have ideas on alternatives to using JPA?

Report message to a moderator

Re: Unable to retrieve TIMESTAMPTZ from Oracle [message #1831494 is a reply to message #1831489]

Thu, 20 August 2020 19:04

Aaron Carnes

Messages: 7
Registered: August 2020
Location: Seattle, WA

Junior Member

I read somewhere else that eclipselink needs to know how to "unwrap the connection" in order to convert TIMESTAMPTZ to java.util.Date when using a datasource in the persistence.xml file.

Here is my persistence.xml file:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
  <persistence-unit name="PAPSWebServicePU" transaction-type="JTA">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <jta-data-source>ORA_PROA_DB</jta-data-source>
    <exclude-unlisted-classes>false</exclude-unlisted-classes>
    <validation-mode>NONE</validation-mode>
    <properties>
      <property name="eclipselink.logging.level" value="FINE"/>
      <property name="eclipselink.logging.level.sql" value="FINE"/>
      <property name="eclipselink.target-database" value="org.eclipse.persistence.platform.database.Oracle12Platform"/>
      <property name="eclipselink.target-server" value="Payara"/>
    </properties>
  </persistence-unit>
</persistence>

Do I need to supply additional information in the persistence.xml file that I have not provided? For instance do I need to populate the DB connection info?

Report message to a moderator

Re: Unable to retrieve TIMESTAMPTZ from Oracle [message #1831599 is a reply to message #1831494]

Mon, 24 August 2020 15:30

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

The Oracle12Platform isn't being used or it would appear on the exception stack trace within the conversion - its Oracle9Platform parent overrides the DatasourcePlatform.convertObject method to add TIMESTAMPTZ support for this operation (it converts it to an EclipseLink TIMESTAMPLTZWrapper class before this part).

Logging while the persistence unit is being deployed and first accessed may indicate how it is getting the connection and the DatabasePlatform class to use. Connection unwrapping is required, but should result in an exception if the Oracle connection cannot be obtained from under the datasource provided connection, as it should be passed to TIMESTAMPLTZ.toTimestamp(connection, tsLTZ.toBytes()); for conversion operations earlier in the stack.

[Updated on: Mon, 24 August 2020 15:31]

Report message to a moderator

Re: Unable to retrieve TIMESTAMPTZ from Oracle [message #1831637 is a reply to message #1831599]

Tue, 25 August 2020 18:46

Aaron Carnes

Messages: 7
Registered: August 2020
Location: Seattle, WA

Junior Member

I added the logging and I noticed I am getting this in the output:

  EclipseLink, version: Eclipse Persistence Services - 2.7.6.payara-p1|#]
  connecting(DatabaseLogin(
	platform=>Oracle10Platform
	user name=> ""
	connector=>JNDIConnector datasource name=>null
))|#]
...
  Missing class details for [oracle/sql/TIMESTAMPTZ].|#]
  Using existing class bytes for [oracle/sql/TIMESTAMPTZ].|#]
  Missing class details for [oracle/jdbc/internal/OracleTimestampWithTimeZone].|#]
...

I also tried with Oracle12Platform and got the same result.

Is it possible I am using the incorrect library. Is the support for this in the JDBC drivers or in some other jar?

The entries in my POM xml are:

<dependency>
			<groupId>org.eclipse.persistence</groupId>
			<artifactId>org.eclipse.persistence.oracle</artifactId>
			<version>2.7.6</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc8 -->
		<dependency>
			<groupId>com.oracle.ojdbc</groupId>
			<artifactId>ojdbc10</artifactId>
			<version>19.3.0.0</version>
		</dependency>

[Updated on: Tue, 25 August 2020 18:47]

Report message to a moderator

Re: Unable to retrieve TIMESTAMPTZ from Oracle [message #1831689 is a reply to message #1831637]

Wed, 26 August 2020 15:29

Aaron Carnes

Messages: 7
Registered: August 2020
Location: Seattle, WA

Junior Member

This is the info when the connection is established. I don't know how to tell what it is using Oracle##Platform it is using from this.

 EclipseLink, version: Eclipse Persistence Services - 2.7.6.payara-p1|#]
  connecting(DatabaseLogin(
	platform=>Oracle10Platform
	user name=> ""
	connector=>JNDIConnector datasource name=>null
))|#]
  Connected: jdbc:oracle:thin:@10.30.0.44:1521:orcl12
	User: STATEDB
	Database: Oracle  Version: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
	Driver: Oracle JDBC driver  Version: 19.3.0.0.0|#]
  Connection acquired from connection pool [read].|#]
  Connection released to connection pool [read].|#]
  connecting(DatabaseLogin(
	platform=>Oracle10Platform
	user name=> ""
	connector=>JNDIConnector datasource name=>null
))|#]
  Connected: jdbc:oracle:thin:@10.30.0.44:1521:orcl12
	User: STATEDB
	Database: Oracle  Version: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
	Driver: Oracle JDBC driver  Version: 19.3.0.0.0|#]
  sequencing connected, state is Preallocation_NoTransaction_State|#]
  sequence SEQ_GEN_IDENTITY: preallocation size 1|#]

Is anybody seeing something I am not. Is there a way to tell which is being used and why it can't do the conversion?

Report message to a moderator

Re: Unable to retrieve TIMESTAMPTZ from Oracle [message #1831699 is a reply to message #1831689]

Wed, 26 August 2020 21:01

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

The "Missing class details for [oracle/sql/TIMESTAMPTZ]" message is logging that occurs during weaving of the persistence unit classes, and just indicates there is a class on the path it was given that it cannot find details for - which makes sense as it isn't an entity class. The class loader makes a call in to the EclipseLink weaving agent for every class it loads.

You mentioned in your first post you have the <property name="eclipselink.target-database" value="org.eclipse.persistence.platform.database.Oracle12Platform"/> value specified in your persistence.xml - this shows it is using "platform=>Oracle10Platform", is this expected? I would have assumed you set the Oracle12Platform and just gone with that.

Is there any mention of a server platform? This is used to unwrap the connection is the server is wrapping them. Your early posts stated "eclipselink.target-server" value="Glassfish" - check that it states it is using the GlassfishPlatform in the logs, as this has its own unwrapConnection and unwrapGlassFishConnectionHelper methods that should be getting fired to unwrap connections if needed to pull the TIMESTAMPTZ info. I honestly am unsure how this can happen without some other warnings or exceptions occurring in the logs indicating what is going wrong, other than some driver bug where it returns the wrong type (returning a TIMESTAMPTZ instance but saying the type is java.sql.Types.TIMESTAMP (93 ?) instead of TIMESTAMPTZ = -101 )

Report message to a moderator

Re: Unable to retrieve TIMESTAMPTZ from Oracle [message #1831724 is a reply to message #1831699]

Thu, 27 August 2020 13:57

Aaron Carnes

Messages: 7
Registered: August 2020
Location: Seattle, WA

Junior Member

To begin I just wanted to thank you Chris for your replies. I have posted in a number of locations and you have been the most helpful by far.

In my original post, I had specified Glassfish but I have since moved to Payara being specified in the persistence.xml. As payara was derived from glassfish, many things require glassfish to be specified. After adding Payara, I didn't get any errors so I stuck with that. As for the change from Oracle12Platform to Oracle 10Platform. I am using jdbc10 jdbc libraries so I thought that would bring them into alignment.

In all honesty, part of my problem is not understanding which JDBC drivers to use with which version of java with which version of oracle with which version of eclipselink.
This is a run down of my setup:
Payara 5.2020.4
Oracle 12c - 12.1.0.1
ojdbc10-19.3.0.0.jar
eclipselink 2.7.6.payara-p1 (version that ships with payara)

If you know of a configuration which works with specific versions of all of these I would be happy to try it. Thank you for your hlep.

Report message to a moderator

Previous Topic:	Issue with Primary Key with Postgresql and Audit Trigger 91+
Next Topic:	NoSql bundle not selectable

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 08:49:50 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter