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

[Andrei Ilitchev <andrei.ilitchev@xxxxxxxxxx>]

Please log a bug.

The culprit in the generated SQL seems to be the double brackets:
  .. IN ((?,?,?))

When substituted for:
  .. IN (?,?,?)
the query succeeds.

On 7/25/2012 11:21 AM, Cozart, Michele L wrote:
> 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
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users