Re: [eclipselink-users] criteria query translation issue (me or defect?)

["Cozart, Michele L" <Michele.L.Cozart@xxxxxxxxx>]

I still get the same error.  Any other suggestions?

Michele Cozart

Senior Programmer Analyst

State of Maine, Office of Information Technology

michele.l.cozart@xxxxxxxxx

(207) 287-7872

-----Original Message-----
From: eclipselink-users-bounces@xxxxxxxxxxx [mailto:eclipselink-users-bounces@xxxxxxxxxxx] On Behalf Of Andrei Ilitchev
Sent: Wednesday, July 25, 2012 10:44 AM
To: eclipselink-users@xxxxxxxxxxx
Subject: Re: [eclipselink-users] criteria query translation issue (me or defect?)

Shouldn't it be:

             Predicate dmrStatusListPredicate = fromCurrentDmrStatusView.get(CurrentDmrStatusView_.newDslpCode).in(dmrStatusListParamExp);

instead of:

             Predicate dmrStatusListPredicate = builder.and(fromCurrentDmrStatusView.get(CurrentDmrStatusView_.newDslpCode).in(dmrStatusListParamExp));

On 7/25/2012 9:52 AM, Cozart, Michele L wrote:
> Would someone please look at my critieria query and the generated SQL 
> and let me know if this is a defect or if my query is wrong. There are 
> two parameters in the query. The first is a String and the second is a 
> List of String.
>
> I've put the generated SQL in Toad and I get the same error (missing
> parenthesis) until I remove one of the right parenthesis at the end of 
> the query. Is this a defect in eclipselink, JPA 2.0 criteria query or 
> do I have something wrong with my query. Any hints on cleaning up the 
> criteria query code would be appreciated as well.
>
> Query code:
>
> List<CurrentDmrStatusView> currentDmrsList = new 
> ArrayList<CurrentDmrStatusView>();
>
> EntityManager em = null;
>
> EntityTransaction txn = null;
>
> try
>
> {
>
> em = super.getEntityManager();
>
> txn = em.getTransaction();
>
> txn.begin();
>
> CriteriaBuilder builder = em.getCriteriaBuilder();
>
> CriteriaQuery<CurrentDmrStatusView> criteriaQuery = 
> builder.createQuery(CurrentDmrStatusView.class);
>
> // create and add the root
>
> Root<CurrentDmrStatusView> fromCurrentDmrStatusView = 
> criteriaQuery.from(CurrentDmrStatusView.class);
>
> // Create a typed expression
>
> ParameterExpression<String> mepdesNumberParamExp = 
> builder.parameter(String.class, "mepdesNumber");
>
> ParameterExpression<List> dmrStatusListParamExp = 
> builder.parameter(List.class,"dmrStatusList");
>
> // Restrictions
>
> Predicate mepdesNumberPredicate =
> builder.equal(fromCurrentDmrStatusView.get(CurrentDmrStatusView_.mepde
> sNumber),
> mepdesNumberParamExp);
>
> Predicate dmrStatusListPredicate =
> builder.and(fromCurrentDmrStatusView.get(CurrentDmrStatusView_.newDslp
> Code).in(dmrStatusListParamExp));
>
> criteriaQuery.select(fromCurrentDmrStatusView)
>
> .where(builder.and(mepdesNumberPredicate, dmrStatusListPredicate));
>
> TypedQuery<CurrentDmrStatusView> query = 
> em.createQuery(criteriaQuery);
>
> query.setParameter("mepdesNumber", mepdesNumber);
>
> query.setParameter("dmrStatusList", dmrStatusList);
>
> currentDmrsList = query.getResultList();
>
> }
>
> catch (Exception e)
>
> {
>
> System.out.println("inside exception: " + e.getMessage());
>
> logger.debug("Exception caught in: " + this.getClass().getName() + "
> Error: " + e.getMessage());
>
> }
>
> finally
>
> {
>
> if (em.isOpen() || em != null)
>
> {
>
> em.close();
>
> }
>
> }
>
> return currentDmrsList;
>
> Resulting query and error message:
>
> 2012-07-25 09:42:45.564 DEBUG CurrentDmrStatusViewDAOImpl: Exception 
> caught in: dep.edmr.dao.impl.CurrentDmrStatusViewDAOImpl Error:
>
> Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00907: 
> missing right parenthesis
>
> Error Code: 907
>
> Call: SELECT DMRS_ID, CURRENT_STATUS, DMRSH_ID, MEPDES_NUMBER, 
> NEW_DSLP_CODE, OLD_DSLP_CODE, OUTFALL_NUMBER, REPORT_MONTH, 
> REPORT_YEAR, SENT_TO_EFIS_DATE, SENT_TO_EFIS_IND, UPDATED_BY_USERID, 
> UPDATED_DATE FROM CURRENT_DMR_STATUS_VIEW WHERE ((MEPDES_NUMBER = ?) 
> AND (NEW_DSLP_CODE IN ((?,?,?))))
>
> bind => [ME0000159, INIT, IPREC, SREC]
>
> Query: ReadAllQuery(referenceClass=CurrentDmrStatusView sql="SELECT 
> DMRS_ID, CURRENT_STATUS, DMRSH_ID, MEPDES_NUMBER, NEW_DSLP_CODE, 
> OLD_DSLP_CODE, OUTFALL_NUMBER, REPORT_MONTH, REPORT_YEAR, 
> SENT_TO_EFIS_DATE, SENT_TO_EFIS_IND, UPDATED_BY_USERID, UPDATED_DATE 
> FROM CURRENT_DMR_STATUS_VIEW WHERE ((MEPDES_NUMBER = ?) AND 
> (NEW_DSLP_CODE IN (?)))")
>
>
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users