Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Expression - dateDifference method usage
Expression - dateDifference method usage [message #387775] Sat, 09 May 2009 00:43 Go to next message
Kenneth Unpingco is currently offline Kenneth UnpingcoFriend
Messages: 4
Registered: July 2009
Junior Member
Hello,

I am trying to use the class:
org.eclipse.persistence.expressions.Expression by invoking the
dateDifference method:

public Expression dateDifference(java.lang.String datePart,
Expression comparisonExpression).

However, when I tried to use it I got the following error
exception:SybSQLException: 'MOD_DATE' is not a recognized datediff option.
All that I am trying to do is to get the total seconds elapsed between two
datetime columns (in my case, its the modDate column) from a table called
DocStore. I've made sure from the JavaDoc API that I've put in the
correct parameters.

I've used the following statement:
responseRequestDateTimeDifferenceExpression =

addrProcessExpression.get("requestDoc").get("modDate").dateDifference( "second",
addrProcessExpression.get("responseDoc").get("modDate"));

I expected the SQL transformation to get something like:
DATEDIFF(second, responseDoc.MOD_DATE, requestDoc.MOD_DATE)
but that did not happen. I ran across the datediff error exception. Shown
below are further details of the code and error exception.

What am I doing wrong? Any advice or ideas are truly appreciated.

Thank you,
Ken Unpingco
**************************
Here is the java code:
addrProcessExpression = new ExpressionBuilder();
reportQuery = new ReportQuery(AddressProcess.class, addrProcessExpression);
requestResponseDateRangeExpression =

addrProcessExpression.get("requestDoc").get("modDate").between(startDate,
endDate)

and(addrProcessExpression.get("responseDoc").get("modDate ").between(startDate,
endDate))
.and(addrProcessExpression.get("responseDoc").notEqual(null))
.and(addrProcessExpression.get("requestDoc").notEqual(null));

responseRequestDateTimeDifferenceExpression =

addrProcessExpression.get("requestDoc").get("modDate").dateDifference( "second",
addrProcessExpression.get("responseDoc").get("modDate"));

reportQuery.setShouldReturnSingleValue(true);

reportQuery.addSum("Total-seconds",
responseRequestDateTimeDifferenceExpression);

reportQuery.setSelectionCriteria(requestResponseDateRangeExp ression);
Query query = JpaHelper.createQuery(reportQuery,
(EntityManager)em.getDelegate());

******** Details for the log on the exception
********************************
Exception [EclipseLink-4002] (Eclipse Persistence Services -
2.0.0.v20090424-r4050):
org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.sybase.jdbc3.jdbc.SybSQLException: 'MOD_DATE' is
not a recognized datediff option.

Error Code: 155
Call: SELECT SUM(DATEDIFF(t0.MOD_DATE, t3.MOD_DATE, t0.MOD_DATE)) FROM
DOCSTORE t3, ADDRESS_PROCESS t2, PROCESS t1, DOCSTORE t0 WHERE
((((((t0.MOD_DATE BETWEEN ? AND ?) AND (t3.MOD_DATE BETWEEN ? AND ?)) AND
NOT ((t2.RESPONSE_DOC_ID IS NULL))) AND NOT ((t2.REQUEST_DOC_ID IS NULL)))
AND ((t2.ID = t1.ID) AND (t1.discriminator = ?))) AND ((t0.ID =
t2.REQUEST_DOC_ID) AND (t3.ID = t2.RESPONSE_DOC_ID)))
bind => [2009-01-01 00:00:00.0, 2009-05-09 00:00:00.0, 2009-01-01
00:00:00.0, 2009-05-09 00:00:00.0, Address]
Query: ReportQuery(referenceClass=AddressProcess sql="SELECT
SUM(DATEDIFF(t0.MOD_DATE, t3.MOD_DATE, t0.MOD_DATE)) FROM DOCSTORE t3,
ADDRESS_PROCESS t2, PROCESS t1, DOCSTORE t0 WHERE ((((((t0.MOD_DATE
BETWEEN ? AND ?) AND (t3.MOD_DATE BETWEEN ? AND ?)) AND NOT
((t2.RESPONSE_DOC_ID IS NULL))) AND NOT ((t2.REQUEST_DOC_ID IS NULL))) AND
((t2.ID = t1.ID) AND (t1.discriminator = ?))) AND ((t0.ID =
t2.REQUEST_DOC_ID) AND (t3.ID = t2.RESPONSE_DOC_ID)))")
root cause

com.sybase.jdbc3.jdbc.SybSQLException: 'MOD_DATE' is not a recognized
datediff option.
Re: Expression - dateDifference method usage [message #387781 is a reply to message #387775] Mon, 11 May 2009 13:46 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

I can't see how this could be occurring. The Expression code looks
correct. Are you sure you are executing the query you think you are?

---
James
http://www.nabble.com/EclipseLink---Users-f26658.html


James : Wiki : Book : Blog : Twitter
Re: Expression - dateDifference method usage [message #387782 is a reply to message #387781] Mon, 11 May 2009 19:02 Go to previous message
Kenneth Unpingco is currently offline Kenneth UnpingcoFriend
Messages: 4
Registered: July 2009
Junior Member
Hi James,

I am very sure that I am executing the correct query. I am using Sybase
ASE 15.0 database (that has a DATEDIFF function). I too, agree with you
that I cannot see why this is not working when I seem to have the
Expression code that looks correct.

Thank you,
Ken
Previous Topic:Interface annotation?
Next Topic:drop-and-create-tables for Oracle
Goto Forum:
  


Current Time: Fri Dec 19 10:13:52 GMT 2014

Powered by FUDForum. Page generated in 0.02414 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software